[cisco-voip] CUCM 9.1 CAR database sql
Anthony Holloway
avholloway+cisco-voip at gmail.com
Tue Sep 17 12:11:55 EDT 2013
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/16b69576/attachment.html>
More information about the cisco-voip
mailing list