on 05-12-2011 03:22 PM
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.
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 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 :
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:
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:
Awesome write up, Thanks Kris
Very informative and helpful! thanks Kris
Ultimate document for troubleshooting & analysis, clearly understandable !
Thanks Cris for sharing such an awesome document for troubleshooting clearly understandable !
Thanks a lot, quiet informative...
Very useful, Thanks!!
Nice explanation.
Fabulous, thank you
In termination Call Detail there are variable 1 to variable 10 I need to know the description detail of it as in the DB schema there is no much information/detail on it. can anyone help me on that
Thanks
really helpful !!
thanks a ton
thank you , I used the same logic to show which PQ the call hit along with the agent .
great info!!!!
Super helpful! Thank you!
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: