Custom Contact Detail report

Last Updated : Mar 04, 2021 |
Prolog information
Metadata
Keywords:
You can create a custom Contact Detail report using the data stored in the cdr table of the PostgreSQL VoicePortal database. Experience Portal applications respond to incoming calls and can place outgoing calls. The details of each such call are recorded in this table. Call records are numbered sequentially by the database across each MPP and the Experience Portal system. Each call is further identified by a distinct universal identifier (UCID) received from the switch for communicating with other call handling systems. SessionID can be used to link a CDR record with its corresponding SDR and vpapplog records. This table has one row for each call handled by the Experience Portal cluster. 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.
Primary Key Column
Name
InsertID
Columns
Column
Data Type
Description
ApplicationName
VARCHAR (255)
Application name as configured in EPM.
AreaCode
INTEGER
The area code as extracted from the OriginatingNumber field based on rules defined in areacode.properties.
AudioCodec
VARCHAR (32)
Audio codec used in the call.
CallID
VARCHAR (255)
Unique call identifier assigned by the MPP. Value is unique within the Experience Portal cluster.
CallStartDate
INTEGER
Date the call started in YYYYMMDD format for the GMT timezone.
CallStartTime
INTEGER
Time in the day that the call started in HHMMSS format for the GMT timezone.
CallTimestamp
TIMESTAMP
Time in the day that the call started stored in a single Timestamp column. Based on GMT timezone.
CallType
INTEGER
Type of call:
  • 0: Inbound call.
  • 1: Outbound call.
ConnectLatency
INTEGER
The number of milliseconds until the call was connected.
DestinationNumber
VARCHAR (255)
DNIS - Pilot number of the application.
EMLEntryId
BIGINT, NUMBER(20,0)
Stores the primary key of the CDR record from the operational database for the email processor component. Contains 0 when CDR is from another source.
Duration
INTEGER
Call length in seconds.
EndDetails
VARCHAR (255)
Additional information to End Type on how the contact ended.
For example, you can specify the Busy filter to view all outbound contacts that received a busy signal.
The options are:
  • Application exited
  • Busy
  • Hangup
  • Insufficient Media Server Resources
  • Insufficient Telephony Resources
  • Internal Error
  • Invalid Called URI
  • Network Busy
  • No Answer
  • SessionTerminated
  • SIT (Special Information tone) for disconnected number
  • tel: transferdestination#
  • Unknown Disconnect Reason
EndType
INTEGER
How the call completed:
  • 1: The call completed successfully; Near End Disconnect
  • 2: Transfer
  • 3: Far End Disconnect
  • 4: Interrupted
  • 5: Not Routed
  • 6: No Resource
  • 7: Session Manager Error
  • 8: Redirected
  • 9: Rejected
  • 10: Merged
  • 11: TimeOut
FirstPromptLatency
INTEGER
The amount of time after the call connected and before the first prompt was played, in milliseconds.
InsertID
BIGSERIAL, NUMBER(20,0) BIGINT_ IDENTITY, BIGINT UNSIGNED
Unique sequence number which is automatically incremented by the database for each row that is inserted into the table. This field exists to permit easy access by extract, transform, and load (ETL) processes that are used by other Avaya products such as, Avaya IQ.
MediaType
INTEGER
Voice=0 (default), Video=1, SMSDelivery=2, SMS=3 EmailDSN=4, Email=5, HTML=6
MPP
VARCHAR (255)
The name of the MPP name that handled this session. This name is configured in the EPM.
OriginatingNumber
VARCHAR (255)
ANI - The caller's number.
PortID
INTEGER
The switch station for H.323.
ProviderName
VARCHAR(65)
The name of the Email or SMS provider that handled the message. The name is configured on the Connections tab for Email and Sms Processors in the EPM.
For HTML contacts: It contains the browser-reported operating system and vendor information. For example, “Windows Desktop;Firefox;44.0” or “IPhone;Safari;8.0”.
The user's web browser determines the content and format.
ProviderMessage
VARCHAR(160)
For SMS, this is the SMS message.
For Email, this is either the subject or body of the email as specified under Reporting Parameters when the application is configured on the EPM.
Note:
  • Only the first n characters are shown, as controlled by the Privacy section under Reporting Parameters when the application is configured on the EPM. The default is 0 characters.
  • The latter section can only be configured by an administrator who has the Privacy Manager role.
