Custom Session Detail report

You can create a custom Session Detail report using the data stored in the sdr table of the PostgreSQL VoicePortal database. Experience Portal supports both simple VoiceXML applications, which have exactly one dialog per session, and general CCXML applications, which can have zero to many dialogs per session. This table describes details of each session and of each dialog. For simple VoiceXML applications there is one row describing both the session and its single dialog. For general CCXML applications, there is a separate row describing the overall session plus one additional row for each dialog that was invoked during that session. These three types of records are distinguished by the RecordType field. Records in this table are uniquely identified across MPP and Experience Portal systems by a sequential number created by the database. SessionID can be used to link a SDR record with its corresponding CDR and vpapplog records. This table has one or more rows for each session handled by the Experience Portal cluster, as described above. Rows are created after the controlling session completes and the scheduled data download has occurred. Rows are deleted according to an administrable data retention limit.

Table : 1. Primary Key Column
Name
InsertID

Column Data Type Description

ApplicationName VARCHAR(255) Application name as configured in EPM.

AppServer VARCHAR(255) Initial URL as configured in EPM that triggered the application.

ASRPercent

INTEGER

Speech recognition percentage for the page with the lowest recognition.

This value is computed using the values for:

The number of recognized utterances on the page with the worst recognition, as shown in the UttCntRecWPage column.
The number of total utterances received on the page, as shown in the UttCntWPage column.

The formula is:

(UttCntRecWPage/UttCntWPage)*100

ASRServer VARCHAR(255) IP address of the ASR server used by the session.

AverageASRpercent INTEGER The average ASR percentage for the session, based on the total number of utterances and the total number of recognized utterances.

AverageLatency INTEGER The average time, in milliseconds, after the caller issued a speech command before the subsequent prompt began playing.

AverageRecConf INTEGER The average confidence level across all recognized utterances in this session.

DialogID VARCHAR(255) The unique ID that describes a specific VXML dialog associated with a particular session.

Duration INTEGER The total length of the session, in seconds.

EMLEntryId BIGINT, NUMBER(20,0) Stores the primary key of the SDR record from the operational database for the email processor component. Contains 0 when SDR is from another source.

ExitCustomerID VARCHAR(65) Optional Information set by the application (through session.exitCustomerID variable in DD or manually through the Exit tag)

ExitPreferredPath

INTEGER

This field is determined by the DD framework:

0 - if a node that has been flagged by the application developer as unpreferred, is ran in this session.
1 – if no unpreferred nodes are ran in this session.

ExitTopic VARCHAR(65) Optional Information set by the application (through session.exitTopic variable in DD or manually through the Exit tag)

HasPerformanceTrace

INTEGER

This can be:

0: No performance data was captured for this session.
1: The performance data was captured for this session.

HasTranscription

INTEGER

This can be:

0: No transcription data was captured for this session.
1: Transcription data was captured for this session.

	Note:
	By default, the DD nodes are “Preferred”.

InsertRecordID INTEGER An uniquely sequential number that resets to 1 when the InsertDate changes.

LatAnswer INTEGER The time after which the call was connected and before the first prompt was played, in milliseconds.

LatencyHistogram1 INTEGER The number of speech application pages that took less than one second to load.

LatencyHistogram2 INTEGER The number of speech application pages that took between one and two seconds to load.

LatencyHistogram3 INTEGER The number of speech application pages that took between two and three seconds to load.

LatencyHistogram4 INTEGER The number of speech application pages that took between three and four seconds to load.

LatencyHistogram5 INTEGER The number of speech application pages that took more than four seconds to load.

LatWPage INTEGER Longest length of time that it took for any page to load, in milliseconds.

LatWPageName VARCHAR (512) URL of the page that took the longest time to load.

MaxConsecutiveRecErrors

INTEGER

The maximum number of times in a row that an utterance was not recognized during a session.

This value tracks the maximum consecutive number of recognition errors, not the total number of recognition errors for a given utterance.

For example, if the caller had to repeat the word "brokerage":

Twice on the first menu
Three times on the second menu

This field would display 3.

MPP VARCHAR(255) The name of the MPP name that handled this session. This name is configured in the EPM.

MRCPSessionIDASR

VARCHAR(255)

The Session ID on the ASR server. This is the MRCP session ID if the ASR server uses the MRCP protocol.

	Note:
	When multiple ASR servers are used, the ASR Session ID field contains only the first Session ID. To find all of the ASR Session IDs, examine the Speech events in the Session Transcription.

