Query for transferred calls

[evan.proios]

I am attempting to create a report of transferred calls from the calldetailrecord table in the CDR database using Call Manager 4.2. I have read the documentation provided for the table and it looks to me like if there is a call from 555-1212 to ext 100 and ext 100 transfers it to 101, I should see something like this:

CallingPartyNumber,FinalCalledPartyNumber

555-1212,100

100,101

555-1212,101

However, I have a few problems. First of all, I can't find anything like this pattern in over 4 months worth of data from our system, where lots of calls are transferred each day, although right now I'm just kind of combing through using different queries by time etc. Also, I have a lot of records where the CallingPartyNumber column is blank, or the OriginalCalledPartyNumber field has a weird value starting with b, like b5627368934. Also I cannot seem to determine which column would link together all of the segments of a call for a transferred call, so that I can see all transfers for one actual call (which is why I'm just trying all sorts of different queries right now). Can anyone help me out with an example query to find some transferred calls and list out all of the transfers for each call?

Thanks

[pklos]

Hi,

OriginalCalledPartyNumber beginning with 'b' means conference call to conference Bridge.

Transfered calls you can analyze using lastRedirectDN (redirecting number) field.

Empty callingPartyNumber means that PSTN caller has restricted number presentation.

Detailed CDR description for CCM 4.2.3 you can find at:

http://www.cisco.com/univercd/cc/td/doc/product/voice/vpdd/cdd/4_2/4_2_3/cdr423.pdf

--

regards,

pk

[RRutkowski@nwnit.com]

Is there an update for this?  I am having issues getting all legs of a transferred call in my cdr records.  I can write basic sql queries but am afraid this is above my skill set.  I feel like I am missing something - consultation calls are not always being included or the final legs.  We have our data in a data warehouse.  any help will be greatly appreciated.

select

globalCallId_ClusterID

, globalCallID_callId

, origLegCallIdentifier

, destLegIdentifier

, callingPartyNumber

, originalCalledPartyNumber

, finalCalledPartyNumber

, lastRedirectDn

, origCause_Value

, destCause_Value

, origCallTerminationOnBehalfOf

, destCallTerminationOnBehalfOf

, joinOnBehalfOf

duration

from CDR

where globalCallId_ClusterID='x-xxx-xxx'

and   ( (origCause_value = 393216 and destCause_value = 393216 and origCallTerminationOnBehalfOf =10 and destCallTerminationOnBehalfOf =10 )

or  joinOnBehalfOf = 10)

ORDER BY  globalCallID_callId , globalcallid_callmanagerid, Calc_dateTimeOrigination