This document is applicable to the appliance model of Cisco Unified Communications Manager (i.e. CUCM version 5.x and later).

Recommended reading

It is highly recommended to read the “Understanding CDR (Call Detail Records)” document at the following location to get a good background of CDR operations:

https://supportforums.cisco.com/docs/DOC-13842

How to check the current CDR records and CDR configuration

Starting with CUCM 7.1.3 release, users get a pop-up message when they login to CAR (from Cisco Unified Serviceability, go to Tools > CDR Analysis and Reporting). The pop-up message provides an overview of the CDR configuration as well high level overview of number of records in CDR database.

Sample screenshot:

The above information is very useful because you can see the time period that the CDR records cover. In the above example, the oldest CDRs are from Jan 4th and the latest CDRs are from Jan 5th. If you are trying to run reports for any days that are not included in this time period (eg: Jan 3rd or before — or — Jan 5th and later), it’ll be unsuccessful because the corresponding data is not available in the database.

Prior to CUCM 7.1.3, the above pop-up message is not available. In that case, an alternate way to check the current records is:

From Cisco Unified Communications Manager CDR Analysis and Reporting page, navigate to System > Database > Manual Purge as seen in the screenshot below:

On the next page, click on Table Information:

A new pop-up window displays:

Using the Event Log

The Event Log is a handy tool to check the CDR Load status.

From Cisco Unified Communications Manager CDR Analysis and Reporting page, navigate to System > Log Screens > Event Log as seen in the screenshot below:

Then, select the ‘CDR Load’ job and specify the search interval as required as seen below:

Troubleshoot: Unable to search CDR data / Unable to view CDR records

Problem:

I can’t search CDR records or generate reports for a particular date.

Approach:

(1) Check if the CDR Enabled Flag is enabled in CallManager service parameters.

(2) Is CDR Load enabled ? (From CAR Tool: System -> Schedule -> CDR Load). Click here for screenshots.

(3) Using the Event Logs, check if the loader is running properly. Click here for screenshots.

(4) Check the current records in CDR as explained here. Does the date that you are searching for fall within the range between oldest and the latest records ? Most likely, the particular date will not be included in this range.

(5) Check the ‘preserve’ and ‘processed’ folders on the Publisher for CDR flat files. Click here for further information.

If you don’t observe files in either the ‘preserve’ or the ‘processed’ location for the specific date, then it means that problem does not reside on the Publisher. Either the CDR records were not generated by the server that handled the call or it could mean that CDR Agent service on that server could not transfer flat files to the Publisher. Was the ‘CDR Enabled Flag is enabled in CallManager service’ enabled on that particular date ? Is the CDR Agent service running on the node that handled the call ?

For the particular date, if the CDR files exist in the ‘preserve’ location, but not in the ‘processed’ location, then it means that the files have not been processed yet.

(5) For what date is the CDR Loader processing data ?  Check the current records in CDR as explained here. Wait few minutes and check the current records in CDR again.

Did the Total records change between the iterations?

—> If yes, then it means that the CDR Loader is running and processing data.The date of the latest record will provide an indication of the day for which the records are being processed.  If the date that you are searching for falls beyond the latest date, then you may need to wait for a while to provide time for the CDR Loader to process all the data leading upto that date.

—> If not, it means that CDR Loader may not be running (try restarting the CAR Scheduler service on the Publisher). If you don’t observe any additional records being processed, then set the trace level for the CAR Scheduler service to ‘Debug’, restart CAR Scheduler service, wait 10 mins, download CAR Scheduler logs via RTMT and engage Cisco-TAC for further troubleshooting.

Troubleshoot: CDR Files not being transfered to billing server

Problem:

CDR flat files are not being transfered to the billing server.

Approach:

Troubleshoot: Alerts

CDRAgentSendFileFailed

This alert gets raised when the CDR Agent cannot send CDR files from a Cisco Unified Communications Manager node to a CDR repository node within the Cisco Unified Communications Manager cluster. This typically occurs when a Subscriber server is unable to send the CDR flat files to the Publisher.

Approach:

– Have there been any changes in the network connectivity between the Publisher and the Subscriber due to which SFTP traffic (port 22) may be getting blocked ?

– Try restarting CDR Agent service running on the Subsriber server for which the alert was raised.

– If the alert continues, change the debug trace level for the CDR Agent service on the Subscriber to ‘Debug’. Wait 10 minutes. Download the CDR Agent service logs using RTMT. Check for errors in the file. You may need to contact Cisco TAC for further assistance.

CDRFileDeliveryFailed

This alert gets raised when FTP/SFTP delivery of CDR files to the outside billing server fails.

Approach:

This alarm signifies that there are CDR flat files in the /cm/cdr_repository/preserve/YYYYMMDD location on the Publisher, however the CDR Repository Manager service is unable to transfer these files to the billing server.  Troubleshoot approach is same as here.https://supportforums.cisco.com/docs/DOC-14548#Troubleshoot_CDR_Files_not_being_transfered_to_billing_server

CDRHighWaterMarkExceeded

This alert gets raised when the high water mark for CDR files gets exceeded. It also indicates that some successfully processed/delivered CDR files got deleted.

This alert will be thrown if the High Water Mark (HWM) configured on the CDR Management page is exceeded. To access the CDR Management page, from Cisco Unified Serviceability, navigate to Tools > CDR Management.

Whenever HWM is exceeded, CDR Repository Manager deletes the oldest processed folders one by one, till the usage goes below the Low Water Mark (LWM). There is no action required for this alert, unless the CDRMaximumDiskSpaceExceeded alarm is also observed.

CDRMaximumDiskSpaceExceeded

This alarm gets raised when the CDR files disk usage exceeds the maximum disk allocation. It also indicates that some unprocessed/undelivered files got deleted. As explained in the ‘CDRHighWaterMarkExceeded’ section, whenever HWM is exceeded, CDR Repository Manager deletes the oldest processed folders one by one, till the usage goes below the Low Water Mark (LWM).  If all the processed folders are deleted, but still the disk usage is higher than HWM and maximum disk alloacted on the CDR Management page, the CDRMaximumDiskSpaceExceeded alarm is thrown and CDR Repository Manager starts deleting the files from oldest preserve folders till the disk usage goes below HWM. This situation is not ideal because the system may delete CDR files before they were transferred to the sftp server or processed by local CDR Loader job.

Note – in typical working scenarios, all the CDR flat files will in the ‘processed’ location as opposed to the ‘preserve’ location. This signifies that all operations for those files have been completed.

So, the main aspect to troubleshoot is – why do the preserve folders contain data to begin with ? Is it because the CDR Files are not being sent to the configured billing servers and/or is it because the files are not being processed by the local CDR Load job on the Publisher ?

Troubleshoot: Numerous Entries in tbl_billing_error

Problem:

There are numerous entries in tbl_billing_error. What’s causing it ?

Approach:

The following commands will work on CUCM version 5.x, 6.x and 7.x. For CUCM 8.x and later, please click here to get the corresponding commands.

Login to the Publisher’s command line interface (CLI).

Check the error codes in tbl_error_id_map. Sort the list such that error codes that occur most often are listed in descending order.

admin:run sql select count(*) as cnt, error_code from car:tbl_error_id_map group by error_code order by cnt desc

cnt error_code

=== ==========

24  31110

14  31148

Check what devices are causing the errors:

admin:run sql select count(*) as cnt, origdevicename from car:tbl_billing_error where datetimeconnect = ‘0’ group by origdevicename order by cnt desc

cnt origdevicename

=== ===============

12  SEP000BFDCDB25A

9   SEP001BD52D292D

3   SEP001D45B64090

In the above example, the error codes are:

31110: which means that dateTimeConnect in a CDR record is not a positive integer

and

31148: which means that destDeviceName in a CDR record is NULL

The above error codes imply that the call was not connected (i.e. it did not enter a connected state) but a CDR record was nevertheless created for it. Since the call was not connected, it’s not a ‘billable’ call. Hence, the corresponding record is entered into tbl_billing_error (as opposed to tbl_billing_data). This behavior is frequently observed on systems where the CallManager service parameter: ‘CDR Log Calls with Zero Duration Flag’ is enabled. If this service parameter is entered, a CDR record will be generated for calls that were never connected or were connected for less than a second. Most common scenarios that cause such CDR records:

Error code meaning:

31101: CDR’s globalCallID_callManagerId <= 0

31102: CDR’s globalCallID_callId <= 0

31103: CDR’s origLegCallIdentifier <= 0

31105: CDR’s dateTimeOrigination <= 0

31108: CDR’s destLegIdentifier <= 0

31110: CDR’s dateTimeConnect <= 0

31111: CDR’s dateTimeDisconnect <= 0

31119: CDR’s originalCalledPartyNumber is empty

31120: CDR’s finalCalledPartyNumber is empty

31122: CDR’s duration < 0

31137: CDR’s Ldap error while retrieving UserID or ManagerID

31139: CDR’s callingPartyNumber is empty

31147: CDR’s origDeviceName is empty

31148: CDR’s destDeviceName is empty

31151: CDR’s origCallTerminationOnBehalfOf < 0

31152: CDR’s destCallTerminationOnBehalfOf < 0

31153: CDR’s lastRedirectRedirectOnBehalfOf < 0

31155: CDR’s destConversationId < 0

31156: CDR’s globalCallId_ClusterID is empty

** Corresponding CLI commands for CUCM 8.0

In CUCM 8.0 and later, the tbl_billing_error and tbl_error_id_map tables have been consolidated. The syntax of the CLI commands has also changed slightly from previous version. Use the following CLI commands from the Publisher:

run sql car select count(*) as cnt, error_codes from car:tbl_billing_error group by error_codes order by cnt desc

run sql car select count(*) as cnt, origdevicename from car:tbl_billing_error where datetimeconnect = ‘0’ group by origdevicename order by cnt desc

Important:

CallManager always generates CDR data for calls that have a duration of 0 seconds and terminate because of an error condition of some sort, regardless of the setting of the CDR Log Calls with Zero Duration Flag service parameter. Calls to busy destinations or bad phone numbers are examples of this type of call. The duration of these calls is 0 because they were never connected, but their CDRs are generated anyway.

In short –  if a call terminates abnormally, the corresponding CDR record will be created irrespective of the setting of the CDR Log Calls with Zero Duration Flag service parameter.

Reporting: Important limitations

All reports (User Reports, System Reports, Device Reports) are generated based on data in tbl_billing_data only.

For CAR (CDR Analysis and Reporting) reports:

– If CSV format is selected, it the report search is limited to 20,000 records

– If PDF format is selected, it the report search is limited to 5,000 records

The above limitation is documented in the Cisco Unified CDR Analysis and Reporting Administration Guide. Unless the CUCM version contains the fix for CSCsm17901, the system does NOT display any notification to the user that the report results may not be accurate if the above limits are violated.

Consider the following example to better understand this:

– Assume 1000 records are populated in tbl_billing_data each day.

– So, for the month of January, there’ll be 31000 records in tbl_billing_data (1000 records per day * 31 days = 31000 records).

Lets say a PDF report is generated for period from Jan 1 to Jan 10th. In this case, the search should ideally span 10000 records (1000 records per day * 10 days = 10000 records). However, a PDF report is limited to search only 5000 records. In this case, the report results will not be accurate since the total number of records in the search period are greater than 5000! However, if you an CSV report is generated, then the report results will be accurate since the corresponding search can span upto 20000 records.

General Tips

• When you add calling and called party transformations to a route pattern or translation pattern, CallManager logs the transformed numbers in the CDRs and renders the transformed numbers on the display of the calling phone. CallManager does not insert transformations specified on the route list’s route group details (or applied by a individual egress gateway) in the CDRs or render them on the calling device.

• Using the CDR Search option (under System -> CDR) will search the records in both tbl_billing_data and tbl_billing_error. Only the first 100 matches will be displayed.

Useful Links/References

• Please refer to the ‘Cisco Unified CDR Analysis and Reporting Administration Guide’ for the appropriate release for additional information. To access the document:

— Navigate to: http://www.cisco.com/en/US/products/sw/voicesw/ps556/prod_maintenance_guides_list.html

— Search for ‘Cisco Unified CDR Analysis and Reporting Administration Guide’ for the appropriate CUCM release

• Understanding CDRs (a document on Cisco’s supportforums)