Wallboard SLA query

I wanted to see how hard it would be to get SLA information to display on my wallboard. I can up with the following two queries that i thought i would share with you all if anyone is interested.

UCCX SLA Query all queues (UCCX 8.X and above)

SELECT RTRIM(csqSum.CSQName) CSQ, loggedInAgents, availableAgents unavailableAgents, totalCalls, (callsHandled + callsAbandoned + callsDequeued) callsOffered, callsHandled, ((MetLevel * 100) / callsHandled) ServiceLevel, DECODE(1 , DECODE( 1 ,DECODE( totalCalls ,0 ,1,0) , 1 , 0 ) ,0,(callsHandled * 100 / totalCalls)) percentCallsHandled, callsAbandoned, (avgWaitDuration / 1000) as avgWaitDuration, callsWaiting CustomersWaiting, DECODE(1 , DECODE( 1 ,DECODE( callsHandled + callsAbandoned ,0 ,1,0) , 1 , 0 ) ,0,(100 * callsAbandoned) / (callsHandled + callsAbandoned)) percentAbandoned FROM RtCSQsSummary csqSum, ( SELECT CSQNAme, SUM(MetLevel) MetLevel FROM ( SELECT RTRIM(CSQName) CSQName, SUM(CASE WHEN cqd.metServiceLevel then 1 else 0 end) MetLevel FROM ContactRoutingDetail crd, ContactQueueDetail cqd, ContactServiceQueue csq, ContactCallDetail ccd WHERE crd.sessionID = cqd.sessionID AND (cqd.targetID = csq.recordID) AND (crd.sessionID = ccd.sessionID) AND crd.StartDateTime >= TODAY AND cqd.metServiceLevel GROUP BY CSQName ) x GROUP BY x.CSQNAme ) y WHERE y.CSQName = csqsum.CSQName ORDER BY CSQ

UCCX SLA Query Single Queue (UCCX 8.X and Above)

SELECT RTRIM(csqSum.CSQName) CSQ, loggedInAgents, availableAgents unavailableAgents, totalCalls, (callsHandled + callsAbandoned + callsDequeued) callsOffered, callsHandled, MetLevel CallsMetSla, ROUND(((MetLevel * 100) / callsHandled),0) ServiceLevelPercent, DECODE(1 , DECODE( 1 ,DECODE( totalCalls ,0 ,1,0) , 1 , 0 ) ,0,(callsHandled * 100 / totalCalls)) percentCallsHandled, callsAbandoned, (avgWaitDuration / 1000) as avgWaitDuration, callsWaiting CustomersWaiting, DECODE(1 , DECODE( 1 ,DECODE( callsHandled + callsAbandoned ,0 ,1,0) , 1 , 0 ) ,0,(100 * callsAbandoned) / (callsHandled + callsAbandoned)) percentAbandoned FROM RtCSQsSummary csqSum, ( SELECT CSQNAme, SUM(MetLevel) MetLevel FROM ( SELECT RTRIM(CSQName) CSQName, SUM(CASE WHEN cqd.metServiceLevel then 1 else 0 end) MetLevel FROM ContactRoutingDetail crd, ContactQueueDetail cqd, ContactServiceQueue csq, ContactCallDetail ccd WHERE crd.sessionID = cqd.sessionID AND (cqd.targetID = csq.recordID) AND (crd.sessionID = ccd.sessionID) AND crd.StartDateTime >= TODAY AND cqd.metServiceLevel GROUP BY CSQName ) x GROUP BY x.CSQNAme ) y WHERE y.CSQName = csqsum.CSQName AND y.CSQName = 'VMSUPPORT'


What this will produce is a result set that has the following headers













Still a bit of a work in progress and take about 1.5 seconds to run so i think needs some improvement


Have i re-invented the wheel ???

On my pursuits to work with .net and the Cisco EM API i created my own EM tool for logging phones in querying etc. Now a little birdie pointed me to another company called IPCommute. These guys seem to be based in the UK and have created a nice little tool called EMSnap.


this seems to do everything mine does just slightly differently and these guys are currently allowing you to use this free of charge.. (Good work) Video Link of the tool in action..

I suggest you check them out as they have a few other cool products..

I will just need to make mine better:)..

Exploring Extension Mobility API – Birth of a Tool !!!

Recently i have had a bit of time to checkout the latest cisco V9 API for Extension Mobility and i have found that creating an app to take advantage of this API was quite easy.

I am always getting bogged down with customer deployments when customers ask if they can have certain people logged in to certain phones.
Do it manually on each phone !!
Not really an option so came the birth of my EM tool.

Main Screen

This tool exposes all of the API giving the user the ability to log a phone in / log a phone out just by supplying the username and extension. Authentication is provided by the Admin username and password of the Cisco Communications Manager.

I also added features to see where a user was currently logged in and what user was logged into what device giving this tool a sort of audit feature too.

One other feature was to see what profiles a user holds and then you can login to a device specify the profile you want to use.

all of this was great to learn about but what i really wanted was a way to read in a csv file with users and phone mac addresses and do a bulk upload.
Something like

Username, Devicename

Well after a bit of think i came up with just that:)

Bulk Screen

Now i have the ability to logout or login users using a cdv which solves my issue in one click:)

One last feature i experimented with was the ability to logout all device attached to the Communications Manager you are controlling. Very Dangerous !! but wow worked first time and all phone logged out perfectly.


All in all the Cisco EM API toolkit has some great power waiting to be exploited. if you get the time take a look.


enjoy ..

Remove CUCM LDAP Sync

I have had a customer needing to revert back from a Cisco Communication Manager synced with Microsoft Active Directory to a stand alone Cisco Communication Manager.

This turned out to be relatively simple to resolve.

You need to first remove all the LDAP configuration from the Cisco Communication Manager

Delete the LDAP Configuration –> Disable the Integration –> Deactivate the DirSync Service from servicability.

All the users at this point will be converted to normal standard cucm users.

Once this has been completed the open up a console connection to the cucm and run this command.

run sql update enduser set status=1

this will then make all the users active again on the system.

You may find that you get a memory allocation error using the sql update command if you have a few users configured. in my case there where 4000+ users so i had to use a slightly altered command

run sql update enduser set status=1 where telephonenumber like ‘2%’

we then just changed the number in the telephone number from 0 to 9 which did all users in batches.

to make sure you got all the users you can use the command

run sql select * from enduser where status=0

if you got them all then this should return no enteries

UCCX Wallboard Development Part 3

Well after my last post you now all should have a good idea on how to get a good connection to the UCCX database. This does have some advantages for us now as we can run custom reports using this connection as well as getting information for the wallboards.
Maybe this could be another post in the future you never know.

To test the wallboard ODBC Connection is working i use a little program called RAZORSQL which you can download from here
I don’t get any money / Sponsorship for recommending this product but i have used it quite a lot and in my opinion worth a buy. They do however give you a free trail copy which should be good enough to get you going. (Has always worked for me)

RazorSQL Connection

doing a simple select from the wallboard queue you should see all of the csq’s configured on your system.

UCCX Real Time Data Write

Before you will see any data in the rtcsqssummary fields you need to enable the writing of this in the UCCX Admin.
Under Tools –> Real Time Snapshot config you will see this window.

UCCX Realtime Snapshot Settings

tick all the boxes and make sure you add the ip address of your webserver in the field provided. This field is very important when you have a cluster. If you do not put the ip address of the wallboard server in here and want to query this web site http://<ip Address of UCCX/uccx/isDBMaster. if you don’t do this you will not see the window below.

UCCX isDBMaster

This will become clearer when i talk about setting up a wallboard to work across clusters.(Futures)

Wallboards – Where to start


Well you can display what ever you want on a wallboard really but i guess we have to have somewhere to start. I am  interested in the following information.

  • Logged In Agents
  • Average Talk Time
  • Longest Talk Time
  • Available Agents
  • Average Wait Time
  • Longest Wait Time
  • Talking Agents
  • Oldest Contact
  • How Many Calls have been handled
  • Calls Waiting in Queue
  • How many Calls Have been abandoned

this should be enough information to get us going.
Using a simple Select * from rtcsqssummary here csqname = ‘<CSQ Name>’ query you can display more information as this query will return the following information.

  • csqname
  • loggedinagents
  • availableagents
  • unavailableagents
  • totalcalls
  • oldestcontact
  • callshandled
  • callsabandonded
  • callsdequeued
  • avgtalkduration
  • avgwaitduration
  • longesttalkduration
  • longestwaitduration
  • callswaiting
  • enddatetime
  • workingagents
  • talkingagents
  • reservedagents
  • startdatetime
  • convavgtalkduration
  • convavgwaitduration
  • convlongestwaitduration
  • convlongestwaitduration
  • convoldestcontact

so as you can see there are lots and lots of fields you can display using just one simple query. if you need more complicated stats like agent status or even display things like SLA of a particular queue / team this is also possible if you have enough SQL knowledge and time (Trust me you will need some time). I may cover this in another post later on but for now lets keep things simple.

