Free of charge wallboard 2.4

Unanswered Question
Feb 16th, 2008

Hi all,

It has been a while since I posted my last version of the free of charge wallboard.

Some of you know that I changed jobs since (because my former employer did not like me working on the walboard). Since then I no longer have the option to further develop the script because the current employer is an Avaya shop for telephony.

I keep receiving a lot of request for the files, for unencrypted version, et cetera (and I note that a lot of people never read the full thread, which I have to say that I think that is just lame).

Because so many of you seem to enjoy the wallbaord and have great ideas for add ons and changes, I am posting version 2.4 which is last release I will post here.

The only thing changed from 2.3 to 2.4 is that I removed the encryption.

This will enable the Cisco netpro forum community to further develop this script. Note that the disclaimer file in the archive contains important info about how I would like you to handle the copyright (don't try to make mony from my efforts, unless you are willing to provide me a fair share :-))

Also important to know is that I had no possibility to run a test on this last version so it may contain small errors (shouldn't be the case, but just so you know).

Last but not least, since I have no test bed, I am unable to provide any form of support. So please, don't email me with support questions. If you do need to contact me for whatever other reason please send a mail to l.mourits@caiway.nl

Kind regards,

Leo

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 4.9 (43 ratings)
Filip Olsen Tue, 01/10/2012 - 23:28

Hi bsugamene1

No , i layed it for a rest.. doing other stuff..

It seems to me that the sql statement makes the ISS hang or timeout if i dont have any teamid in statement.. and if i put in , no output.

I think i will try out tristans sql statement and see..

I will preform a upgrade later this month to 8.5 , so i wont spend much time on this for now.. but if i'll find out something new , i will post here :-)

Tristan Blackburn Thu, 01/05/2012 - 17:23

I use the following query to pull the agent stats, which also includes agent info such as number of calls handled, average ring time, average talk time, max talk time. There is a hold time stat that is available, but not all that useful in my environment. I use a new constant in the parameters file as the Team ID.

You'll just need to add the fields in the appopriate locations in the HTML display. Also, the

"INTERVAL(11) HOUR TO HOUR "  is making a timezone adjustment. In this instance adding 11 hours for Sydney. You may need to adjust as appropriate.

"SELECT x.resourceName, t.eventType, x.assignedTeamID, x.datetime, s.handled, s.avring, s.avtalk, s.maxtalk FROM (SELECT t1.resourceID, t1.resourceName, t1.assignedTeamID, 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.resourceName, t1.assignedTeamID ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime INNER JOIN (select resourceid, count(resourceid) as handled, AVG(ringtime) as avring, AVG(talktime) as avtalk, MAX(talktime) as maxtalk from agentconnectiondetail WHERE (startdatetime + INTERVAL(11) HOUR TO HOUR) > TODAY GROUP BY resourceid) AS s ON s.resourceid = x.resourceID WHERE x.assignedTeamID ='" & SpecificCSQTeamID & "' ORDER BY s.handled DESC, x.assignedTeamID, x.resourceName"

gbljoba Tue, 02/07/2012 - 09:03

Finally got this working on 8.5!

One question, all of our CSQ's are seperate teams and as such want to only see their own CSQ's. The overall stats etc would ideally need to be for selected CSQ's.

Is that easy to achieve?

/Jon

k.davis Tue, 02/07/2012 - 11:48

You can adjust your query to include a list of CSQ's via where and or commands-

SELECT CSQName, loggedInAgents, availableAgents, talkingAgents, callsWaiting, convoldestContact, callsHandled, totalCalls, callsAbandoned, convLongestWaitDuration, endDateTime FROM RtCSQsSummary WHERE CSQName='CSQA' OR CSQName='CSQB' OR CSQName='CSQC'

colin.flanagan Fri, 02/17/2012 - 19:36

A little IIS 7 help with Gila. 

Working to get this installed on 2008 64 bit with uccx 8.5SU1 .   ODBC drivers installed , webpage just sits there and churns .  I have verified ASP is installed.   Anyone know of any easy way to debug the ASP to see why the page is failing.  Default IIS logs were not much assistance.  Almost appears to be a permissions issue although everything authenitcates perfectly fine with the uccxhruser .   Does anyone have any setup instructions for IIS 7 specifically ?

I tried this on a 2003 box and was getting

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/wallboard/wallboard.asp, line 305

Line 305 is where the DSN is called for the ODBC connection .

Any pointers or suggestions would be awesome! 

Thanks

gbljoba Thu, 03/01/2012 - 09:06

Great, i got it to just one CSQ and adjusted the font size etc so its now looking much better.

One more question, i enabled the Logo part again but our logo looks a little clumsy. I changed the alignment ot the bottom which is better but has anyone changed the background to have their logo as a faded/ghost image instead of the plain white background?

Regards

Jon

k.davis Thu, 04/12/2012 - 10:48

We recently upgraded from UCCX 5 to 8.5.  While planning that upgrade and testing the wallboard on UCCX 8.5, I found that the time being returned in the SQL query against the UCCX 8.5 server is returned in UTC.  The time returned by that same query against the UCCX 5 server was returned in local server time(Eastern time in my case).  My solution was to add the correct time offset for my timezone in the Sub CalculateStatusTime section.  Sounds like rarcebido may be running into the same thing.  Here's what I used to correct the time calculations-

GMT = DateAdd("h", TimeOffset, Now)

  ' calculate the difference between current date/time and date/time of the last event change

  StatusSeconds = DateDiff("s",EventHappenedDateTime,GMT)

I have a TimeOffset value defined in my parameters.cfg file that represents the current offset of 4 hours for Eastern time.

It works for my wallboards, but I am not running gila-wallboard.asp.  Mine is same concept just not as fancy.  Twice a year I have to update the parameters.cfg file which is a pain, but it's quick fix, so I'm living with it.  There may be fancier ways to determine the offset based on day/month, but I'm not a programmer.

Hope that helps. If you look at the results of your SQL queries in a query tool, you should see what I'm talking about.

colin.flanagan Tue, 04/17/2012 - 10:52

UCCX 8.5 Agentstat's customization .  

Hello,  I recently performed a migration where the customer had *** next to the agents name who took the last call.  After the upgrade to UCCX8.5 the asp page is now returning all agents with the *** of who took the last call.   Here are some excepts from the code.   Any suggestions or feedback would be greatly appriciated.   I'm assuming some of these queries are not valid moving to the UCCX 8.5 platform..  

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

<% If intAgentStatsMaxDays = 0 Then %>

     

       

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>' COLOR='<%= TextColor %>'><%= rs3("resourceName") %>

         

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

        

' COLOR='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

         

' COLOR='<%= TextColor %>'><%= strReasonCode %>

     

     

           

               

           

       

<% End If %>

<% If intAgentStatsMaxDays = 1 And intStatusDays < intAgentStatsMaxDays And intStatusHours < intAgentStatsMaxHours Then %>

      

<%    If AgentStatusBlink = 1 And strAgentStatus = strStatusCode2 then %>

         

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>' COLOR='<%= TextColor %>'><%= rs3("resourceName") %>

         

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

         

' COLOR='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

         

' COLOR='<%= TextColor %>'><%= strReasonCode %>

    <% Else %>

         

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>'><%= rs3("resourceName") %>

         

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

         

' COLOR='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

         

' COLOR='<%= TextColor %>'><%= strReasonCode %>

      <% End If %>

      

          

           

               

           

       

<% End If %>

<% If intAgentStatsMaxDays > 1 And intStatusDays < intAgentStatsMaxDays Then %>

       

           

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>' COLOR='<%= TextColor %>'><%= rs3("resourceName") %>

           

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

           

' COLOR='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

             

' COLOR='<%= TextColor %>'><%= strReasonCode %>

       

       

           

               

           

       

<% End If %>

<% Else %>

<% If intAgentStatsMaxDays = 0 Then %>

     

       

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>' COLOR='<%= TextColor %>'><%= rs3("resourceName") %>

         

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

        

' COLOR='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

         

' COLOR='<%= TextColor %>'><%= strReasonCode %>

     

     

           

               

           

       

<% End If %>

<% If intAgentStatsMaxDays = 1 And intStatusDays < intAgentStatsMaxDays And intStatusHours < intAgentStatsMaxHours Then %>

      

<%    If AgentStatusBlink = 1 And strAgentStatus = strStatusCode2 then %>

         

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>' COLOR='<%= TextColor %>'><%= rs3("resourceName") %>

         

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

         

' COLOR='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

         

' COLOR='<%= TextColor %>'><%= strReasonCode %>

    <% Else %>

         

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>'><%= rs3("resourceName") %>

         

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

         

' COLOR='<%= TextColor %>'><%= strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

         

' COLOR='<%= TextColor %>'><%= strReasonCode %>

      <% End If %>

      

          

           

               

           

       

<% End If %>

<% If intAgentStatsMaxDays > 1 And intStatusDays < intAgentStatsMaxDays Then %>

       

           

<% If rs3("resourceName") = rs5("resourceName") Then Response.Write("***") End If %>' COLOR='<%= TextColor %>'><%= rs3("resourceName") %>

           

' COLOR='<%= TextColor %>'><%= strAgentStatus %>

           

' COLOR='<%= TextColor %>'><%= strStatusDays & " days / " & strStatusHours & ":" & strStatusMinutes & ":" & strStatusSeconds %>

             

' COLOR='<%= TextColor %>'><%= strReasonCode %>

       

       

           

               

           

       

<% End If %>

colin.flanagan Tue, 04/17/2012 - 12:32

One more peoples ...

We had a page for agent details basically displaying  

PURPOSE:     Retrieves Agent Sumary stats & team names and formats them for display.   

This page now return's error 500 .   Anyone have any luck converting this over for UCCX 8.5

  sqlGetAgentSummary = "exec sp_agent_summary '" & Date & "', '" & Now & "'"

Set rs3 = server.createobject("adodb.recordset")

Set cm3 = Server.CreateObject("ADODB.Command")

       cm3.ActiveConnection = cn

       cm3.commandtext = sqlGetAgentSummary

Set rs3 = cm3.execute

  sqlGetQueueSummary = "exec sp_csq_activity '" & Date & "', '" & Now & "'"

Set rs4 = server.createobject("adodb.recordset")

Set cm4 = Server.CreateObject("ADODB.Command")

       cm4.ActiveConnection = cn

       cm4.commandtext = sqlGetQueueSummary

Set rs4 = cm4.execute

**************************************//

%>

   

Jeff Thompson Tue, 04/17/2012 - 16:03

Thanks K.davis for the response. The query that we are using for this section of the wallboard is not pulling any date/time from the database. I am no programmer by any stretch so I could be over looking something, but I can't see where the date/time for this section of the wallboard comes into play. Here are the sections that I believe to in play for this section of the walloard page.

The query and execution:

sqlGetICDstats = "SELECT SUM(totalCalls) as totalCalls, Sum(callsWaiting) as callsWaiting, Sum(callsHandled) as callsHandled, Sum(callsAbandoned) as callsAbandoned, "


  sqlGetICDstats = sqlGetICDstats & "Sum(loggedInAgents) as loggedInAgents, Sum(talkingAgents) as talkingAgents, Sum(availableAgents) as availableAgents, "


  sqlGetICDstats = sqlGetICDstats & "Sum(unavailableAgents) as unavailableAgents, Sum(reservedAgents) as reservedAgents, Sum(avgTalkDuration) as avgTalkDuration, "


  sqlGetICDstats = sqlGetICDstats & "Sum(avgWaitDuration) as avgWaitDuration, Sum(longestTalkDuration) as longestTalkDuration, Sum(longestWaitDuration) as longestWaitDuration FROM RtCSQsSummary Where CSQName Like '%Service%'OR CSQName like '%HondaRecep%'"

Set rs2 = server.createobject("adodb.recordset")
Set cm2 = Server.CreateObject("ADODB.Command")
    cm2.ActiveConnection = cn
    cm2.commandtext = sqlGetICDstats
Set rs2 = cm2.Execute

sqlGetQueueCnt = "Select Count(CSQName) as qCnt FROM RtCSQsSummary Where CSQName Like '%Service%' OR CSQName Like '%HondaRecep%' "

Set rs5 = server.createobject("adodb.recordset")
Set cm5 = Server.CreateObject("ADODB.Command")
    cm5.ActiveConnection = cn
    cm5.commandtext = sqlGetQueueCnt
Set rs5 = cm5.Execute

The display:

       

             

  • Total: <%= Get_Agent_Count(rs2("loggedInAgents"), rs5("qCnt")) %>
  •          

  • Talking: <%= Get_Agent_Count(rs2("talkingAgents"), rs5("qCnt")) %>
  •          

  • Ready: <%= Get_Agent_Count(rs2("availableAgents"), rs5("qCnt")) %>
  •    

  • Not Ready: <%= Get_Agent_Count(rs2("unavailableAgents"), rs5("qCnt")) %>
  •    

  • Reserved: <%= Get_Agent_Count(rs2("reservedAgents"), rs5("qCnt")) %>
  •    

  • Working: <%= Get_Agent_Count(rs2("reservedAgents"), rs5("qCnt")) %>
  •        

       

             
  • Total: <%= Get_Agent_Count(rs2("loggedInAgents"), rs5("qCnt")) %>

  •          
  • Talking: <%= Get_Agent_Count(rs2("talkingAgents"), rs5("qCnt")) %>

  •          
  • Ready: <%= Get_Agent_Count(rs2("availableAgents"), rs5("qCnt")) %>

  •    
  • Not Ready: <%= Get_Agent_Count(rs2("unavailableAgents"), rs5("qCnt")) %>

  •    
  • Reserved: <%= Get_Agent_Count(rs2("reservedAgents"), rs5("qCnt")) %>

  •    
  • Working: <%= Get_Agent_Count(rs2("reservedAgents"), rs5("qCnt")) %>

  •        

Then a function to organize the dispay:

Function Get_Agent_Count(intAgents, intQ)

Get_Agent_Count = intAgents / intQ

End Function

We have another page for overall queue stats. And on that page, on the left side column there is a diplay for 'Overall Agent Stats' The query used for this section is pretty basic -  sqlGetICDstats = "SELECT * FROM RtICDStatistics"

and this works. So it seems there is something that is not collecting correctly on the query in question. I have ran queries against the table and I get back the resuilts I would expect. There is just something off on how this code work against 5.x and 8.x

Again, I certainly know programmer. So if still feel it is time issue, could you show or explain a little more on how to apply the code you think could fix my issue.

I appreciate all the help....

k.davis Tue, 04/17/2012 - 18:21

Jeff T. my response was more towards what rarcebido described as time being off by one hour.(post 341) As you suggest, I don't see any issue related to time in your situation.  I don't see anything wrong, but my wallboard is much simpler. 

decaturutilities Thu, 05/31/2012 - 11:33

Just installed and tweak the gila-wallboard on IPCC Express 8.5.  Works great and very easy to setup.  Does anyone have any code available to display Service Levels or know how to calculate?  Is Service Level information calculated through the (2) Snap Shot Tables or through other (Live) tables?  Our CSR's would really like this displayed.

uncled1023 Fri, 07/06/2012 - 14:48

Hello everyone, I have recently developed a PHP and Ajax wallboard based off of this idea.  It has a report generator, notification system, and modifyable modules for users to view.  If you would like, I could post the source code for you guys if it would be something your interested in.

colin.flanagan Fri, 07/06/2012 - 14:53

Sounds sweet I would like to check it out.  Asp isn't the best but it works. 

ahmedpower Sun, 07/08/2012 - 01:34

Hi

How are you

How i can use the source and apply for our IPCC configuration.

Can you advise please . if I want to deploy it in IPCC , for editing php i can use the PHP designer 7.0.

Thanks

Ahmed Soliman

bryantmarsh Wed, 07/25/2012 - 10:44

Is there a way to filter the second page of the Wallboard 2.4 to limit the number of agents that are displayed?

I have custom wallboards configured among 80+ agents depending on the Queues they are configured for in UCCX.

The second page in the Wallboard displays all agents and I would like to only display only specific agents.

Filip Olsen Tue, 08/07/2012 - 03:04

Hi all... is there any way to speed up the output from agent states ?.. we have 250 + agents and it takes around 15 sec to get output , even if i select teamid where there is 15 agents in team it ...

This is my sql statement :

SELECT x.resourceName, t.eventType, x.assignedTeamID, x.datetime, s.handled, s.maxhold, s.avtalk, s.maxtalk FROM (SELECT t1.resourceID, t1.resourceName, t1.assignedTeamID, 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.resourceName, t1.assignedTeamID ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime LEFT JOIN (select resourceid, count(resourceid) as handled, MAX(holdtime) as maxhold, ROUND(AVG(talktime)) as avtalk, MAX(talktime) as maxtalk from agentconnectiondetail WHERE (startdatetime + INTERVAL(2) HOUR TO HOUR) > TODAY GROUP BY resourceid) AS s ON s.resourceid = x.resourceID WHERE x.assignedTeamID = '69' ORDER BY s.handled DESC, x.assignedTeamID, x.resourceName

Thanks

k.davis Tue, 08/07/2012 - 06:29

How long has your system been up?  Have you ever purged the database?  When I moved from UCCX 5 to UCCX 8.5,(earlier this year) my UCCX 5 system had been up for 4 years, never purged.(tried to purge it but never could get it to work)  Users complained that on UCCX 8.5, the wallboard response was slow.  I am no sql expert, but I got some help from someone who modified the query to only look at 30 days worth of agent state information.  Here's the modified query, note the "sysdate - 30" section.  The 'xxxxxx' section is where I am filtering out the supervisor by name, replaced it with xxxx to keep their name out of this post.  Not sure if you are running into the same issue, but, this query resolved the slow respone in my environment.

SELECT x.resourceName, t.eventType, t.eventDateTime, t.reasonCode, x.datetime FROM (SELECT t1.resourceID, t1.resourceType, t1.resourceName, t1.assignedTeamID, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID and t1.dateInactive is null AND t1.assignedTeamID =" & TeamID & " AND resourceName <> 'xxxxx' and t2.eventdatetime >= sysdate - 30 UNITS DAY  GROUP BY t1.resourceID, t1.resourceType, t1.resourceName, t1.assignedTeamID ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime ORDER BY x.assignedTeamID, x.resourceName

Filip Olsen Tue, 08/07/2012 - 06:50

Hey k.davis

Thank you very much for reply / support , it did the trick.

Now update is less than 1 sec :-)

System's up for more than 1/2 year , never purged the database...

I just need to figure out where to put in handled , avgtalk time and max talktime :-)

Filip Olsen Wed, 08/08/2012 - 07:21

Hi Again.

Anyone knows why  response times is so slow makeing request in this table ( agentconnectiondetail ), even tho i have purged the database until 24 month and ask very simple in it... like : "select resourceid , count(resourceid) as handled from agentconnectiondetail where startdate = TODAY group by resourceid order by handled desc"

It takes around 20-25 sec to data back... even if i make selection of a resource , it takes the same amount of time.

BrianRoberson Tue, 08/07/2012 - 10:32

Having a problem - on Windows 2008 Server Standard 32bit.

UCCX 8.5.1

Was able to setup ODBC DSN and test and apply comes back with success.

In the IIS logs when trying to get stats I receive:

GET /wallboard/wallboard.asp Stats=CSQ|305|80004005|[Microsoft][ODBC_Driver_Manager]_Data_source_name_not_found_and_no_default_driver_specified 80

Does the DSN "name" have to be something specific?  Does the DSN name go in some configuration file that we missed?  The DSN is configured, but i'm unsure how the code knows which one to pull from?

Skip

EDIT: ** Got it fixed ** -- missed the DSN in the main wallboard.asp file.  Modified and it works great.

BrianRoberson Tue, 08/14/2012 - 12:25

Hi,

I'm trying to limit the wallboard to a specific CSQ.  I've succeeded in doing this by modifying the following:

strsql = "SELECT CSQName, loggedInAgents, availableAgents, callsWaiting, callsHandled, totalCalls, callsAbandoned, convoldestContact, convLongestWaitDuration, endDateTime FROM RtCSQsSummary where CSQName like 'HelpDesk' ORDER BY CSQName"

However, on the agent Statistics page, i'm still getting members of the other CSQs showing up in the list.  Can anyone point to why this is happening?

Also, on the Overall Stats page, i'm also seeing all of the teams included in the stats.

Obviously, i'm missing a critical change in the wallboard.asp file to limit the CSQs!

Skip

k.davis Tue, 08/14/2012 - 13:03

Are your agents broken up into different teams?  If yes, you can specify the assignedteamid in the query associated with the agent stats.

BrianRoberson Tue, 08/14/2012 - 13:10

Do you think you could share the whole line? I'm having trouble with the syntax on that particular line.  I also can't tell if it is one single line, or two:

strSQL = "SELECT x.resourceName, t.eventType, 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 x.resourceName"

'strSQL = "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 x.resourceName"

These two lines appear one after another -- or are they on the same line?  Which one gets modified?

k.davis Tue, 08/14/2012 - 13:27

That second one you have listed is commented out.(the single quote mark)  If you look at post 364, you'll see an example of a working query with the assignedteamid specified.  Your query is different, but you should get the idea.  Or maybe someone else can help with SQL syntax.

BrianRoberson Tue, 08/14/2012 - 13:38

k. davis:

OK I used the one from post 363.

the syntax is good, however, I get no stats.  I might have the teamID wrong.  How can I verify the team ID?  I'm wondering if the ID corresponds to the Application Managements Name ID.

BrianRoberson Tue, 08/14/2012 - 13:27

OK, So I added this:

WHERE x.assignedTeamID = '1'

However, I get nothing back now for agent stats.  I looked in UCCX and found the team ID is "1" -- I think?  Does the ID in Application Management correspond to the teamid?

Skip

k.davis Tue, 08/14/2012 - 13:59

No, it's not the ID in Application Management.  The only way I've ever been able to find this information is to browse the database and find the correct table.  The table name you are looking for is- team.  There are plenty of tools out there, I've used SQL Workbench.(http://www.sql-workbench.net/downloads.html) You setup an odbc connection on your workstation and have the tool use it to connect to the database using the same uccxhruser you are using for the wallboard.  Run a select * from team, and you should get back a list of all of your teams with the correct teamid shown.  Hope that helps.

BrianRoberson Tue, 08/14/2012 - 15:56

OK, I was able to get sql workbench installed.  Now i'm getting errors about the informix driver - I assume I need the .jar (java) informix driver?

BrianRoberson Wed, 08/15/2012 - 06:17

I wasn't able to get SQL Bench working, but I was able to find the teamID in our old 5.x wallboard implementation.  I guess when it migrated the team ID stayed the same.  It is all good now!

Thanks k.davis!

BrianRoberson Fri, 08/24/2012 - 08:05

One more bug and I think we are golden.

The "Time in Status" is adding exactly one hour.  Can someone point me to the line that would correct this?

k.davis Fri, 08/24/2012 - 09:34

Are you referring to the amount of time and Agent has been in a particular state?

Sounds like you may be seeing the time issue that's been discussed here before.(I think?)  The query results that come back from the database are in UTC. Your webserver's time is in whatever your local time is.  One of the work arounds is to use the dateadd function to add the correct offset to UTC so that the calculations work against localtime.  Here's the piece of code I'm talking about.  I use a variable called TimeOffset in parameters.cfg to store the current offset value, then I have to manually update that value twice a year when the time changes. Not the fanciest solution, but it works. I think other workarounds may have been discussed here in the past.  Hope that helps.

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))

  GMT = DateAdd("h", TimeOffset, Now)

  ' calculate the difference between current date/time and date/time of the last event change

  StatusSeconds = DateDiff("s",EventHappenedDateTime,GMT)

  ' we will need not only seconds, but also minutes, hours and days

  StatusMinutes = Int(StatusSeconds / 60)

  StatusHours = Int(StatusSeconds / 3600)

  StatusDays = Int(StatusSeconds / 86400)

  StatusSeconds = StatusSeconds mod 60

  StatusMinutes = StatusMinutes mod 60

  StatusHours = StatusHours mod 24

  If StatusMinutes < 10 Then

    If StatusMinutes = 0 Then StatusMinutes = "00"

    If StatusMinutes = 1 Then StatusMinutes = "01"

    If StatusMinutes = 2 Then StatusMinutes = "02"

    If StatusMinutes = 3 Then StatusMinutes = "03"

    If StatusMinutes = 4 Then StatusMinutes = "04"

    If StatusMinutes = 5 Then StatusMinutes = "05"

    If StatusMinutes = 6 Then StatusMinutes = "06"

    If StatusMinutes = 7 Then StatusMinutes = "07"

    If StatusMinutes = 8 Then StatusMinutes = "08"

    If StatusMinutes = 9 Then StatusMinutes = "09"

  End If

  If StatusSeconds < 10 Then

    If StatusSeconds = 0 Then StatusSeconds = "00"

    If StatusSeconds = 1 Then StatusSeconds = "01"

    If StatusSeconds = 2 Then StatusSeconds = "02"

    If StatusSeconds = 3 Then StatusSeconds = "03"

    If StatusSeconds = 4 Then StatusSeconds = "04"

    If StatusSeconds = 5 Then StatusSeconds = "05"

    If StatusSeconds = 6 Then StatusSeconds = "06"

    If StatusSeconds = 7 Then StatusSeconds = "07"

    If StatusSeconds = 8 Then StatusSeconds = "08"

    If StatusSeconds = 9 Then StatusSeconds = "09"

  End If

End sub

leslie-meade Tue, 06/11/2013 - 09:39

I have got the page to work great.. it dispalys all the information and the agents who are logged into the UCCX.

My client now wants to edit the wallboard to onyl show the agents in a particular CSQ or CSQ's.

My very limited http and sql skills have no idea on how to do this. I am hoping someone out there can  help me out ?

etmarcof Tue, 06/11/2013 - 09:44

Hi,

In asp file  just put the name of CSQ you want. For example if your CSQ name is CSQ_IT you should have something like that:

Const CSQ1 = "CSQ_IT"

Const strProdutcCSQ1 = "CSQ_IT"

Regards,

k.davis Fri, 07/19/2013 - 07:04

Hello all,

I have created a C++ application that takes the same information as the browser based wallboard that has been discussed in this thread for years and displays it on the workstation as a standalone application instead of using a web browser.  My original goal was to eliminate the requirement of a Windows based web server in the UCCX environment.  For now I'd say it's a work in progress but I think it's functional.  I have a version that only shows the CSQ based stats for Windows and Linux on sourceforge with executable and source code, plus install instructions.  If interested, go here-

http://sourceforge.net/projects/uccxstatsapp/

There's a discussion board there.  Let me know what you think.  I'll respond to questions and/or problems posted there when I can.  Reviews and comments are welcome!

