Written by: Vishal Chougule, Bhavuk Srivastava, Ratheesh Vazhayil

Introduction
IBM Sametime 9 introduces high quality video allowing administrators to enable a hassle free Real Time Communication service. With improved bandwidth management and the latest Scalable Video Coding (SVC) capability for H264 Video, Sametime 9 enables enterprises to provide large scale deployment within the organization and externally, thus making video a highly used and consumed service. As the video usage increases, it brings a requirement for administrators to provide auditing and call records for the usage of video service by users.

Retrieving CDR (Call Data Record) Data

Automate Report Generation

Use the script fetchCDRReports.sh to automate report generation.

Schedule the fetchCDRReports.sh script as a cron job to run on a daily basis. The fetchCDRReports.sh script fetches the report from the Video Manager and stores it in the directory specified as input.

Usage example

./fetchCDRReports.sh /home/cddrdata

This example generates a file as output CDR_MM-DD-YY.zip. Schedule the script as a cron job that runs near midnight, for example at approximately 23.58 – 23.59.

If you have a Video Manager cluster, schedule the script to run on each of the Video Manager nodes in the cluster.

Fetching CDR information using REST API

To retrieve the following CDR information, use the REST API.

Resources

https://:8443/api/rest/billingUse CDR to audit all of the details of a conference. Unzip the download file, billing.zip, to obtain all of the conference details provided in these two files:

• call.csv
• conference.csv

Use Microsoft Excel or another spreadsheet application to open these CSV files. The CSV files contain a line for each call or conference during the selected time frame.

The ZIP file also includes a text file that contains a single line specifying:

• The number of calls in the call CDR file.
• The number conferences in the conference CDR file.
• Call and conference counts in txt file

To retrieve CDR information from the Video Manager cluster, use the REST API on each individual Video Manager node and collect the zip files.

https://:8443/api/rest/billing
https://:8443/api/rest/billing

Sample CDR Analysis

Call Data Records(CDR) contain all of the detail information about an audio video conference.
Example:
Alice starts an audio video call on Sametime 9. This call is then joined by Bob and Peter.

The conference.csv file provides the details about the conference while call.csv file provides the details of all the participants who joined this conference. The mapping between the two csv files is done using Conf-uuid.
A detailed description of each field in both of these csv files is shown in the following table.

The following table contains fields from the conference.csv file for the conference.

Table 1 - conference.csv file fields

confUUID	a9a521ec-655d-4d29-94af-8ec3d521985b
startTime	2013-11-19T05:38:11.609Z
endTime	2013-11-19T05:41:50.495Z
partCount	3
mcuNameList	VMCU_1
cluster	vmgr01.abc.com

The call.csv file contains the participant details for the same conference. The refConfUUID field in call.csv can be mapped with confUUID value to get details of all the participants in this conference. In this example, we see three entries for this confUUID because there are three participants in the call.

The following tables (Tables 2, 3 and 4) contain fields from the call.csv file for all participants who joined the conference.

Table 2 - call.csv file fields for Participant 1

dialString	sip:2383_808824@vmgr01.abc.com:5060;transport=TCP;endpoint=client
origEndpoint	sip:alice%40abc.com@sippr01.abc.com:5080;placeid=911852112973;endpoint=mcu
userRole	CHAIRPERSON

Table 3 - call.csv file fields for Participant 2

dialString	sip:2383_808824@vmgr01.abc.com:5060;transport=TCP;endpoint=client
origEndpoint	sip:bob%40abc.com@sippr01.abc.com:5080;placeid=911852112973;endpoint=mcu
userRole	PARTICIPANT

Table 4 - call.csv file fields for Participant 3

dialString	sip:2383_808824@vmgr01.abc.com:5060;transport=TCP;endpoint=client
origEndpoint	sip:peter%40abc.com@sippr01.abc.com:5080;placeid=911852112973;endpoint=mcu
userRole	PARTICIPANT

Call Records Details
Call.csv Details

The following table (Table 5 ) describes the fields in the call records in the call.csv file. Times and dates in the call.csv file are expressed in the time zone of the Video Manager node that created the CDR export, with the GMT offset shown at the end. Note that if a conference spans a daylight savings time change, the offset for the endTime value differs from the offset for the startTime value.

Table 5 - Call records in call.csv file