What Server Technology To Use


This is quite an important choice really as there are many programming languages out there in the wilds of the internets for us to use. You can choose any language you like for this so long as it can connect to an ODBC connection and query a SQL server, as you might think there are loads from classic ASP/JSP to the .nets or even PHP, this is why i use ODBC as so many different technologies can connect to it and use it.
i am going to use the same technologies as the GILA wallboards use for now and maybe later on expand on this to some other languages like .net or PHP.

So we are going to use a mix of classic ASP and JavaScript. Also just because we can i want to try and make this a little bit modular if possible (Programming gurus don’t hate on me Sad smile i am not the best programmer in the world, even though i studied it for a while) to allow me to re use a lot of the code across multiple pages and keep thing more manageable in the future. So i am going to use some JQuery too. JQuery is a cool framework built of top of JavaScript making it easier to manipulate the DOM, this is good for us because we can use its DOM Manipulation goodness to make the cells of our nice new wallboard change colour when certain values are returned from the database.

Shall we talk about wallboard layouts now ?


I want to keep this simple for now and if any of you have been using the GILA wallboard then a lot of this will make a lot of sense, my version will be a little simpler well for now anyway and i know there are a million ways to create a wallboard i am just trying to spark some sort of idea in your minds as i have found little or no good resources that explain how to do any of this other than a great free solution (GILA-Wallboard 2.4) that we can use as a good template. Ramble Ramble Ramble …


I want a simple layout to start with a page containing 4 cells down and 3 cells across or 4×3 Smile

ok time for some simple html code to get us started. (Before i get shot down in flames some of this code does need re-factoring and cleaning so i guess suggestions welcome Winking smile)

Parameters File

To keep all the config settings in one place (Again thank GILA-WB Guys) i am using a parameters file parameters.cfg

' **********************  Wallboard Parameters **************************
Const IPCCversion = 8
Const PageTitle = "UCCX Wallboard"
Const ServiceName = "Service Name"
Const Left_Logo = "../images/Blank.png"
Const Left_Logo_Alt_Txt = "Blank"
Const Right_Logo = "../images/Blank.png"
Const Right_Logo_Alt_Txt = "Blank"
Const ServerURL = ""
Const DBsource = ""
Const DSN = "Wallboard-1"
Const ServerName = "uccx01_uccx" 
Const ServicePort = "1504" 
Const Protocol = "onsoctcp"
Const DBdatabase = "db_cra"  
Const DBuserID = "uccxhruser"  
Const DBpass = "mypassword"  
Const companyName = "VOIPMonkey"
Const RefreshTime = 5
Const wbPageTitle = "Wallboard"
Const wbqueuename = "Service"
Const UCCX_1_IP = "" ' * Future Use *
Const UCCX_2_IP = "" ' * Future Use *
Const UCCX_Rest_URI = "/uccx/isDBMaster" ' * Future Use *
Const SQL_Conn_String = "" ' * Future Use *
Const SQL_Command = "select * from rtcsqssummary where csqname like 'Queue1'"

currently this is a very simple parameters file but this may grow later on in the project.

All the layouts are done using divs and a css file.

CSS File we are using

/*  Author:  	voipmonkey						*/
/*  Pupose:		Style Sheet for Wallboard		*/

