Monday, March 26, 2012

CUCM SQL Queries: CDR Analysis (CAR)

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).

Primer

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:
  1. Activate the CDR Analysis and Reporting (CAR) service on the CUCM publisher node.
  2. 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
Fortunately, the customer had activated CAR and had enabled the appropriate parameters on CUCM. 
    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:
    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

    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!








    13 comments:

    1. Great job on this series, this is kind of a "forgot" topic, until the day someone ask you something.. lol..

      Questions:

      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.

      ReplyDelete
      Replies
      1. Bruno,

        Thanks 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

        Delete
    2. First of all, great site with lots of helpful information.

      I'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

      ReplyDelete
      Replies
      1. Greg,

        The 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

        Delete
    3. Dear William,
      I 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?

      ReplyDelete
      Replies
      1. Taher,

        I do not believe this is possible.

        -Bill (@ucguerrilla)

        Delete
    4. 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 .
      Is there any solution/query that can solve this?
      Thanks for the response!

      ReplyDelete
      Replies
      1. AXL/SOAP isn't going to expose the CDR tables. You need to leverage the CDROnDemand service API.

        http://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)

        Delete
    5. Thanks for a helpful article. You can also use informix dbinfo('utc_to_datetime', dateTimeOrigination) function to get output datetime in human readable format.

      ReplyDelete
    6. Thanks 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.


      Many thanks

      ReplyDelete
    7. 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?

      ReplyDelete
    8. Hello,
      How do you pull call signaling and RTP information of completed and failed calls?
      Regards

      ReplyDelete
      Replies
      1. Kasem,

        You 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


        Delete