Wallboard on IPCC 8.5.1.10000-37

[jstickler]

Hello all,

I had a previous wallboard that was running great on IPCC 7.x.  We have recently upgraded to IPCC 8.5.  I have followed the instructions on how to connect to the database with SQuirreL SQL and that is successful.  I am not sure what to put in for the Connection string to pull up this information.  Here is a snip of some of the connection code that we previously used:

Set objCN = Server.CreateObject("ADODB.Connection")

objCN.Open "Provider=SQLOLEDB; Data Source = XXX.XXX.XXX.XXX\CRSSQL; Initial Catalog = db_cra; Integrated security=SSPI;"

strsql = "SELECT CSQName, loggedInAgents, availableAgents, talkingAgents, callsWaiting, convoldestContact, callsHandled, totalCalls, callsAbandoned, convLongestWaitDuration, endDateTime FROM RtCSQsSummary WHERE CSQName = 'LOGV_SUPPORT_CSQ' "

strAgent1 = "SELECT x.resourceName, x.resourceType, t.eventType, t.reasonCode, x.resourceGroupID, x.datetime, 'x.STATUS' = CASE WHEN t.eventType = 1 THEN 'Logged In' WHEN t.eventType = 2 THEN 'Not Ready' WHEN t.eventType = 3 THEN 'Ready' WHEN t.eventType = 4 THEN 'Call queued' WHEN t.eventType = 5 THEN 'On Call' WHEN t.eventType = 6 THEN 'Working' WHEN t.eventType = 7 THEN 'Logged Out' END 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 = 1 ORDER BY x.resourceName"

Set objRS = objCN.Execute(strsql)

My issue is I do not know how to connect to Squirrel SQL to pull the information from that connection.

Thanks for any assistance.

Jason

[Aaron Harrison]

Hi

Squrrel SQL is a java app so will use JDBC. Squirrel connects to the DB, your app connects to the DB - your app won't 'use' Squirrel at all.

You'll be wanting the ODBC drive for your native Windows business... the parameters for the DB connection should be pretty similar. You can get the ODBC driver by installing the current version of HRC on your wallbaord, then you'll just need to edit this line I think:

objCN.Open "Provider=SQLOLEDB; Data Source = XXX.XXX.XXX.XXX\CRSSQL; Initial Catalog = db_cra; Integrated security=SSPI;"

Replacing the provider= with an informix one... a quick google shows lots of examples out there (http://www.google.co.uk/search?q=informix+adodb.connection&rls=com.microsoft:en-gb&ie=UTF-8&oe=UTF-8&startIndex=&startPage=1&redir_esc=&ei=cD7dTrO7L8PAswbGlPjHCw#sclient=psy-ab&hl=en&safe=off&rls=com.microsoft:en-gb&source=hp&q=informix+(%22adodb.con...)

Here's one:

sConnectString = "Provider=MSDASQL.1:Password=password;" _
"Persist Security Info=True;User ID=userid;Data " _ 
"Source=""odbc_dsn"";Initial Catalog=database_name" 
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConnectString

odbc_dsn will be the name of a DSN you create - if you add a system DSN you can put in most of the fields you set when you make the connection with Squirrel and test it so you know you are halfway there..

Regards

Aaron

Please rate helpful posts...

[jstickler]

I figured that I would respond as I now have this working on my side.  These are the steps I had to take.

1) Download and install the Informix ODBC driver - http://www.ciscounitytools.com/Applications/CxN/InformixODBC/InformixODBC.html
2) Setup an ODBC connection to the IPCC Informix server - http://amrgaber.wordpress.com/2011/02/24/create-an-odbc-connection-to-connect-to-cisco-uccx-server/
3) Setup the ASP page as follows:

<% @LANGUAGE = VBScript %>
<%
Option Explicit
Response.Expires = 0
Dim objCN, objRS, objAS, strsql, strAgent1, queuedcalls, lastupdate, strArray, oldinqueue
Const queuelimit = 2
Const oldinqueuelimit = "0:02:00"
Const RefreshTime = 120
Response.AddHeader "Refresh", RefreshTime
%>



    
    
    
    
    
    
    
    
    
<%
Set objCN = Server.CreateObject("ADODB.Connection")
objCN.Open "UCCX"

