CUCM disconnects and CDR SQL

Please see this post for original content: http://smbitsolutions.wordpress.com/2010/06/11/dropped-call-troubleshooting-in-cucm/

Call disconnect CDR cause codes can be found here: http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_0_1/car/carsrch.html#wp1085459

For datetimeorigination conversions, use: http://www.onlineconversion.com/unix_time.htm

My customer's complaint was that user at DN 1129 called DN 1194 and was immediately disconnected.  The user at 1129 then called 1194 back and was able to complete the call normally.  The users were power users and their descriptions of their symptoms weren't doubted.

They have an ISI call accounting system that was capable of supplying a report verifying 2 calls were made between these lines as described, and the disconnecting cause code was 16 (normal).

A little CUCM investigation showed that DN 1194 actually existed on 2 devices, only one of which is typically used.  It was the second DN on another device in a remote maintenance area used occasionally by IT vendors.

I imagine the CUCM administrator may have been able to decipher this via the ISI reports, but they asked me to investigate the disconnect report (without access to ISI).  I suspected the issue may simply be user error by the IT vendor.

Assuming originator is DN 1129 on SEP001794668C3D, disconnect code is 16, and destination is DN 1194 on SEP0017596ECEEA (good user):

run sql select datetimeorigination,callingpartynumber,origdevicename,destdevicename,finalcalledpartynumber,orignodeid,origlegcallidentifier,destnodeid,destlegidentifier from car:tbl_billing_data where destcause_value=16 and origdevicename='SEP001794668C3D' and destdevicename='SEP0017596ECEEA'

returns:

datetimeorigination callingpartynumber origdevicename destdevicename finalcalledpartynumber orignodeid origlegcallidentifier destnodeid destlegidentifier
=================== ================== =============== =============== ====================== ========== ===================== ========== =================
1307710272 1129 SEP0017946683DC SEP0017596ECEEA 1194 2 44890976 2 44890977

The 1307710272 time converts to Fri, 10 Jun 2011 12:51:12 UTC, one completed call in question.

Assuming originator is DN 1129 on SEP0017946683DC, disconnect code is 16, and destination is DN 1194 on SEP40F4ECEFAE38 (IT vendor device):

run sql select datetimeorigination,callingpartynumber,origdevicename,destdevicename,finalcalledpartynumber,orignodeid,origlegcallidentifier,destnodeid,destlegidentifier from car:tbl_billing_data where destcause_value=16 and origdevicename='SEP0017946683DC' and destdevicename='SEP40F4ECEFAE38'

datetimeorigination callingpartynumber origdevicename destdevicename finalcalledpartynumber orignodeid origlegcallidentifier destnodeid destlegidentifier
=================== ================== =============== =============== ====================== ========== ===================== ========== =================
1307710258 1129 SEP0017946683DC SEP40F4ECEFAE38 1194 2 44890956 2 44890957

The 1307710258 time converts to Fri, 10 Jun 2011 12:50:58 UTC, the one disconnected call in question.

Other interesting CDR SQL:
!

! identify common disconnect cause codes
! by originating cause
!
run sql select count(*) as count,origcause_value from car:tbl_billing_data where origcause_value not in (0,16,126,128,393216,458752) group by origcause_value order by count asc
!
! identify common disconnect cause codes
! by destination cause
!
run sql select count(*) as count,destcause_value from car:tbl_billing_data where destcause_value not in (0,16,126,128,393216,458752) group by destcause_value order by count asc
!
! identify endpoints associated with disconnect cause codes
!
run sql select count(*) as count,origdevicename from car:tbl_billing_data where origcause_value = 47 group by origdevicename order by count asc