Disclaimer: This article is a journal of my experiences solving a particular problem, it is not suggesting that you should follow my approach and I cannot say doing so wouldn’t cause serious issues on your system. As with anything you find on the Internet, take it with a grain of salt and don’t blindly run code or follow advice without doing your own research first. Employ at your own risk.
Microsoft does not offer a native contact center option with Lync/Skype. Instead, they’ve made it easy for third party vendors to integrate with Lync using the available SDKs. This gives us a best of breed approach, allowing for many contact center vendors to exist and giving you the ability to choose from disruptive startups to very mature firms that have been around for 20+ years.
There are numerous benefits to a full contact center solution including real time monitoring and dashboards, pre-built reporting, call recording, callers hearing their position in the queue, you get the picture. There is also a cost associated with all of this. Before we get too far off track, the intention of this article isn’t to sell you on a contact center, it’s to help those who don’t need a full contact center solution but want a touch more than what comes in the box.
Lync does offer response groups that can route calls in a serial, parallel, round robin, or longest idle fashion. These groups can be set as “formal” allowing users to log in and out via a web portal as seen in Figure 1 below. Further, Lync logs answered calls into an LcsCDR database when monitoring is deployed. What I’ve found on occasion are clients that don’t need a full call center, just stronger reporting around call flow into response groups. In response to this, I’ve written several reports that monitor agent availability, wait time before answer, abandoned (to another queue or voicemail) calls, average call time, calls per agent. All of that is available in the databases, you just need to write the report to suit your needs.
Figure 1 – Lync Response Group Sign-in Web Portal
One item that is lacking, is the ability to monitor agent’s break time and how long they were logged into the queues. This information can be key when reviewing agent performance. Knowing how many calls the agent answered without knowing how long they were available to receive calls gives an incomplete picture. Unfortunately, within the Lync databases we can only see if a user is or isn’t logged into a response group queue using the AgentGroupSignInStates table in the rgsdyn database. Agents are marked with a 0 or 1 to represent logged out or in, respectively.
Figure 2 – AgentGroupSignInStates Table
To overcome this, what I’ve used in the past is a SQL trigger. Before we go any further, I want you warn you that if you opt to use this method, you’ll need to understand the pros, cons, and risks of using triggers. Used or written improperly, they can cause performance and other issues. One reason for this is because a trigger is basically a procedure that executes when an event such as an INSERT, UPDATE, or DELETE occurs within a table. If you have a heavily written table, you’re going to have a heavily executed procedure. That said, in the environments where I’ve deployed this, there are a minimal number of agents (40 as seen in the above figure) and table events usually total 2-6 per day per agent.
The trigger I employ basically writes the login and logout times to a simple table. Those times can later be added to a report to show total login time, or a simple audit of login/logout. To start, let’s create the table.
USE [rgsdyn] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RGAgentSessions]( [AgentId] [uniqueidentifier] NOT NULL, [GroupId] [uniqueidentifier] NOT NULL, [LoginDate] [datetime] NOT NULL, [LogoutDate] [datetime] NULL ) ON [PRIMARY] GO
Figure 3 – RGSAgentSessions Table Created
The table I’ve created exists in rgsdyn because that’s where our AgentGroupSignInStatesTable exists and it’s easy for me to reference later in SQL calls. You may prefer it in the LcsCDR database. Four fields are added, the AgentID and GroupID to uniquely identify the agent and group represented, and the login and logout date/time. On it’s own, we’ve got an empty table. With the trigger, it begins to populate as users sign in and out. I’ll create the trigger now.
USE [rgsdyn] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Trigger1] On [dbo].[AgentGroupSignInStates] AFTER INSERT, UPDATE AS SET NOCOUNT ON IF (SELECT State FROM inserted) = 0 BEGIN UPDATE dbo.RGAgentSessions SET LogoutDate=GETDATE() WHERE LogoutDate IS NULL AND AgentId=(SELECT AgentId FROM inserted) AND GroupId=(SELECT GroupId FROM inserted) AND LoginDate=(SELECT Max(LoginDate) FROM dbo.RGAgentSessions where AgentId=(SELECT AgentId FROM inserted) and GroupId=(SELECT GroupId FROM inserted)) END ELSE BEGIN INSERT INTO dbo.RGAgentSessions SELECT AgentId,GroupId,GETDATE(),NULL FROM INSERTED END GO
Figure 4 – The Trigger Code
The code watches INSERT and UPDATE modifications to the AgentGroupSignInStates table. This is the table above where we can see the 1 or 0 state for each agent per group. When a new row is added to the table (INSERT), or an agent toggles their login state (UPDATE), our trigger will spot it using the below line and the rest of our code will execute.
CREATE TRIGGER [dbo].[Trigger1] On [dbo].[AgentGroupSignInStates] AFTER INSERT, UPDATE
The meat of the code has an IF statement that watches the State field from the original table. If we see any value other than 0, we consider it a login and create a new record writing the current time as the login time and a logout time of NULL. This is in the ELSE portion of the code below.
If we see a 0 it means the agent must have just logged out of the response group, setting the state field in the original table to 0. We’ll then search for the last login record matching that agent and group and put the current time in as the logout.
IF (SELECT State FROM inserted) = 0 BEGIN UPDATE dbo.RGAgentSessions SET LogoutDate=GETDATE() WHERE LogoutDate IS NULL AND AgentId=(SELECT AgentId FROM inserted) AND GroupId=(SELECT GroupId FROM inserted) AND LoginDate=(SELECT Max(LoginDate) FROM dbo.RGAgentSessions where AgentId=(SELECT AgentId FROM inserted) and GroupId=(SELECT GroupId FROM inserted)) END ELSE BEGIN INSERT INTO dbo.RGAgentSessions SELECT AgentId,GroupId,GETDATE(),NULL FROM INSERTED END
In time as users log in and out of the formal response groups, data will populate as you can see in figure 3. The example above has now been in place for three years and has moved from Lync 2010 to 2013 without issue. The data is used in different ways within customer Lync SSRS reports, typically showing hours and minutes logged in per time range selected. Exporting the data for simple auditing can be useful as well.
If you have a more efficient method of storing this data, or just want to correct my horrible SQL, please reach out and let me know! Thanks for reading!