I think bwheeler was on the

cliffj64 · ‎10-18-2013

I have a UCCX 8.5 with the Gila-Wallboard running, the setup is perfect(after many hours) but the Agent Statistics is not displaying correctly. Sometimes it will show a logged in user but most times it doesn’t. enclosed is the wallboard.asp file. I am using the uccxhruser for the Informix login. Is this a user issue or ASP issue? I am looking into the wrong table on the db_cra?

thanmad · ‎12-06-2013

Hey Cliff, any luck? I'm having the same issue. All my overall stats are accurate but the agent field is just hosed. Spotty at best.

dandaigle · ‎01-17-2014

I have had good results using the following query:

SELECT x.resourceName, t.eventType, t.reasonCode, x.datetime FROM (SELECT t1.resourceID, t1.resourceName, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID GROUP BY t1.resourceID, t1.resourceName ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime ORDER BY t.reasonCode,t.eventType,x.datetime

by my understanding (schema attached) AgentStateDetail stores state changes, rather than current state, resulting in muliple records for each agent. Resource stores one record per agent, so joining them as above will produce the latest record for each agent (please note that I didn't write the query originally, I got from an open source wallboard that I updated for 8.5/9.x)

bwheeler · ‎06-05-2014

I have found that when the overall stats are good but the agents are jacked, it is the time. That is the issue.
Here is what resolved it for our site...

Open each .asp file and scroll to:

//***************************START CALC STATUS TIME MODULE******************************//

Find the entry below and either add or subtract where the current number is "1", next to the "h".

It is currently set for DST and will need the 1 probably removed during winter (add a zero[0] in

the one [1] place).

EventHappenedDateTime = DateAdd("h",1,EventHappenedDateTime)

This will have to be done for each .asp file if there are multiples

Jesse Northington · ‎06-05-2014

mine looks like this:

EventHappenedDateTime = CDate(strArray(0) & " " & strArray(1) & " " & strArray(2))
' get the current date and time
CurrentDateTime = CDate(Date() & " " & Time())

Jesse Northington · ‎06-03-2014

ever figure this out? I am trying to adapt my wallboards to UCCX 9.X and everything seems to be fine except I can only see logged out agents in my wallboard.

dandaigle · ‎06-05-2014

Did you write your own sql query? What is the query that you're using?

Jesse Northington · ‎06-05-2014

Here is the whole .asp. The only part that isn't populating is the Agent specific stats. Queue stats are rolling. The agents I can see look like this:

Christina Smith

Logged Out

11:16:08

Unavailable for Call

<%
//******************************START AGENT STATS MODULE********************************//

//**************************************************************************************//
// Module: Agent Stats //
// PURPOSE: Retrieves Agent Sumary stats & team names and formats them for display. //
//**************************************************************************************//

sqlGetAgentstats = "SELECT x.resourceName, x.resourceType, t.eventType, t.reasonCode, x.resourceGroupID, x.datetime FROM (SELECT t1.resourceID, t1.resourceType, t1.resourceName, t1.resourceGroupID, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID and t1.dateInactive is null GROUP BY t1.resourceID, t1.resourceType, t1.resourceName, t1.resourceGroupID ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime WHERE x.resourceGroupID = '4' ORDER BY t.eventType, x.resourceName"

Set rs3 = server.createobject("adodb.recordset")
Set cm3 = Server.CreateObject("ADODB.Command")
cm3.ActiveConnection = cn
cm3.commandtext = sqlGetAgentstats
Set rs3 = cm3.execute

teamID = rs3("Partner Services")

Sub getTeamName (teamID)

sqlGetTeamName = "SELECT teamName from Team where teamID = "'& teamID &'"

Set rs4 = server.createobject("adodb.recordset")
Set cm4 = Server.CreateObject("ADODB.Command")
cm4.ActiveConnection = cn
cm4.commandtext = sqlGetTeamName
Set rs4 = cm4.execute

teamName = rs4("teamName")

rs4.close

End Sub

Sub getReasonCodeText (reasonCode)
Select Case reasonCode
'System Codes
Case 33
  strReasonCode = sysReasonCode33Text
Case 32755
  strReasonCode = sysReasonCode32755Text
Case 32757
  strReasonCode = sysReasonCode32757Text
Case 32758
  strReasonCode = sysReasonCode32758Text
Case 32759
  strReasonCode = sysReasonCode32759Text
Case 32760
  strReasonCode = sysReasonCode32760Text
Case 32761
  strReasonCode = sysReasonCode32761Text
Case 32762
  strReasonCode = sysReasonCode32762Text
Case 32763
  strReasonCode = sysReasonCode32763Text
Case 32766
  strReasonCode = sysReasonCode32766Text
'User Codes
Case udReasonCode1ID
  strReasonCode = udReasonCode1Text
Case udReasonCode2ID
  strReasonCode = udReasonCode2Text
Case udReasonCode3ID
  strReasonCode = udReasonCode3Text
Case udReasonCode4ID
  strReasonCode = udReasonCode4Text
Case udReasonCode5ID
  strReasonCode = udReasonCode5Text
Case udReasonCode6ID
  strReasonCode = udReasonCode6Text
Case udReasonCode7ID
  strReasonCode = udReasonCode7Text
'Catch all
Case Else
  strReasonCode = udReasonCodeUndefined
End Select
End Sub

Sub getAgentStatus (eventType)

Select Case eventType
Case 7
   Call getReasonCodeText(rs3("reasonCode"))
   strAgentStatus = strStatusCode7
   TextColor = TextColor_AgentStats_LoggedOut
Case 6
      strAgentStatus = strStatusCode6
   strReasonCode = udReasonCodeWorking
      TextColor = TextColor_AgentStats_Working
Case 5
      strAgentStatus = strStatusCode5
   strReasonCode = udReasonCodeTalking
      TextColor = TextColor_AgentStats_Talking
Case 4
      strAgentStatus = strStatusCode4
   strReasonCode = udReasonCodeReserved
      TextColor = TextColor_AgentStats_Reserved
Case 3
      strAgentStatus = strStatusCode3
   strReasonCode = udReasonCodeReady
      TextColor = TextColor_AgentStats_Ready
Case Else
  Call getReasonCodeText(rs3("reasonCode"))
  If rs3("reasonCode") = 32761 or rs3("reasonCode") = 32762 Then
   strAgentStatus = strStatusCode8
       TextColor = TextColor_AgentStats_NonACDCall
  Else
       strAgentStatus = strStatusCode2
       TextColor = TextColor_AgentStats_NotReady
  End If
End Select

End Sub

%>

<%

While Not rs3.EOF

Call getAgentStatus (rs3("eventType"))
Call CalculateStatusTime (rs3("datetime"))
Call CheckAgentStatusBlinking

%>

<% If rs3("ResourceType") = 2 Then %>

<% If intAgentStatsMaxDays = 0 Then %>
<tr>
<td>' color='<%= TextColor %>'><%= rs3("resourceName") %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strAgentStatus %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strReasonCode %></td>
 </tr>
 <tr>
 <td colspan=4>
 <hr colspan=4>
 </td>
 </tr>
<% End If %>

<% If intAgentStatsMaxDays = 1 And intStatusDays < intAgentStatsMaxDays And intStatusHours < intAgentStatsMaxHours Then %>
 <tr>
<% If AgentStatusBlink = 1 And strAgentStatus = strStatusCode2 then %>
 <td><BLINK speed="1000">' color='<%= TextColor %>'><%= rs3("resourceName") %></BLINK></td>
 <td align='center'><BLINK speed="1000">' color='<%= TextColor %>'><%= strAgentStatus %></BLINK></td>
 <td align='center'><BLINK speed="1000">' color='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></BLINK></td>
 <td align='center'><BLINK speed="1000">' color='<%= TextColor %>'><%= strReasonCode %></BLINK></td>
 <% Else %>
 <td>' color='<%= TextColor %>'><%= rs3("resourceName") %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strAgentStatus %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strReasonCode %></td>
<% End If %>
 </tr>
 <tr>
 <td colspan=4>
 <hr colspan=4>
 </td>
 </tr>
<% End If %>

<% If intAgentStatsMaxDays > 1 And intStatusDays < intAgentStatsMaxDays Then %>
 <tr>
 <td>' color='<%= TextColor %>'><%= rs3("resourceName") %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strAgentStatus %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strReasonCode %></td>
 </tr>
 <tr>
 <td colspan=4>
 <hr colspan=4>
 </td>
 </tr>
<% End If %>

<% Else %>

<% If intAgentStatsMaxDays = 0 Then %>
 <tr>
 <td>' color='<%= TextColor %>'><%= rs3("resourceName") %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strAgentStatus %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strReasonCode %></td>
 </tr>
 <tr>
 <td colspan=4>
 <hr colspan=4>
 </td>
 </tr>
<% End If %>

<% If intAgentStatsMaxDays = 1 And intStatusDays < intAgentStatsMaxDays And intStatusHours < intAgentStatsMaxHours Then %>
 <tr>
<% If AgentStatusBlink = 1 And strAgentStatus = strStatusCode2 then %>
 <td><BLINK speed="1000">' color='<%= TextColor %>'><%= rs3("resourceName") %></BLINK></td>
 <td align='center'><BLINK speed="1000">' color='<%= TextColor %>'><%= strAgentStatus %></BLINK></td>
 <td align='center'><BLINK speed="1000">' color='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></BLINK></td>
 <td align='center'><BLINK speed="1000">' color='<%= TextColor %>'><%= strReasonCode %></BLINK></td>
 <% Else %>
 <td>' color='<%= TextColor %>'><%= rs3("resourceName") %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strAgentStatus %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strReasonCode %></td>
<% End If %>
 </tr>
 <tr>
 <td colspan=4>
 <hr colspan=4>
 </td>
 </tr>
<% End If %>

<% If intAgentStatsMaxDays > 1 And intStatusDays < intAgentStatsMaxDays Then %>
 <tr>
 <td>' color='<%= TextColor %>'><%= rs3("resourceName") %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strAgentStatus %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %></td>
 <td align='center'>' color='<%= TextColor %>'><%= strReasonCode %></td>
 </tr>
 <tr>
 <td colspan=4>
 <hr colspan=4>
 </td>
 </tr>
<% End If %>

<% End If %>

<% rs3.MoveNext %>
<% Wend %>
</table>
</td>
</tr>
</table>
<%

rs3.close

//*******************************END AGENT STATS MODULE*********************************//

%>

dandaigle · ‎06-05-2014

At the start of the agent stats module, try changing sqlGetAgentstats to the following. Right before the GROUP BY clause I've changed "and t1.dateInactive is null" to "WHERE t1.active='t'

Note that I haven't tested this, so let me know if it works.

sqlGetAgentstats = "SELECT x.resourceName, x.resourceType, t.eventType, t.reasonCode, x.resourceGroupID, x.datetime FROM (SELECT t1.resourceID, t1.resourceType, t1.resourceName, t1.resourceGroupID, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID WHERE t1.active='t' GROUP BY t1.resourceID, t1.resourceType, t1.resourceName, t1.resourceGroupID ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime WHERE x.resourceGroupID = '4' ORDER BY t.eventType, x.resourceName"

Jesse Northington · ‎06-05-2014

trying it now. The whole wallboard seems to be taking a LOOOOOOOONG time to come up with your change.

completely broke it... :)

dandaigle · ‎06-05-2014

Fair enough, I haven't used it on large databases (I'm using it with maybe 20 agents active at once max). If the results are still wrong then just change it back.

