Cisco CDR Reporting Tool Automated
When you are working on the CDR Dump either for reporting or troubleshooting, are you tired of converting Signed Integers to an IP Address or referring to the cause codes again and again and do your analysis? I have created an excel sheet for Cisco CDR which is actually handy while troubleshooting or reporting. The excel sheet can do the following task.
- Converting origIpAddr to a readable IP Address. For Example. The value “-1139627840” is converted to an X.X.X.X IP Address which is 192.168.18.188
- Converting destIpAddr to a readable IP Address. For Example. The value “-1139627840” is converted to an X.X.X.X IP Address which is 192.168.18.188
- Converting origCause_value from Number to a readable error code. For Example. The value “16” is 16-Normal call clearing
- Converting destCause_value from Number to a readable error code. For Example. The value “16” is 16-Normal call clearing
- Converting dateTimeOrigination to a readable Time Format in GMT. For Example. The value “1436193921” is converted to a readable format “7/6/2015 2:45:21 PM” in GMT
- Converting origCallTerminationOnBehalfOf from Number to a readable error code. For example. The value “12” is 12 – Device
- Converting destCallTerminationOnBehalfOf from Number to a readable error code. For example. The value “12” is 12 – Device
- Converting origCalledPartyRedirectReason from Number to a readable error code. For example. The value “354” is 354-Recording
- Converting lastRedirectRedirectReason from Number to a readable error code. For example. The value “354” is 354-Recording
To download the File, click on the the link here.
Please share your comments and let me know if you would like to add or modify something and i will try my best to achieve it.
Have fun!!
I am working in an IT company and having 10+ years of experience into Cisco IP Telephony and Contact Center. I have worked on products like CUCM, CUC, UCCX, CME/CUE, IM&P, Voice Gateways, VG224, Gatekeepers, Attendant Console, Expressway, Mediasense, Asterisk, Microsoft Teams, Zoom etc. I am not an expert but i keep exploring whenever and wherever i can and share whatever i know. You can visit my LinkedIn profile by clicking on the icon below.
“Everyone you will ever meet knows something you don’t.” ― Bill Nye
Wow, thanks. Lifesaver. Though, I notice your formula for ip (dest and orig) isn’t consistently correct when there aren’t 8 hex characters.
Can I suggest this which does work as expected:
=CONCATENATE(HEX2DEC(RIGHT(DEC2HEX(RC[-1],8),2)),””.””,HEX2DEC(MID(DEC2HEX(RC[-1],8),LEN(DEC2HEX(RC[-1],8))-3,2)),””.””,HEX2DEC(MID(DEC2HEX(RC[-1],8),LEN(DEC2HEX(RC[-1],8))-5,2)),””.””,HEX2DEC(MID(DEC2HEX(RC[-1],8),LEN(DEC2HEX(RC[-1],8))-7,2)))
Sean
Hello Sean,
Thank you for referring to my blog!!
I really appreciate you taking a step and correcting me by providing the correct formula, it works like a charm. I have now uploaded the newer version v1.4. It should reflect the changes now.
Thank You!!
Hello Avinash,
Your tool is great, it saved my day! I was searching for a free portable tool that do not need any installation such as the professional tools we can find on the market: my goal is to be able to analyze a CDR dump quickly, for example determine how many calls our CUCM managed on a specific day.
CDR-Reporting-Ver 1.5 did it great 🙂
Some suggestions though:
– I get an execution error 1004 when clicking on the “origIpAddr” (or “destIpAddr”) even though the IP addresses are correctly translated.
– It would be great to add a “globalCallID” column in the “Conversion” tab containing the “globalCallID_callId” information from the CDR Dump so that we can see the calls unique IDs there.
– A feature/button to merge the call legs of a call would be great (for example for call forwards, transfers, calls to CTI Route Points or CTI Ports scenarios) so that we can only see in the unique calls in the “Conversion” tab by clicking on it.
In any case, many thanks for your work!
Julien
That’s an awesome tool you’ve built – thank you very much.
I’ve built a tool of my own to read CDRs & CMRs – it’s an appliance (OVA) that you need to install in your VMware infrastructure, so it’s not as portable as yours, but it does quite a bit (reports, alerting, etc). There is a free version (no expiration or anything), so everyone can use it. You can check it out at http://www.VoIPDetective.com
The website listed in the excel sheet is no longer accessible.
https://uccisco.wordpress.com/2014/05/16/cisco-cdr-call-detail-record-reporting/
Apology, that’s the old link in the excel – Follow this link https://www.uccollabing.com/configuring-cisco-cdr-call-detail-record/
Fantastic tool, thank you! One improvement would be to add the duration field in the conversion sheet.