strsql = "SELECT CSQName, loggedInAgents, availableAgents, talkingAgents, callsWaiting, convoldestContact, callsHandled, totalCalls, callsAbandoned, convLongestWaitDuration, endDateTime FROM RtCSQsSummary WHERE CSQName = 'LOGV_SUPPORT_CSQ' "
strAgent1 = "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 = 1 ORDER BY x.resourceName"
Set objRS = objCN.Execute(strsql)
While Not objRS.EOF
 Response.Write "
"
 Response.Write ""
 Response.Write ""
 Response.Write ""
 queuedcalls = objRS("callsWaiting")
 If queuedcalls > queuelimit Then
  Response.Write "
"
 Else
  Response.Write ""
 End If
' Response.Write "
"
 oldinqueue = objRS("convoldestContact")
 If oldinqueue > oldinqueuelimit Then
  Response.Write "
"
 Else
  Response.Write ""
 End If
 Response.Write "
"
 Response.Write ""
 Response.Write ""
 Response.Write "" & "" & VbCrLf
 lastupdate = objRS("endDateTime")
 objRS.MoveNext
Wend

%>
CSQ Name
Logged In Agents
Available Agents
Talking Agents
Calls In Queue
Oldest In Queue
Calls Handled
Total Calls
Calls Abandoned
Longest Wait Time
" & objRS("CSQName") & "
" & objRS("loggedInAgents") & "
" & objRS("availableAgents") & "
" & objRS("talkingAgents") & "
" & queuedcalls & "
" & queuedcalls & "
" & objRS("convoldestContact") & "
" & oldinqueue & "
" & oldinqueue & "
" & objRS("callsHandled") & "
" & objRS("totalCalls") & "
" & objRS("callsAbandoned") & "
" & objRS("convLongestWaitDuration") & "
    
    
<%

Set objAS = objCN.Execute(strAgent1)
While Not objAS.EOF

 Response.Write "
"
 If objAS("eventtype") = "1" Then
 Response.Write "" 
 ElseIf objAS("eventtype") = "2" Then
 Response.Write "" 
 ElseIf objAS("eventtype") = "3" Then
 Response.Write "" 
 ElseIf objAS("eventtype") = "4" Then
 Response.Write "" 
 ElseIf objAS("eventtype") = "5" Then
 Response.Write "" 
 ElseIf objAS("eventtype") = "6" Then
 Response.Write ""  
 ElseIf objAS("eventtype") = "7" Then
  Response.Write ""  
 End If
 Response.Write "" & "" & VbCrLf
 objAS.MoveNext
Wend

objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
' The string returned in endDateTime is of the form "MM/DD/YYYY HH:MM:SS [AM/PM]"
' Extract just the time and the AM/PM indicator
strArray = split(lastupdate," ")
lastupdate = strArray(1) & " " & strArray(2)
%>
Agent
Status
Last Change
" & objAS("resourceName") & "
Logged In
Not Ready
Ready
Call Queued
On Call
Working
Logged Out
" & DATEADD("h",-6,objAS("datetime")) & "


This page will update every <%= RefreshTime %> seconds

(Last updated: <%= lastupdate %>)

The last Response.Write during the Agent information is subtracting 6 hours from the time that appears in the database to show the correct CST time.  The database appears to be writing time in UTC.  UCCX is the name of the ODBC connection that I created.  There is also a line referring to x.resourceGroupID = 1.  1 is the GroupID for one of the call centers we have at this location, I just change that to correspond to the correct call center.   This is a heavily modified version of another wallboard that I found on the forums, unfortunately I do not know who wrote the original information.  I hope that this helps someone else, I spent a lot of time trying to get this working when we upgraded from 7.x to 8.5.

[Aaron Harrison]

Hey

Good of you to report back so comprehensively, will no doubt help someone else out...

Aaron

[Joe Murray]

jstickler - This was a huge help to me, as we were facing the same issue.  Thank you so much for posting this!!