Informix .Net Provider (CSDK) UCCX

I have been having some real fun over the last few days trying to deploy a Informix To SQL Proxy i have written. In essence what this does is take all the important information from UCCX and places it on another server which supplies the wallboards with information.

This came about when i have been seeing issues with the more and more pages that query the UCCX agent status live from the database, causing the system to slow down taking minutes to return data rather than seconds. I think that the UCCX throttle SQL connections eventually if you send a lot to it (Not confirmed this with Cisco yet).

Anyway along came an idea to write a windows .Net service to process the back end database and store all information into a separate server running SQLExpress. This will also allow me to bring in some sort of cluster awareness to the wallboards too as the current solution i have been working with fails when the system fails over. So what a good idea to make this part of the InformixtoSQL service. (Well i thought so, Phase 2)

I am blabbing as usual but i have a few challenges doing this and wanted to share my experience with both the people who read my Blog :)

writing an interface to process data into SQL is not that hard but getting the data proved to be a little more challenging. In comes the Informix .Net Provider (CSDK)

There are two types of .net Provider that you get with the SDK.

  • IBM Informix .Net Provider (CSDK)
  • IBM Data Server .Net Provider (Common.Net)

These two Providers are quite different when it comes to connecting to the database.

The CSDK version uses quite a complicated connection string like the one below

 

Database=db_cra;Host=1.1.1.1;Server=voipmonkey01_uccx;DB_LOCALE=en_US.UTF8;

CLIENT_LOCALE=en_US.UTF8;Service=1504;Protocol=onsoctcp;UID=uccxhruser;Password=password;"

 

Where as the Common .net provider uses a protocol call  DRDA which i am not sure the uccx db supports, this was the first place i fell down i think. as far as the .net codes goes you setup connections and tear them down the same way.

 

Database=perf;Server=localhost:9092;User ID=informix;Password=informix123;";

 

So the code would look something like this to read the data.

String connectionString = "Database=perf;Server=localhost:9092;User ID=informix;
Password=informix123;";

IfxDataReader rd;
int i = 0;
Boolean testStatus = true;

try
{
     using (IfxConnection conn = new IfxConnection(connectionString))
     {
          conn.Open();

          using (IfxCommand cmd = conn.CreateCommand())
          {
              cmd.CommandText = "select * from simpletable";

              rd = cmd.ExecuteReader();
              rd.Read();
              do
              {
                   if (rd.HasRows)
                   {
				//Assuming the table has two columns
                        Console.WriteLine("{0}: {1}", rd[0], rd[1]);
                   }

              } while (rd.Read());

          }
      }
  }
  catch (IfxException exc)
  {
       Console.WriteLine("Update: {0}", exc.Message);
       foreach (IfxError error in exc.Errors)
           Console.WriteLine("Error: ({1}): {0}", error.Message, 
           error.NativeError);

       testStatus = false;
  }
  return testStatus;

now on my dev machine this worked great using the first method (CSDK) but when i moved i over to a new platform to try deploying it everything started to go wrong !!

so i turned to the only place i could, GOOGLE :)

But as you know sorting through the chaff took a while hence my long post ..

I finally gave up on the common .net provider for now but as the CSDK is never getting updated i need to think of a better way to get this working. Even on the Dev platform the common.net provider fails stating the UCCX db is not catalogued correctly so no idea how i can resolve this one (Thoughts welcome).

so moving over the new platform the IBM.Data.Informix dll would not load, i search high and low and finally found it.

On the connection string you need to tell the program which provider name to use. So the connection string will look like this

   1:  string ConnectionString = "Database=db_cra;Host=1.1.1.1;Server=voipmonkeyx01_uccx;DB_LOCALE=en_US.UTF8;CLIENT_LOCALE=en_US.UTF8;Service=1504;Protocol=onsoctcp;UID=uccxhruser;Password=password;" providerName="IBM.Data.Informix";

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

