Join cdr and cmr records

[k.oldfield]

How do find the cmr records for a specific cdr record?

Given a cmr record, how do I find the corresponding cdr record?

I've loaded 12 months of cdr and cmr records from CUCM 6.1 into a database and now I'm having some problems correlating calls.

Here are some cdr and cmr joins I have looked at:

• cdr.globalcallid_callid = cmr.globalcallid_callid, but globalcallid_callid is not unique.
• cdr.origlegcallidentifier = cmr.callidentifier and/or cdr.destlegidentifier = cmr.callidentifier, same problem with not being unique.
• cdr.datetimeconnect = cmr.datetimestamp, these are often, but not always the same. Also, if 2 calls finish at the same time this won't work either.
  

[srsivara]

I was testing this on my 8.0.3 lab server -

Single call - from 8021006 to 8021011 :

globalCallID_callId - 6067

origLegCallIdentifier - 18783902 (callIdentifier in cmr)

destLegIdentifier - 18783903 (callIdentifier in cmr)

"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","origLegCallIdentifier","dateTimeOrigination","origNodeId","origSpan","origIpAddr","callingPartyNumber","callingPartyUnicodeLoginUserID","origCause_location","origCause_value","origPrecedenceLevel","origMediaTransportAddress_IP","origMediaTransportAddress_Port","origMediaCap_payloadCapability","origMediaCap_maxFramesPerPacket","origMediaCap_g723BitRate","origVideoCap_Codec","origVideoCap_Bandwidth","origVideoCap_Resolution","origVideoTransportAddress_IP","origVideoTransportAddress_Port","origRSVPAudioStat","origRSVPVideoStat","destLegIdentifier","destNodeId","destSpan","destIpAddr","originalCalledPartyNumber","finalCalledPartyNumber","finalCalledPartyUnicodeLoginUserID","destCause_location","destCause_value","destPrecedenceLevel","destMediaTransportAddress_IP","destMediaTransportAddress_Port","destMediaCap_payloadCapability","destMediaCap_maxFramesPerPacket","destMediaCap_g723BitRate","destVideoCap_Codec","destVideoCap_Bandwidth","destVideoCap_Resolution","destVideoTransportAddress_IP","destVideoTransportAddress_Port","destRSVPAudioStat","destRSVPVideoStat","dateTimeConnect","dateTimeDisconnect","lastRedirectDn","pkid","originalCalledPartyNumberPartition","callingPartyNumberPartition","finalCalledPartyNumberPartition","lastRedirectDnPartition","duration","origDeviceName","destDeviceName","origCallTerminationOnBehalfOf","destCallTerminationOnBehalfOf","origCalledPartyRedirectOnBehalfOf","lastRedirectRedirectOnBehalfOf","origCalledPartyRedirectReason","lastRedirectRedirectReason","destConversationId","globalCallId_ClusterID","joinOnBehalfOf","comment","authCodeDescription","authorizationLevel","clientMatterCode","origDTMFMethod","destDTMFMethod","callSecuredStatus","origConversationId","origMediaCap_Bandwidth","destMediaCap_Bandwidth","authorizationCodeValue","outpulsedCallingPartyNumber","outpulsedCalledPartyNumber","origIpv4v6Addr","destIpv4v6Addr","origVideoCap_Codec_Channel2","origVideoCap_Bandwidth_Channel2","origVideoCap_Resolution_Channel2","origVideoTransportAddress_IP_Channel2","origVideoTransportAddress_Port_Channel2","origVideoChannel_Role_Channel2","destVideoCap_Codec_Channel2","destVideoCap_Bandwidth_Channel2","destVideoCap_Resolution_Channel2","destVideoTransportAddress_IP_Channel2","destVideoTransportAddress_Port_Channel2","destVideoChannel_Role_Channel2","IncomingProtocolID","IncomingProtocolCallRef","OutgoingProtocolID","OutgoingProtocolCallRef","currentRoutingReason","origRoutingReason","lastRedirectingRoutingReason","huntPilotPartition","huntPilotDN"
1,1,6067,18783902,1295899028,1,0,404959246,"8021006","",0,0,4,404959246,28736,4,20,0,0,0,0,0,0,"0","0",18783903,1,0,186855438,"8021011","8021011","afrankel",0,16,4,186855438,18652,4,20,0,0,0,0,0,0,"0","0",1295899036,1295899058,"8021011","4fa940c0-01c4-4555-818a-2025de44d6aa","","","","",22,"SEP00179551EF24","SEP001E13E5BDA6",0,12,0,0,0,0,0,"StandAloneCluster",0,"","",0,"",3,3,0,0,64,64,"","123456","","14.48.35.24","14.48.35.11",0,0,0,0,0,0,0,0,0,0,0,0,0,"",0,"",0,0,0,"",""

