How to do a forensic search within a Cisco CUCM CDR (Call Detail Record)

Your company is using Cisco Unified Communications and all is running smoothly until you suddenly receive a call from the receptionist that Emergency Services has just arrived at your front door following up on a 911 call that was made from someone inside the company and you need to find out who dialed the number. Or maybe you just received the monthly invoice from your Telco provider and you need to find out who has been repeatedly calling overseas to a country that you do not do business with. Does one of these situations sound familiar?

So what is the best way to find the answer to these questions? In both cases, the solution is to open Cisco Unified CM CDR Analysis and Reporting tool, export the raw data into an excel table then begin your forensic search for that needle in the haystack. The CDR extract is useful when you need to troubleshoot failed calls, find all long-distance calls or simply list all calls made by a specific individual for an HR related request. However, it is raw data and not formatted in a user friendly way; to make things even more challenging, Cisco Unified Communications Manager 10.5(2) or higher now includes 120 fields for each call, making a simple search request quite complex. So where do we begin?

PROBLEM

Our challenge is to forensically identify specific search criteria in a simple and easy way without combing through massive amounts of data.

SOLUTION

Our proposed solution is to break things down into key criteria and help you interpret certain fields to allow you to better conduct your analysis. We will first help you identify the main criteria required for most searches along with a secondary list of the most common criteria used for more advanced troubleshooting.  Next,  we will help you interpret two very important fields usually required to get to the bottom of your problem: the IP address and date and time. And finally show you a simple reporting format to share your results.

1)  Identifying Main Criteria

First,  the following information contains the main criteria required to begin any search whether it be high level or microscopic. We have included the column number, title and definition to help you quickly locate these fields.

2) identifying Advanced Criteria

Next, you will find a secondary list  with some the most popular criteria for more advanced troubleshooting, such as identifying the IP address or a caller device name.  Don’t forget there are far more criteria available, we are just suggesting some of the most useful below.

3)  Interpreting the data

Now that we have identified and located the key criteria, we need to make sense of it all. Some of the fields are rendered in a very specific way; the IP addresses, for example are displayed as signed numbers that need to be converted to hex, byte-inverted and converted to decimal. It sounds more complex than it is, with the following basic scripting you can convert that data back to the original IP address format. In addition, the timestamps are presented in Unix epoch, aka. elapsed seconds since January 1^st, 1970 and we provide with 3 options to convert them into a readable format.

interpreting the IP address

To convert the IP address into a readable format follow these 3 steps.

Step 1: Convert the signed number to hex (including the “-” sign if present). You might use a decimal/hex converter such as binary convert:

• 2014611210  to 0x7814870A

Step 2: Invert the four obtained bytes:

• 78 14 87 0A  to  0A 87 14 78

Step 3: Convert each byte to decimal and separate values with dots:

• 0x0A to 10, 0x87 to 135, 0x14 to 20, 0x78 to 120: the IP address is 10.135.20.120.

You may also want to take a look at columns 81 and 82 (signaling origin and destination IP addresses presented directly as IP addresses).

Note:  We noted that with some v9 clusters columns 81 and 82 do not always represent the real RTP source/destination IP addresses; for example, if an incoming call goes through the IVR on Unity before being directed to a user, column 82 will still indicate Unity IP address instead of the user phone’s IP address on the second leg of the call – while column 36 indicates the real user IP address.

Interpreting the Date and Time 

To convert the date and time into readable dates, here are three options you can use:

Option 1: If you are using a Unix-based system, simply enter the following command in a terminal window: date –r CDR_timestamp

Option 2: If you are using Excel, you may also add your time zone offset (e.g. -5 for EST):(CDR_timestamp + timezone_offset * 3600) / 86400 + 25569

Option 3: Alternatively, the website epochconverter will help you convert Unix epoch to readable date.

For more information about the CUCM CDR Analysis and Reporting tool read the Call Detail Records Administrative Guide for Cisco CUCM release 11.0(1). 

4) Reporting

Now that you have the fields identified and the data interpreted for your query, you may need to present your findings in a report that others can read.  With the help of excel and some visual basic scripting, you can present your results in a visually appealing and easily readable report format. Here is an example of a CDR report that we created for one of our clients.

If you would like help creating CDR reports like the one above or are looking for support with your Cisco Unified Communications needs reach out we here to help make Cisco easy.