Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

Silver

(UCCE) CUIC - Interval Boundary & Agent Not Ready

From the db schema handbook:

NOT_READY: Number of seconds the agent was in the NotReady State with respect to the Media Routing Domain, from the last state change, in NOT_READY state with reason code change, or from the last interval boundary.

For example:

Time=12:10:00 Agent NotReady state transition

Time=12:11:00 Agent Ready state transition, AgentEventDetail.Duration=60

Time=12:25:00 Agent NotReady state transition

Time=12:30:00 Interval boundary change, AgentEventDetail.Duration=300

Time=13:00:00 Interval boundary change, AgentEventDetail.Duration=1800

 

Does that mean that if an agent enters the NotReady state at 1:45 and enters Ready state at 2:15, both the 2:00 and 2:30 intervals will reflect 15 mins?

 

 

thanks,

will

2 ACCEPTED SOLUTIONS

Accepted Solutions

In the report definition, I

In the report definition, I would update this statement (under AuxReason(...)):

CASE WHEN DATEPART(mi,DateTime) < 30 THEN '00' ELSE '30' END

And change it to this:

CASE WHEN (DATEPART(mi,DateTime) < 30 
OR (DATEPART(mi,DateTime)=30 AND DATEPART(s,DateTime)=0)) 
AND (DATEPART(mi,DateTime)<>0 OR DATEPART(s,DateTime)<>0) 
THEN '00' ELSE '30' END

Basically, this should update the Interval evaluation to force X:30:00 into the X:00:00 interval, and X:00:00 into the (X-1):30:00 interval, which is where those durations actually belong.

 

As it's a stock report, you will likely have to make a copy of the definition to make this change.

-Jameson

-Jameson

Just realized I forgot to

Just realized I forgot to adjust the hour down when putting X:00:00 into the previous interval... we're not there yet it seems.

Better would be to replace the whole:

Interval=(lots of stuff),

with this:

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

Everything is looking much better on my end after this change.

-Jameson

-Jameson
15 REPLIES

Will,Close. The 1:30 and 2:00

Will,

Close. The 1:30 and 2:00 intervals would each reflect a 15 minute duration for Not Ready.

-Jameson

-Jameson
Silver

Sorry - that's what I had

Sorry - that's what I had meant.

 

The same would hold true for not-ready reason codes as well? In the highlighted area of the attached image, you'll see 4 intervals (18:00 - 20:00). The '1-Lunch' reason code value is greater than the interval period for 2 intervals. It almost seems that the reason code values are not being split at the interval boundary.

 

Any thoughts on that?

Can you provide the SQL query

Can you provide the SQL query for that report? I think could be an issue with the calculation for that column.

-Jameson

-Jameson
Silver

Sure thing Jameson. It's CUIC

Sure thing Jameson. It's CUIC v9.1.1.10000.117 (in case that matters), with the stock 'agent not ready historical' report (v2.0), with the stock 'rd agent not ready historical' definition.

There's no calculation - just 'a.aux1'.

 

The

Will,That report does not

Will,

That report does not show Media Routing Domain. Do you have MRDs in your setup besides Cisco_Voice? EIM, for example? If an agent has activity in multiple MRDs during an interval, you might potentially see a not ready total larger than 30 minutes.

 

EDIT: a.aux1 IS a calculation. It's a sum of all Agent_Event_Detail.Duration for ReasonCode=1 within that particular SkillTargetID and Interval. You can see how it's defined under AuxReason(...) and AuxDetails(...) in the SQL definition.

-Jameson

-Jameson
Silver

Nope - just Cisco_Voice. Also

Nope - just Cisco_Voice. Also, I've noticed a handful of instances where a specific interval value for a particular reason code is higher than the not ready value for the same interval. I can't fathom how that would happen. Also, if the not ready value is higher than the sum of the reason code values, what could be the cause of that? Shouldn't the sum of all reason code values equal the not ready time for the same interval?

I'm almost to the point of caving and opening a TAC case. +5 for your help, but I would really like to get to the bottom of this still.

