Reading and Understanding CUCM CDR files

Cisco’s Unified Communications Manager (CUCM) is build to allow third party applications to provide call reports. This means that Call Manager will provide raw call logs, called CDRs (Call Detail Records) to whatever destination the engineer specifies. In fact, you can have up to three report destinations defined in Call Manager, allowing for multiple call reporting servers. Once you specify the address of your call reporting server inside of CUCM, it will begin pushing CDR files to that server. I believe it’s also important to mention that you can use the CDR Analysis and Reporting (CAR) to extract CDR files from CUCM. This will allow you to either manually review the CDR, or to manually feed it to a reporting / analyzing program.

So, what’s in these CDR files?

Up to this point, we’ve talked about what a CDR is (a flat file full of call information), and where it comes from (either pushed from CUCM, or pulled from CUCM’s CAR interface). What I’d like to do now is talk about this file, and what information it contains. Let’s say that you’ve saved a few CDR files to your PC’s desktop. You’ll see filename like: cdr_StandAloneCluster_02_201810261040_329890

This filename is composed of : the type of file _ type of CUCM cluster _ Cluster ID of the server _ date.

Opening up this file, you’ll see quite a bit of information:

Using Excel to read Call Detail Records

You can see from the screenshot, that this information runs off the screen – not an easily readable format! What I prefer to do is import this data into Microsoft Excel. Just open a blank worksheet in excel, then go to Data -> From Text. You’ll want to be sure to Choose “Delimited”, and then comma, like so:

Once we’ve imported the file into Excel, it will be much easier to read and understand

You may notice that fields like origIpAddr are not in an IP address format – Cisco has stored these in the CDR as Signed Decimal Value, and we’ll need to convert them to a dotted decimal format. I like to insert a new blank column in to excel – for example, insert a column to the right of origIpAddr, and then paste the following Excel formula. This code will convert the value in cell H3 to a dotted decimal format

=IF(LEN(DEC2HEX(H3))=8,HEX2DEC(MID(DEC2HEX(H3),7,2))&"."& HEX2DEC(MID(DEC2HEX(H3),5,2))&"."&HEX2DEC(MID(DEC2HEX(H3),3,2))&"."&HEX2DEC(MID(DEC2HEX(H3),1,2)),HEX2DEC(MID(DEC2HEX(H3),9,2))&"."& HEX2DEC(MID(DEC2HEX(H3),7,2))&"."&HEX2DEC(MID(DEC2HEX(H3),5,2))&"."&HEX2DEC(MID(DEC2HEX(H3),3,2)))

Understanding the fields contained in our CDRs

At this point, we can read the CDR, and look for information that may be helpful. There are 100+ columns in this report, and while some provide good data, others are less useful. Some fields that I would draw your attention to are:

• dateTimeOrigination – this is the timestamp of when the call was placed (not connected, but placed)
• callingPartyNumber – the number who made the call.
• originalCalledPartyNumber – the number that was originally called. I would note that translation patterns are applied before this is recorded, so if you translated PSTN numbers to an extension, only the extension (after translation) will show here.
• finalCalledPartyNumber – the number that the call terminated on. This could likely be a voicemail pilot, or somewhere the user has forwarded their phone.
• finalCalledPartyUnicodeLoginUserID – the CUCM end user associated with the device that received the call
• origDeviceName – the device that originated the call. If this call is inbound from the outside world, you’ll likely see a gateway name here.
• destDeviceName – the destination device. This could be a phone, voicemail, gateway, conference bridge, or just about anything where a call could land

Is manually reading Call Detail Records the best way to understand this data?

There is a wealth of information contained in the CDRs from Call Manager, so much so that it can be overwhelming. Using Excel to parse your records, while workable, doesn’t always scale as well as other solutions, like VoIP Detective. VoIP Detective will automatically parse your CDRs, and put them in an easy to read interface. If you’re using Excel, or Notepad to look through call records currently, you really should look at the free version of VoIP Detective. VoIP Detective is a call reporting server that you install, leveraging your VMware environment – it will wait for CUCM to send it CDR files, then process those files into a searchable database. Using a web browser, you’re able to search VoIP Detective to find information about inbound calls, outbound calls, call quality, gateway utilization, and more. Did I mention that VoIP Detective has a “free forever” version?

Full list of all Cisco Call Manager / CUCM CDR fields and their meanings

If you’d like a full explanation of the CDR fields, feel free to look at this chart, pulled from Cisco’s website: