Monthly Archives: August 2011

Lync 2010 Response group SQL query

A query that returns call details about calls made to response groups. It was not easy to find documentation of this, but here’s my first successfull attempt. The query is work-in-progress as I’m still trying to figure out how to connect the SessionDetails to the AgentGroups table in rgsConfig database.

use LcsCDR;

select
--SessionDetails users
User1.UserUri as User1Uri, User2.UserUri as User2Uri, StartedByUser.UserUri as StartedByUser,

--VoipDetails
FromPhone.PhoneUri as FromPhoneUri, ConnectedPhone.PhoneUri as ConnectedPhoneUri,

--SessionDetails stats
SessionDetails.ResponseCode, SessionDetails.ResponseTime, SessionDetails.SessionEndTime,
DateDiff(ss,SessionDetails.ResponseTime, SessionDetails.SessionEndTime) as 'Call Duration',

--Client Versions
Client1Version.ClientType as User1ClientType, Client2Version.ClientType as User2ClientType

from SessionDetails
--VoipDetails
join VoipDetails on SessionDetails.SessionIdTime = VoipDetails.SessionIdTime and SessionDetails.SessionIdSeq = VoipDetails.SessionIdSeq
left outer join Phones as FromPhone on FromPhone.PhoneId = VoipDetails.FromNumberId
left outer join Phones as ConnectedPhone on ConnectedPhone.PhoneId = VoipDetails.ConnectedNumberId

--Users
left outer join Users as User1 on User1.UserId = SessionDetails.User1Id
left outer join Users as User2 on User2.UserId = SessionDetails.User2Id
left outer join Users as StartedByUser on StartedByUser.UserId = SessionDetails.SessionStartedById

--Used to filter response group service calls only
left outer join ClientVersions as Client1Version on Client1Version.VersionId = SessionDetails.User1ClientVerId
left outer join ClientVersions as Client2Version on Client2Version.VersionId = SessionDetails.User2ClientVerId

where

--Filter response group service calls only
Client1Version.ClientType = 1024 or Client2Version.ClientType = 1024 --'RTCC/4.0.0.0 Response_Group_Service'

A Microsoft Lync 2010 custom SQL query

This query can be used for example when designing a custom SSRS report against Lync 2010 databases. It will show the current state of users in response groups.

SELECT Groups.Name, Agents.DisplayName, [State] FROM rgsdyn.dbo.AgentGroupSignInStates AS States JOIN rgsconfig.dbo.Agents AS Agents ON Agents.ID = States.AgentId JOIN rgsconfig.dbo.AgentGroups AS Groups ON Groups.ID = States.GroupId ORDER BY GroupId, States.State DESC