"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","nodeId","directoryNum","callIdentifier","dateTimeStamp","numberPacketsSent","numberOctetsSent","numberPacketsReceived","numberOctetsReceived","numberPacketsLost","jitter","latency","pkid","directoryNumPartition","globalCallId_ClusterID","deviceName","varVQMetrics"
2,1,6067,1,"8021011",18783903,1295899059,1038,178536,1035,178020,0,0,0,"d7572b4e-0b37-4a71-9e48-c6339ef1696a","","StandAloneCluster","SEP001E13E5BDA6","MLQK=4.5000;MLQKav=4.4951;MLQKmn=4.4438;MLQKmx=4.5000;ICR=0.0000;CCR=0.0005;ICRmx=0.0035;CS=1;SCS=0;MLQKvr=0.95"
2,1,6067,1,"8021006",18783902,1295899059,1038,178536,1036,178192,0,0,0,"d2e31045-2a30-4156-86ed-1ba8b6a16299","","StandAloneCluster","SEP00179551EF24","MLQK=4.5000;MLQKav=4.5000;MLQKmn=4.5000;MLQKmx=4.5000;ICR=0.0000;CCR=0.0000;ICRmx=0.0000;CS=0;SCS=0;MLQKvr=0.95

The cdr.origLegCallIdentifier and cdr.destLegIdentifier should be unique - that's how we track calls in the CUCM traces.

What do you see in the records which indicate that the globalCallID_callId, origLegCallIdentifier and destLegIdentifier are not unique ?

- Sriram

[k.oldfield]

Looking at a specific example where globalcallid_callid is not unique.

cdr.globalcallid_callid=4316482 there are 5 cdr records.

3 of these records were from yesterday and appear to be from a call going through uccx. The origlegcallidentifier in all 3 cdr records is 105930488, while the destlegidentifier is different on each. If there are 3 cdr records with the same globalcallid_callid and origlegcallidentifier how am I supposed to work out which cdr record the cmr record relates to?

The other 2 calls were from 20 days ago and 3 months ago. Why are there calls with the same globalcallid_callid on completely different days?

