Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

CUCM 7.1.3 sql script

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.

Everyone's tags (4)
13 REPLIES
Cisco Employee

CUCM 7.1.3 sql script

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

www.cisco.com/go/pdi
New Member

CUCM 7.1.3 sql script

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

Cisco Employee

CUCM 7.1.3 sql script

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

www.cisco.com/go/pdi
Cisco Employee

CUCM 7.1.3 sql script

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

New Member

CUCM 7.1.3 sql script

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.

Cisco Employee

CUCM 7.1.3 sql script

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

New Member

CUCM 7.1.3 sql script

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

Cisco Employee

CUCM 7.1.3 sql script

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

New Member

CUCM 7.1.3 sql script

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

Cisco Employee

CUCM 7.1.3 sql script

do you have CAR service running on the server??

CUCM 7.1.3 sql script

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

New Member

Re: CUCM 7.1.3 sql script

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.

CUCM 7.1.3 sql script

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

1542
Views
20
Helpful
13
Replies
CreatePlease to create content