Call Manager retrieving CDR and CMR records

Answered Question
May 12th, 2010
User Badges:

Hi,


I ma trying to monitor QOS in a Call Manager setup and so I need access to the CDR/CMR records. I have read a lot of references about it and currently quite confused as to which way to go. I know the CDR/CMR records got be retrieved in the quite a few ways, but i seem to hit a roadblock in each. So if someone can advice me as to which would be the most appropriate, it would be great. I have listed them below.


1) Access CISCO-VOICE-DIAL-CONTROL mib on the Voice gateways - This gives the QOS values for the active and historical calls. But the problem here is if a Cisco Catalyst switch is used as a gateway, then this MIB is not supported on it. It is supported on most of the other Cisco routers.


2) Access the Microsoft SQL db and query the CDR and CMR tables. But I guess this option has been deprecated in  Call Manager version > 7 or so


3) Access the CDR/CMR files through SOAP request. Is the request format for the CDR  and CMR the same? I found an eg in the XML developer guide. But the request has not parameter that can distinguish between a request for the CDR or CMR.

              <?xml version="1.0" encoding="utf-8"?>
               <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema"                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
               <soapenv:Body>
               <ns1:get_file_list soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="http://schemas.cisco.com/ast/soap/">
               <in0 xsi:type="xsd:string">200511161000</in0>
               <in1 xsi:type="xsd:string">200511161059</in1>
               <in2 href="#id0"/>
               </ns1:get_file_list>
               <multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="xsd:boolean"                xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">true</multiRef>
               </soapenv:Body>
               </soapenv:Envelope>


How do I access the CMR records using this approach. Is this approach valid ofr versions of the Call Manager and the CME?


4) CDR and CMR can be sent as syslog message using the CISCO-SYSLOG-MIB. Can you please tell me if there is some specific configuration to be done for this? Is this valid for all versions of Call Manager and the CME?


If I have missed out on some approach please do let me know.


Appreciate your response.


Regards

Roycey

Correct Answer by William Bell about 7 years 1 month ago

Roycey,


How are you.  There are plenty of options to be sure.  Let's try to cut through this some:


roycey_cheeran wrote:



1) Access CISCO-VOICE-DIAL-CONTROL mib on the Voice gateways - This gives the QOS values for the active and historical calls. But the problem here is if a Cisco Catalyst switch is used as a gateway, then this MIB is not supported on it. It is supported on most of the other Cisco routers.




I wouldn't go this route if you are talking about a Cisco UCM deployment.  Yes, you can get information from the gateways but that is not really necessary and, as you found out, platform dependant.


roycey_cheeran wrote:


2) Access the Microsoft SQL db and query the CDR and CMR tables. But I guess this option has been deprecated in  Call Manager version > 7 or so


Correct.  In pre-5x days that CDRs were flat files collected by subscribers and send to the publisher (to a MS share).  A service on the publisher (CDRInsert) would take these flat files and push them into a database.  This was a MS SQL DB.  From here the data could be groomed by other apps and processes.  In the appliance version, the CDRs are still aggregated at the publisher but are maintained as flat files.  If you enabled CDR Analysis and Reporting (CAR) on the CUCM system, the flat files will be available in a database format and you can actually query them from the CLI on the server.  More on CAR in a bit.


roycey_cheeran wrote:


3) Access the CDR/CMR files through SOAP request. Is the request format for the CDR  and CMR the same? I found an eg in the XML developer guide. But the request has not parameter that can distinguish between a request for the CDR or CMR.

How do I access the CMR records using this approach. Is this approach valid ofr versions of the Call Manager and the CME?


It doesn't quite work the way you are thinking. The CDR On Demand SOAP interface you are referring to is simply a command that you can send to the CUCM system to find out what CDRs are sitting in the repository.  You can then use the interface to retrieve the CDRs.  The intended use for the CDR On Demand API is to allow a billing server to "pickup" any CDRs it may have missed due to a disruption in the communiction from the CUCM system.  As far as differentiating between CDR and CMR.  I have not looked at this API recently but my recollection is that your application/script would need to determine what type of call record is available via the name of the flat file (cmr vs. cdr).  But I may be mistaken.


roycey_cheeran wrote:


4) CDR and CMR can be sent as syslog message using the CISCO-SYSLOG-MIB. Can you please tell me if there is some specific configuration to be done for this? Is this valid for all versions of Call Manager and the CME?



I did not know this to be true for CUCM.


For CUCM, I would look at setting up a call detail billing server (CUCM Serviceability web pages).  The billing server is basically a FTP/SFTP server that the CUCM cluster will stream the CDRs to based on a specified interval.  You just need FTP/SFTP (I recommend SFTP), a user name, and a server IP address.  The default interval is 1 minute (Set in the Enterprise Parameters).  You can tweak the interval.  This will give you the raw data that you can process to your hearts content.  For CME, the data structure is different and you will need to send call details via syslog I believe.  You would need to normalize the records if you want to compare/contrast.  I have not looked at this aspect of CME myself.


I mentioned CAR earlier.  This is an application that Cisco ships with CUCM for free. You just have to enable it.  As a free application it isn't really the most useful tool.  But it sometimes can meet the exact need you are looking to fulfil. Search on CCO for "CDR Analysis and Reporting Tool".


HTH.


Regards,

Bill

Please remember to rate helpful posts.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (3 ratings)
Loading.
Correct Answer
William Bell Wed, 05/12/2010 - 05:29
User Badges:
  • Purple, 4500 points or more

Roycey,


How are you.  There are plenty of options to be sure.  Let's try to cut through this some:


roycey_cheeran wrote:



1) Access CISCO-VOICE-DIAL-CONTROL mib on the Voice gateways - This gives the QOS values for the active and historical calls. But the problem here is if a Cisco Catalyst switch is used as a gateway, then this MIB is not supported on it. It is supported on most of the other Cisco routers.




I wouldn't go this route if you are talking about a Cisco UCM deployment.  Yes, you can get information from the gateways but that is not really necessary and, as you found out, platform dependant.


roycey_cheeran wrote:


2) Access the Microsoft SQL db and query the CDR and CMR tables. But I guess this option has been deprecated in  Call Manager version > 7 or so


Correct.  In pre-5x days that CDRs were flat files collected by subscribers and send to the publisher (to a MS share).  A service on the publisher (CDRInsert) would take these flat files and push them into a database.  This was a MS SQL DB.  From here the data could be groomed by other apps and processes.  In the appliance version, the CDRs are still aggregated at the publisher but are maintained as flat files.  If you enabled CDR Analysis and Reporting (CAR) on the CUCM system, the flat files will be available in a database format and you can actually query them from the CLI on the server.  More on CAR in a bit.


roycey_cheeran wrote:


3) Access the CDR/CMR files through SOAP request. Is the request format for the CDR  and CMR the same? I found an eg in the XML developer guide. But the request has not parameter that can distinguish between a request for the CDR or CMR.

How do I access the CMR records using this approach. Is this approach valid ofr versions of the Call Manager and the CME?


It doesn't quite work the way you are thinking. The CDR On Demand SOAP interface you are referring to is simply a command that you can send to the CUCM system to find out what CDRs are sitting in the repository.  You can then use the interface to retrieve the CDRs.  The intended use for the CDR On Demand API is to allow a billing server to "pickup" any CDRs it may have missed due to a disruption in the communiction from the CUCM system.  As far as differentiating between CDR and CMR.  I have not looked at this API recently but my recollection is that your application/script would need to determine what type of call record is available via the name of the flat file (cmr vs. cdr).  But I may be mistaken.


roycey_cheeran wrote:


4) CDR and CMR can be sent as syslog message using the CISCO-SYSLOG-MIB. Can you please tell me if there is some specific configuration to be done for this? Is this valid for all versions of Call Manager and the CME?



I did not know this to be true for CUCM.


For CUCM, I would look at setting up a call detail billing server (CUCM Serviceability web pages).  The billing server is basically a FTP/SFTP server that the CUCM cluster will stream the CDRs to based on a specified interval.  You just need FTP/SFTP (I recommend SFTP), a user name, and a server IP address.  The default interval is 1 minute (Set in the Enterprise Parameters).  You can tweak the interval.  This will give you the raw data that you can process to your hearts content.  For CME, the data structure is different and you will need to send call details via syslog I believe.  You would need to normalize the records if you want to compare/contrast.  I have not looked at this aspect of CME myself.


I mentioned CAR earlier.  This is an application that Cisco ships with CUCM for free. You just have to enable it.  As a free application it isn't really the most useful tool.  But it sometimes can meet the exact need you are looking to fulfil. Search on CCO for "CDR Analysis and Reporting Tool".


HTH.


Regards,

Bill

Please remember to rate helpful posts.

Roycey Cheeran Wed, 05/12/2010 - 05:40
User Badges:

Hi Bill


Thanks for you speedy response. Now that I have a better understanding, I will try your suggestion.


Thanks

Roycey

rhegde Fri, 10/08/2010 - 14:39
User Badges:

If I want to query Jitter,packet loss,latency  values from CMR which sql query I need to run from CUCM 7 CLI ?  In which database table jitter , packet loss , latency details  are stored..


TIA

Rav

William Bell Wed, 11/24/2010 - 05:56
User Badges:
  • Purple, 4500 points or more

Rav,


Maybe you could try this:


admin:run sql select callingpartynumber, originalcalledpartynumber,orignumberpacketslost,origjitter,origlatency,destnumberpacketslost,destjitter,destlatency from car:tbl_billing_data




HTH.  Please remember to start a new post when you have a question that isn't directly related to a thread. I almost completely missed your question.


Regards,
Bill

Please rate helpful posts.

rhegde Wed, 11/24/2010 - 07:51
User Badges:

Thanks for the reply.. This exactly I was looking for.


Essentially where we can find , what type of table we have in CM 7/8 and objects of the table so that we can formulate the correct query. I could not find a good document in CCO . If you know one and let me know , that would be great.


I didn't find  an option to rate. Or else I would definetly..


Thanks

Rav

William Bell Thu, 11/25/2010 - 00:12
User Badges:
  • Purple, 4500 points or more

Rav,


Take a look at this document for CDR Analysis and Reporting.


http://www.cisco.com/en/US/partner/docs/voice_ip_comm/cucm/service/7_1_2/car/CAR.html


You may also want to be familiar with this document on CDR administration:


http://www.cisco.com/en/US/partner/docs/voice_ip_comm/cucm/service/7_1_2/cdrdef/cdradmin.html


Note that CDRs are not stored in a database on the appliance models. Instead, they are stored in flat files. CDR Analysis and Reporting (CAR) has a database that will process and store the raw CDR files. So, the first document focuses on CAR and the second focuses on CDR administration in general. Note that in cases where you want to have regular reports ran, it is better to have CUCM export the CDR data to an external billing server. That process could include an "off the shelf solution" or it could be something you develop yourself.


HTH.


Regards,
Bill


PS: To rate a post you simply select the stars displayed below this message.  If a post is not helpful pick the star on the left, if it is very helpful then pick the star on the right. Somewhere in between? Then select the appropriate point value.  Rating is optional but appreciated.

Actions

This Discussion