Accessing CDR Data in the Central CDR Database

CallManager stores all CDR data in the central CDR database. It is not the same database as the CallManager Administration database. You can access the CDR database in a read/write fashion.

When you are processing CDR data, you might want to read other tables in the CallManager Administration database to obtain information about the type of device for which this CDR was written. The device name is provided in the CDR, but the device type and other information is not. CallManager uses the Microsoft SQL 2000 database. You can gain direct access to the database with Open Database Connectivity (ODBC).

Gaining Access to Database Tables

The easiest way for an external application to read data from the SQL database is to use ODBC. To access the Publisher's CDR or CallManager database, an NT user account must be used that is authenticated on both the external application server and the CallManager Publisher. It is easier if the NT user for the external application is an NT user, and not a domain user.

You must add the NT user that the application uses to the CallManager Publisher, which will allow the application to gain access. You also need to add matching user accounts to the SQL Server and CDR database and grant them either read or write access as needed.

The following example illustrates what a good connection string looks like:

For Windows NT authentication:

 Driver=SQL Server;SERVER=pubname;DATABASE=CDR;Trusted_Connection=yes

The registry on servers hosting a database can also be checked. Look at the following registry key for DBConnection0:

\HKEY_LOCAL_MACHINESoftwareCisco Systems Inc.DBL

The DBConnection0 string item contains a connection string similar to the preceding with the machine name and database name of the primary database. Also, the CDR database name is stored in a local registry key.

\HKEY_LOCAL_MACHINESoftwareCisco Systems Inc.DBLPrimaryCdrServer

You will need access to both the configuration database and CDR database to properly resolve the CDR information.

Performance Issues Related to Processing and Removing CDR Data

Keep the following performance guidelines in mind when you consider how to process CDR data. If the database is on the same server as CallManager, CDR processing during normal to heavy call activity might have a significant impact on system performance. Cisco recommends that no CDR processing should be done on the data in the CDR table except to move the data to a separate machine. This is the best way to ensure that CDR analysis will not impact system performance. The following are additional tips for processing CDR data:

• Partner's software and databases should never reside on the MCS platform and should be placed on a separate physical system.
• Do not use database triggers on CallManager tables.
• Do not use database-stored procedures on CallManager tables.
• If a partner does not want CDR data written to the CallManager Publisher database, a Data Source Name (DSN) entry on CallManager can be changed so that CDR data is written to the separate server. If a large memory cache is made available on the separate server, these actions should improve CallManager performance.
• If bulk pulling CDR data from the database, a rule of thumb is to pull no more than 10,000 records at a time.

Maintaining CDR/CMR Data in the Database

CallManagers within the cluster generate CDR data and write it into the database. In general, the system does not make any attempt to process the data or remove the data when it has been processed except those actions necessary to preserve system integrity. The section "System Actions and Limits on Record Storage" covers the actions taken to preserve system integrity.

Administrator's Responsibility

The administrator has the responsibility either to ensure that the post-processing software removes the CDR data when all processing of the data has been completed, or to establish and execute other procedures to remove the data. CallManager removes data as necessary to enforce a limit on disk usage if the data store grows too large. This preserves system integrity, but it can result in lost CDR data if it has not been processed.

The Database Layer creates a protective shield around the database to prevent altering configuration data except through CallManager Administration. You must have write access into the database to remove data, because this involves modifying the database.

When CDR data is removed from the database after analysis is completed, all related CMRs should also be removed. If either CMR data or CDR data is not removed when the corresponding records have been removed in the other table, no particular harm is done, except that the data is not available and takes up disk space. It is recommended that you do regular backups on your CDR database. If you do regular backups, no further action is needed.

System Actions and Limits on Record Storage

The CallManager server platforms ship with sufficient disk storage to safely store approximately 10 million CDRs and their associated CMRs. Once a day, the system checks the number of CDRs currently stored in the database against the maximum number of records allowed as defined by the Cisco Database Layer Monitor service parameter Max CDR Records. If the number of records contained in the CallDetailRecord table exceeds this maximum value, the system takes the following actions to ensure that sufficient disk space is maintained to safely operate the cluster:

• If CDRs accumulate to a configured maximum, the oldest CDRs are removed, along with related CMRs, once a day. The CDR data count is reduced back to the configured maximum.
• When records are removed from the database, an alarm is generated that says "Local CDR tables grew too large. Records were deleted." This alarm is routed to the Event Viewer and to trace files.

Note

The configured maximum number of CDRs is set to 1.5 million when the system is shipped. CallManager makes no attempt to intelligently remove data, so if CallManager is required to remove CDR data, it is possible to remove part of the CDR data for a given call but not all of it.

Note

You should remove records more often than once a day or per week in large systems. Queries to remove records consume CPU time and transaction log space relative to the size of the table. The smaller the table, the quicker your query runs. Removing significant quantities of records from the database during normal system operation might have a severe performance impact on CallManager.