k.davis Sat, 08/31/2013 - 09:52

I have uploaded an additional version of an application for both Windows and Linux that displays CSQ and Agent stats, similar to the free browser based wallboard discussed here.   I've seen some downloads of the original CSQ only based app but have only recieved one question.  No reports of any success or problems.  If you decide to download and install, please let me know if it works for you (or not!).  I've run it on one machine against my production system and it works. I did have one scenario where while this app was running, I needed to run a report in HRC.  When I launced HRC, I recieved an error saying something about all connections in use.  I shutdown the app and was later able to get into HRC.  This only happend once, but please remember this app is a work in progress, I am not responsible for any problems it may cause on your production system.

http://sourceforge.net/projects/uccxstatsapp/

k.davis Sat, 08/31/2013 - 09:53

Does anyone know if the free of charge wallboard works with UCCX 9?  If so, I would assume my app would work, but I have not had time to build a UCCX 9 server for testing.

billmatthews Wed, 09/18/2013 - 09:00

I had a similar question.  We're running the wallboard (2.4) against our UCCX 8.0 environment.  We're planning an upgrade to 8.5.  Will any changes be necessary?

RobertRhan Thu, 10/10/2013 - 08:01

You wont need to make any changes. If it works now in 8, it will work with 8.5 and 9.0

