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.
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!
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.
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?
Load up Splunk and switch to the DB Connect app:
- Create a New Identify using user ‘uccxhruser‘ and its password, and name this UCCXUser.
- Create a new connection to your UCCX box. There are a lot of settings in here so I’ll provide a little help below:
- 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.
- The Host is the host IP (or dns name if it’s discoverable).
- Port should maybe be 1504? Informix defaults to 1536 but when that didn’t work I found hints to use 1504 and that did work, so… YMMV. Also once I swear it worked with port 1506. Just keep that in mind if nothing else is working. 🙂
- Default database should be db_cra (I wonder if they didn’t have enough letters for “db_crazy?”)
- And the informix server setting: This is so confusing.
- It’s not the host name, but might be. I found instructions somewhere else that said to take the hostname (as the UCCX reports, which may or may not be the same as the DNS name and which I got from uccx’s command line, but I don’t remember which command), if it starts with a number then prepend an “i”. Convert all dashes to underscores. Append a “_uccx” to the end. So mine, which started out as “hq-uccx” ended up “hq_uccx_uccx” (no leading digit so I didn’t need the initial “i”) and it worked.
- If you get stuck here … umm. Use the interwebs!
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 firstname.lastname@example.org 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!
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.
Read more posts in the Cisco UCCX General Splunk category