CODEWITHSUNDEEP
mysqlsqlinner-joinjoinlync-server-2010
Greg Booth
Greg Booth

Reputation: 31

In a Lync 2010 LCSCDR database, is their a way to tell who answered a call to a response group

I am writing some SQL code to query the Lync LCSCDR database.

If someone calls a response group externally and another one answers it, a record goes into the sessionDetails table (and the Voipdetails table) - to reflect the call made.

However, there appears to be no way to identify who answered the call. The user2id column is populated with the userid (from the Users table) of the response group rather than the person who answered the call. Does anyone know if there is a way to guess who answered the call ?

The query is:

SELECT 
    sd.SessionIdTime, 
    sd.SessionEndTime,
    DATEDIFF(
        ss, 
        sd.SessionIdTime, 
        ISNULL(sd.SessionEndTime, sd.SessionIdTime)
    ) AS duration,
    ph1.PhoneUri AS caller1,
    ph2.PhoneUri AS receiver1, 
    U1.UserUri AS user1uri, 
    U2.UserUri AS user2uri, 
    U3.UserUri AS refeereruri, 
    sd.User1Id, 
    sd.User2Id,
    sd.ReferredById,
    sd.IsUser1Internal, 
    sd.IsUser2Internal,
    sd.SessionIdTime, 
    voipd.*, 
    '|||',
    sd.*
FROM 
    dbo.VoipDetails AS voipd 
INNER Join 
    dbo.SessionDetails AS sd 
ON 
    (
        voipd.SessionIdTime = sd.SessionIdTime AND 
        voipd.SessionIdSeq = sd.SessionIdSeq
    ) 
LEFT OUTER JOIN 
    dbo.Users AS U2 -- still 4795 with outer
ON 
    U2.UserId = sd.User2Id
LEFT OUTER JOIN 
    dbo.Users AS U1
ON 
    U1.UserId = sd.User1Id 
LEFT OUTER JOIN 
    dbo.Users AS U3
ON 
    U3.UserId = sd.ReferredById
LEFT OUTER JOIN 
    dbo.Phones AS ph1
ON 
    ph1.PhoneId = voipd.FromNumberId
LEFT OUTER JOIN 
    dbo.Phones AS ph2
ON 
    ph2.PhoneId = voipd.ConnectedNumberId
WHERE 
    sd.SessionIdTime > (GETDATE() - 2) 
    AND sd.MediaTypes = 16 
    AND voipd.FromGatewayId  is not null -- external
    AND User1Id IS NULL

Upvotes: 3

Views: 1448

Answers (1)

Joseph de Jamblinne
Joseph de Jamblinne

Reputation: 1

There is a way to find an anwser to your question but you will need an second application to do it

When a RG is called, the call will fill the CorrelationID

For each line where the CorrelationID is filled, you must search for a line without CorrelationID where the RefferdByID is the called RG. Most of the time the answering user will become the caller and the callee is the caller of the line with the CorrelationID

Upvotes: 0

Related Questions