In this week's installment of my series on CUCM SQL queries we are going to switch gears a little. Thus far, we have been exploring configuration data in the Cisco Unified Communications Manager (CUCM) Informix database. This week, we are going to take a look at another table: The billing data table in the CAR database (tbl_billing_data).
A brief primer is provided in the first blog of this series.
Some Additional Info...
For this week's post I am going to explore the Cisco CDR Analysis and Reporting (CAR) database. CAR is an application that Cisco has included with CUCM since before the name change from "CallManager". Actually, if we want to get all nostalgic about it, CAR is the "artist formerly known as" ART. Fortunately, I don't see the point in exploring the history and won't bore you with the details. We just want to get at the data.
What data? Well, CAR is an application service that runs on the CUCM cluster (the publisher, actually). If activated, this application can consume call detail records (CDR) and call maintenance (or diagnostic) records (CMR). The CDR/CMR data is written into a separate database on the CUCM publisher. So, the data we are interested in is CDR data.
To use the methods described in this blog you need to ensure that the following steps are taken on your CUCM system:
- Activate the CDR Analysis and Reporting (CAR) service on the CUCM publisher node.
- Go to System>Service Parameters and set the Cisco CallManager service "Call Diagnostics Enabled" parameter to true on every cluster node that has the CallManager service activated.
This Week's Query
I am going to use an example from an assessment I did for one of my customers. The customer did not have any class of restriction applied in the CUCM dial plan. They wanted me to redesign their dial plan and apply COR. However, before they locked down the system they wanted to know:
- Who had placed calls to international destinations
- If there were any instances where a phone line actively forwarded calls to LD and international destinations
The Moving Parts
Referencing the CAR Database:
From a db perspective the CAR database and CUCM database are separate database instances. Which means that when you run a query on the CAR database, you need to identify the data source. To keep things simple, I am assuming people are testing these queries from the CLI on the CUCM. So, something like:
admin:run sql select [field list] from [table] where [expression]
The above command is accurate when querying the CUCM (or CCM) database. However, to access the CAR database you would actually need to reference the database by name. For example:
/*if accessing CAR data from CUCM 5.x to 7.x*/ admin:run sql select [field list] from car:[table] where [expression] /*if access CAR data for version 8.x and newer*/ admin:run sql car select [field list] from [table] where [expression]
Note that we are clearly identifying the CAR database when defining the query. In versions of CUCM prior to 8.0, you would identify the CAR database in the from clause. Starting in CUCM version 8.0, you actually specify the data source prior to the select clause.
The tbl_billing_data Table:
Getting back to our queries. The table we are most interested in is the tbl_billing_data table. This table stores all of the elements we need to accomplish the task at hand. The first requirement was to identify calls to international destinations. This information is accessible by running the following query: (You can run the query from the CLI using run sql [insert query])
select datetimeOrigination,callingPartyNumber,finalCalledPartyNumber from tbl_billing_data where finalCalledPartyNumber like '9011%' order by datetimeOrigination
This would provide something like the following:
I often hear folks ask about the dateTimeOrigination field and how to interpret the field. CUCM stores the CDR date/time information in epoch format. There are numerous conversion tools on the web. If you were pulling CDR data into Excel then you can use the following formula (in a new cell) to do the conversion:
=(((A1-(6*3600))/86400)+25569)
The above assumes that cell A1 has the date/time field you wish to convert. You may also need to change the format the cells which hold the formula (e.g. dd/mm/yyyy hh:mm).
datetimeorigination callingpartynumber finalcalledpartynumber =================== ================== ====================== 1332612655 3011055200 9011441234567890 1332613921 3011055202 9011331234567890
Of course, you would need to adjust your where clause to accommodate your dial plan. In our example, we are assuming a prefix of "9" is used for off net calls. We'll get to that nasty dateTimeOrigination field in a moment.
The next requirement is to identify calls where the calling party was forwarded to an off net Long Distance or International destination. This information is accessible by running the following query: (You can run the query from the CLI using run sql [insert query])
select datetimeOrigination,callingPartyNumber,originalCalledPartyNumber, finalCalledPartyNumber from tbl_billing_data where (originalCalledPartyNumber!=finalCalledPartyNumber) and (finalCalledPartyNumber[1,4] = '9011' or finalCalledPartyNumber[1,2] = '91')) order by datetimeOrigination
This would provide something like the following:
datetimeorigination callingpartynumber originalcalledpartynumber finalcalledpartynumber =================== ================== ========================= ====================== 1332613921 3011055202 3011053026 9011331234567890 1332613981 3011055202 3011053026 917035551212
So, I threw in some little "twists" in the second query. I am using a shortcut to parse strings in the where clause of the query. One of the expressions used is "finalCalledPartyNumber[1,4] = '9011'. Basically, this expression checks if the first four characters of the finalCalledPartyNumber is exactly "9011". Using the same technique I can check for a leading "91" on the finalCalledPartyNumber string using: "finalCalledPartyNumber[1,2]". There are other wildcards you can use in conjunction with the LIKE statement but I have my own preferences. That's just how I roll.
Of more pressing interest is that we are able to easily find forwarded calls by looking at the originalCalledPartyNumber and finalCalledPartyNumber fields. When these two are different, you are usually looking at a forwarded call. Building on this general assumption, we can then fine tune the where clause to narrow down our criteria to international and long distance final call destinations.
The dateTimeOrigination Field
Of more pressing interest is that we are able to easily find forwarded calls by looking at the originalCalledPartyNumber and finalCalledPartyNumber fields. When these two are different, you are usually looking at a forwarded call. Building on this general assumption, we can then fine tune the where clause to narrow down our criteria to international and long distance final call destinations.
The dateTimeOrigination Field
I often hear folks ask about the dateTimeOrigination field and how to interpret the field. CUCM stores the CDR date/time information in epoch format. There are numerous conversion tools on the web. If you were pulling CDR data into Excel then you can use the following formula (in a new cell) to do the conversion:
=(((A1-(6*3600))/86400)+25569)
The above assumes that cell A1 has the date/time field you wish to convert. You may also need to change the format the cells which hold the formula (e.g. dd/mm/yyyy hh:mm).
Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!
Great job on this series, this is kind of a "forgot" topic, until the day someone ask you something.. lol..
ReplyDeleteQuestions:
1 - I see these queries results on outputs on the terminal client. Is there a way to make it into a file (like txt) then download it via RTMT or so, so in case you have a large result you can open in on excel and work on it?
2 - As I'm not that familiar with SQL and so, is there a doc that contains the expressions/wildcards that can be used on the queries?
I see the "CUCM" side is pretty straight forward, you just need to use the Field name of CDR to get what you want on the result, but as it comes to filter I'm not sure what can be used.
In my case I'm looking to get a query for all calls that contains a calling/called number 5XXX.
So not sure if there is a wild card that I can use to filter like that (maybe the X itself? =)).
Congrats again for the posts, very clear and well written.
Regards.
Bruno,
DeleteThanks for the feedback.
1. Yes, I am using the CLI for examples to give people an interface they can use straight away to get familiar with the interface. In practice, I actually use a tool I developed to run the queries remotely. That tool uses AXL/SOAP to query the db. You would need something along those lines as you can't dump the results to a file on the CUCM node.
2. This is a good question. There are wildcards you can use when the where clause uses LIKE or MATCH. LIKE is the ANSI standard whereas MATCH is the Informix extension. I always use LIKE for no other reason other than that is what I used with other DBMS query interpreters.
See this link as a good reference point: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls977.htm
For your example, you could try:
select datetimeOrigination,callingPartyNumber,
originalCalledPartyNumber, finalCalledPartyNumber
from tbl_billing_data
where callingPartyNumber like '%5___%'
The where clause above captures any string contain 5 followed by 3 characters (assumed to be numeric given the context of the question). So, 4445000 matches as does 444500044, 5123, 5000, 599944, etc. The "%" is a match of zero or more characters while the underscore "_" means any single character.
HTH.
Regards,
Bill
First of all, great site with lots of helpful information.
ReplyDeleteI'm building a reporting system that queries the cucm database. I have a soap request embedded in my page that queries the cucm database perfectly. I would also like to incorporate the cdr data into my report. Is there a way that I can query the cdr data (or tbl_billing_data) via SOAP/AXL? I would love to be able to query the cdr database and join to the tables in the cucm database to create reports from a menu driven website. Is this possible or am I stuck with having to deal with the cdr text files that are exported from my system?
Thanks,
Greg
Greg,
DeleteThe CDR API operates in a different manner. You can query for a list of CDR files and then you can query for specific files. In pre-8x versions you may be able to query the CAR table via AXL/SOAP give that you can reference the db by name in the from clause. In version 8x and later, you no longer can do that and I have tried ;-)
I would check the CDROnDemand service/API. Google: "CDROnDemand service site:cisco.com"
HTH.
-Bill
Dear William,
ReplyDeleteI am wondering if I can write a query that get the result from two databases e.g. car and ccm:
Example :
run sql car, ccm select finalCalledPartyUnicodeLoginUserID, labelascii
, from car:tbl_billing_data, ccm:devicenumplanmap
But unfortunately it did not work, Do you have any idea of how does it work?
Taher,
DeleteI do not believe this is possible.
-Bill (@ucguerrilla)
Hello William, I would like to get call participants(callingpartynumber finalcalledpartynumber) via axl soap . The parameter that i want to pass to query is Call Id from Sip Message.Sip Message -> Message Header -> Call Id .
ReplyDeleteIs there any solution/query that can solve this?
Thanks for the response!
AXL/SOAP isn't going to expose the CDR tables. You need to leverage the CDROnDemand service API.
Deletehttp://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cucm/service/10_0_1/cdrdef/CUCM_BK_CBB143DE_00_cucm-cdr-administration-guide-100/CUCM_BK_CBB143DE_00_cucm-cdr-administration-guide-100_chapter_00.html#CUCM_RF_C19F5555_00
HTH
-Bill (@ucguerrilla)
Thanks for a helpful article. You can also use informix dbinfo('utc_to_datetime', dateTimeOrigination) function to get output datetime in human readable format.
ReplyDeleteThanks for this helpful information. I would like to ask if and how we can delete a row from the CAR database ? for example a call that has been placed and we would like to delete it.
ReplyDeleteMany thanks
Hi William, first of all thanks for this,i'm new to CUCM and your tutorials saved me. I'm trying to generate a report out of the data i'm getting, is there to export this info to another file,or maybe another to query the db so i can generate reportable data?
ReplyDeleteHello,
ReplyDeleteHow do you pull call signaling and RTP information of completed and failed calls?
Regards
Kasem,
DeleteYou first have to identify a "failed" call by looking at the originating party and/or destination party cause codes. You'll need to identify the cause codes that you consider a failure. Any queries should filter on those target values.
You may also be interested in enabling the option to log zero duration calls to the CDR database. Otherwise, the UCM won't record the call details.
CDR Config info: http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cucm/service/11_5_1/car/CUCM_BK_C72B9385_00_cdr-analysis-and-reporting_1151/CUCM_BK_C72B9385_00_cdr-analysis-and-reporting_1151_chapter_010.html#CUCM_RF_C0838F29_00
Cause Codes: http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cucm/service/11_5_1/car/CUCM_BK_C72B9385_00_cdr-analysis-and-reporting_1151/CUCM_BK_C72B9385_00_cdr-analysis-and-reporting_1151_chapter_011010.html#CUCM_TK_G94FC472_00
HTH
-Bill
I have exported all my cdr records into a sql database for reporting. I need to capture all legs of a transferred call. According to Cisco documentation 'For the consultation call, the origCause_value and destCause_value fields get set to split = 393216, which indicates that the call was split. The origCallTerminationOnBehalfOf and destCallTerminationOnBehalfOf fields get set to Transfer = 10 to indicate that this call was involved in a transfer.
ReplyDeleteFor the final transferred call, the joinOnBehalfOf field gets set to Transfer = 10 to indicate that this call resulted from a transfer.' However this doesn't give me all legs. Consultation calls don't usually have the same globalCallID_callId. Have you resolved this issue? Ultimately I want to capture the 'set' of legs and try to match it to our PSTN SIP provider to validate charges. below is my sql for capturing transferred calls. select
globalCallId_ClusterID
, globalCallID_callId
, origLegCallIdentifier
, destLegIdentifier
, callingPartyNumber
, originalCalledPartyNumber
, finalCalledPartyNumber
, lastRedirectDn
, origCause_Value
, destCause_Value
, origCallTerminationOnBehalfOf
, destCallTerminationOnBehalfOf
, joinOnBehalfOf
duration
from CDR.dbo.FullCDR
where globalCallId_ClusterID='C-SWC-CL1'
and Calc_dateTimeOrigination BETWEEN '2018-01-12 20:10:00' AND '2018-01-12 20:16:00'
-- AND globalCallID_callManagerId = 1
and globalCallID_callId=117108
--and Duration/60 > 0
and ( (origCause_value = 393216 and destCause_value = 393216 and origCallTerminationOnBehalfOf =10 and destCallTerminationOnBehalfOf =10 )
or joinOnBehalfOf = 10)
ORDER BY globalCallID_callId , globalcallid_callmanagerid, Calc_dateTimeOrigination
Hey Bill,
ReplyDeleteI know that this comment is years down the line from when the post was first created but I find that your site comes up in search results quite a bit, so maybe it will still help someone out.
To convert epoch to human readable time directly on the CLI, you can just run:
run sql select dbinfo("utc_to_datetime", [column_containing_epoch_time])from [table]
Nice tip. Thanks!!
Delete