Query database for CDR

Version 1
    This document was generated from CDN thread

    Created by: Joel Paulraj on 09-04-2009 08:45:46 PM
    I am trying to query the CallManager 6.0 database to get CDR details.
     
    I am able to SSH into the server and run query like this.
     
    run sql select callingpartynumber, originalcalledpartynumber, finalcalledpartynumber, duration, datetimestamporigination, datetimestampdisconnect from car:tbl_billing_data where duration > 100 >
     
    How can I do this externally and get the output in a file.
     
    AXL test using Telnet never works for me. I tried doing the AXL test using CURL and it works for select * from device or numplan, but does not work for car:billing_data
     
    Thanks in adv,
    Joel
     

    Subject: RE: Query database for CDR
    Replied by: Mayank Sharma on 10-04-2009 03:58:57 PM
    There is no database as such for the CDRs for this version of call manager. All the CDR data is managed in flat files. You have to specify a billing server (an SFTP server) in your CUCM Admin Interface which will then send CDR flat files to that server. You can then process these flat files as you like.
     
    Hope this helps.
    Max

    Subject: RE: Query database for CDR
    Replied by: Joel Paulraj on 20-04-2009 04:17:33 PM
    thanks for the response..
     
    I am able to run a query like this and get an output on the screen, is there a way to write this to a file or to run this externally?
     
    run sql select callingpartynumber, originalcalledpartynumber, finalcalledpartynumber, duration, datetimestamporigination, datetimestampdisconnect from car:tbl_billing_data where duration > 100 >

    thanks in adv,
    joel

    Subject: RE: Query database for CDR
    Replied by: David Staudt on 20-04-2009 05:40:40 PM
    The CAR application is an on-board solution for doing some CDR manipulation and reporting.  It obtains flat files from UCM and stores processed records in its internal database - the car.* tables it looks like you're querying below from the CLI.
     
    These tables are intended for use by the CAR application only, and cannot be accessed via AXL or any other external means.  As above, the designated mechanism for getting CDR info is to configure the application as a CDR FTP push target, to which the UCM will periodically deliver flat-file (comma delimited) CDR records.

    Subject: RE: Query database for CDR
    Replied by: Joel Paulraj on 20-04-2009 07:36:11 PM
    The CAR application is an on-board solution for doing some CDR manipulation and reporting.  It obtains flat files from UCM and stores processed records in its internal database - the car.* tables it looks like you're querying below from the CLI.
     
    These tables are intended for use by the CAR application only, and cannot be accessed via AXL or any other external means.  As above, the designated mechanism for getting CDR info is to configure the application as a CDR FTP push target, to which the UCM will periodically deliver flat-file (comma delimited) CDR records.


     
     
    thanks for the clarification David...
     
    Without having a 3rd party billing server, these files seem useless.
     
    If I do a CDR search and do a Send Report the report is completely UNreadable.
     
    Is there another way for me to obtain these reports without having to buy a 3rd party billing server.
     
    thanks,
    joel

    Subject: RE: Query database for CDR
    Replied by: David Staudt on 20-04-2009 08:29:45 PM
    CAR is intended to provide some built-in billing/reporting functionality - I'm not really familiar with what it can do and how to make the most of it.  I'm sure there is plenty of documentation on Cisco.com.
     
    Otherwise you can obtain very sophisiticated/flexible dedicated billing servers from 3rd parties, or manipulate the data on your own (recieve the CSV files, import into a database, build reports.)