"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","origLegCallIdentifier","dateTimeOrigination","origNodeId","origSpan","origIpAddr","callingPartyNumber","callingPartyUnicodeLoginUserID","origCause_location","origCause_value","origPrecedenceLevel","origMediaTransportAddress_IP","origMediaTransportAddress_Port","origMediaCap_payloadCapability","origMediaCap_maxFramesPerPacket","origMediaCap_g723BitRate","origVideoCap_Codec","origVideoCap_Bandwidth","origVideoCap_Resolution","origVideoTransportAddress_IP","origVideoTransportAddress_Port","origRSVPAudioStat","origRSVPVideoStat","destLegIdentifier","destNodeId","destSpan","destIpAddr","originalCalledPartyNumber","finalCalledPartyNumber","finalCalledPartyUnicodeLoginUserID","destCause_location","destCause_value","destPrecedenceLevel","destMediaTransportAddress_IP","destMediaTransportAddress_Port","destMediaCap_payloadCapability","destMediaCap_maxFramesPerPacket","destMediaCap_g723BitRate","destVideoCap_Codec","destVideoCap_Bandwidth","destVideoCap_Resolution","destVideoTransportAddress_IP","destVideoTransportAddress_Port","destRSVPAudioStat","destRSVPVideoStat","dateTimeConnect","dateTimeDisconnect","lastRedirectDn","pkid","originalCalledPartyNumberPartition","callingPartyNumberPartition","finalCalledPartyNumberPartition","lastRedirectDnPartition","duration","origDeviceName","destDeviceName","origCallTerminationOnBehalfOf","destCallTerminationOnBehalfOf","origCalledPartyRedirectOnBehalfOf","lastRedirectRedirectOnBehalfOf","origCalledPartyRedirectReason","lastRedirectRedirectReason","destConversationId","globalCallId_ClusterID","joinOnBehalfOf","comment","authCodeDescription","authorizationLevel","clientMatterCode","origDTMFMethod","destDTMFMethod","callSecuredStatus","origConversationId","origMediaCap_Bandwidth","destMediaCap_Bandwidth","authorizationCodeValue"
1,6,4316482,105930488,1295840966,6,24,253739650,"00","",0,0,4,253739650,19036,4,20,0,0,0,0,0,0,"0","0",105930493,2,0,-301022590,"32777","883068","",0,0,4,-301022590,29042,4,20,0,0,0,0,0,0,"0","0",1295840966,1295840991,"32777","3ae46cd0-a834-4047-a2b9-3a30d500100d","PT-OnNet","","PT_CTI","PT-OnNet",25,"S0/SU3/DS1-1@caul-a-vgw2","UCCX_883068",1,1,1,1,18,18,0,"StandAloneCluster",1,"","",0,"",1,1,0,0,64,64,""
1,6,4316482,105930488,1295840966,6,24,253739650,"00","",0,393216,4,253739650,19036,4,20,0,0,0,0,0,0,"0","0",105930525,2,0,-301022590,"62904","883066","",0,393216,4,-301022590,29058,4,20,0,0,0,0,0,0,"0","0",1295840991,1295841017,"62904","f5741118-a7f6-468c-aab4-c26baec02419","PT-OnNet","","PT_CTI","PT-OnNet",26,"S0/SU3/DS1-1@caul-a-vgw2","UCCX_883066",10,10,1,1,18,18,0,"StandAloneCluster",1,"","",0,"",1,1,0,0,64,64,""
1,6,4316482,105930488,1295841017,6,24,253739650,"00","",0,16,4,253739650,19036,4,20,0,0,0,0,0,0,"0","0",42505945,3,0,993233526,"35026","35026","35026",0,0,4,993233526,17024,4,20,0,0,0,0,0,0,"0","0",1295841017,1295841081,"883066","c9d80022-65bb-4c3f-a2c2-0c3b343c8844","PT-HideCLI-ITS-SD-Staff","","PT-HideCLI-ITS-SD-Staff","PT_CTI",64,"S0/SU3/DS1-1@caul-a-vgw2","SEP002497AB097B",12,10,0,10,0,4,0,"StandAloneCluster",10,"","",0,"",1,3,0,0,64,64,""
1,2,4316482,45592275,1294179028,2,0,-233600394,"51490","",0,16,4,0,0,0,0,0,0,0,0,0,0,"0","0",45592276,0,0,0,"","","",0,0,4,0,0,0,0,0,0,0,0,0,0,"0","0",0,1294179029,"","fc1e713b-d724-4cc0-bbb9-621eee5b165f","","PT-OnNet","","",0,"AN2414AEF1E1413","",12,0,0,0,0,0,0,"StandAloneCluster",0,"","",0,"",3,0,0,0,0,0,""
1,2,4316482,42712590,1287135628,2,0,-417625482,"62661","",0,16,4,0,0,0,0,0,0,0,0,0,0,"0","0",42712591,0,0,0,"","","",0,0,4,0,0,0,0,0,0,0,0,0,0,"0","0",0,1287135628,"","e25fbad5-856d-4bf4-8590-6841d1170fc6","","PT-OnNet","","",0,"AN1E4A543F0C406","",12,0,0,0,0,0,0,"StandAloneCluster",0,"","",0,"",3,0,0,0,0,0,""

"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","nodeId","directoryNum","callIdentifier","dateTimeStamp","numberPacketsSent","numberOctetsSent","numberPacketsReceived","numberOctetsReceived","numberPacketsLost","jitter","latency","pkid","directoryNumPartition","globalCallId_ClusterID","deviceName","varVQMetrics"
2,6,4316482,3,"35026",42505945,1295841081,3178,546616,3176,546272,0,1,0,"5fe9f4a9-161e-4b22-a485-cdfaf0759c7e","PT-HIDE-CLI-ITS_SD_STAFF","StandAloneCluster","SEP002497AB097B","MLQK=4.5000;MLQKav=4.5000;MLQKmn=4.5000;MLQKmx=4.5000;ICR=0.0000;CCR=0.0000;ICRmx=0.0000;CS=0;SCS=0;MLQKvr=0.95"
2,6,4316482,6,"",105930488,1295841081,5597,895520,5597,895520,0,0,0,"2124c3de-d181-403a-b98e-3116bbd38baa","","StandAloneCluster","S0/SU3/DS1-1/24@caul-a-vgw2",""

