Guest

Cisco Unified Communications Manager (CallManager)

Using SQL Queries to Search the Call Detail Record with Cisco CallManager

Cisco - Using SQL Queries to Search the Call Detail Record with Cisco CallManager

Document ID: 45542

Updated: Jun 28, 2007

   Print

Introduction

It is sometimes useful to search in the Call Detail Record (CDR) for calls that are made after a certain time or to a specific number. This document provides the procedures for when you use Query Analyzer in order to search for a call made after a certain date and time, and to specific numbers.

Prerequisites

Requirements

Readers of this document need to be knowledgeable of these topics:

  • Cisco CallManager 3.x and 4.x administration

  • SQL database management

Components Used

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

  • Cisco CallManager 3.x

  • Cisco CallManager 4.x

Note: Although there is a different version of the SQL database in Cisco CallManager 4.x (namely SQL Server 2000), this document still applies.

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 the Cisco Technical Tips Conventions for more information on document conventions.

Open SQL Query Analyzer

All of the examples given in this document are run using Query Analyzer. Complete these steps in order to open Query Analyzer.

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

    sql-queries-cdr-1.gif

  2. Log into the server (your local server). For Cisco CallManager 3.x, select Use SQL Server authentication, and enter your username and password. For Cisco CallManager 4.0 and later, select Use Windows NT authentication. Windows NT Authentication is recommended, although the system supports SQL Authentication. Setting Cisco CallManager for mixed mode authentication in release 4.0 and later is not supported. Upgraded earlier versions fail with SQL Server authentication and the system needs to be changed back to Windows NT authentication.

    sql-queries-cdr-2.gif

    Note: When third party applications need to access the Cisco CallManager database, SQL authentication might be required, but this is not supported by Cisco.

  3. From the database pull-down (top right-hand side of window) select the CDR Database.

    sql-queries-cdr-3.gif

  4. Enter a query in the panel.

Date Format in the CDR Database

In order to select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.

For example, 973995954 translates to 11/12/00 2:25 AM. Complete these steps in order to decipher the time stamp.

  1. Go to Microsoft Excel.

  2. In cell A1 type the number that is found in the last record for dateTimeOrigination.

  3. In cell A2, paste the formula =A1/86400+DATE(1970,1,1).

  4. Right-click on cell A2 and select format cells.

  5. Under the Number tab select Time where the format is 3/14/98 130 PM.

    The result is the actual time in readable format.

Complete these steps in order to convert the day you want into the date.

  1. Enter the date in cell B2 in the format MM/DD/YYYY 00:00 AM.

  2. In Cell A enter the formula =(B2-DATE(1970,1,1))*86400.

  3. Right-click on cell B1 and select format cells.

  4. Under the Number tab, select Number.

    This number is the universal time in seconds that is used in the formula.

    In order to get the correct time, download the CiscoCDRTime Converter-0.1.0.0.zip.

    caution Caution: This tool is not officially supported by Cisco Systems. If you download, install, or use this tool, you do so at your own risk. Cisco Systems, Inc. is not responsible for correcting problems that may arise as a result of using this unsupported tool.

Search for Calls Made After or Between Two Dates/Times

In order to select all calls after a certain date, you need to convert the date you wish into a value in universal time and in seconds since January 1st, 1970. See the Understanding the Date Format in the CDR Database section of this document.

  1. Go to the window in SQL Query Analyzer and enter the formula select * from CallDetailRecord where dateTimeConnect > '973995954' for all calls made after a date.

  2. If you want to look at all calls that happen between two times, enter the formula select * from CallDetailRecord where dateTimeConnect > '973995954' and datetimeconnect < '1063574868'.

  3. In order to execute the script, click on the check mark at the top of the page and then click the play button.

    sql-queries-cdr-4.gif

Search for Calls Made to a Specific Number

In some situations, you might need to find out the extension which called 911 on a specific date. Complete these steps in order to search for calls made to a specific number such as 911.

  1. In order to search for calls that are made to a specific number, you can run a different query.

    For example, if you want to look for calls to 911 you can enter the query select * from CallDetailRecord where finalcalledpartynumber='911'.

  2. The bottom of the page displays all the entries in the CDR that have the final called party number equal to 911.

  3. Add a date to this query in order to refine it.

    For example, for all calls to 911 after August 1st, 2003, run the query select * from CallDetailRecord where finalcalledpartynumber='911' and datetimeconnect > '1059696000'.

  4. The bottom of the page displays all the entries in the CDR that have the final called party number equal to 911 made after August 1st, 2003. '1059696000' is the universal time in seconds for August 1st, 2003.

  5. In order to see who calls 911, run the query select callingpartynumber from CallDetailRecord where finalcalledpartynumber='911'.

  6. In order to save the results in CSV format, see the Saving the Results of Your Query section of this document.

Note: If you try to find the number of calls made to a number, which is a translation pattern, it does not work. CDR does not record translation pattern; it only monitors DNs and Route Patterns.

Save the Results of Your Query

Once you run the query, if you want to save the data you have into a .CSV file that you can open in Excel, highlight the results as shown in this procedure.

  1. Click on the first column and drag your mouse to the last column, or, click on the grey square above the number 1.

    sql-queries-cdr-5.gif

  2. Select File > Save As.

    sql-queries-cdr-6.gif

  3. Save it as a .CSV file, and view it in Excel or in Notepad.

    sql-queries-cdr-7.gif

Related Information

Updated: Jun 28, 2007
Document ID: 45542