Few months ago, I wrote an post about Genesys CCPulse powerful feature (Read here) and explained how JScript language can be used to show various other details within CCPulse. Last week, I had requirement from contact center supervisor to view agent’s login time i.e.) when they logged in. We needed this information to analyze default routed calls by looking at load on Genesys servers and WAN bandwidth.

In this post, I will explain the steps to show agent’s logged in time in CCPulse as below

Step 1 : Configure StatServer to store login data

• Create new database [StatServerDB] and initialize it with scripts from StatServer folder. Refer to StatServer Deployment Guide Appendix A for more information about database and tables
• Login into CME, create new DAP [RealtimeReportingDAP] application and configure it to use [StatServerDB]
• Open StatServer application, add [RealtimeReportingDAP] in the connections DAP
• Go to ‘Options’ tab in Statserver application, open section ‘statserver’ and set option ‘login-table’ to ‘true’

Step 2: Configure CCPulse application

• Open CCPulse application -> Options  -> CustomStatistic section and change ‘ExtendedCurrentStatus’ to ‘true’

Step 3: Create ‘LastLoggedIn’ field in CCPulse Template

•  Login into CCPulse application and open ‘Template Wizard’
• Select object type as ‘Agent’, select the template to modify it from the options and click ‘Next’
• Click ‘Formula’ to create new reporting field and click ‘Properties’ to open expression editor
• Copy and paste the code below. Update database details in the code and Click ‘Ok’ to complete it.
• Open agent report using this template and you will see last login time of the agent

var objRs=new ActiveXObject("ADODB.Recordset")
var strConn="Provider=SQLOLEDB.1;Data Source=<SQLServer Instance>;Initial Catalog=<Statserver Database>;User Id=<UserName>;Password=<UserPassword>;"
strEmployeeID=state.LoginID
strQuery=" select top 1 dbo.ConvertToLocalTime([Time]) as Logintime from [Login] "
strQuery+="where LOGINID='"+strEmployeeID+"' "
strQuery+=" and left (dbo.ConvertToLocalTime([Time]),11) = left(getdate(),11) "
strQuery+=" and status = '1' order by logintime desc"
objRs.Open(strQuery,strConn,3,1,1)
var resultCount="";
if(!objRs.BOF)
{
objRs.MoveFirst();
while(!objRs.EOF)
{
if(objRs("Logintime").value!=null)
{
	resultCount=objRs("Logintime").value
}
objRs.MoveNext();
}
}
objRs.Close
objRs=null;
resultCount; 

StatServer stores time in Unix format and hence, use below SQL script to convert field into local time

create function converttolocaltime (@timestamp int)
returns datetime
as
begin
declare @ret datetime
declare @offset bigint, @localdate bigint;
    set @offset = datediff(second,getdate(),getutcdate());
    set @localdate = @timestamp - @offset;
select @ret = dateadd(second, @localdate, '1970/01/01 00:00:00')
return @ret
end
go