One of the most challenging aspects of troubleshooting a Unified Contact Center Enterprise / Intelligent Contact Management system is the sheer size of most deployments. It can be difficult to obtain reliable information about the exact issue from callers or agents. And even when that information is available, the call could involve any number of CVP Call Servers, IPIVRs, CUCM nodes, Peripheral Gateways, Agents, etc..
A first step in analyzing such issue should always be to look into the call details that are available in the Administration and Data Server (previously Historical Data Server). To get the most out of this document you should be familiar with the SQL language, Microsoft SQL Server and the configuration of a UCCE system. Having a copy of the Database Schema Guide for your UCCE release will be invaluable as well, so pick that up from:
http://tools.cisco.com/squish/21468
Two tables are of particular interest to us.
- The Termination_Call_Detail table gets a new entry for each call leg that is terminated.
- The Route_Call_Detail table gets a new entry when the call router has completed routing a call.
A typical call will have exactly one Route_Call_Detail (RCD) record and one or more Termination_Call_Detail (TCD) records. One of the most important concepts here is the RouterCallKey. It is a unique ID assigned by the Router for each call; it is increased by 1 for each new call. The RouterCallKey is only unique on any given day and to get a truly unique record, you need to combine it with the RouterCallKeyDay. The RouterCallKeyDay value remains the same throughout the day and is increased by 1 at midnight Router time. At the same the RouterCallKey starting value in the Router is reset and starts again at 200.
Let’s put that into practice with an example. We have a customer who said he called in today from 027045000 and he received an error message while he was interacting with our self-service IVR. The query we want to run could look like this.
USE ipcc_awdb
GO
SELECT RouterCallKeyDay, RouterCallKey, ANI, DialedNumberString, DateTime
FROM Route_Call_Detail
WHERE DateTime >= '2011-05-11 00:00:00'
AND ANI LIKE '%27045000'
Some notes about this query:
- The USE ipcc_awdb statement will run our query against the awdb; even though the ipcc_hds database actually holds these historical records. But the awdb has all the necessary views mapped. You should always query the awdb, it will make it easier to later map historical records with current configuration.
- We’re querying the Route_Call_Detail table here, but if that doesn’t return the records you’re looking for, for any reason, just check what’s available in the TCDs.
- Notice how we’re not querying for the exact ANI provided, but leave off the first few digits and use a LIKE clause. Doing so will make sure that our search doesn’t turn up empty because some international dialing codes were stripped off during call routing.
The output may look like this:
| RouterCallKeyDay | RouterCallKey
| ANI | DialedNumberString | DateTime |
|---|
| 149880 | 201 | 027045000 | 80044555 | 2011-05-12 14:53:38.347 |
This gives us the basic information that we’ve been looking for, but there’s a lot more to dig out. Let’s get the TCD records for that same call.
SELECT RouterCallKeySequenceNumber, EnterpriseName, CallDisposition
FROM Termination_Call_Detail
INNER JOIN Peripheral
ON Termination_Call_Detail.PeripheralID = Peripheral.PeripheralID
WHERE RouterCallKeyDay = 149880
AND RouterCallKey = 201
Some notes about the query :
- Our WHERE clause is now the RouterCallKey and RouterCallKeyDay that we’ve obtained before.
- The RouterCallKeySquenceNumber gives us a basic idea of the order in which each of the call legs for this call were created.
- EnterpriseName is not a Termination_Call_Detail field, we’re joining this table with the Peripheral table, so the EnterpriseName is actually the peripheral name for the peripheral that handled this call leg.
The above query returns two results.
RouterCallKeySequenceNumber
| EnterpriseName | CallDisposition |
|---|
| 0 | BRUCVP5 | 13 |
| 1 | BRUCVP5 | 13 |
One of these will be the CVP switch leg, the other is the CVP VRU leg. Of particular importance to us is the Call Disposition. This is an indication of how the call ended. All Call Dispositions are explained in the Database Schema Guide. From this we also learn that this call was handled by BRUCVP5.
It would be really nice if we could now figure out exactly at which point in the callflow the caller got in trouble, and we can. The RCD record can tell us in which script the call ended and in which node of that script.
SELECT Master_Script.EnterpriseName, FinalObjectID
FROM Route_Call_Detail
INNER JOIN Script
ON Route_Call_Detail.ScriptID = Script.ScriptID
INNER JOIN Master_Script
ON Script.MasterScriptID = Master_Script.MasterScriptID
WHERE RouterCallKeyDay = 149880
AND RouterCallKey = 201
Some notes about the query:
- The Script table doesn’t actually contain the scripts as you know them from Script Editor, but rather each version of the scripts, we need to check the Master_Script table to get the actual name of the script.
- The Final Object ID is the script node where the call ended, this is not necessarily the node where the failure occurred, but it should help you work backwards and figure out what could have gone wrong. To see node IDs enable Script – Display Node IDs in the Script Editor.
And finally, the output.
| EnterpriseName | FinalObjectID |
|---|
| ivr_entrypoint_script | 28 |
Armed with this information, it will be a lot easier to figure out what could have gone wrong for this particular caller. You know know which CVP server handled the call, the time, calling number and which part of the ICM Script had an issue. You could now check the CVP.Error logs, monitor the script in question, try to reproduce the issue, ...
A second example is one where we’re getting a report from a caller saying that one of our agents hung up on him, but he does not remember the agent’s name. As a starting point we want to run the exact same query as above. From what we again pick up the RouterCallKey and RouterCallKeyDay.
In the Termination_Call_Detail records, we’re interested in finding the details of the agent that handled that call, so we want to add in a few extra columns and join in the Agent table to get the agent’s details. The query could look like this.
SElECT AgentPeripheralNumber, EnterpriseName, InstrumentPortNumber
FROM Termination_Call_Detail
INNER JOIN Agent
ON Termination_Call_Detail.AgentSkillTargetID = Agent.SkillTargetID
WHERE RouterCallKeyDay = 149881
AND RouterCallKey = 533
And the output of that would be i.e..
| AgentPeripheralNumber | EnterpriseName | InstrumentPortNumber
|
|---|
| 20 | cucm.Doe_John | 2000 |
From this we know that agent John Doe with agent ID 20 at extension 2000 handled this particular call.
Obviously the above is just a brief introduction and there is a lot more information to be found in your ADS / HDS database. The Database Schema handbook will help you in further exploring the wealth of information available. Whenever you open a TAC Service Request, it’s a good idea to provide the TAC engineer with the RCD and TCD records to improve the resolution time of your issue. Having the information from TCD and RCD records available also allows for much easier log reading.
A few final remarks:
- The Management Studio for SQL Server 2008 Express, which is a free download from Microsoft, is able to connect to the SQL 2005 database engine running on your ADS / HDS servers. So if you will be doing a lot of querying, it’s a good idea to load the Management Studio directly on your workstation.
- To easily export the output of queries into a readable format, choose Query - Results To Text in Management Studio.