Field	Description
version	Changes each time the format of CDRs change.
type	CALL
callType	VMGR
callUuid	Unique identifier for the call.
dialin	If this is point-to-point or a Video Manager dial-in call, this value is TRUE. Otherwise, this value is FALSE.
startTime	YYYY-MM-DDTHH:MM:SS.FFF[+|-|Z][HH:MM] (ISO 8601 syntax, where FFF is milliseconds and Z is zero offset) When call signaling reached the DMA system, not when media started. If multiple call records, the start of this segment of the call.
endTime	YYYY-MM-DDTHH:MM:SS.FFF[+|-|Z][HH:MM] (ISO 8601 syntax, where FFF is milliseconds and Z is zero offset) When the DMA system’s involvement with the call ended, not when media ended. If there are multiple call records, the end of this segment of the call.
origEndpoint	The originating endpoint’s display name, name, alias, or IP address (in that order of preference), depending on what it provided in the call signaling. If the originator is a Video MCU, the Video MCU name.
dialString	Initial dial string as supplied by the originator. If there are multiple call records, this value is the same across all segments of the call.
destEndpoint	The destination endpoint’s display name, name, alias, or IP address (in that order of preference), depending on what it provided in the call signaling. If the destination is a Video MCU, the Video MCU name.
origSignalType	One of the following: h323 sip
destSignalType	One of the following: h323 sip
refConfUUID	If this is a Video Manager call, confUUID of the associated conference.
lastForwardEndpoint	If call forwarding, the endpoint that forwarded call to the final destination endpoint.
cause	Cause value for call termination or termination of this CDR. This may not be the end of the call.
causeSource	Source of the termination of the call record: originator destination callserver
bitRate	Bit rate for call, in kbps. If the bit rate changes during the call, this is a list of bit rate values separated by plus signs (+). For example: 1024+768+384
classOfService	Class of service for the call: Gold Silver Bronze
ingressCluster	Video Manager node name
egressCluster	Video Manager node name
VMRCluster	Video Manager node name
VEQCluster	Not currently used.
userRole	If this is a Video Manager call, the role of the caller in conference: PARTICIPANT CHAIRPERSON (entered passcode) Null if not a Video Manager call.
userDataA	Not currently used.
userDataB	Not currently used.
userDataC	Not currently used.
userDataD	Not currently used.
userDataE	Not currently used.
failureSignalingCode	For SIP calls, the SIP code and reason, separated by a colon, that the call was disconnected. For example: 486:BUSY HERE
origModel	The hardware model of the originating device, if available from the device’s registration or other signaling.
origVersion	The software version of the originating device, if available from the device’s registration or other signaling.
destModel	The hardware model of the destination device, if available from the device’s registration or other signaling.
destVersion	The software version of the destination device, if available from the device’s registration or other signaling.
displays	Currently value is always 1
minVideoResolution	Currently value is always 0
maxVideoResolution	Currently value is always 0
videoPeakJitter	Currently value is always blank.
videoTotalPackets	Currently value is always blank.
videoTotalLostPackets	Currently value is always blank.
minContentResolution	Currently value is always 0
maxContentResolution	Currently value is always 0
contentPeakJitter	Currently value is always blank.
contentTotalPackets	Currently value is always blank.
contentTotalLostPackets	Currently value is always blank.
origSignalingId	For SIP point-to-point or Video Manager calls (dialin=TRUE), the complete From header of the INVITE received from the endpoint. For Video Manager SIP dial-outs (dialin=FALSE), the To header sent by the DMA system to the Video MCU. Otherwise, blank.
origCallId	The SIP or H.323 call ID of the originating endpoint. For Video Manager dial-outs, the call ID of the call between the DMA system and the Video MCU.
destCallId	The SIP or H.323 call ID of the destination endpoint. For calls to a Video Manager, the call ID of the call between the DMA system and the Video MCU.

Conference CSV

The following table (Table 6) describes the fields in the conference records in the conference.csv file. Times and dates in the conference.csv file are expressed in the time zone of the DMA cluster that created the CDR export, with the GMT offset shown at the end. Note that if a conference spans a daylight savings time change, the offset for endTime will be different from the offset for startTime.

Table 6 - Conference records in the conference.csv file

Field	Description
version	Changes each time the format of CDRs change.
type	CONF
confType	ADHOC
cluster	The VMGR node name from which conference data is extracted.
confUUID	Unique identifier for the conference.
startTime	YYYY-MM-DDTHH:MM:SS.FFF[+|-|Z][HH:MM] (ISO 8601 syntax, where FFF is milliseconds and Z is zero offset) When the first participant joined the conference.
endTime	YYYY-MM-DDTHH:MM:SS.FFF[+|-|Z][HH:MM] (ISO 8601 syntax, where FFF is milliseconds and Z is zero offset) When the last participant left the conference.
userID	Conference room (Video Manager) owner, shown as: domain\user Domain is LOCAL .
roomID	Conference room (Video Manager) number.
partCount	Maximum number of concurrent calls in the conference (high water mark).
classOfService	Class of service for the call: Gold Silver Bronze
userDataA	Not currently used
userDataB	Not currently used
userDataC	Not currently used
maxResourcesUsed	The maximum number of video and voice ports used for the conference, reported as follows: video: Note: Voice calls may use video ports if voice ports aren’t available.
mcuNameList	The Video MCUs used by the conference. If there is more than one (due to cascading or a Video MCU failover), this is a comma-separated list enclosed in quotes. If the conference was cascaded, the hub Video MCU is listed first. If there was a failover, the original Video MCU is listed first.
confDisplayNameList	The conference display name of the conference as it appears on the Video MCU. If there is more than one Video MCU (due to cascading or a Video MCU failover), this is a comma-separated list enclosed in quotes. If the conference was cascaded, the display name from the hub Video MCU is listed first. If there was a failover, the display name from the original Video MCU is listed first. This information is included to support the correlation of Video MGR CDRs with CDRs on the Video MCUs. Video MCUs use the conference display name as part of the name of the CDR file for a conference.

Strategies to maintain CDR

There are two approaches for managing Call Data Records:

1. Use the attached fetchCDRReports.sh script to automate generating the CDR report to ensure that daily reports are fetched from the Video Manager and stored to the local file system.

2. CDR can be fetched directly using REST API described in the Fetch CDR information using REST API section of this article. Store the output files from this API for accessing historical billing data in future. This REST API can be used for integrating to any third party application, such as billing.