cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
2510
Views
20
Helpful
13
Replies

CUCM 7.1.3 sql script

m.ateeque
Level 1
Level 1

is there a way to get a  list of calls made for a particular number i.e 911 etc. we have 6 sites using MGCP on voice gateways, reading from various  blogs i understood we can use run sql command on pub/sub to send a query and get results, apprecaite if anyone on these forum can point me into right direction or a sample script will be helpful.

13 Replies 13

Jaime Valencia
Cisco Employee
Cisco Employee

You can just use CAR for that

HTH

java

If this helps, please rate

www.cisco.com/go/pdihelpdesk

HTH

java

if this helps, please rate

What is CAR ? how do i use it to find out called details,

Cisco Unified CDR Analysis and Reporting  Administration Guide, Release 7.1(2)

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_1_2/car/CAR.html

HTH

java

If this helps, please rate

www.cisco.com/go/pdihelpdesk

HTH

java

if this helps, please rate

Vipul Jindal
Cisco Employee
Cisco Employee

try the below on CLI of call manager

run sql car select callingpartynumber,originalcalledpartynumber,finalcalledpartynumber,datetimestamporigination from tbl_billing_data where originalcalledpartynumber IN('911','9911') or finalcalledpartynumber IN ('911','9911')

thanks,

Vipul Jindal

if this helps, please rate!!

Vipul, im  getting syntax error, runnign query from pub

admin:run sql car select callingpartynumber,originalcalledpartynumber,finalcalldpartynumber,datetimestamporigination from tbl_billing_data where originalcallepartynumber IN('911','9911') or finalcalledpartynumber IN ('911','9911')

A syntax error has occurred.

try this one

run sql car select callingpartynumber,originalcalledpartynumber,finalcalledpartynumber, datetimeorigination from tbl_billing_data where originalcalledpartynumber IN ('911','9911') or finalcalledpartynumber IN ('911','9911')

thanks,

Vipul Jindal

i have accessed PUB throgh putty and copy/paste your command from Admin: im still getting syntax error,

dont copy/paste it..write it down...also check if the car service is running on the server or not!!

i tried the same in my lab and it worked!!

thanks,

Vipul Jindal

thanks for thequick update, these time i did type the entire command, please see below details.

admin:run sql car select callingpartynumber,originalcalledpartynumber,finalcalledpartynumber, datetimeorigination from tbl_billing_data where originalcalledpartynumber IN ('911','9911') or finalcalledpartynumber IN ('911','9911')
A syntax error has occurred.
admin:run sql car select ?
Syntax:
run sql sql_statement
sql_statement mandatory  the sql command to run

do you have CAR service running on the server??

I think the issue is that Vipul is running his query on a CUCM 8.x system the OP has a CUCM 7.1.3 system. At least, according the title of the thread it is 7.1.3.

In 7.1.3, you would use this query:

admin:run sql select callingpartynumber,originalcalledpartynumber,finalcalledpartynumber,datetimeorigination from car:tbl_billing_data where originalcalledpartynumber IN ('911','9911') or finalcalledpartynumber IN ('911','9911')

HTH.

Regards,

Bill

please rate helpful posts

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Thanks much Bill,  i really appreciate your help, i was able to run your script succesfully, with  one obstacle datetime is appearing as " 1323803298" i tried converting file to .csv import in excel, didn't work,  yes as the title of these discussion i did mentioned cucm version 7.1.3, bill,  thanks for amazing informatic articles...great work, finally where can i find the  "callingpartynumber originalcalledpartynumber finalcalledpartynumber datetimeorigination" etc.  if i need to trim or include few other fields in my query.

Yes, date/time will appear as epoch time. In Excel, you may be able to use this formula to convert the data value. Add a new column, insert the formula in one of the cells and paste down.

*=(((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).

In regards to what fields are in the CDR table, see the following:

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_1_2/cdrdef/cdrfdes.html

HTH.

Regards,

Bill

please rate helpful posts

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: