UCCX 8.0 and wallboards - general question

[shikamarunara]

I'm writing a wallboard application and have started putting together queries to make against the AgentStateDetail table.  Here is an example;

SELECT agentid, max(eventdatetime) from agentstatedetail where date(eventdatetime) = today group by agentid

This query can take more than five minutes to get results for.  I'm no used to db lookups taking that long.  Am I doing something wrong?

[Jonathan Schulenberg · Accepted solution]

The problem is that table isn't supported for real-time queries. There are only two tables that are (they start with Rt) and only if you turn them on under Tools menu in /appadmin. These two tables are updated every 10 seconds or so by the engine but all other tables are written to asynchronously and are only to be accessed for historical reporting purposes via the HRC.

WIth that disclaimer out there, other vendors have ignored the rule including a SourceForge project I believe. You may want to review how others have structured their queries for comparison. The agentstatedetail table will become quite large over time because it journals every state change over time, not only the current state of the agent. It may be that the table has become so large that it just takes that long to run the query. The only place current state exists for agents - other than the two Rt tables - is the OpenLDAP database that powers CAD. The trouble with that is that: a) it's not SQL; and, b) there is no documented way to access it. You have to find the password to it on the black market.

PS- Sorry I can't give you a perfect SQL query to run here. Partially because you're off the reservation but partially because I am no DBA. If we're lucky someone else will chime in with a magic answer.

Please remember to rate helpful responses and identify helpful or correct answers.

[shikamarunara]

Well, Jonathan, that's very interesting (and good to know).  Thank you for your input.

[Gergely Szabo · Accepted solution]

Hi,

this is an interesting topic.

Actually, let's take a look at your SQL query:

SELECT agentid, max(eventdatetime) from agentstatedetail where date(eventdatetime) = today group by agentid

We are using one table (agentstatedatail) only. No joins. Good.

Next, we are filtering rows by column eventDateTime, comparing the values returned by the DATE function with the value of the TODAY operator. Now, let's stop for a second. The server needs to take the value of each row's eventDateTime, convert to their value to the DATE type, and finally, comparing the result to a static value. It would be much, much easier to compare values without performing this conversion on each row. If you take a look at the schema, eventDateTime is DATETIME YEAR TO FRACTION(3) - it would be easier to compare this with another DATETIME value. So let's start off removing that DATE function from the eventDateTime column and replace the TODAY operator with something else, something representing the today's date, as DATETIME. "Now" is CURRENT. "Today" would be CAST(CURRENT AS DATETIME YEAR TO DAY). So, as the first step, let's modify your query to

SELECT agentid, max(eventdatetime) FROM agentstatedetail WHERE eventdatetime > CAST(CURRENT AS DATETIME YEAR TO DAY) GROUP BY agentid

The next step might sound silly, but... don not run such queries on UCCX at all. Write a simple application, transferring the contents of the desired tables periodically and then do the aggregation. In this case, you could first copy all the rows for the current date, and then perform additional expensive operations.

G.

[shikamarunara]

Hi Gergely,

     Thank you for replying.  The original query I posted is not really a full one, I had originally intended to use joins and filters on it.  For example, this table has a field "eventtype" that I would use to determine the state of the user.  But the user's name is not in this table, I believe it's in "resource" - I think I'd have to do a join between these two tables to pull this information.  Joins and filters are inevitable I think.  Informix's SQL is kind of a pain compared with MySQL.  Your suggestion is very likely the path I will be going down - transfer of contents to a local database on the wallboard server.  Can you offer suggestions?  Have you moved an Informix database to a different SQL? 

[Tanner Ezell · Accepted solution]

I have so I'll chime in here.

The biggest challenge you are going to face is data types and conventions between two different database engines such as Informix and MySQL. Ultimately if you're familiar with SQL you can make this happen without much issue. Don't poll too frequently, preferrably off hours; you don't want to add unnecessary load to the UCCX.

Other best practice measures apply. Best of luck!

Shameless plug: We're wrapping up a piece of software that does this and a bit more, look forward to that Q1 '14!

Tanner Ezell

www.ctilogic.com

[shikamarunara]

Hi Tanner,

     So, a wallboard (my understanding of one, anyway) is intended to have pretty up to date information.  I was planning on updating every ten seconds, so I think this kind of removes some possibilities from the table for me.  I COULD just use the information from the Rt tables (which, it seems, is what they're designed for).  But for things like pulling specific agent states and agent names, for instance, I might be out of luck since this information doesn't live in the Rt tables.  Unless someone hapily tells me I'm wrong and that there's another way.

[Tanner Ezell · Accepted solution]

My apologies, for some reason I had it that you wanted to replicate historical data. For real-time agent state information we built a custom CTI based solution for our wallboard product. Assuming you're not going that route, you're best bet is to calculate via agentstatedetail or possibly figure out the OpenLDAP bits.

I will say though, having written against the OpenLDAP crap in UCCX it is a mess.

If you needed something a bit more custom, I bet we could whip up a webservice that returns real-time information on queues and agents via json or xml.

Tanner Ezell
www.ctilogic.com

[Gergely Szabo · Accepted solution]

Hi,
this way: everything in the database - including the realtime tables - is past. History. A snapshot has been taken from 'now' and saved into the Informix DB. This is why I prefer the term 'near realtime' - it's like with that pun (women's panties are not the best thing in the world). If you want realtime, you want to tap into the power of the UCCX CTI protocol. Sure, it's a bumpy road, and you have to maintain the information flow starting at the CTI server of UCCX all the way up to the wallboard, but it's not impossible.
Looks like a tasty little project.
Good luck.
G.

Sent from Cisco Technical Support iPad App

[Jonathan Schulenberg · Accepted solution]

That ACMI CTI server dies with CAD though, right? Finesse/CUIC has taken a totally different real-time approach I thought.

[Tanner Ezell]

CAD still exists in 10. I have not heard of the CTI protocol being depreciated. In 10, CUIC's "real-time" reports are derrived from the same data that is already provided by the Rt tables. I doubt seriously anything has changed from the CTI side of things.

Tanner Ezell
www.ctilogic.com

[shikamarunara]

I think, for the time being, I'll create just a traditional wallboard with the stats that are available in the two Rti tables.  Creating more than that sounds like it'll be a different and more involved animal and my plate is too full these days   It won't take more than a half hour to make a decent page between the two tables.  Thank you for the insight, much appreciated.

[dandaigle · Accepted solution]

I'm using a wallboard system right now with some info based on the historical reporting tables such as AgentStateDetail, and the information is still pretty accurate. It updates every 5 seconds and it has captured every state change that I've tested it with immediately, and nobody in my office has noticed any lag or innaccuracies. I've uploaded it for anyone to use, feel free to give it a try, there's an installation guide inside.

If you'd rather use your own system, then you need to use the uccxhruser instead of uccxwallboarduser for your datasource access. Other than that it's exactly the same. Note that cisco does not support using the historical reporting tables for wallboards.

[shikamarunara]

Interesting, it looks like you're joining the agentstatedetail and resource tables to post output on agent states.  I started going down this path when I started messing with a query that made cuccx kind of choke (query took 5 minutes to execute).  Anyway, I will take a look at your SQL statement, it looks like you're have some decent luck with it.  Thanks, Dan.

[dandaigle · Accepted solution]

Credit for the sql query goes to l.mourits, who wrote the original script for IPCC 4.x. There hasn't been any impact on the uccx server here, but we also have a pretty small database. Good luck.