As for the Not Ready Value

As for the Not Ready Value being higher than the sum of reason codes, if you have any codes defined outside of the ones in the report (0-9, 50002, 50003, 50004, 50010, 50020, 50030, 50040, 50041, 50042, 32767, 20001, 20002, 20003), they will not show in the report.

 

It looks like this is an issue with the way the report is built.  The NotReadyTime total is coming directly Agent_Interval, not Agent_Event_Detail, so that should be accurate. From your original screenshot, I suspect that the interval calculation in the report is wrong. It looks like the 30-minute report durations are getting rolled into one interval, while the durations less than 30 minutes get rolled into another. For example, the 18:00 Interval in your first screenshot should likely have 29:21 under Lunch, while the 20:00 interval should have 0:00:37 under Lunch.

I think the culprit is the "Interval" calculation for AuxReason. Any Agent_Event_Detail.DateTime value that's exactly at an interval boundry should be counted in the previous interval. I notice in my Agent_Event_Detail table, that sometimes when Duration=1800, the DateTime is exactly on an interval, and sometimes the DateTime is 1 second before an interval boundary.

-Jameson

-Jameson

In the report definition, I

In the report definition, I would update this statement (under AuxReason(...)):

CASE WHEN DATEPART(mi,DateTime) < 30 THEN '00' ELSE '30' END

And change it to this:

CASE WHEN (DATEPART(mi,DateTime) < 30 
OR (DATEPART(mi,DateTime)=30 AND DATEPART(s,DateTime)=0)) 
AND (DATEPART(mi,DateTime)<>0 OR DATEPART(s,DateTime)<>0) 
THEN '00' ELSE '30' END

Basically, this should update the Interval evaluation to force X:30:00 into the X:00:00 interval, and X:00:00 into the (X-1):30:00 interval, which is where those durations actually belong.

 

As it's a stock report, you will likely have to make a copy of the definition to make this change.

-Jameson

-Jameson
Silver

Many thanks Jameson!! I

Many thanks Jameson!! I believe that you're right and the numbers look better now.

Yes, that looks much improved

Yes, that looks much improved.

Thanks for the +5!

-Jameson

-Jameson

Just realized I forgot to

Just realized I forgot to adjust the hour down when putting X:00:00 into the previous interval... we're not there yet it seems.

Better would be to replace the whole:

Interval=(lots of stuff),

with this:

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

Everything is looking much better on my end after this change.

-Jameson

-Jameson
Silver

Brilliant! I won't pretend

Brilliant! I won't pretend that I completely understand it, but I do at a high level at least based on your dumbed down explanation.

Seriously, Jameson... I'm impressed by how you were able to quickly identify the issue and provide the fix. It seems that each time that I post a question/problem, you provide this same level of service. I wish that I could rate higher than 5.

Will,

Will,

Thank you for the compliment!

The magic is this part of the CASE statement:

DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0)

The inner DATEDIFF gives the total number of minutes between the DateTime value and "0" (which is generally 1900-01-01 00:00:00.000 in SQL, but that's not important). We then divide by 30 using integer division (which rounds down). Then multiply by 30 again, and you have the number of minutes between "0" and whatever interval the DateTime value is in. The outer DATEADD turns the value back into a SQL DATETIME value.

I've been using this DATEADD/DATEDIFF trick for a little while for computing Interval in my own reports. I adapted it here by using the CASE statement to calculate the previous interval in the case of a boundary value (X:00:00 or X:30:00 time).

 

In any case, I'm glad to help out... I always learn something new when helping others on here.

-Jameson

-Jameson
Silver

Jameson,The specific example

Jameson,

The specific example that we looked at was corrected, but there are still a bunch of other instances which were not corrected. Any additional thoughts?

Silver

Upon closer examination, the

Upon closer examination, the corrections you made from the single example I provided in the screenshots corrected issues with the hour intervals and the remaining issues are all on the half-hour intervals. I'm not a dba, so hoping that's enough detail to tweak the interval logic. :)

473
Views
10
Helpful
15
Replies