MRCPSessionIDTTS VARCHAR(255) The Session ID on the Text-to-Speech (TTS) server. This is the MRCP session ID if the TTS server uses the MRCP protocol.

PageReqCacheHits INTEGER Number of VXML page cache hits.

PageReqTotal INTEGER Number of VXML page requests.

ParentID VARCHAR(255) Session ID of parent session, “root”.

RecordDate INTEGER The date on which the record is written on the MPP. The format is YYYYMMDD and is always in GMT.

RecordID INTEGER SDR entry count by day.

RecordType

INTEGER

This can be:

0: maintains statistics for an entire session and all dialogs within the session
1: maintains statistics for a single dialog when multiple dialogs exist within a session
2: maintains statistics for a session containing only a single dialog.

SessionID VARCHAR(255) Session ID generated by the media server .

SessionTimestamp TIMESTAMP Date and Time of the start of the session stored in a single Timestamp column. Based on GMT time zone.

Slot INTEGER MPP slot number that handled the call. This is related to the MPP session log generation.

SMSEntryId BIGINT, NUMBER(20,0) Stores the primary key of the SDR record from the operational database for the SMS processor component. Contains 0 when SDR is from another source.

Source

VARCHAR(255)

Cause of the session startup

This can be:

Inbound: specifies session was generated from inbound call or an outbound call.
LaunchCCXML: specifies that the session was generated as an outbound call from LaunchVXML.
LaunchVoiceXML: specifies that the session was generated as an outbound call from LaunchCCXML.
SMSInbound: The session was generated by an incoming SMS.
SMSReceipt: The session was initiated by a SMS delivery receipt.
EmailInbound: The session was generated by an incoming Email.
EmailReceipt: The session was initiated by an Email DSN.
LaunchSMS: The session was initiated through the LaunchSMS method on the outcall webservice. That is, through the SMS application.
LaunchEmail: The session was initiated through the LaunchEmail method on the outcall webservice. That is, through the Email application.

LaunchHTML: The session was initiated through the LaunchHTML method on the outcall webservice. For example, called by the HTML Redirector app in response to a request from a mobile web browser.

StartDate INTEGER Date the session started in YYYYMMDD format. Based on GMT timezone.

StartPageName VARCHAR(512) URL of initial page loaded when call started.

StartTime INTEGER Time of day that the call started in HHMMSS format for GMT timezone.

TerminationInfo1 VARCHAR(64) Optional Information set by the application (through the Exit tag or the session.exitInfo1 variable in DD).

TerminationInfo2 VARCHAR(64) Optional Information set by the application (through the Exit tag or the session.exitInfo2 variable in DD)

TerminationInfo3 VARCHAR(64) Optional Information set by the application (through the Exit tag*)

TerminationInfo4 VARCHAR(64) Optional Information set by the application (through the Exit tag*)

TerminationInfo5 VARCHAR(64) Optional Information set by the application (through the Exit tag*)

TerminationInfo6 INTEGER Optional Information set by the application (through the Exit tag*)

TerminationInfo7 INTEGER Optional Information set by the application (through the Exit tag*)

TerminationInfo8 INTEGER Optional Information set by the application (through the Exit tag*)

TerminationInfo9 INTEGER Optional Information set by the application (through the Exit tag*)

TerminationInfo10 INTEGER Optional Information set by the application (through the Exit tag*)

TerminationPageName VARCHAR(512) The VoiceXML or CCXML page where the disconnect event occurred.

TerminationPageNameShort VARCHAR(255) The VoiceXML or CCXML page where the disconnect event occurred. Similar to terminationpagename column except contains only the page name and the file extension, rather than the full URL.

TerminationReason VARCHAR(64) Reason call terminated as set by the application (through the Exit tag). If an application does not set a value, the system defines the default value as Application exited.

TTSServer VARCHAR(255) IP address of the TTS server used by the session.

UttCntRecWPage INTEGER Number of recognized utterances received on the page with the worst recognition

UttCntTot INTEGER Total number of utterances in session.

UttCntTotRec INTEGER Total number of recognized utterances in session.

UttCntRecWPage INTEGER Nmber of recognized utterances made on the page with the lowest percentage of correctly recognized utterances.

UttCntWPage INTEGER Total number of utterances made on the page with the lowest percentage of correctly recognized utterances.

VPID INTEGER Identifies the EP system within a cluster of multiple EP systems that merge the data into the same external database. If there is only one EP system at an installation, this field is always 1.

ZoneID INTEGER Identifies the Zone of the component creating this record. This value can be used as a key into the vpZones table. Added in EP 7.0