02-07-2012 01:31 PM - edited 03-16-2019 09:27 AM
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.
02-07-2012 06:38 PM
You can just use CAR for that
HTH
java
If this helps, please rate
www.cisco.com/go/pdihelpdesk
02-08-2012 07:00 AM
What is CAR ? how do i use it to find out called details,
02-08-2012 07:07 AM
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
02-07-2012 07:28 PM
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!!
02-08-2012 07:02 AM
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.
02-08-2012 07:16 AM
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
02-08-2012 07:36 AM
i have accessed PUB throgh putty and copy/paste your command from Admin: im still getting syntax error,
02-08-2012 07:38 AM
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
02-08-2012 07:55 AM
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
02-08-2012 04:24 PM
do you have CAR service running on the server??
02-08-2012 06:21 PM
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
Please remember to rate helpful responses and identify
02-09-2012 07:15 AM
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.
02-21-2012 02:44 PM
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
Please remember to rate helpful responses and identify
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: