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.
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:
The Event Log output is then displayed:
If a DRS backup starts while CDR Loading is in progress, then the CDR Load status shows "Unsuccessful" in the Event Log output. In this case, the "Unsuccessful" status is cosmetic. Refer to CSCsy90529 in Cisco's Bug Toolkit for additional details.
Troubleshoot: Unable to search CDR data / Unable to view CDR records
I can't search CDR records or generate reports for a particular date.
(1) Check if the CDR Enabled Flag is enabled in CallManager service parameters.
(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.
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
CDR flat files are not being transfered to the billing server.
(1) Confirm that the billing server is configured correctly (verify the IP address). From the Cisco Unified Serviceability page, navigate to Tools > CDR Management to view the billing server parameters.
(2) Check if you can ping the billing server from the Publisher. From the Publisher CLI, you can use the following command:
utils network ping a.b.c.d
where a.b.c.d is the IP address of the billing server
(3) Check if the FTP (or SFTP) service/software running on the billing server.
(4) Check if the billing server is running out of disk-space.
(5) Check if the CDR Respository Manager service is running on the Publisher. If not, start the CDR Repository Manager service.
(6) On the Publisher, check the 'preserve' and 'destinationX' (X can be 1, 2 or 3) directories for CDR flat files. For example: if transfer of CDR files to billing server is broken from Jan 6th, then use the following commands:
file list activelog /cm/cdr_repository/preserve/20110106
file list activelog /cm/cdr_repository/destination1/20110106
(if multiple billing servers are configuration, the location will be similar to above, but the corresponding directories will be named destination2, destination3). This location contains symbolic links to files under preserve. CDR Repository Manager service uses these soft-links to determine what files need to be transfered to billing server.
If you don't observe files in either the 'preserve' or the 'destinationX' locations 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 ?
If you observe files in the 'preserve' directory for the specific date, try restarting the CDR Repository Manager service on the Publisher. If the file transfer to the billing server does not commence, then set the trace level for the CDR Repository Manager service to Debug, wait 10 minutes, download CDR Repository Manager logs via RTMT and engage Cisco-TAC for further troubleshooting.
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.
- 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.
This alert gets raised when FTP/SFTP delivery of CDR files to the outside billing server fails.
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.
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
There are numerous entries in tbl_billing_error. What’s causing it ?
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
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
In the above example, the error codes are:
31110: which means that dateTimeConnect in a CDR record is not a positive integer
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:
- When a user goes off-hook and then on-hook without completing a call.
- When a user completes a blind transfer (the consultation call does not connect prior to transfer completion).
- MWI On/Off calls.
If you observe a large number of entries in tbl_billing_error due to any of above scenarios, you can disable the CallManager service parameter: 'CDR Log Calls with Zero Duration Flag'.
A complete list of error codes and corresponding meanings is listed below:
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
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.
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.
Please refer to the 'Cisco Unified CDR Analysis and Reporting Administration Guide' for the appropriate release for additional information. To access the document: