Guest

Cisco Unified Communications Manager (CallManager)

Data is Absent in Reports Created by the CAR Tool

Document ID: 68140

Updated: Jun 17, 2008

   Print

Introduction

This document describes one reason that data for a certain period of time does not appear in the reports created by the Call Detail Record (CDR) Analysis and Reporting (CAR) tool and provides a solution in a Cisco CallManager environment.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

  • Cisco CallManager

  • Cisco CDR Analysis and Reporting

  • Microsoft SQL Server

Components Used

The information in this document is based on these software and hardware versions:

  • Cisco CallManager version 3.3 and later

  • Microsoft SQL Server

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Background

CAR is a tool that provides reports on calls based on CDR records. Reports provided include calls on a user basis, calls through gateways, simplified Call Quality, and CDR search mechanism. In addition, limited database administration (the deletion of records based on DB size) is also provided. All of this information is installed in the ART database.

The Cisco CallManager records information related to each call in CDRs and Call Management Records (CMRs). CDRs and CMRs serve as the basic information source for ART and are stored in the ART database (see Figure 1).

Figure 1—Relationship between CDR and ART Database

absent-data-car-reports-1.gif

Problem

When you run the CDR Analysis and Reporting tool, reports fail to display data for a certain period of time.

Solution

When the user runs CDR reporting for a date range using ART / CAR, report generation fails with the 30023: Data is not available for the date range selected error message, or the CAR tool is not able to generate reports for a certain date range.

Complete these steps to determine if there are any records in the SQL database:

  1. Select Start > Programs > SQL > Enterprise Manager > Microsoft SQL Servers > SQL Server Group.

  2. Select Publisher Server > Databases.

  3. Select CDR > Tables and right click on the CallDetailRecord table.

  4. Select Open Table > Return All Rows and determine if there are any records.

If you see records, then problem is that the CDR database and ART database are out of synchronization. Follow the procedure in Cause 1 to resolve the problem. If you do not see any records, there are no CDR entries for that time and date range. Refer to the procedure in Cause 2 to determine if you have met the conditions for CDR generation. If you use Cisco CallManager release 4.1(3) or later, the problem can also be caused when the contents of the Comments column exceeds 256 characters. See Cause 3 to verify whether this is the cause of problem and for the steps to resolve the issue. If these solutions do not resolve the issue, uninstall CDR from Add/Remove Programs, delete the ART database manually, and then reinstall the CDR from the CCM Administration > Plugins page.

Note: If there is more than one user associated with a device (MAC address), the report gives results only for the last "most recent" user associated with that device. For example, if there are two users associated with the same device, based on which user the database sees as "first," the reports for all other associated users is always empty. These are just some considerations to keep in mind when you contrast these two types of database queries within CAR/ART. If you want to run Billing or other reports instead of CDR searches on these users who currently come up "empty," you need to query SQL to find out all users associated with the device in question, and then remove all associations from that device except for the one you want.

Note: If Cisco CallManager does not create the new CDR records, and the error message ReadConfiguration Unable to read/write to Source directory is received, make sure that c:\Program Files\Cisco\CallDetail is shared on Publisher and Subscribers. If shared, check the security settings to make sure if the ccmcdr user has the modify rights to this folder.

Note: Data is available in CDR from the date it is configured.

Cause 1

If you see records while you query the CDRDetailRecord table, then the CDR database does contain all the missing CDRs. However, these missing CDRs do not exist in the ART database. The root cause for this problem is that the CDR database and ART database are out of synchronization.

Complete these steps to synchronize these two databases:

  1. Purge the ART database.

    1. Select Start > Programs > Microsoft SQL Server > Query Analyzer.

    2. Select the ART database from the drop-down list of database selection (see the red arrow in Figure 2).

      Figure 2—SQL Query Analyzer

      absent-data-car-reports-2.gif

    3. Run these SQL query commands:

      • delete from Tbl_Load_History

      • delete from Tbl_Error_Id_Map

      • delete from Tbl_Billing_Data

      • delete from Tbl_Billing_Error

      • delete from Tbl_Dump_PkID

      • delete from Tbl_Dump_CallDetailRecord

      • delete from Tbl_Dump_CallDetailRecordDiagnostic

  2. Restart the Cisco CAR Scheduler service.

    It should start to load CDRs as per the CDR load setting. Cisco recommends that you perform this process after hours so that it does not impact normal call processing.

Cause 2

If you do not see any records, there are no CDR entries for that time and date range. Determine if you have met the conditions for CDR generation. CDR records are generated in these circumstances:

  • The CallManager service parameters CDR Enabled and Call Diagnostics Enabled are set to True.

  • The call is established and has a talk duration greater than 0.

  • The call fails and has a cause code to reflect the error.

