Response Group Queue monitoring

Create your own RSG Queue monitor, yes it’s possible and not even so hard to get this done. In a few steps I will show you my road to the Queue monitor.

Special thanks for my friend Maurice for helping me with the SQL stuff and more!!!

First of all it’s a website which is refreshed every 5 seconds so it’s near real-time information. In the future it will be in AJAX so you don’t have to refresh every time to get real –time queue information.
The queue information in this example is in Dutch.

Step1:
First build your SQL queries and find a friend to help you. My colleague Maurice, a SQL Server guy, knows a lot of building queries and did help me to get the right information out of the database.

I will explain here what we have built to monitor the queue. We created a new database inside the Lync SQL instance with a unique name GNGACD. This database will be used to save a new  function we created to get the presence state of the agents who will be serving the queue. Yes we don’t use the UC Managed API to get the presence state cause I am not a developer and presence information is inside the database.

The query which will get the queue information is: (SET @queuetarget, here you can set the name of the queue to monitor)

Declare @queuetarget AS varchar (40)

SET @queuetarget = 'helpdesk%'

--Aantal wachtende (Number of Calls waiting)

Select COUNT(*) as 'Aantal wachtende'

  FROM [rgsconfig].[dbo].[Queues] Q

  Join [rgsdyn].[dbo].[MatchRequests] R on R.[QueueId] =  Q.[ID]

Where Q.Name  Like @queuetarget

--Bellers die wachten (Calls waiting list with name or number and waiting time, notice DATEIFF here SQL server calculates the waiting time of the call)

SELECT    R.CallingPartyUri as Beller

      , DATEDIFF (MINUTE,  R.Timestamp, DateAdd (hour, -1, GETDATE()) ) as Wachttijd

      , Q.[Name] as Naam

  FROM [rgsconfig].[dbo].[Queues] Q

  Join [rgsdyn].[dbo].[MatchRequests] R on R.[QueueId] =  Q.[ID]

  Where Q.[Name]  Like @queuetarget

-- Agents in groep (List of Agents Signed in and presence state of the agent)

SELECT Q.[Name] as 'Anwoord Groep' -- RSG Queue Name

      ,AG.Name as 'Groepsnaam' -- RSG Group Name

      ,A.DisplayName as Naam

      ,[gngacd].[dbo].[gngPresence] (SUBSTRING(A.SipAddress, 5, LEN(A.SipAddress)-4)) as Status

  FROM [rgsconfig].[dbo].[Queues] Q

  JOIN [rgsconfig].[dbo].[QueuesToAgentGroupsMap] QTAGM on Q.ID = QTAGM.QueueId

  JOIN [rgsconfig].[dbo].[AgentGroups] AG on QTAGM.AgentGroupId = AG.ID

  join [rgsdyn].[dbo].[AgentGroupSignInStates]AGSIS on AGSIS.GroupId = AG.ID

  JOIN [rgsconfig].[dbo].[Agents] A on AGSIS.AgentId = A.ID

  WHere Q.[Name] Like @queuetarget AND AG.ParticipationPolicy = 1 AND AGSIS.State = 1

SELECT Q.[Name] as 'Anwoord Groep' -- RSG Queue Name

      ,AG.Name as 'Groepsnaam' -- RSG Group Name

     ,A.DisplayName as Naam

     ,[gngacd].[dbo].gngPresence (SUBSTRING(A.SipAddress, 5, LEN(A.SipAddress)-4)) as Status (here we call the function to read the presence of the agents who are signed in the groups from the queue which is monitored)

  FROM [rgsconfig].[dbo].[Queues] Q

  JOIN [rgsconfig].[dbo].[QueuesToAgentGroupsMap] QTAGM on Q.ID = QTAGM.QueueId

  JOIN [rgsconfig].[dbo].[AgentGroups] AG on QTAGM.AgentGroupId = AG.ID

  JOIN [rgsconfig].[dbo].[AgentGroupsToAgentsMap] AGTAM on AG.ID = AGTAM.AgentGroupId

  JOIN [rgsconfig].dbo.[Agents] A on AGTAM.AgentId = a.ID

  WHere Q.[Name] Like @queuetarget AND AG.ParticipationPolicy = 0

DB and tables overview:
Rgsconfig database, all configuration from responsegroups such as queue name and agents is found here.
Rgsdyn database, all dynamic data from responsegroups such as Caller information is found here.

Let me explain the presence state of the agent. Agents who are part of a RSG group and the group participation policy is set to informal are always signed in but are not always able to answer incoming calls so therefore it’s necessary to know there presence  so you can show this in your queue monitor.

The function which will get the presence state, this is really a tricky one:

USE [gngacd]

GO

/****** Object:  UserDefinedFunction [dbo].[gngPresence]    Script Date: 01/14/2011 16:17:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:  <Maurice and Frederik>

-- Create date: <14-01-2011,,>

-- Description:              <Presence state,,>

-- =============================================

ALTER FUNCTION [dbo].[gngPresence] (@sipaddress varchar(4000))

returns nvarchar(40)

as

Begin

                        declare @sipstate nvarchar(4000)

                        declare @status nvarchar(40)

                        SET @sipstate = (    select

           TOP (1) cast(substring(i.Data, 0, 4000) as varchar(4000)) as Data

      from [rtc].dbo.PublishedCategoryInstanceView as I (the PublishedCategoryInstanceView database contains the state of every user in XML format.)

     inner join [rtc].dbo.CategoryDef as d

            on (d.CategoryId = i.CategoryId)

     where i.PublisherId = (select ResourceId from rtc.dbo.Resource where UserAtHost = @sipaddress)

                             AND d.Name = 'state' AND cast(substring(i.Data, 0, 156) as varchar(157)) like '<state xsi:type="aggregateState" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2006/09/sip/state"><availabil%'  (here we search for the correct XML file with the most accurate state, this done by sorting them)

                             --AND cast(substring(i.Data, 0, 156) as varchar(157))

     ORDER by i.LastPubTime desc

     )

                        IF (SELECT SUBSTRING (@sipstate, CHARINDEX ('<avail', @sipstate)+14, CHARINDEX ('</avail', @sipstate)-(CHARINDEX ('<avail', @sipstate)+14)))>3500

                                               BEGIN

                                               set @status = 'Niet beschikbaar'

                                               END

                                               ELSE

                                               set @status = 'Beschikbaar'

RETURN @status

END

The result of all this gives you the following output for the queue Helpdesk ICT.

The text in red will explain where the information is coming from and where it will be viewed in the queue monitor. There is a lot information that isn’t needed to be shown in the queue monitoring but we leave here for now and we will remove them later from the webpage.

Step 2:
Now the query is ready we build a simple website with an ASP DataSource connection to run the query and show the results.

This webpage consist of 3 files, web.config, default.aspx  and lync-server-logo.png.

Web.config, here you configure the datasource connector, you Lync SQL database server.

Default.aspx, the webpage wich actually runs the query and shows it

Lync-server-logo.png, don’t know what this is???

To make life easier I put all 5 files together so you can use and modify them for your needs.
That’s it for now , it wasn’t really hard to make this working it was harder for me to get this running on IIS 7.5 with all security things. One things to notice is, when viewing this webpage remotely the user that runs the query must have access to the database and normally this will be the machine account where IIS runs, IIS also needs to run under the ASP.NET v4.0 Classic pool.

Quicksteps:

·         Create gngacd database in Lync instance

·         Create SQL function in gngacd database

·         Give IIS machine correct security rights on SQL Lync databases

·         Create Website in IIS and copy webconfig, default.aspx and lync-server-logo.png in root folder

RSGQueueMon.zip