UCCX, the Beginnings

We occasionally get requests to incorporate UCCX information into the Cisco CDR Reporting and Analytics app. That turns out to be a rather large undertaking!

Just because it is unlikely to get incorporated into our flagship product any time in the near future doesn’t mean that you can’t ingest that data and take a look on your own.

Prerequisites

This assumes you

• Have some reasonable proficiency with Splunk.
• Hopefully have at least heard of the Splunk DB Connect app.
• Have UCCX, and can make changes to it (or find a person who can – it’s one password change/​reset so you can use it)
• Are using UCCX 11.6 or possibly 12? (These instructions can be adapted for other versions, possibly fairly simply, but it will take some adaptation.)
• Have a desire to get at least some basic information out of UCCX.

If that sounds like you, then read on!

Preliminary Setup

Set or enable the password in UCCX for the uccxhruser by navigating to Unified CCX Administration menu bar > Tools > Password Management > Historical Reporting User and setting it. Remember this password, obviously.

Have Splunk installed, and be sure a reasonably recent version of DB Connect is installed (I’m using the latest, 3.14). This will necessitate java, but you can find information about all that in Splunk’s documentation for DB Connect.

Also create an index ​‘cisco_​uccx’ using whatever method you need to use for your system.

Download the Informix jdbc connector. This took some searching – IBM is the official host and you’ll find a LOT of IBM pages talking about how to install this, and use this, and how to find this, and they’re all sideskirting the simple fact that it’s nearly impossible to actually *find* on their site. But I’m sure you’ll be successful – I was! Anyway, I used version 4.10 I think, and it worked fine. Once found, drop the jar file into the splunk_​app_​db_​connect/​drivers folder and restart the task server. Again, you can read Splunk’s fine documentation linked above for more info.

Setup

Don’t you love that I have a section of prerequisites, a section for preliminary setup, and only now am getting to the section on setup?

First, …

1. Log into your Cisco Unified Intelligence Center.

2. Depending on your version, click either ​“Data Sources” on the left, or ​“Configure” then ​“Data Sources”

3. Edit the UCCX data source

4. Note the Datasource Host, Port, Database Name, and Instance for use below.

Load up Splunk and switch to the DB Connect app:

1. Create a New Identify using user ​‘uccxhruser‘ and its password, and name this UCCXUser.
2. Create a new connection to your UCCX box. 
   
   • The connection name should be ​“UCCX” (it’s what I use in my configs – if you use something else you’ll have to change all the ​“connection = UCCX” strings in the files to whatever you used.)
   • Pick your UCCXUser Identity.
   • Use a connection type of Informix
   • Pick the appropriate timezone.
   • From the UCCX data source values, enter 

     • Host = Datasource Host field

     • Port = Port field (probably 1504)

     • Default Database = Database Name (probably db_cra)

     • Informix Server = Instance field

Testing

You should now be able to, in DB connect, switch to the Data Lab, then to the SQL Explorer.

Pick your connection UCCX, the Catalog db_​cra, and select one of the tables with data, like contactcalldetail. After giving it a few seconds (or minutes) you’ll see data populate.

IF YOU GET DATA, then continue.

If you do not get data back…

Try double-checking everything, or maybe just pick a different table? I know DB Connect pretty well, but at this stage in the game it likely has nothing to do with DBX and is instead all about UCCX and Informix, neither of which I’m an expert on.

Also in both cases, this document ​“Cisco Unified Contact Center Express Database Schema Guide, Release 11.6(1)” may be useful.

On to the amazing parts…

Well, not really amazing.

But I wrote up an app. It’s a small app, it is almost a TA but does include like one simple dashboard as a really terrible example.

Most importantly, it’s a skeleton of how to build out what you may need.

You can get it from here… email us at support@​sideviewapps.​com for the moment and we’ll get it to you. We’re working through some details for a Splunkbase link yet).

In it I include a few things that should help you. Let me list those and why they’re useful, so that you have a shortcut to adding more tables!

Standard Tables

What do I mean about standard tables? Ones that have a reasonable timestamp, and also a reasonable rising column.

A good example is the AgentConnectionDetail table. The timestamp should be the startDateTime field, and though there’s no ​“perfect” rising column*, the endDateTime is probably good enough.

*Why can’t I use something like sessionID? Because it’s not unique and different rows in the DB may be written at different times with the same values. Also in order to use any field as a rising column, it’d have to be guaranteed also to be monotonically increasing. Unfortunately, that’s not the case here – that field can be reset at times or possibly skip around depending on timing.

*But why can’t I use the primary key (which in this case is a composite key consisting of half a dozen fields)? Well, while it does uniquely identify, there’s nothing about that group of fields that let’s you have a simple way to select ​“all the ones I haven’t seen before”. An incrementing ID of some sort could be used easily – ​“select all rowids where it’s greater than the last rowid I’ve seen”. But the way these composite keys are, there’s just no way to do this, given our limitations in Splunk (mostly in DB connect).

* So while a timestamp field isn’t great, it’s generally good enough and it’s all we have in this case.

Tables without a good timestamp field

You’d think this wouldn’t be an issue, but it is. For instance the table ContactQueueDetail simply has no timestamp in it. Maybe there’s a hidden field, but my quick searching didn’t find one. But that’s OK, on this we just set the index_​time_​mode to current which says that if it reads a new row and inserts it into Splunk, use the time of the insert as the time for the Splunk event.

Also for that particular table, it’s ALSO an example of the next section…

Tables without a visible rising column

The ContactQueueDetail table also has another problem. There’s no obvious rising column!

It turns out that often – but not always! – there’s a hidden ​“rowid” field that you can use. In this case, you’ll see I prepend the rowid to the SQL query and use the resulting field as the rising column.

SELECT rowid, * FROM ....

That says to select columns ​“rowid”, then ​“all the others”.

I’d love to do this with all the tables, but it’s simply not there on a lot of them so you can’t blindly assume it is. Still, if you are adding new tables, you can try this one simple trick and see if rowid actually is there and is populated – if it is, it’s the thing to use in ANY table because it’s more likely to be unique and rising than enddatetime or whatever.

Tables that maybe should be a lookup

A judgement call! Sometimes you’d have data that’s relatively static and might be easier to include as a lookup, so I give one example on doing this.

The Resource table, which is what UCCX calls Agents and people and stuff, is likely to remain fairly static, and the way it needs to be referenced is fairly conducive to being a lookup. So I use dbxquery in a saved report that can be scheduled however often you feel it should (Daily? Every 3 hours? Weekly?) to download a new copy of the Resource table and use the outputlookup command to write it to a csv file.

To see what that one does, go to the uccx app, click on Settings, then Searches, Reports, and Alerts. In there find the Generate Resources from UCCX via dbxquery search.

In there, the search does a

| dbxquery connection="UCCX" query="SELECT * FROM db_cra:resource" |outputlookup resources.csv

Which does an inline query and outputs what it finds into a lookup file called ​“resources.csv” Then I simply created a lookup (Settings, Lookups, Lookup Definitions) so that I could use that as a lookup.

(And of course, it could if desired be set up as an automatic lookup!)

Where to go from here?

If this sounds interesting, and if you’d have a little time and energy to help move this along, please contact us and request access to the app! Email us at Support@​sideviewapps.​com.

We’d love to offer some assistance in helping you to figure out what you actually need from this data and how to build that.