Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Wallboard on IPCC 8.5.1.10000-37

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

Everyone's tags (4)
4 REPLIES
Super Bronze

Wallboard on IPCC 8.5.1.10000-37

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

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
New Member

Re: Wallboard on IPCC 8.5.1.10000-37

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

%>


IPCC Queue Information


   
   
   
   
   
   
   
   
   

<%

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 NameLogged In AgentsAvailable AgentsTalking AgentsCalls In QueueOldest In QueueCalls HandledTotal CallsCalls AbandonedLongest 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)
%>


AgentStatusLast Change
" & objAS("resourceName") & "Logged InNot ReadyReadyCall QueuedOn CallWorkingLogged 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.

Super Bronze

Re: Wallboard on IPCC 8.5.1.10000-37

Hey

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

Aaron

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!
New Member

Wallboard on IPCC 8.5.1.10000-37

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

3509
Views
9
Helpful
4
Replies
CreatePlease to create content