Complete these steps to enable CDR generation.

  1. Go to the Service menu and select Service Parameters from Cisco CallManager Administration page.

  2. Select the Cisco CallManager service for each server in the cluster.

  3. Scroll down to CDR Enabled Flag under the System section. Set the value of this parameter to True.

  4. Scroll down to Call Diagnostics Enabled under the Clusterwide Parameter (Device - General) section and assign a value of True to this parameter.

  5. Click Update.

  6. Go to the System menu and select Enterprise Parameters from the Cisco CallManager Administration page.

  7. Scroll down to the CDR Parameters section of the Enterprise Parameters Configuration page.

  8. Enter 1 for the CDR File Time Interval (min).

  9. Select CDRs will be inserted into database for the CDR Format.

  10. The value is set during the CallManager installation for the CDR UNC Path. Make sure the value is not empty or invalid.

  11. If the Cisco CallManager server is standalone, then enter the value as StandAloneCluster for the Cluster ID. Otherwise, enter the ID of the Cisco CallManager cluster.

  12. Enter the directory for local CDR files that are written by Cisco CallManager for the Local CDR Path. The value should not be empty or invalid, or Cisco CallManager cannot write CDRs.

  13. Leave the value field empty for the Off Cluster CDR Connection String.

  14. Click Update.

  15. Go to theService menu and select Service Parameters from the Cisco CallManager Administration page.

  16. Select the Cisco Database Layer Monitor service.

  17. Scroll down to Max CDR Records under the Clusterwide Parameters (Parameters that apply to all servers) section. Set the value as 1500000.

  18. Click Update.

  19. Go to the Tools menu and select Control Center from the Cisco CallManager Serviceability page.

  20. Make sure these services are activated for each sever in the cluster.

    • Cisco RIS Data Collector

    • Cisco Database Layer Monitor

  21. Activate the Cisco CDR Insert service in the Publisher CallManager.

Note: Sometimes, the zero and minimum duration calls to 911 do not show up in the CDR database if the calls are hung up quickly (as if 911 was dialed mistakenly). This issue can happen when the CDR parameter called Log calls with zero duration is set to False. Unless this parameter is enabled, the server does not log calls with zero duration in the CDR database. This includes calls that are never connected. In this situation, go to the CallManager Service Parameters and make sure that the Log calls with zero duration flag is set to True.

Cause 3

In Cisco CallManager release 4.1(3) or later, when the content of the Comments column exceeds 256 characters, an attempt to run the CAR/ART report receives the 30023:Data is not available for the date range selected error. This issue is tracked by Cisco bug ID CSCsb99593 (registered customers only) .

Complete these steps to verify whether the problem is due to loading of records.

  1. Select Start > Programs > Microsoft SQL Server > Query Analyzer.

  2. Select the CDR database from the drop-down list of database selection.

  3. Execute the query select max(len(comment)) from CallDetailRecord. If the return value is more than 256, then perform the steps to resolve the problem.

    1. Select the ART database from the database selection drop-down list.

    2. Execute these SQL commands.

      • Truncate Table Tbl_Billing_Data

      • Truncate Table Tbl_Billing_Error

      • Truncate Table Tbl_Error_Id_Map

      • Truncate Table Tbl_Load_History

      • Truncate Table Tbl_Event_Log

      • Truncate Table Tbl_Error_Log

      • Truncate Table Tbl_Dump_Calldetailrecord

      • Truncate Table Tbl_Dump_Calldetailrecorddiagnostic

      • Truncate table Tbl_Dump_Pkid

      • Alter Table Tbl_Billing_Data Alter Column Comment varchar(2048) NULL

      • Alter Table Tbl_Billing_Error Alter Column Comment varchar(2048) NULL

      • Alter Table Tbl_Dump_CallDetailRecord Alter Column Comment varchar(2048) NULL

    3. Configure the loader to run or take default loader times.

    4. Start the CAR Scheduler.

Cause 4

If the CAR data does not populate, and the error [Root exception is javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found] appears in the DC Directory logs, disable SSL from the DirectoryConfiguration.ini and UMDirectoryConfiguration.ini as given in these steps:

  1. Use a text editor to open C:\dcdsrvr\DirectoryConfiguration.ini, and verify useSSL=false.

  2. Open C:\dcdsrvr\Config\UMDirectoryConfiguration.ini, and verify the UseSSL=false.

  3. Open HKEY_LOCAL_MACHINE > SOFTWARE > Cisco Systems, Inc. > Directory Configuration and verify that UseSSL=false.

  4. Restart IIS, Cisco Tomcat, and Cisco CAR services from Windows services.

Cause 5

The number of calls that is reported through CDR does not match the number of calls that shows up in the Cisco Agent Desktop Agent Call Log. This issue can occur when the size of the CDR database contains more records. In this case, refer to Shrink the CDR Database to resolve the issue.

Cause 6

After you upgrade from Cisco CallManager 4.x to 6.0.1, CAR is unable to load CDR flat files into the CAR database. This can also result in the error message Data is not available for the date range selected.

In order to resolve the issue, perform these steps:

  1. From CLI, run this query: Run sql Select max(Error_Record_Id) from car:tbl_error_id_map <value A> returned.

  2. Get the result of the above query as <value A> and run this: run sql update car:tbl_system_preferences set param_value= <value A+1> where param_name='MAX_ERROR_RECORD_ID'

    For example, if max(Error_Record_Id) from car:tbl_error_id_map returns 100, use the MAX_ERROR_RECORD_ID as 101.

If these steps did not resolve the issue, perform this procedure:

  1. Go to the Cisco CallManager CAR page.

  2. Go to System > Scheduler > CDR Load.

  3. Check the Continuous Loading 24/7 box.

  4. Go to the Load CDR & CMR area, and make sure that the Time, Loading Interval, and Duration are configured correctly.

  5. Go to the CallManager Serviceability page.

  6. Go to Tools > Control Center.

  7. Choose the publisher and restart the Cisco CAR Scheduler service.

Related Information

Updated: Jun 17, 2008
Document ID: 68140