International CDR Report from CUCM CLI

By using SQL query we would be able to generate CDR details from CUCM CLI

Here is the query for international calls

Partition= here you should have to mention your international partition

eg : -

run sql select datetimestampconnect,datetimestampdisconnect,duration,callingPartyNumber,originalCalledPartyNumber from car: tbl_billing_data where finalCalledPartyNumberPartition='PT_Int' and datetimestamporigination between '2011-11-21 00:00:00' and '2011-11-26 23:59:59'

Here are the few more queries from CM 


run sql select datetimestampconnect,datetimestampdisconnect,duration,callingPartyNumber,originalCalledPartyNumber from car: tbl_billing_data where datetimestamporigination between '2012-05-01 00:00:00' and '2012-06-02 00:00:00'

 =>This query will give you the details about call disconnection. run sql select 

run sql select datetimestampconnect,datetimestampdisconnect,duration,callingPartyNumber,origCause_value,originalCalledPartyNumber,destCause_value from car: tbl_billing_data where datetimestamporigination between '2012-03-30 17:20:00' and '2012-03-30 17:35:00'


 run sql select * from car:tbl_billing_data

run sql select * from car:tbl_billing_data where datetimestampconnect > '2011-04-15 11:02:25'

run sql select * from car:tbl_billing_data where originalcalledpartynumber like '%2966'

SQL CDR reporting in CUCM ver 8:

https://supportforums.cisco.com/thread/2087799

There are changes in the DB table. new format is given below

run sql car select call_type,callingpartynumber, originalcalledpartynumber, datetimestamporigination from car:tbl_billing_data where originalcalledpartynumber in ("911")

run sql car select * from car:tbl_billing_data where originalcalledpartynumber like '%2966'

https://community.cisco.com/t5/ip-telephony-and-phones/car-database-tables-in-cucm-10-5/td-p/3210520

UCCX SQL access for third party application

Open the command line on your UCCX server and do the following:

Step 1: -

C:\>osql -L

Servers:

(local)\CRSSQL

Step 2: -

Connect to MSDE using Local account

osql -E -S SERVERNAME\CRSSQL

Step 3: -

List the current logins in SQL

exec sp_helplogins

go

Step 4: -

Create a user account and grant access to db_cra database:

use db_cra

go

exec sp_addlogin 'username','password'

go

exec sp_grantdbaccess 'username'

go

exec sp_addrolemember 'db_wallboard_read','username'

go

Eg:-

1> exec sp_addrolemember 'db_datareader','Muttil'

2> go

'Muttil' added to role 'db_datareader'.

Cisco IP Phone - SSH login

Step 1: go to Device phone configuration page and configure the SSH login user name and password and reset the phone.

Step 2: SSH to the phone using that username and password.
It would prompt again. Enter user debug and password debug

· show command and ” ? “ will give you entire command details

Sample Commands

clear call history

show brightness

show call-history summary

show config network

show cpu

show power

show stream active all

erase all settings

reset factory

show stream active all

Troubleshooting

If you are not able to login SSH , download the cnf.xml file and check the config file.
Check if the user name and password is updated on the cnf.xml
Here is the cmd for that : tftp 172.22.32.12 GET SEPFCFBFBCA1B1B.cnf.xml

SQL Query to check SSH enabled or not

run sql SELECT Device.name,TypeModel.name,"SSH Enabled" FROM Device,TypeModel,DeviceXml4k WHERE DeviceXml4k.fkDevice=Device.pkid AND Device.tkModel=TypeModel.enum AND xml LIKE "%0%"

CAD Logs

Farzan.SKSocket[addr=10.91.80.162,port=1736,localport=43027] timed out or stuck so closed.

%MIVR-ICD_CTI-3-CLIENT_SOCKET_CLOSED:Client connection

ADOutOfServiceMsg

Reason code:32765

DeviceOutOfServiceMsg (Rsrc:Matiuj.Zaman,Reason:DEVICE_UNREGISTERED)

128210861: Aug 16 14:01:00.095 EDT %MIVR-SS_RM-7-UNK:Rsrc: Matiuj.Zaman New State:UNAVAILABLE Old State:UNAVAILABLE Reason code:32759

PING_INFO:Server pinging client for info.: trace=Ping results to client: 10.91.80.184