I am writing some SQL to query data in the CDR database - particularly re external calls.
Particularly I am trying to work out if a record in sessiondetails relates to a call that started externally (i.e. via a PSTN).
Thought I had this cracked - but in testing have come across a scenario:
a) Call is made from a mobile via the PSTN to a Lync response group number (record is created in the sesssion details table - (matching record in voipdetails has a value in FromGatewayid - to indicate call is external via PSTN)
b) everyone in response group is rung - resulting in lots of records in sessiondetails (SD) and voipdetails (VD). These do not have FromGatewayId set - but all the records in Sessiondetails have a matching correlationId and this can be used to work out that the call did originate externally. One of the extra records in sessiondetails represents the peerson who answered the call.
All the above is well and good - and thought that that was it... BUT
reporting as per above - some of the calls had durations that were obviously too short and it after further investigation I can see that often (not sure why), what I expected to be the final record in session details (with a duration that does not reflect the true call time) is "replaced" by another record, ie
d) record representing the call from the mobile to the person responding has a short duration, but ANOTHER recordappears in Sessiondetails with the columns ReplacesDialogIDtime (and replacesDialogIdseq) populated, and the values in these fields point to the original (SessionIDtime of the) record in Session DEtails. This duration (sessionendtime - sessionidtime) of this extra record appears to reflect the true call duration. (I dont know why Lync is doing this -maybe something to do with the communications between all the different elements of Lync...) However, the identity of the callers is reversed - it looks like the person answering the call has initiated an extra session - where they have rung the mobile back - but they havent.
I am planning to somehow "link" the records together - using the REplacesDialogIDTime (and seq) to join the final record back to the previous record - to work out the true duration of the call to populate the call record in my data warehouse.
So was wondering
a) has anyone seen this happen and is the solution outlined above (looking for a record with the ReplacesDialogIDTime and seq) to link up the records a sound idea ?
b) can this happen multiple times ?
Best regards
Greg
Other recent topics
