Solved: IPCC Ent 7.1 reporting/db question

kre8or2007 · ‎10-21-2009

Experts,

I have been asked for reports that show Longest wait time in queue

The amount of time the queue is full

I am not much of a programmer and know that I can use something like a global or peripheral variable to get this done, is there an easier way to get data like this? We don't have anyone that can customize reports so I am stuck with having to figure a way to do it with programming.

geoff · ‎10-21-2009

With IPCC, the call type set just before the queue to skill group node (or nodes, because there could be complexities with overflow skills and queue to multiple skills) is the key metric to measure the customer experience - how long they wait before being answered. It is not easy to get an exact answer to the question, but you can get a "histogram" on the answer time of the call type by setting up the 10 Bucket Intervals to suit your purposes.

The Reporting Guide then says:

WebView Reports for Answered/Abandoned Within Separate Intervals

WebView provides two call type interval reports that measure answered and abandoned calls for interval increments:

caltyp31: Call Type Abandon/Answer Distribution by Half Hour (IPCC only) Report

caltyp32: Call Type Abandon/Answer Before Interval Report

These reports show calls that abandoned and were answered within the increments that you set: for example, 0-5 seconds, 5-10 seconds, and so on.

WebView Reports for Answered/Abandoned Accumulated in Intervals

WebView also provides two call type interval reports that provide cumulative data based on the intervals you configured:

caltyp33: Call Type Abandon/Answer During Interval by Half Hour Report

caltyp34: Call Type Abandon/Answer Before Interval by Half Hour Report

These reports show cumulative data for calls that were abandoned and calls that were answered for the increments that you set: for example, less than 5 seconds (<00:05), less than 10 seconds (<00:10), less than 15 seconds (<00:15), and so on.

If you were to query the Termination_Call_Detail table you could find out the exact time the longest call had to wait for that call type, but that's a different kettle of fish.

Regards,

Geoff

View solution in original post

geoff · ‎10-21-2009

With IPCC, the call type set just before the queue to skill group node (or nodes, because there could be complexities with overflow skills and queue to multiple skills) is the key metric to measure the customer experience - how long they wait before being answered. It is not easy to get an exact answer to the question, but you can get a "histogram" on the answer time of the call type by setting up the 10 Bucket Intervals to suit your purposes.

The Reporting Guide then says:

WebView Reports for Answered/Abandoned Within Separate Intervals

WebView provides two call type interval reports that measure answered and abandoned calls for interval increments:

caltyp31: Call Type Abandon/Answer Distribution by Half Hour (IPCC only) Report

caltyp32: Call Type Abandon/Answer Before Interval Report

These reports show calls that abandoned and were answered within the increments that you set: for example, 0-5 seconds, 5-10 seconds, and so on.

WebView Reports for Answered/Abandoned Accumulated in Intervals

WebView also provides two call type interval reports that provide cumulative data based on the intervals you configured:

caltyp33: Call Type Abandon/Answer During Interval by Half Hour Report

caltyp34: Call Type Abandon/Answer Before Interval by Half Hour Report

These reports show cumulative data for calls that were abandoned and calls that were answered for the increments that you set: for example, less than 5 seconds (<00:05), less than 10 seconds (<00:10), less than 15 seconds (<00:15), and so on.

If you were to query the Termination_Call_Detail table you could find out the exact time the longest call had to wait for that call type, but that's a different kettle of fish.

Regards,

Geoff

kre8or2007 · ‎10-23-2009

Geoff,

Awesome, that looks like it may work! Any idea on how to set the increments? I looked at the reporting guide and see where it is under the "planning" part but no explanation as to how to set it up.

I am guessing I would have to go to the call type list, aand click "Override System information Default"

The only thing in the list is Default_bucket_intervals, any idea of how I make a different one?

kre8or2007 · ‎10-23-2009

I have found it under the "list menu" after looking again, I will toy around with this; thanks for the help

CindyHill · ‎10-23-2009

In Config, open List Tools, then Bucket Interval List. Once the time ranges are setup save it, then edit each of the call types you want to associate to the new interval list.

kre8or2007 · ‎10-26-2009

Although a good report, these caltyp reports weren't sufficient enough for the business users; is there a way to report on a global user variable, or is that another database query? I have an administrative script that sets a user variable and seems to be working, I just can't seem to report on it. Any ideas?

geoff · ‎10-26-2009

Essentially - no. That's the nature of the beast.

What do you mean by a global variable? If an admin script sets a global variable, it can then be used by routing scripts to influence a call. But it is not stored anywhere. You could set a call type and report on that.

If it's a CALL VARIABLE - unique to each call and carried with the call - then it will be stored in the TCD with the call and be in the database. But you can't use WebView to report on variations.

There is another option - custom reports. This may be be what you need.

Regards,

Geoff

kre8or2007 · ‎10-26-2009

Geoff,

I don't think I can set a call variable on a max wait time in queue, so I used an admin script; it has to be stored somewhere though, the script I wrote basically says "If call queued in router now is older than the time set in the variable, then write this time to the variable". It logically seems to be working, it's been running all day and I've been watching the wait times.

I have also been looking at custom reports, but we only have Crystal Reports and the only thing I see recommended is Sybase. Is Crystal Reports not supported? I am guessing I would have to do some magic with the ASA5 variable to create this report.

geoff · ‎10-26-2009

If the call goes through that if node, indicating a longer than normal wait time, set a call type. You can then report on calls that had to wait longer than the setting of the variable. Even if you adjust this, the call type would remain.

But if the business users did not like the buckets (which are all they should need) they won't like that. Why do some customers have unreasonable views on reporting - go for the big picture.

The only solution is to query the TCD, including the Peripheral Variable where you stored this value. Of course, if you are querying the TCD you never needed to go to that trouble in the first place, since the TCD knows how long it was waiting.

How are your SQL skills. You can query the HDS after hours and get the answer. Cisco are very protective of this database and don't like you talking to it, but if you are reasonable, it is the source of all knowledge. ;-)

Regards,

Geoff

kre8or2007 · ‎10-27-2009

It seems as if that is the only answer; I cannot set a call type since they are wanting the actual number value of the max wait time. My SQL skills aren't very good, I have asked our DB group if they can help; would it be acceptable to use Crystal Reports to connect to the HDS and grab this data after hours?

geoff · ‎10-27-2009

Cisco state that one should not directly query the HDS. This is the party line. I tried to find chapter and verse about querying the HDS directly, but could not find that in a quick search - but I know it's there.

The TCD table is optimized for writing, and not for reading, so it is not indexed optimally for SQL selects required for your queries.

You probably have a regular backup of the HDS running (say once every Saturday night) and you could take this backup and build a HDS copy from it, and then run your queries against that.

The report would be a week late, but you are guaranteed not to be compromising the ICM system.

This is an area in which you need to be very careful. If you query the HDS directly and have a problem with the AW/HDS, TAC could refuse to support you.

Because of the difficulties around this, I strongly suggest going back to the business and pushing hard on the standard bucket interval reports.

Regards,

Geoff

kre8or2007 · ‎10-27-2009

That's what I was afraid of

Thanks for the help!