[cisco-voip] CUCM 9.1 CAR database sql
Erick Wellnitz
ewellnitzvoip at gmail.com
Tue Sep 17 12:34:09 EDT 2013
The document I was reading still referenced the old way for 8.x but I was
able to find a few emails Wes and I had exchanged on the topic a few years
back
On Tue, Sep 17, 2013 at 11:11 AM, Anthony Holloway <
avholloway+cisco-voip at gmail.com> wrote:
> Hi Erick,
>
> I'm glad to hear you were able to figure it out on your own. CUCM 8x and
> 9x have not changed in the way you query CDR from the command line, but
> there was a change from CUCM 7x to 8x. It was how you referenced the
> database name: "CAR".
>
> 7x and lower it was like this: *run sql select colname from car:tablename*
> 8x and higher it is like this: *run sql car select colname from tablename
> *
>
> I also just wanted to drop a quick note on how to quickly query CDR on the
> command line interface, without having to go through the bloat of the CAR
> web console. This is the for benefit of anyone who read your post today,
> and either didn't know it was possible, or kind of knew, but needed a
> little refresher.
>
> You will need to SSH into the Publisher node, where the CDR database is
> held, and then run your queries there.
>
> The following command and output is an example of one type of query
>
> admin:run sql car select datetimestampconnect, duration,
> callingpartynumber, finalcalledpartynumber from tbl_billing_data where
> callingpartynumber = '16125551212'
> datetimestampconnect duration callingpartynumber finalcalledpartynumber
> ===================== ======== ================== ======================
> 2013-09-16 10:30:31.0 40 16125551212 16125551313
> 2013-09-16 11:16:13.0 46 16125551212 16125551414
> 2013-09-17 13:16:29.0 170 16125551212 16125551515
> admin:
>
> I like this CLI approach because it’s quick and I can be descriptive about
> my query very easily all while limiting the output to only those columns I
> care about.
>
> There are some other interesting columns you can use in your output,
> search filtering, and sorting, and those are documented here:
>
> http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/8_6_1/cdrdef/cdrfdes.html
>
> Some of the ones I frequently use are:
>
> - datetimestamporigination
> - Sometimes the call does not connect, so you need to know when it
> was attempted
> - datetimestampconnect
> - Interestingly, the time difference between this one and the
> origination is the ring duration of the call
> - duration
> - The duration of the call
> - callingpartynumber
> - The calling party number (but some how I think you knew that)
> - origdevicename
> - The phone, trunk, or gateway which placed the call
> - origipv4v6addr
> - The ip address in dotted decimal notation of the calling device
> (phone/trunk/gateway)
> - origcause_value
> - This is the reason code the call disconnected, if it was
> disconnected from the calling party side
> - originalcalledpartynumber
> - The number dialed by the caller
> - finalcalledpartynumber
> - The number the caller was actually connected to (takes into
> consideration forwarding)
> - destdevicename
> - The phone, trunk or gateway which received the call
> - destipv4v6addr
> - The ip address in dotted decimal notation of the called device
> (phone/trunk/gateway)
> - destcause_value
> - This is the reason code the call disconnected, if it was
> disconnected from the called party side
>
>
> Knowing that there’s already a datetime column for these date/time fields
> lends itself to a neat trick using a where clause which limits the result
> set to today’s records only
>
> admin:run sql car select datetimestampconnect, duration,
> callingpartynumber, finalcalledpartynumber from tbl_billing_data where
> callingpartynumber = '16125551212' and date(datetimestamporigination) =
> today
> datetimestampconnect duration callingpartynumber finalcalledpartynumber
> ===================== ======== ================== ======================
> 2013-09-17 10:49:24.0 939 16125551212 16125551313
> admin:
>
> Or if you need a specific date in the past you could do this, albeit not
> as clean as the keyword based “today” limit, because you have to now the
> format (YYYY-MM-DD)
>
> admin:run sql car select datetimestampconnect, duration,
> callingpartynumber, finalcalledpartynumber from tbl_billing_data where
> callingpartynumber = '16125551212' and date(datetimestamporigination) =
> '2013-09-16'
> datetimestampconnect duration callingpartynumber finalcalledpartynumber
> ===================== ======== ================== ======================
> 2013-09-16 13:01:26.0 1853 16125551212 16125551313
> 2013-09-16 13:32:31.0 802 16125551212 16125551414
>
> Or if you needed a date range
>
> admin:run sql car select datetimestampconnect, duration,
> callingpartynumber, finalcalledpartynumber from tbl_billing_data where
> callingpartynumber = '16125551212' and date(datetimestamporigination)
> between '2013-09-10' and '2013-09-14'
> datetimestampconnect duration callingpartynumber finalcalledpartynumber
> ===================== ======== ================== ======================
> 2013-09-10 17:05:11.0 95 16125551212 16125551313
> 2013-09-11 15:01:17.0 1128 16125551212 16125551414
> 2013-09-13 11:00:58.0 945 16125551212 16125551515
> admin:
>
> And with date ranges, feel free to use the today keyword in the upper date
> value if needed
>
> admin:run sql car select datetimestampconnect, duration,
> callingpartynumber, finalcalledpartynumber from tbl_billing_data where
> callingpartynumber = '16125551212' and date(datetimestamporigination)
> between '2013-09-10' and today
> datetimestampconnect duration callingpartynumber finalcalledpartynumber
> ===================== ======== ================== ======================
> 2013-09-10 17:05:11.0 95 16125551212 16125551313
> 2013-09-11 15:01:17.0 1128 16125551212 16125551414
> 2013-09-13 11:00:58.0 945 16125551212 16125551515
> 2013-09-16 13:01:26.0 1853 16125551212 16125551616
> 2013-09-16 13:32:31.0 802 16125551212 16125551717
> 2013-09-17 10:49:24.0 939 16125551212 16125551818
> admin:
>
> Or, as my last example, if you’re just needing the last N number of days,
> you can also do this (E.g., last 3 days)
>
> admin:run sql car select datetimestampconnect, duration,
> callingpartynumber, finalcalledpartynumber from tbl_billing_data where
> callingpartynumber = '16125551212' and date(datetimestamporigination) >=
> today-3
> datetimestampconnect duration callingpartynumber finalcalledpartynumber
> ===================== ======== ================== ======================
> 2013-09-16 13:01:26.0 1853 16125551212 16125551313
> 2013-09-16 13:32:31.0 802 16125551212 16125551414
> 2013-09-17 10:49:24.0 939 16125551212 16125551515
> admin:
>
> Some other documents you may find interesting on CDR:
>
> - Understanding CDR: https://supportforums.cisco.com/docs/DOC-13842
> - Troubleshooting CDR: https://supportforums.cisco.com/docs/DOC-14548
>
> I hope someone found this useful/interesting/helpful/etc.
>
>
> On Tue, Sep 17, 2013 at 10:25 AM, Erick Wellnitz <ewellnitzvoip at gmail.com>wrote:
>
>> Nevermind. I found the correct syntax.
>>
>>
>> On Tue, Sep 17, 2013 at 10:21 AM, Erick Wellnitz <ewellnitzvoip at gmail.com
>> > wrote:
>>
>>> Has the syntax changed between 8.x and 9.x for running sql queries
>>> against the CAR database?
>>>
>>
>>
>> _______________________________________________
>> cisco-voip mailing list
>> cisco-voip at puck.nether.net
>> https://puck.nether.net/mailman/listinfo/cisco-voip
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20130917/0b66eec3/attachment.html>
More information about the cisco-voip
mailing list