CUCM SQL Queries: CDR Analysis (CAR)

In this week's installment of my series on CUCM SQL queries we are going to switch gears a little. Thus far, we have been exploring configuration data in the Cisco Unified Communications Manager (CUCM) Informix database. This week, we are going to take a look at another table: The billing data table in the CAR database (tbl_billing_data).

Primer

A brief primer is provided in the first blog of this series.

Some Additional Info...

For this week's post I am going to explore the Cisco CDR Analysis and Reporting (CAR) database. CAR is an application that Cisco has included with CUCM since before the name change from "CallManager". Actually, if we want to get all nostalgic about it, CAR is the "artist formerly known as" ART. Fortunately, I don't see the point in exploring the history and won't bore you with the details. We just want to get at the data.

What data? Well, CAR is an application service that runs on the CUCM cluster (the publisher, actually). If activated, this application can consume call detail records (CDR) and call maintenance (or diagnostic) records (CMR). The CDR/CMR data is written into a separate database on the CUCM publisher. So, the data we are interested in is CDR data.

To use the methods described in this blog you need to ensure that the following steps are taken on your CUCM system:

1. Activate the CDR Analysis and Reporting (CAR) service on the CUCM publisher node.
2. Go to System>Service Parameters and set the Cisco CallManager service "Call Diagnostics Enabled" parameter to true on every cluster node that has the CallManager service activated.

This Week's Query

I am going to use an example from an assessment I did for one of my customers. The customer did not have any class of restriction applied in the CUCM dial plan. They wanted me to redesign their dial plan and apply COR. However, before they locked down the system they wanted to know:

• Who had placed calls to international destinations
• If there were any instances where a phone line actively forwarded calls to LD and international destinations

Fortunately, the customer had activated CAR and had enabled the appropriate parameters on CUCM. 

The Moving Parts

Referencing the CAR Database:

From a db perspective the CAR database and CUCM database are separate database instances. Which means that when you run a query on the CAR database, you need to identify the data source. To keep things simple, I am assuming people are testing these queries from the CLI on the CUCM. So, something like:

admin:run sql select [field list] from [table] where [expression]

The above command is accurate when querying the CUCM (or CCM) database. However, to access the CAR database you would actually need to reference the database by name. For example:

/*if accessing CAR data from CUCM 5.x to 7.x*/
admin:run sql select [field list] from car:[table] where [expression]

/*if access CAR data for version 8.x and newer*/
admin:run sql car select [field list] from [table] where [expression]

Note that we are clearly identifying the CAR database when defining the query. In versions of CUCM prior to 8.0, you would identify the CAR database in the from clause. Starting in CUCM version 8.0, you actually specify the data source prior to the select clause.

The tbl_billing_data Table:

Getting back to our queries. The table we are most interested in is the tbl_billing_data table. This table stores all of the elements we need to accomplish the task at hand. The first requirement was to identify calls to international destinations. This information is accessible by running the following query: (You can run the query from the CLI using run sql [insert query])

select datetimeOrigination,callingPartyNumber,finalCalledPartyNumber
from tbl_billing_data
where finalCalledPartyNumber like '9011%'
order by datetimeOrigination

This would provide something like the following:

datetimeorigination callingpartynumber finalcalledpartynumber
=================== ================== ======================
1332612655          3011055200         9011441234567890
1332613921          3011055202         9011331234567890

Of course, you would need to adjust your where clause to accommodate your dial plan. In our example, we are assuming a prefix of "9" is used for off net calls. We'll get to that nasty dateTimeOrigination field in a moment.

The next requirement is to identify calls where the calling party was forwarded to an off net Long Distance or International destination. This information is accessible by running the following query: (You can run the query from the CLI using run sql [insert query])

select datetimeOrigination,callingPartyNumber,originalCalledPartyNumber, finalCalledPartyNumber
from tbl_billing_data
where (originalCalledPartyNumber!=finalCalledPartyNumber) and 
       (finalCalledPartyNumber[1,4] = '9011' or finalCalledPartyNumber[1,2] = '91'))
order by datetimeOrigination

This would provide something like the following:

datetimeorigination callingpartynumber originalcalledpartynumber finalcalledpartynumber
=================== ================== ========================= ======================
1332613921          3011055202       3011053026                9011331234567890
1332613981          3011055202       3011053026                917035551212

So, I threw in some little "twists" in the second query. I am using a shortcut to parse strings in the where clause of the query. One of the expressions used is "finalCalledPartyNumber[1,4] = '9011'. Basically, this expression checks if the first four characters of the finalCalledPartyNumber is exactly "9011". Using the same technique I can check for a leading "91" on the finalCalledPartyNumber string using: "finalCalledPartyNumber[1,2]". There are other wildcards you can use in conjunction with the LIKE statement but I have my own preferences. That's just how I roll.

Of more pressing interest is that we are able to easily find forwarded calls by looking at the originalCalledPartyNumber and finalCalledPartyNumber fields. When these two are different, you are usually looking at a forwarded call. Building on this general assumption, we can then fine tune the where clause to narrow down our criteria to international and long distance final call destinations.

The dateTimeOrigination Field

I often hear folks ask about the dateTimeOrigination field and how to interpret the field. CUCM stores the CDR date/time information in epoch format. There are numerous conversion tools on the web. If you were pulling CDR data into Excel then you can use the following formula (in a new cell) to do the conversion:

=(((A1-(6*3600))/86400)+25569)

The above assumes that cell A1 has the date/time field you wish to convert. You may also need to change the format the cells which hold the formula (e.g. dd/mm/yyyy hh:mm).

---

Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!