<div dir="ltr"><div><div>Hi Erick,<br><br>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". <br>
<br></div>7x and lower it was like this: <b>run sql select colname from car:tablename</b><br></div>8x and higher it is like this: <b>run sql car select colname from tablename</b><br><div><div><div><br>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.<br>
<br>You will need to SSH into the Publisher node, where the CDR database is held, and then run your queries there.<br><br>The following command and output is an example of one type of query<br><br><div style="margin-left:40px">
<span style="font-family:courier new,monospace">admin:run sql car select datetimestampconnect, duration, callingpartynumber, finalcalledpartynumber from tbl_billing_data where callingpartynumber = '16125551212'</span><br>
<span style="font-family:courier new,monospace">datetimestampconnect duration callingpartynumber finalcalledpartynumber</span><br><span style="font-family:courier new,monospace">===================== ======== ================== ======================</span><br>
<span style="font-family:courier new,monospace">2013-09-16 10:30:31.0 40 16125551212 16125551313</span><br><span style="font-family:courier new,monospace">2013-09-16 11:16:13.0 46 16125551212 16125551414</span><br>
<span style="font-family:courier new,monospace">2013-09-17 13:16:29.0 170 16125551212 16125551515</span><br><span style="font-family:courier new,monospace">admin:</span><br></div><br>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.<br>
<br>There are some other interesting columns you can use in your output, search filtering, and sorting, and those are documented here:<br><a href="http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/8_6_1/cdrdef/cdrfdes.html">http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/8_6_1/cdrdef/cdrfdes.html</a><br>
<br>Some of the ones I frequently use are:<br><ul><li>datetimestamporigination</li><ul><li>Sometimes the call does not connect, so you need to know when it was attempted</li></ul><li>datetimestampconnect</li><ul><li>Interestingly, the time difference between this one and the origination is the ring duration of the call</li>
</ul><li>duration</li><ul><li>The duration of the call</li></ul><li>callingpartynumber</li><ul><li>The calling party number (but some how I think you knew that)</li></ul><li>origdevicename</li><ul><li>The phone, trunk, or gateway which placed the call</li>
</ul><li>origipv4v6addr</li><ul><li>The ip address in dotted decimal notation of the calling device (phone/trunk/gateway)</li></ul><li>origcause_value</li><ul><li>This is the reason code the call disconnected, if it was disconnected from the calling party side</li>
</ul><li>originalcalledpartynumber</li><ul><li>The number dialed by the caller</li></ul><li>finalcalledpartynumber</li><ul><li>The number the caller was actually connected to (takes into consideration forwarding)</li></ul>
<li>destdevicename</li><ul><li>The phone, trunk or gateway which received the call</li></ul><li>destipv4v6addr</li><ul><li>The ip address in dotted decimal notation of the called device (phone/trunk/gateway)</li></ul><li>
destcause_value</li><ul><li>This is the reason code the call disconnected, if it was disconnected from the called party side</li></ul></ul><br>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<br>
<br><div style="margin-left:40px"><span style="font-family:courier new,monospace">admin:run sql car select datetimestampconnect, duration, callingpartynumber, finalcalledpartynumber from tbl_billing_data where callingpartynumber = '16125551212' and date(datetimestamporigination) = today</span><br>
<span style="font-family:courier new,monospace">datetimestampconnect duration callingpartynumber finalcalledpartynumber</span><br><span style="font-family:courier new,monospace">===================== ======== ================== ======================</span><br>
<span style="font-family:courier new,monospace">2013-09-17 10:49:24.0 939 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551313</span><br>
<span style="font-family:courier new,monospace">admin:</span><br></div><br>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)<br>
<br><div style="margin-left:40px"><span style="font-family:courier new,monospace">admin:run sql car select datetimestampconnect, duration, callingpartynumber, finalcalledpartynumber from tbl_billing_data where callingpartynumber = '16125551212' and date(datetimestamporigination) = '2013-09-16'</span><br>
<span style="font-family:courier new,monospace">datetimestampconnect duration callingpartynumber finalcalledpartynumber</span><br><span style="font-family:courier new,monospace">===================== ======== ================== ======================</span><br>
<span style="font-family:courier new,monospace">2013-09-16 13:01:26.0 1853 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551313</span><br>
<span style="font-family:courier new,monospace">2013-09-16 13:32:31.0 802 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551414</span><br>
</div><br>Or if you needed a date range<br><br><div style="margin-left:40px"><span style="font-family:courier new,monospace">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'</span><br>
<span style="font-family:courier new,monospace">datetimestampconnect duration callingpartynumber finalcalledpartynumber</span><br><span style="font-family:courier new,monospace">===================== ======== ================== ======================</span><br>
<span style="font-family:courier new,monospace">2013-09-10 17:05:11.0 95 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551313</span><br>
<span style="font-family:courier new,monospace">2013-09-11 15:01:17.0 1128 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551414</span><br>
<span style="font-family:courier new,monospace">2013-09-13 11:00:58.0 945 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551515</span><br>
<span style="font-family:courier new,monospace">admin:</span><br></div><br>And with date ranges, feel free to use the today keyword in the upper date value if needed<br><br><div style="margin-left:40px"><span style="font-family:courier new,monospace">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</span><br>
<span style="font-family:courier new,monospace">datetimestampconnect duration callingpartynumber finalcalledpartynumber</span><br><span style="font-family:courier new,monospace">===================== ======== ================== ======================</span><br>
<span style="font-family:courier new,monospace">2013-09-10 17:05:11.0 95 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551313</span><br>
<span style="font-family:courier new,monospace">2013-09-11 15:01:17.0 1128 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551414</span><br>
<span style="font-family:courier new,monospace">2013-09-13 11:00:58.0 945 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551515</span><br>
<span style="font-family:courier new,monospace">2013-09-16 13:01:26.0 1853 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551616</span><br>
<span style="font-family:courier new,monospace">2013-09-16 13:32:31.0 802 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551717</span><br>
<span style="font-family:courier new,monospace">2013-09-17 10:49:24.0 939 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551818</span><br>
<span style="font-family:courier new,monospace">admin:</span><br></div><br>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)<br><br><div style="margin-left:40px">
<span style="font-family:courier new,monospace">admin:run sql car select datetimestampconnect, duration, callingpartynumber, finalcalledpartynumber from tbl_billing_data where callingpartynumber = '16125551212' and date(datetimestamporigination) >= today-3</span><br>
<span style="font-family:courier new,monospace">datetimestampconnect duration callingpartynumber finalcalledpartynumber</span><br><span style="font-family:courier new,monospace">===================== ======== ================== ======================</span><br>
<span style="font-family:courier new,monospace">2013-09-16 13:01:26.0 1853 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551313</span><br>
<span style="font-family:courier new,monospace">2013-09-16 13:32:31.0 802 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551414</span><br>
<span style="font-family:courier new,monospace">2013-09-17 10:49:24.0 939 1</span><span style="font-family:courier new,monospace"><span style="font-family:courier new,monospace">6125551212</span> 16125551515</span><br>
<span style="font-family:courier new,monospace">admin:</span><br></div><br>Some other documents you may find interesting on CDR:<br><ul><li>Understanding CDR: <a href="https://supportforums.cisco.com/docs/DOC-13842">https://supportforums.cisco.com/docs/DOC-13842</a></li>
<li>Troubleshooting CDR: <a href="https://supportforums.cisco.com/docs/DOC-14548">https://supportforums.cisco.com/docs/DOC-14548</a></li></ul>I hope someone found this useful/interesting/helpful/etc.<br></div></div></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Sep 17, 2013 at 10:25 AM, Erick Wellnitz <span dir="ltr"><<a href="mailto:ewellnitzvoip@gmail.com" target="_blank">ewellnitzvoip@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Nevermind. I found the correct syntax.</div><div class="HOEnZb"><div class="h5"><div class="gmail_extra">
<br><br><div class="gmail_quote">On Tue, Sep 17, 2013 at 10:21 AM, Erick Wellnitz <span dir="ltr"><<a href="mailto:ewellnitzvoip@gmail.com" target="_blank">ewellnitzvoip@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Has the syntax changed between 8.x and 9.x for running sql queries against the CAR database?</div>
</blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
cisco-voip mailing list<br>
<a href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</a><br>
<a href="https://puck.nether.net/mailman/listinfo/cisco-voip" target="_blank">https://puck.nether.net/mailman/listinfo/cisco-voip</a><br>
<br></blockquote></div><br></div>