Ive migrated to all three with the same wallboard.

RobertRhan Thu, 10/10/2013 - 08:55

Hello everyone,

I will provide limited support, but I do want to put this out there if you are just getting started trying to setup your own wallboard. The files are attached with the wallboard I created. This is me giving back to the community that helped me. copy the contents of the zip file to your website folder.

Extensive searching, tinkering, reading and testing results in a fully working wallboard with several stats. Saved my Help Desk many thousands of $$

Using info gathered from this thread and some from server setup help here http://voipmonkeys.wordpress.com/category/cisco-uccx/

Ive been able to put together a working Wallboard solution.

Description:

2 views (1080 formated view) and a resiable agent view

Files include: .asp pages, paremeter.cfg files, css, java scripts and images

Boxes change color based on defined thresholds and agent states

Each page refreshes to display info based on specific time (this is set to 2 second refresh)

Page is set to specific "CSQ Name" you will need to check out the SQL queries in the paremeter files.

Requirements: A working webserver, uccxhruser credentials, odbc connection setup on webserver. I am using a windows server with IIS and .asp extensions.

This is the 1080 formated view

snapshot.png


Agent View

Have Fun,

Robert

Message was edited by: Robert Rhan (Update the contents of the zip file)

cliffj64@gmail.com Mon, 10/21/2013 - 13:58