providerName="IBM.Data.Informix at the end resolved my issue. long i know but the simple things normally are :)

 

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 …

Layouts

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 = "http://1.1.1.1/wb/"
Const DBsource = "2.2.2.2"
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 = "2.2.2.2" ' * Future Use *
Const UCCX_2_IP = "3.3.3.3" ' * 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 */
* {
	text-align:center;
	vertical-align:text-top;
	font-family:Arial,Georgia,Serif;
}

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

#clear_float
	{
		clear: both;
	}
.clearfix:after {
   content: ".";
   visibility: hidden;
   display: block;
   height: 0;
   clear: both;
}
	
#lia,#at,#lt,#aa,#aw,#lw,#ta,#oc,#ch
	{
		float:left;
		margin: 0;
		width: 33%;
		height: 20%;
		display: block;
		padding: 0;
		border-style:solid;
		border-width:1px;
		border-color:black;
	}
#cw,#tm,#ca
	{
		float:left;
		margin: 0;
		width: 33%;
		height: 20%;
		display: block;
		padding: 0;
		border-style:solid;
		border-width:1px;
		border-color:black;
	}
	
/*page css*/
			
body
	{ 
		font: 85.5% "Arial", sans-serif;
		background-color: Black;
	}
	
h1	
	{
		text-align:center;
		vertical-align:text-top;
		font-size:60px;
		font-family:Arial,Georgia,Serif;
		color:White;
	}
	
h2	
	{
		text-align:center;
		vertical-align:text-top;
		font-size:30px;
		font-family:Arial,Georgia,Serif;
		color:White;
	}


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>
<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
					RS.MoveFirst
	
				%>


Header Section

<head>
	<meta http-equiv="Refresh" content="<%=RefreshTime%>;">
	<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
	<title><%=PageTitle%></title>
	<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 !!
			$(function(){
			// Data Check and Div Change
			
			// Check Available Agents and set Display Colours
				CheckAvailableAgents(<%=RS("AvailableAgents")%>);
				
			// Check Logged In Agents and set Diaplay Colours
				CheckLoggedInAgents(<%=RS("LoggedInAgents")%>);
				
			// Check Calls Waiting and set Display Colours
				CheckCallsWaiting(<%=RS("CallsWaiting")%>);
			});
		</script>
		
		
</head>


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

(function($){
	CheckActiveServer = function(){
	//Check the UCCX Server for the master database for displaying data
		
	};
	CheckAvailableAgents = function(AvailableAgents){
		if (AvailableAgents == 0) {
				$('#aa').css('background-color','Red');
				$('#aa').css('color','white');
				}
		if (AvailableAgents >= 1) {
				$('#aa').css('background-color','Green');
				$('#aa').css('color','white');
				}
	}
	CheckLoggedInAgents = function(LoggedInAgents){
		if (LoggedInAgents == 0){
			$('#lia').css('background-color','Red');
			$('#lia').css('color','white');
		}
		
		if (LoggedInAgents >= 1){
			$('#lia').css('background-color','Green');
			$('#lia').css('color','white');
		}
	}
	CheckCallsWaiting = function(CallsWaiting){
		if (CallsWaiting >= 6){
			$('#cw').css('background-color','Red');
			$('#cw').css('color','white');
		}
		if (CallsWaiting >= 4){
			$('#cw').css('background-color','CD4232');
			$('#cw').css('color','white');
		}
		if (CallsWaiting >= 2){
			$('#cw').css('background-color','CD5547');
			$('#cw').css('color','white');
		}
		if (CallsWaiting == 1){
			$('#cw').css('background-color','CD826F');
			$('#cw').css('color','white');
		}
		/*if (CallsWaiting == 0){
			$('#cw').css('background-color','Black');
			$('#cw').css('color','White');
		}*/
	}
})(jQuery);


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.

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


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

ODBC 1

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.

https://supportforums.cisco.com/thread/269435

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.

https://supportforums.cisco.com/thread/269430

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