Anyone know where call statistics are stored?

[BrianChernish]

I would like to be able to create a report based on a specific user and get details such as incoming call totals by hour or day and the fate of those calls (i.e. answered, went to voicemail, answered and forwarded to another DN, etc). Ideally it would be nice if I could get this type of info for Attendant Console users as well.

I have an in-house SQL programmer who can "mine" the CM data bases and I was looking for a leg up on where I might start looking for this type of info.

Any thoughts appreciated.

Thanks in advance

[tgriffin]

We're trying to do this too. The first thing you have to do is make sure that all users (and phones) are listed in the Global Directory of Call Manager. If they are not in there, you won't get the data you're looking for. After that you can pull up reports in CMR (CallManager reporting application..or is it CDR? One of those). It's actually another application that you access from the main call manager page. If that aplet isn't installed you'll have to add it. Honestly, the reports you get require a lot of work to clean up. Maybe a SQL person can make it a little less painless.

[k6lw]

Just use the SQL Query Analyzer on the Publisher. Make sure you select the CDR database.

Also ensure that the CDR insert is turned on every call manager in your cluster.

To pull records based on a certain date, you'll need to build an excel spreadsheet data converter to allow you to enter the date in question and have it return the number of seconds since Jan 1, 1970 to find the call record. The CDR records are recorded by this number.

Lee

[aaronw.ca]

The call detail records list the details of the calls (calling party, called party, duration, etc), and these records are stored in the CallDetailRecord table in the CDR database on the Publisher. You must ensure that the "CDR enabled" flag is set to "true" on all CallManagers in the cluster. You will find this flag in the Service Parameters/Cisco CallManager configuration under the "System" category.

The diagnostic (quality of service) records list the QoS details associated with IP phone and MGCP gateways such as jitter, packet loss, and latency. These records are stored in the CallDetailRecordDiagnostic table in the CDR database on the Publisher. You must ensure that the "Call Diagnostics enabled" flag is set to "true". You will find this flag in the Service Parameters/Cisco CallManager configuration under the Clusterwide Parameters category.

You can find information about the CDR and CMR fields in the Cisco documentation:

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_administration_guides_list.html

Find your version of CallManager in the list of Call Detail Record Definition guides. For example, here is the guide for CallManager version 3.3(4):

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_administration_guide09186a00802375db.html

As noted, the date/time values are stored as integers, so to get a legible value you can either have your SQL coder to the conversion in SQL, or do it in code in later processing. For a quick check, you can use MS Excel with a formula such as:

=C5/86400+DATE(70,1,1)

where C5 is the cell containing the integer value from the date/time field of the CDR table.

One final note, the date/times are stored as UTC/GMT timezone values, so to get a date/time meaningful to you, you may need to add/subtract your timezone offset in seconds.

Most commercial Call Accounting applications do all this for you, though Cisco does provide a no-charge (I believe) basic package (CAR - CDR Analysis and Reporting). Look in the Cisco CallManager Servicability System Guide from the above link that corresponds to your CallManager version and follow the CDR Analysis and Reporting link for more information. Again, here is the CAR page for CallManager 3.3(4):

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_administration_guide_chapter09186a00801f0279.html