Robert,

this looks pretty straight forward how do you setup within IIS? is this a new site? new virtual directory? i was able to get the GILA 2.4 working with server 2008r2 and iis but the pages arent correct. do you have step by step? i am on UCCX 8.5.1 and have a pretty good display but the Agent statistics is blank most of the time.

cliffj64@gmail.com Mon, 10/21/2013 - 14:14

I am having an issue with the following screen this is a Gila wallboard 2.4 setup(Server2008R2). all is working except the Agent Statistics page.sometimes it will show users logged in but mostimes not.(seems like only when user initially logs in then it drops them). is this a Db query issue? i have enclosed my screenshot and also parameters and wallboard.asp pages any help would be great.

<%

Const IPCCversion = 8

Const ServerURL = "http://localhost/test/"

Const DBsource = "172.30.60.11"

Const DBServer = "labccx01_uccx"

Const DBdatabase = "db_cra" 

Const DBuserID = "uccxhruser" 

Const DBpass = "12345" 

Const strPageTitle = "Customer Service Statistics"

Const LogoFile = ""

Const RefreshTime = 10

Const DisplayAgentsStatsTrue = 1

Const DisplayCSQStatsTrue = 1

Const DisplayCSQloggedInAgents = 1

Const DisplayCSQavailableAgents = 1

Const DisplayCSQcallsWaiting = 1

Const DisplayCSQcallsHandled = 1

Const DisplayCSQtotalCalls = 1

Const DisplayCSQcallsAbandoned = 1

Const DisplayCSQlongestWaitCurrent = 1

Const DisplayCSQlongestWaitDuration = 1

Const DisplayOverallStatsTrue = 1

Const DisplayICDTotalCSQs = 1

Const DisplayICDStatsTrue = 1

Const DisplayICDloggedInAgents = 1

Const DisplayICDavailableAgents = 1

Const DisplayICDcallsWaiting = 1

Const DisplayICDcallsHandled = 1

Const DisplayICDtotalCalls = 1

Const DisplayICDcallsAbandoned = 1

Const DisplayICDlongestWaitCurrent = 1

Const DisplayICDlongestWaitDuration = 1

Const intAgentStatsMaxDays = 1

Const intAgentStatsMaxHours = 8

Const intAgentStatsMaxNotReady = 5

Const intCSQStatsMinAgentsAvailable = 1

Const intCSQStatsMaxQueuedCalls = 0

Const strPageHeader_CSQStats = "Queue Statistics" 

Const strPageHeader_AgentStats = "Agent Statistics" 

Const strPageHeader_ICDStats = "Overall Statistics" 

Const TextColor_PageHeader = "Navy"

Const TextColor_TableHeader_CSQStats = "Navy"

Const TextColor_TableHeader_AgentStats = "Navy"

Const TextColor_TableHeader_ICDStats = "Navy"

Const TableColumnHeader_CSQStats_Column1 = "CSQ Name"

Const TableColumnHeader_CSQStats_Column2 = "Logged In Agents"

Const TableColumnHeader_CSQStats_Column3 = "Available Agents"

Const TableColumnHeader_CSQStats_Column4 = "Calls In Queue"

Const TableColumnHeader_CSQStats_Column5 = "Calls Handled"

Const TableColumnHeader_CSQStats_Column6 = "Total Calls"

Const TableColumnHeader_CSQStats_Column7 = "Calls Abandoned"

Const TableColumnHeader_CSQStats_Column8 = "Current Wait Time"

Const TableColumnHeader_CSQStats_Column9 = "Longest Wait Time"

Const TableColumnHeader_ICDStats_Column1 = "Total CSQs"

Const TableColumnHeader_ICDStats_Column2 = "Logged In Agents"

Const TableColumnHeader_ICDStats_Column3 = "Available Agents"

Const TableColumnHeader_ICDStats_Column4 = "Calls In Queue"

Const TableColumnHeader_ICDStats_Column5 = "Calls Handled"

Const TableColumnHeader_ICDStats_Column6 = "Total Calls"

Const TableColumnHeader_ICDStats_Column7 = "Calls Abandoned"

Const TableColumnHeader_ICDStats_Column8 = "Current Wait Time"

Const TableColumnHeader_ICDStats_Column9 = "Longest Wait Time"

Const TableColor_ICDStats = "White"

Const TextColor_ICDStats = "Green"

Const TableColor_CSQStats_StatusOkay = "White"

Const TableColor_CSQStats_AlertNoAgents = "White"

Const TableColor_CSQStats_AlertCallsInQueue = "White"

Const TableColor_CSQStats_ArtNoAgentsAndCallsInQueue = "White"

Const TextColor_CSQStats_StatusOkay = "Green"

Const TextColor_CSQStats_AlertNoAgents = "Orange"

Const TextColor_CSQStats_AlertCallsInQueue = "Orange"

Const TextColor_CSQStats_ArtNoAgentsAndCallsInQueue = "Red"

Const TextSize_CSQStats = 5

Const TextSize_ICDStats = 5

Const TableColumnHeader_AgentStats_Column1 = "Customer Service Representative"

Const TableColumnHeader_AgentStats_Column2 = "Current Status"

Const TableColumnHeader_AgentStats_Column3 = "Time in Status"

Const TableColor_AgentStats_LoggedOut = "White"

Const TableColor_AgentStats_Ready = "White"

Const TableColor_AgentStats_NotReady = "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 = "Gray"

Const TextColor_AgentStats_Ready = "Green"

Const TextColor_AgentStats_NotReady = "Red"

Const TextColor_AgentStats_Talking = "Teal"

Const TextColor_AgentStats_Working = "Teal"

Const TextColor_AgentStats_Reserved = "Teal"

Const TextColor_AgentStats_LoggedIn = "Gray"

Const TextSize_AgentStats = 5

%>

RobertRhan Wed, 10/30/2013 - 07:06

It is probably related to the SQL Query and the eventdatetime calculation. I had similar issues until I used the below Queries for the Agent stats page.

Try this one (just change the team ID):

SELECT x.resourceName, x.resourceID, t.eventType, t.eventDateTime, t.reasonCode, x.datetime FROM (SELECT t1.resourceID, t1.resourceType, t1.resourceName, t1.assignedTeamID, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID and t1.dateInactive is null AND t1.assignedTeamID = '18' and t2.eventdatetime >= sysdate - 30 UNITS DAY  GROUP BY t1.resourceID, t1.resourceType, t1.resourceName, t1.assignedTeamID ) AS x INNER JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime where eventtype < '7' ORDER BY x.assignedTeamID, x.resourceName

Or this one (Shows agent calls taken, Change the team ID):

SELECT x.resourceName, x.resourceID, t.eventType, t.eventDateTime, t.reasonCode, x.datetime,s.handled FROM (SELECT t1.resourceID, t1.resourceName, t1.assignedTeamID, MAX(t2.eventDateTime) AS datetime FROM Resource AS t1 JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID and t2.eventdatetime >= TODAY  GROUP BY t1.resourceID, t1.resourceName, t1.assignedTeamID ) AS x JOIN AgentStateDetail AS t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime LEFT OUTER JOIN (select resourceid, count(resourceid) as handled from agentconnectiondetail WHERE (startdatetime + INTERVAL(0) HOUR TO HOUR) >= TODAY  GROUP BY resourceid) AS s ON s.resourceid = x.resourceID where eventtype <= '6' and assignedTeamID = '18' ORDER BY s.handled DESC

cliffj64@gmail.com Mon, 11/04/2013 - 09:16

Robert thanks for the updaed queries, where do i put these? would they go on the wallboard.asp or the agentstats.asp? when  you say change the team ID where is that or how can I find that? Thanks for the time on this.

matthew.horvat Wed, 10/30/2013 - 15:44

I have found this exact same issue with both versions of the wall board. It appears to be an issue with the agent time module as when i comment that out it all shows.

I am having no luck in fixing it though

daneboulton Thu, 11/14/2013 - 10:13

@Robert I got your code up on an IIS 7 server linked to UCCX 9.  I havent changed anything but it looks like something is wrong with the SQL queries provided in your files at least for our instance of uccx.  All I get is a blank page and if you view the source you get:

/ DOM Ready !!

   $(function(){

   // Data Check and Div Change

  

   // Check Available Agents and set Display Colours

    CheckAvailableAgents(

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Also running the SQL query against the ODBC connection returns no results. I was using RazorSQL to test the Queries.  I'm still trying to go over your code to learn how it works but this is also my first time working with UCCX and I'm not the greatest with SQL.  I'll keep tinkering with it but any help or nudge in the right direction would be appreciated. 

Thanks!

RobertRhan Thu, 11/14/2013 - 17:12

Hello,

Ive included new files. Copy the contents of the zip file to the root of your wwwroot or website folder.

If using IIS you will need to install the Classic ASP features. this is different than ASP.NET 4.5 etc.

as for the SQL queries start off in your razorSQL by running this one SELECT * from RTSCQSSUMMARY

if you see data, you should be ok. from here you need to collect the CSQ NAME and the Team ID that you will then need to modify the parts of the SQL Queries in the paremeter.cfg files. There is also time parts in the SQL queries that you might need to tweek if nessesary. Modify the SQL queries and run them in razor sql to make sure they work.

My CSQ is HD_LVL1_CSQ and my team id is 18. This should help you find the right part to modify with your info.

As for the time offset, this can be "set" in the time calculation section of the mainwb.asp files

I will try to update the original zip file I posted.

Let me know if you have any luck.

PS, if the time is an issue, you might want to change/tweak the agent SQL query part: INTERVAL(0) to something like INTERVAL(11) or INTERVAL(12) or something else to see if you see any results.       

Message was edited by: Robert Rhan

daneboulton Thu, 11/14/2013 - 17:54

Thanks for the help I actually figured out the main issue.  I didnt realize the CSQ name was case sensative and I had a letter that was Caps that should have been lowercase.  Once I fixed that it poped right up.  As for the SQL queries I was not able to get the agent stats untill I removed the assignedTeamID all together.  our team ID is 1 for the CSQ i'm working with but even that was not working.  I removed it from the query and it was working.  I will keep playing with it but for testing purposes I just hard coded a few agents in to the SQL query using their resource name.  I'll take a look at your new files tomorrow and see what I come up with.

Thanks for all the help and for sharing your great work!

Actions

This Discussion