ProviderStatus
INTEGER
The status of the message from the provider.
For SMPP, this column will store the 'command_status' from the submit_sm response. A value of 0 indicates Success. A value of -1 indicates that a status was not received within the configured timeout period.
The following are the Status values for emails with a MediaType of EmailDSN(4):
  • 0: Indicates that the message was successfully delivered to the recipient address specified by the sender, which includes "delivery" to a mailing list exploder. It does not indicate that the message has been read. This is a terminal state and no further DSN for this recipient should be expected.
  • 1: Indicates that the message could not be delivered to the recipient. The Reporting MTA has abandoned any attempts to deliver the message to this recipient. No further notifications should be expected.
  • 2: Indicates that the message has been relayed or gatewayed into an environment that does not accept responsibility for generating DSNs upon successful delivery.
  • 3: Indicates that the Reporting MTA has so far been unable to deliver or relay the message, but it will continue to attempt to do so. Additional notification messages may be issued as the message is further delayed or successfully delivered, or if delivery attempts are later abandoned.
  • 4: Some other unknown error occurred. The details might be in the DSN message
ReasonCode
VARCHAR (255)
Reason code from a transferred call.
ReceiptRequested
INTEGER
0 = contact was not sent with a delivery receipt request.
1 = contact was sent with a delivery receipt request.
RecordDate
INTEGER
The date on which the record was written on the MPP. The format is YYYYMMDD and is always in GMT. 
RecordID
INTEGER
CDR entry count by day.
RedirectedNumber
VARCHAR (255)
The number of the extension that the call is transferred from when calls are transferred to Experience Portal.
RtpRcvJitter
INTEGER
An estimate of the statistical variance of the RTP data packet inter-arrival time, measured in timestamp units and expressed as an unsigned integer.
RtpRcvLost
INTEGER
The total number of RTP data packets from source that have been lost since the beginning of reception.
This is the number of packets expected minus the number of packets actually received, where the number of packets received includes any which are late or duplicates.
RTPRcvPackets
INTEGER
The total number of RTP data packets received from the source.
RTPRoundTripTime
INTEGER
The delay, expressed in units of 1/65536 seconds, between receiving the last SR packet from source SSRC_n and sending this reception report block. If no SR packet has been received yet from SSRC_n, the DLSR field is set to zero.
RTPSendJitter
INTEGER
The Jitter reported from the far end of the RTP stream.
RTPSendLost
INTEGER
The total RTP data packets lost as reported by the far end of the RTP stream.
RTPSendPackets
INTEGER
The total number of RTP data packets transmitted by the sender.
SessionID
VARCHAR (255)
Dialog ID generated by MPP .
SMSEntryId
BIGINT, NUMBER(20,0)
Stores the primary key of the CDR record from the operational database for the SMS processor component. Contains 0 when CDR is from another source.
SwitchOrProxy
VARCHAR (255)
Name of the Switch as configured by the EPM.
TelMediaEncryption
VARCHAR(255)
The media encryption used during the call, if any.
TelProtocol
VARCHAR (255)
Telephony protocol use. This can be:
  • H.323
  • SIP
  • SIPS
UCID
VARCHAR (255)
The Universal Call ID.
VideoBitRate
VARCHAR (32)
Video bit rate used in the call.
VideoCodec
VARCHAR (32)
Video codec used in the call.
VideoFrameRate
INTEGER
Video frame rate used in the call in Frame Per Second (FPS).
VideoScreenSize
VARCHAR(32)
Video screen resolution delivered on the call.
VPID
INTEGER
The ID number of the EP system that handled the call. If there is only one EP system at your installation, the value of this field will always be 1.
ZoneID
INTEGER
Identifies the Zone of the component creating this record. This value can be used as a key into the vpZones table.