[srsivara]

Hi,

A UCCX call gets routed the following way :

Incoming call - CI1

call goes to CTI Route Point. CTI Route Point - CI2

call gets redirected to CTI Port by App. CTI Port - CI3

CTI Port can terminate audio. So, CI1 and CI3 will get connected. Most probably, the calling party hears the UCCX prompt.

Then, the CTI Port redirects the call to an available agent. Agent - CI4. When agent answers, CI1 and CI4 get connected.

Let's say agent blind transferred call to another person's phone (CI5). When that new person picks the call up, CI1 and CI5 are connected.

As you can see here, the original calling party's CI remains constant, while the destination CI's keep changing, as the calling party gets connected to different parties / entities.

From the CUCM's perspective, the global call id is for the entire call of the user calling in - so that's why we have multiple entries with same global call id for this particular call.

Hope this helps.

- Sriram

Please rate helpful posts !

[k.oldfield]

Thanks for your reply about the UCCX calls.

What I still don't understand is why is the same cdr.globalcallid_callid used by CUCM on 3 separate days?

[srsivara]

Can you please attach those cdr and cmr records to this thread ? Did you, by any chance, restart the Cisco Callmanager service (or) reboot the server at any time during these 3 days ?

- Sriram

Please rate helpful posts !

[k.oldfield]

The CDR and CMR records were included in my post on 2. Jan 24, 2011 7:43 PM.

The cluster was last rebooted 150 days ago. I'm not aware of the Call Manager service being restarted since the last reboot.

[srsivara]

Looking at the cdr files, the first three records seem to be from CallManager nodeid 6 (6,4316482) and the last two from nodeid 2 (2,4316482). I am guessing that the callid is unique for each CallManager (or) the global_callid value rolled over - I will look into this sometime soon and let you know if I find a good explanation for it.

- Sriram

[srsivara · Accepted solution]

Hi,

Here's the answer :

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_1_2/cdrdef/cdrcallinfo.html#wp1051090

"The Cisco Unified Communications Manager allocates  a global call identifier (GlobalCallID_callId) each time that a Cisco  Unified IP Phone is taken off hook or a call is received from a gateway.  The GlobalCallID_callId is allocated sequentially on a Cisco Unified  Communications Manager server, independent of calls running on other  call servers in the cluster."

"The maximum value that gets assigned to the  GlobalCallID_callId is limited to 24 bits. When this limitation occurs,  the GlobalCallID_callId value gets reset to 1."

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_1_2/cdrdef/cdrfdes.html#wp1210820

"The Global Call ID comprises two fields: globalCallID_callId globalCallID_callManagerId

All records that are associated with a standard call have the same Global Call ID in them"

So, you can identify a unique call using the combination of globalCallID_callId globalCallID_callManagerID. globalCallID_callManagerId alone doesn't give you the information about a unique call.

I am guessing in your scenario that CallManager nodeid 2 is pretty busy, and the globalCallID_callId rolled over, causing 1 CDR on 15 Oct 2010  09:40:28 GMT (epoch time 1287135628) and another on 04 Jan 2011 22:10:28 GMT  (epoch time 1294179028).

Hope this helps.

- Sriram

Please rate helpful posts !

[k.oldfield]

Treating the combination of globalcallid_callmanagerid and globalcallid_callid makes the CDR records look much better. There are still calls with the same globalcallid_callmanagerid and globalcallid_calli, but looking at several of these in detail they appear to be related, eg uccx, conference calls, transfers, etc.

Doing a cdr and cmr join using (cmr.globalcallid_callmanagerid = cdr.globalcallid_callmanagerid and cmr.globalcallid_callid = cdr.globalcallid_callid and (cdr.origlegcallidentifier = cmr.callidentifier or cdr.destlegidentifier = cmr.callidentifier)) gives mostly consistant results (there are still 2840 cmr records (0.01%) which have no coresponding cdr records).

[Chris Lee]

Hello,

Wow, how long did it take to get your PhD in reading the CDR/CMR logs to make sense of them?

If beneficial, perhaps this third party UC app might help:

Regards,

Chris

If there's any interest, pls ping me: chris [at] variphy [dot] com.