Mind providing a bit more detail about the results? So far it sounds like you're seeing all the logged out agents, but none of the logged in agents, correct? are there any duplicates?

Jesse Northington · ‎06-05-2014

I think I'm only seeing agents who have been logged out for over 8 hours and less than 12. Looks like this

Agent Name:	Current Status:	Time In Status:	Reason in Status:

Abigail Gutierrez	Logged Out	8:17:03	Unavailable for Call

Cilvia Ortiz	Logged Out	9:10:13	Unavailable for Call

Miriam Rodriguez	Logged Out	8:17:13	Unavailable for Call

Patricia McGee	Logged Out	9:12:36	Unavailable for Call

Takiya Dean	Logged Out	11:48:33	Unavailable for Call

Taminka Franklin	Logged Out	8:16:49	Unavailable for Call

Yolanda Sanchez	Logged Out	8:18:30	Unavailable for Call

dandaigle · ‎06-05-2014

I think bwheeler was on the right track about the time being wrong, what time zone are you in?

What's the code for your calc status time module? I think I recall a hard coded time zone compensation in the original 2.4

Jesse Northington · ‎06-05-2014

Central.

//***************************START CALC STATUS TIME MODULE******************************//

//**************************************************************************************//
// Module: Calculate Status Time //
// PURPOSE: Calculates the time an agent has been in the reported state. //
//**************************************************************************************//

Sub CalculateStatusTime (eventDateTimeIn)
' The string returned in eventDateTime is a string of the form "MM/DD/YYYY HH:MM:SS [AM/PM]"
' The difference between current date/time and event date/time give the time that the agent is in the current status
' dateDiff calculates the difference between two date values and can return this in seconds, minutes, hours, et cetera
strArray = split(eventDateTimeIn," ")
EventHappenedDateTime = CDate(strArray(0) & " " & strArray(1) & " " & strArray(2))
' get the current date and time
CurrentDateTime = CDate(Date() & " " & Time())
' calculate the difference between current date/time and date/time of the last event change
intStatusSeconds = DateDiff("s",EventHappenedDateTime,CurrentDateTime)
' we will need not only seconds, but also minutes, hours and days
intStatusMinutes = Round((intStatusSeconds / 60),0)
intStatusHours = Round((intStatusSeconds / 3600),0)
intStatusDays = Round((intStatusSeconds / 86400),0)
intStatusSeconds = intStatusSeconds - (intStatusMinutes * 60)
' because of the roun function used, we will need to fix some miscalculations because of rounding differences
If intStatusSeconds < 0 Then intStatusSeconds = intStatusSeconds + (1 * 60)
intStatusMinutes = intStatusMinutes - (intStatusHours * 60)
If intStatusMinutes < 0 Then intStatusMinutes = intStatusMinutes + (1 * 60)
intStatusHours = intStatusHours - (intStatusDays * 24)
If intStatusSeconds > 30 Then intStatusMinutes = intStatusMinutes - 1
If intStatusMinutes > 30 Then intStatusHours = intStatusHours - 1
If intStatusHours < 0 Then intStatusHours = intStatushours + (1 * 24)
If intStatusHours > 12 Then intStatusDays = intStatusDays -1
' next we convert the integers to strings to be displayed on the page
If intStatusMinutes < 10 Then
 If intStatusMinutes = 0 Then strStatusMinutes = "00"
 If intStatusMinutes = 1 Then strStatusMinutes = "01"
 If intStatusMinutes = 2 Then strStatusMinutes = "02"
 If intStatusMinutes = 3 Then strStatusMinutes = "03"
 If intStatusMinutes = 4 Then strStatusMinutes = "04"
 If intStatusMinutes = 5 Then strStatusMinutes = "05"
 If intStatusMinutes = 6 Then strStatusMinutes = "06"
 If intStatusMinutes = 7 Then strStatusMinutes = "07"
 If intStatusMinutes = 8 Then strStatusMinutes = "08"
 If intStatusMinutes = 9 Then strStatusMinutes = "09"
Else
 strStatusMinutes = CStr(intStatusMinutes)
End If
If intStatusSeconds < 10 Then
 If intStatusSeconds = 0 Then strStatusSeconds = "00"
 If intStatusSeconds = 1 Then strStatusSeconds = "01"
 If intStatusSeconds = 2 Then strStatusSeconds = "02"
 If intStatusSeconds = 3 Then strStatusSeconds = "03"
 If intStatusSeconds = 4 Then strStatusSeconds = "04"
 If intStatusSeconds = 5 Then strStatusSeconds = "05"
 If intStatusSeconds = 6 Then strStatusSeconds = "06"
 If intStatusSeconds = 7 Then strStatusSeconds = "07"
 If intStatusSeconds = 8 Then strStatusSeconds = "08"
 If intStatusSeconds = 9 Then strStatusSeconds = "09"
Else
 strStatusSeconds = CStr(intStatusSeconds)
End If
strStatusHours = CStr(intStatusHours)
strStatusDays = CStr(intStatusDays)
End Sub

//****************************END CALC STATUS TIME MODULE*******************************//

Jesse Northington · ‎06-05-2014

Here is my parameters for agents from the .cfg

//*******************************AGENT PARAMETERS***************************************//

Const strPageHeader_AgentStats = "Agent Statistics"
Const strPageTitleAgent = "Agent Statistics"
Const TextColor_TableHeader_AgentStats = "Navy"
Const TextSize_AgentStats = 3

Const DisplayAgentsStatsTrue = 1

Const intAgentStatsMaxDays = 1
Const intAgentStatsMaxHours =12
Const intAgentStatsMaxNotReady = 5

Const TableColumnHeader_AgentStats_Column1 = "Agent Name:"
Const TableColumnHeader_AgentStats_Column2 = "Current Status:"
Const TableColumnHeader_AgentStats_Column3 = "Time In Status:"
Const TableColumnHeader_AgentStats_Column4 = "Reason in Status:"

Const TableColor_AgentStats_LoggedOut = "White"
Const TableColor_AgentStats_Ready = "White"
Const TableColor_AgentStats_NotReady = "White"
Const TableColor_AgentStats_NonACDCall = "White"
Const TableColor_AgentStats_Talking = "White"
Const TableColor_AgentStats_Working = "White"
Const TableColor_AgentStats_Reserved = "White"
Const TableColor_AgentStats_LoggedIn = "White"

Const TextColor_AgentStats_LoggedOut = "DimGray"
Const TextColor_AgentStats_Ready = "Green"
Const TextColor_AgentStats_NotReady = "Red"
Const TextColor_AgentStats_NonACDCall = "DarkBlue"
Const TextColor_AgentStats_Talking = "RoyalBlue"
Const TextColor_AgentStats_Working = "Orange"
Const TextColor_AgentStats_Reserved = "Orange"
Const TextColor_AgentStats_LoggedIn = "Red"

UCCX Wallboard not displaying Agent statistics correctly