/* layout css */
* {

#container {
  margin: 0 auto;
  height: 100%;
  width: 100%;
  text-align: left;

		clear: both;
.clearfix:after {
   content: ".";
   visibility: hidden;
   display: block;
   height: 0;
   clear: both;
		margin: 0;
		width: 33%;
		height: 20%;
		display: block;
		padding: 0;
		margin: 0;
		width: 33%;
		height: 20%;
		display: block;
		padding: 0;
/*page css*/
		font: 85.5% "Arial", sans-serif;
		background-color: Black;

This is a basic css to get the right layout for our wallboard. You need to create a css folder under the wallboard folder and save this into it with the name layout.css. I know this is not the cleanest but hey work in progress. This will be enough to get the desired layout we want. Now the gila wallboard does show you the status of your agents and this is something i want to utilise but for now as i keep saying simple simple simple.

ok so we got our parameters file and the css for this wallboard so i guess we should look at the layout for the wallboard.asp file. I will break this down into small chunks so can explain what each section does.

Lets Begin

The first thing we need to do is get a connection to the database and setup a record set to collect all the data and store it for display purposes. As you can see all the connection string has been broken up to use the variables we set in the parameters.cfg file.

<!DOCTYPE html>
<!-- Created VOIPMonkey			  -->
<!--#include file="./parameters.cfg"-->
					WBConnect = "Dsn="& DSN &";" & _
						   "Host="& DBsource &";" & _
						   "Server="& ServerName &";" & _
						   "Service="& ServicePort &";" & _
						   "Protocol="& Protocol &";" & _
						   "Database="& DBdatabase &";" & _
						   "Uid="& DBuserID &";" & _
						   "Pwd="& DBpass &""
					Set cn = Server.CreateObject("ADODB.Connection")
					cn.ConnectionTimeout = Session("ConnectionTimeout")
					cn.CommandTimeout = Session("CommandTimeout")
					cn.open WBConnect
					Set SQLStmt = Server.CreateObject("ADODB.Command")
					Set RS = Server.CreateObject ("ADODB.Recordset")
					SQLStmt.CommandText = SQL_Command
					SQLStmt.CommandType = 1
					Set SQLStmt.ActiveConnection = cn
					RS.Open SQLStmt

Header Section

	<meta http-equiv="Refresh" content="<%=RefreshTime%>;">
	<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
	<link type="text/css" href="../css/wallboard.css" rel="stylesheet" />
		<script type="text/javascript" src="../js/jquery-1.6.2.min.js"></script>
		<script type="text/javascript" src="../js/Wallboard.js"></script>

The head section uses the old refresh technique to refresh the page. i need to figure out how to get this to refresh using Ajax i guess to make it slicker but for now this does function ok. we use the head section also to set the page title again from the parameters file and also load all the external JQuery files. All query files can be downloaded from www.JQuery.com or a better way if your web server has internet access is to point to a web location then the query is always up to date. as i have no internet on the web server calling locally is fine. CSS files are also loaded here too.

JQuery Section (Where the magic Happens) Wallboard.js

Most of the data returned by the query can just be displayed but i thought it would be nice to checked the returned data for certain fields and change the colour of the Div Background. This was it will catch your eye and come one replicate most wallboard out there. So for Available Agents, LoggedInAgents and calls waiting we have some JQuery code to alter the Divs

<script type="text/javascript">
			// DOM Ready !!
			// Data Check and Div Change
			// Check Available Agents and set Display Colours
			// Check Logged In Agents and set Diaplay Colours
			// Check Calls Waiting and set Display Colours

The JQuery below is what makes the changes to wallboard when the DOM is ready.

	CheckActiveServer = function(){
	//Check the UCCX Server for the master database for displaying data
	CheckAvailableAgents = function(AvailableAgents){
		if (AvailableAgents == 0) {
		if (AvailableAgents >= 1) {
	CheckLoggedInAgents = function(LoggedInAgents){
		if (LoggedInAgents == 0){
		if (LoggedInAgents >= 1){
	CheckCallsWaiting = function(CallsWaiting){
		if (CallsWaiting >= 6){
		if (CallsWaiting >= 4){
		if (CallsWaiting >= 2){
		if (CallsWaiting == 1){
		/*if (CallsWaiting == 0){

This code above need to be store in a file called wallboard.js which we referenced in the head section above.

Main Body Section

Now for the rest of the code this takes all of the stuff we setup above and renders the page for the user.

<div id="container">
<div id="main_content">
	<div id="lia">
		<h2>Logged In Agents</h2>
	<div id="at">
		<h2>Average Talk</h2>
	<div id="lt">
		<h2>Longest Talk</h2>
<div class="clearfix"></div>
	<div id="aa">
		<h2>Available Agents</h2>
	<div id="aw">
		<h2>Average Wait</h2>
	<div id="lw">
		<h2>Longest Wait</h2>
<div class="clearfix"></div>	
	<div id="ta">
		<h2>Talking Agents</h2>
	<div id="oc">
		<h2>Oldest Contact</h2>
	<div id="ch">
		<h2>Calls Handled</h2>
<div class="clearfix"></div>
	<div id="cw">
		<h2>Calls Waiting</h2>
	<div id="tm">
		<h2 class="csqname"><%=RS("csqname")%></h2>
				sOffset = +0 'The time offset from GMT at the server 
				cTime = i + sOffset 
				tDiff = (DateAdd("h",cTime,Now)) 
				Response.write FormatDateTime(tDiff,3)
	<div id="ca">
		<h2>Calls Abandoned</h2>
<div class="clearfix"></div>
		<!-- End of Main Content Div -->
	<!-- End of Container Div -->

This will now give you a very simple wallboard using old technologies but works ok. I will at some point in the future be changing all of this to use c# and aspx files so will post an update when this has been completed.

Hope you found this useful.

UCCX Wallboard Development Part 2

Well i guess i better share my finding and research with you guys as promised.

I am going to take this from the beginning so for the more advanced guys out there please stay with me.

Setting up the Data Connection

first thing we need to do is get a connection between the web server and the Cisco Contact Centre Express server, additional information can be found in the historical reporting and admin guides if required.
This is a little different depending on what web server you are using but i am sure it wont be too hard to spot the differences.
I will be using a 2008 web server so all screen shots will be from this OS. 1 small note before you get configuring as the Cisco Contact Centre Express solution is not a 64-bit application you need to make sure you use the 32 bit IBM Informix Drivers not the 64 bit drivers. if you do use the 64 bit drivers a nasty error will follow, so use the 32 bit drivers which i will give a link to later in this post.

Download and install the IBM Informix Drivers

To download the IBM Informix Driver go to the IBM site and go through a lot of pain to login and accept EULA’s etc. or simply download the 32bit 3.50 TC9 Server from here (This is 100MB+ so be patient).

Once downloaded then run through the install clicking next next next (Usual Windows Install)

Informix Install 1                      Informix Install 2

Informix Install 3                      Informix Install 4

Informix Install 5                      Informix Install 6 Drivers

Now When you have nearly finished the install you need to make sure you install the Drivers package that comes with the SDK, click the link and click finish will launch it. Accept all the defaults.


Informix Install 7                      Informix Install 8

When you have completed this step then you are ready to setup the connection to the server.

Setting Password on the UCCX Servers

Before you can configure the ODBC Source you have to reset two password on the UCCX server. Login to the UCCX Admin and then go to Tools –> Password Management

UCCX Password Rest Wallboard

Reset the Wallboard user and the Historical Reporting User password to anything you like we will use these later on. One thing to note here is do not use Special Characters like &!”$£ if you do you will break the Historical reporting tool. You have been warned !!!!

If you have a cluster make sure you do this on both nodes.

Setup ODBC Connection

Under admin tools on the web server launch the ODBC Connection Manager (ODBC Data Sources)
If you are using a 64bit OS for the wallboard server then you need to use the ODBC manager from the wow64 subsystem, if you don’t you will not see the INFORMIX Connections.

Under the System DSN add a New DSN


Select the IBM Informix ODBC Driver ( I know ODBC is not the best thing in the world for all you budding developers out there but it works well and with a wallboard you need stability)

ODBC General

Give the data source a name and description this can be anything you like.

ODBC Connection

Now it comes to the Connection setup, In the server name you need to add the name of the uccx server generally in lower case with the post fix of _uccx after it. The host name is simply the ip address of the uccx server.

Set the following options

  • Server Name = Name of Server with the _uccx extension (If you have – in server name use _ instead)
  • Host Name = This is the IP Address of the UCCX Server
  • Service = Set this to 1504
  • Protocol = The Protocol must be set to onsoctcp
  • Options = Leave these blank
  • Database Name = As before is db_cra
    • UserId = You can use Two Different Usernames here (WallboardUser and uccxhruser) if you use Wallboard user then you do not get access to agent state information so for our purposes use uccxhruser)

Password = this is the password you set earlier in this post.

Environment Settings

ODBC Envornment

Set the Client and database settings to en_US.UTF8 to match the server. now your are done you should no click apply then under connection select Apply and test, this should come back saying successful of you get an error go back and try checking the settings again. Looking at the case of the server name is always a good place to start as Linux is case sensitive.

Wallboard Preview

Just to keep you guys watching i thought i would add a little teaser to this post and show you what i am trying to achieve with this series of posts.

Wallboard No Logo

Wallboard no logo

Wallboard with Logo

Wallboard Logo

Additional Boards

I am working on additional boards with graphs and nice SLA stats but you will have to wait and stay tuned to see these.

Until Next Time !!!

UCCX Wallboard Development Part 1

I have been kicking around the idea of re-developing a wallboard for Cisco UCCX, that can be found free on the cisco support pages. GILA-wallboard i think is the wallboard name and was written in Classic ASP.


This is a great board but i wanted to be able to style this very quickly using just one config file.

I also have seen links to a Phone Service on the cisco support forums which i think will also be quite useful and will be trying to add this in too.


I am sure that the requirements will change moving forward with this and this will be a diary / log of how i get on developing this app.

I hope this help some of the readers of my blog (the small few Smile)

Stay Tuned !!!