CUCM 7.1.3 sql script

[m.ateeque]

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.

[Jaime Valencia]

You can just use CAR for that

HTH

java

If this helps, please rate

www.cisco.com/go/pdihelpdesk

[m.ateeque]

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

[Jaime Valencia]

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

[Vipul Jindal]

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!!

[m.ateeque]

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.

[Vipul Jindal]

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

[m.ateeque]

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

[Vipul Jindal]

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

[m.ateeque]

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

[Vipul Jindal]

do you have CAR service running on the server??

[William Bell]

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

[m.ateeque]

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.

[William Bell]

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