CCE Precision Queue Membership Report

Background

Back in version 9.0, Cisco introduced a new way to assign agents to queues: Precision Queues (PQs). Whereas Skill Groups are a static, direct relationship between Agents and Skills, Precision Queues introduced the concept of custom “Attributes” that can be assigned to Agent. Each Precision Queue is then defined as a complex series of expressions matching those attributes. For example, if you have the attributes Skill.CustomerService and Language.English, you could create an English Customer Service Queue by targeting only agents that have those two attributes. With more complex routing schemes, this means you can have potentially hundreds of Precision Queues covering different combinations of skills, while only having to manage a dozen or so attributes to assign to actual agents. With Skill Groups, you’re stuck directly assigning each Skill Group to the agents eligible for that skill.

The Problem

Precision Queues brought a lot of power and flexibility to agent skilling, but the relationship between Precision Queues and Agents became much murkier, especially if you’re trying to drill down to the level of Precision Queue Steps. With Skill Groups it was simple: you had a Skill_Group table with all your Skill Groups, an Agent table with all your agents, and a Skill_Group_Member table that joined Agent to Skill_Group and defined exactly which skills each agent was assigned. With Precision Queues, Cisco unfortunately didn’t supply an equivalent Precision_Queue_Member table. The Agent_Skill_Group_Real_Time (which powers the out-of-box Agent Precision Queue Membership Report) provides a list of agents logged in to each Precision Queue, but it doesn’t provide any information for logged out agents or give information on what specific step each agent is eligible for. In more complex setups, this makes it difficult to gauge how your queues are setup.

The Solution

All the information we need to understand what PQ Steps are assigned per agent are available within the configuration database, they’re just not designed to be easily queryable. Complex conditional logic is like ( Language.English > 5 AND Skill.CustomerService == 2 ) OR Skill.CustomerService == 10 ) are broken out into individual rows in the database for each term and for each step, while agent attributes are associated via another join table, making merging this information and evaluating the logic very difficult in pure SQL. Fortunately, difficult <> impossible.

A client recently requested a CUIC report showing Precision Queue membership broken out by step, and after some intense SQLing we were able to make it happen. It’s one of the hairiest SQL statements we’ve ever written, but it works: a query you can drop into CUIC and get back PQ Membership without any custom code!

We believe this functionality should be out-of-box, and long believed in the power of sharing knowledge. That’s why we’ve decided to give back to the community by open sourcing the result with the permissive, commercial-friendly MIT license. Feel free to use it, remix it, do what you want with it – whether you’re a partner or a CCE customer (just don’t expect official support from us without a contract). Understanding exactly how the query works is left as an exercise to the reader at this time, but we may do a write up in the future digging into the details. Suffice it to say we rely heavily on “dynamic SQL,” where a SQL statement is dynamically built up as a string and then executed.

You can find a repo with the raw SQL and exported CUIC report (v11.5) in our Github repo. Also: direct link to the SQL

Here is screenshots of the results:

Enjoy!

Notes / Caveats

With larger environments with a lot of PQs and steps, the query doesn’t seem to work if you leave the PQ filter off (i.e. if you try to query every PQ in the system). This is likely because we’re running into size limits either for some of the parameters that build up the dynamic SQL or in the size of the query itself. One idea to fix this is to use cursors to loop through each PQ one by one, running the query and inserting the results into a temp table. That way each query executed only applies to a single PQ. If someone feels like taking this on, please contribute back by issuing a PR to the above linked repo.

We’re happy to accept PR requests if you find any fixes or enhancements.

If you find any issues, please post them to the Github repo and not here. In any case, like everyone we’re very busy and can’t guarantee that we respond to issue requests. We’re always open to PRs though if you want to take it on yourself.