20. Appendix D. CDR

This appendix explains how to remotely access the CDR database, how to export and purge CDR files, and describes the schema, configuration, and access mechanisms for call detail records.

Before VidyoPortal version 2.2, the Vidyo conferencing system saved call details records (CDRs) on installations, conferences, and point-to-point calls in three separate tables in CDRv1:

The Client Installation Table (ClientInstallation) (Client installations).
The Conference Call Table (ConferenceCall) (Every time a user joins or leaves a conference).
The Point-to-Point Call Table (PointToPoint) (Every time a user makes a point-to-point call).
Version 2.2 and later uses CDRv2 or CDRv2.1, and maintains more information in just two tables:
ConferenceCall2 - The Conference Call Table and Point-to-Point Call Table were combined in a single table. Some fields were added, some deleted, and some changed.
ClientInstallations2 - The Client Installation Table also has new or changed fields.

In addition, the following features exist in the CDRv2 and CDRv2.1 tables:

Recording CDR data is optional. It’s turned off by default. If you’ve been recording it, you’ll need to enable it after you upgrade to VidyoPortal version 2.2 or later.
There’s an option to purge CDR based on filter criteria. (This option is not available with CDRv1 and is disabled on the CDR Access page of the Super Portal.)
There’s an option to export CDRs in CSV format based on filter criteria. (This option is not available with CDRv1 and is disabled on the CDR Access page of the Super Portal.)
Your filter can be based on Tenant Name or From or To date.
All time stamps used in CDR tables are based on the time zone configured for the VidyoPortal.
The default time zone is the Eastern Time Zone (US and Canada). You can change the time zone in the System Console. (You must be a Shell Admin user.)

As with the earlier tables, the call detail records are stored in a MySQL database on the VidyoPortal server. You need an SQL client to use the CDR database. For information on how to configure it, refer to the SQL documentation.

Note

VidyoPortal version 2.3 and later does not support CDRv1. If you are using CDRv1 and VidyoPortal version 2.1 and earlier, you are advised to make changes to your CDR collection programs to migrate to CDRv2 or CDRv2 prior to upgrading to VidyoPortal version 2.3 or later. If you do not do so, you will no longer be able to collect CDR information from the VidyoPortal. If you are a Vidyo Reseller or Vidyo End User with "Plus" coverage, please feel free to contact the Vidyo Customer Support team via email with any questions or if you need assistance. If you are a Vidyo End User without "Plus" coverage, please contact your Vidyo Reseller for further details.

CDR configuration

The Call Detail Records (CDR) database resides on the same server as your VidyoPortal. Version 2.2 and later maintains more information than earlier versions in just two tables:

ConferenceCall2 - The Conference Call Table and Point-to-Point Call Table have been combined in a new single table. Some fields have been added, some deleted, and some changed.
ClientInstallations2 - The new Client Installation Table also has new or changed fields.
The VidyoPortal has been configured to allow remote MySQL clients to read and delete the details from all the tables within the CDR database.
Because the maximum number of entries in the CDR database is limited by the size of VidyoPortal storage, we advise you to delete old entries from time to time to avoid performance issues.
The VidyoPortal server is configured to allow remote MySQL clients to read and delete data. The Vidyo conferencing Admin portal allows you to grant permissions for a password and IP address, or host name for the cdraccess user.
The CDR database listens on port 3306.
The CDR access page enables you to grant permission via the access password and host IP or hostname for the cdraccess user.

Configure the CDR database for remote access

You can configure the CDR database for remote access as the Super Admin.

Export and purge CDR files

The Super Admin may export and purge specific CDR records from the Super Admin portal while Admins may export CDR records from their specific tenant or tenants.

The export record limit is 65,000 records. If the export contains more than 65,000 records, a message displays warning you to restrict the range before proceeding with the download.

For more information, see Export and purge CDR files from the Super Admin portal and Export CDR files from the Admin portal.

CDR version 2.1 tables

The CDR version 2.1 tables follow:

ClientInstallations2

This table is used to record software installation.

Field	Description
Username	Login Name of the user installing client software
DisplayName	Display name
TenantName	The Endpoint ID of a user’s host machine
EID	Endpoint identifier of a user’s host machine
ipAddress	IP address of machine where client software is installed
HostName	Host name of machine where client software is installed
RoomName	This field is populated for guest users and indicates which room they were trying to join that started the client software installation
RoomOwner	Room owner
TimeInstalled	Time of installation

ConferenceCall2

Note

Refer to this table when working with exported CDR file data.

Fields marked with an asterisk* on this table will be released and announced during upcoming endpoint releases.

Field	Description
CallID	Auto increment
UniqueCallID	A newly created conference receives a new, unique call id so the customer can track all conference participants. For example, a conference “green” starting at 10 AM and ending at 11 AM has a different unique call ID from a conference “green” starting at 3 PM and ending at 4 PM.
ConferenceName	Name of the conference
TenantName	Name of the Tenant
ConferenceType	D – Direct Call (two party) C – Conference Call ID – Inter-portal Direct Call IC – Inter-portal Conference Call
EndpointType	R – VidyoConnect Room D – VidyoConnect Desktop G – Guest L – Call to Legacy via VidyoGateway C – Call Recorded via VidyoReplay
CallerID	Caller identifier [Login name of the caller]. For Legacy Calls, this is the extension number used.
CallerName	Display Name of the Caller or name of the legacy device
JoinTime	Join time
LeaveTime	Leave time
Call State	Current state of the call: RINGING – The status of the side initiating the call (P2P or conference). RING ACCEPTED – This status indicates to the initiating side that the callee has accepted the call. It will switch to “in progress” once the conference begins. RING REJECTED – This status indicates to the initiating side that the alert was not accepted. RING NO ANSWER – This status indicates to the initiating side that the call timed out. RING CANCELLED – This status indicates to the initiating side that the call was aborted from the initiating side. ALERTING – The status indicates to the callee side that there is an incoming call (P2P or conference). ALERT CANCELLED – This status indicates to the callee side that the initiating side cancelled the call. IN PROGRESS – This status indicates to both sides that the call is in progress. COMPLETED – This status indicates to both sides that the call was completed.
Direction	I – Inbound Call O – Outbound Call
RouterID	VidyoRouter used for this call.
GwPrefix	Service prefix used. This applies only to calls that involve a VidyoGateway. For other calls, it is set to NULL.
GwID	Gateway ID used for this call. Set to NULL otherwise.
ReferenceNumber	This is a numeric string identifier passed by the endpoint to the VidyoPortal at conference join time. This field is a placeholder for Client lib based apps implementation.
ApplicationName*	This field identifies VidyoConference usage from different endpoint types. The information is reported by endpoints when connecting to the VidyoPortal. Usage is reported from the following endpoint types: VidyoSlate Lync plug-in Jabber plug-in Bott client VidyoMonitoring app VidyoConnect VidyoConnect Room and VidyoRoom VidyoGateway VidyoReplay VDI
ApplicationVersion*	This field identifies the endpoint software version.
DeviceModel*	This field identifies the endpoint device model.
EndpointPublicIPAddress	This field identifies the IP address of an endpoint that has joined a conference.
AccessType	U – Registered User G – Guest L – Call to Legacy via VidyoGateway R – Call Recorded via VidyoReplay
RoomType	M – Private room belonging to a registered member on the VidyoPortal P – Public room S – Scheduled room
RoomOwner	The logged in username of the room owner.
ApplicationOS*	This field identifies the operating system on which a Vidyo client is running. All VidyoClients (and Client lib based apps) are required to provide this information if requested. The following operating systems are supported: Windows macOS iOS Android
CallCompletionCode	This field provides one of the following call completion codes: 0 – The call completion reason is not available. 1 – The user disconnected the call. 2 – The call was disconnected by the admin, operator, or room owner. 3 – The call was disconnected due to a network failure on the VidyoManager.
EndpointGUID	This field captures the endpoint's GUID in the conference.