State Trace Report - Returns same values on same DateTime

[roberteliasson]

Heya,

I´m currently writing a report definition which shows every event that occurs on Agent level.

However - the query below returns multiple rows with the exact same time stamp.

Anyone who know how to reduce this to one row per time stamp?

SELECT

CONVERT(char(10),AST.DateTime,111) as Date,

CONVERT(char(5), AST.DateTime, 108) as Time,

CASE RC.ReasonText WHEN 'Undefined' THEN '' ELSE RC.ReasonText END ReasonText

(Person.FirstName + ' ' + Person.LastName) as FullName,

AST.ReasonCode,

AST.SkillTargetID,

CASE AST.AgentState

WHEN 0 THEN 'Logged Off'

WHEN 1 THEN 'Logged On'

WHEN 2 THEN 'Not Ready'

WHEN 3 THEN 'Ready'

WHEN 4 THEN 'Talking'

ELSE 'Unknown' 

END AS AgentState,

CASE AST.Direction

WHEN 0 THEN ''

WHEN 1 THEN 'In'

WHEN 2 THEN 'Out'

WHEN 3 THEN 'Other In'

WHEN 4 THEN 'Other Out/Direct Preview'

WHEN 5 THEN 'Outbound Reserve'

WHEN 6 THEN 'Outbound Preview'

WHEN 7 THEN 'Outbound Predictive/Progressive'

ELSE 'Unknown'

END AS Direction,

FROM

(Select AgentState,

DateTime,

Direction,

ReasonCode,

SkillTargetID

FROM Agent_State_Trace

Where DateTime >= :startDate

and DateTime <= :endDate

and SkillTargetID IN (:AgentSkillTargetID)

) AST

LEFT OUTER JOIN (SELECT

ReasonCode,

ReasonText

FROM Reason_Code

GROUP BY ReasonCode, ReasonText

) RC

ON RC.ReasonCode=AST.ReasonCode

LEFT JOIN Agent

ON Agent.SkillTargetID=AST.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

[Jameson Gagnepain · Accepted solution]

Agent State Trace? I hope you don't have that enabled for very many agents, there are performance concerns with leaving that active. As you can tell from your query, it creates MANY rows in SQL. Sometimes multiple rows per second for a single Agent. It's basically like "debug" level output, and should only be used when you're trying to diagnose an issue.

In the query you have above, I see absolutely no reason why this should be an Anonymous Block with sub-queries as you have it laid out. It seems to me that you're making this more difficult than it needs to be. Joining AST, RC, Agent, and Person directly should be sufficient.

As for reducing the row output, you'll need to GROUP BY something. I would say a good start may be to group by SkillTargetID and PeripheralCallKey... but the problem with that is you would join all of your NULL PCKs together - everything that's not a call. Maybe narrow it down more, and group by SkillTargetID, PeripheralCallKey, and DateTime down to the second (not microseconds). In your SELECT, take the MAX of Direction, as you may see one entry with a blank direction and one with an actual direction for each call.

-Jameson

[roberteliasson]

Jameson,

Thank you very much! That sorted the problem - Didn´t realize the report was in milliseconds!

The reason why I´m using Anonymous Block is because I´m most comfortable with this kind of query.

I would also like to add the duration of each state, I tried using LEAD to get the next row´s time value and subtract that with the current row´s time value - but apparently Cisco doesn´t support the LEAD function.

Any brilliant idea on how I can do that, or is it simply impossible?

Current query:

SELECT

(Person.FirstName + ' ' + Person.LastName) as FullName,

CASE AST.AgentState

WHEN 0 THEN 'Logged Off'

WHEN 1 THEN 'Logged On'

WHEN 2 THEN 'Not Ready'

WHEN 3 THEN 'Ready'

WHEN 4 THEN 'Talking'

WHEN 5 THEN 'Work Not Ready'

WHEN 6 THEN 'Work Ready'

WHEN 7 THEN 'Busy Other'

WHEN 8 THEN 'Reserved'

WHEN 9 THEN 'Call Initiated'

WHEN 10 THEN 'Call Held'

WHEN 11 THEN 'Active'

WHEN 12 THEN 'Paused'

WHEN 13 THEN 'Interrupted'

WHEN 14 THEN 'Not Active'

ELSE 'Unknown' 

END AS AgentState,

AST.Date,

AST.Time,

CASE AST.Direction

WHEN 0 THEN ''

WHEN 1 THEN 'In'

WHEN 2 THEN 'Out'

WHEN 3 THEN 'Other In'

WHEN 4 THEN 'Other Out/Direct Preview'

WHEN 5 THEN 'Outbound Reserve'

WHEN 6 THEN 'Outbound Preview'

WHEN 7 THEN 'Outbound Predictive/Progressive'

ELSE 'Unknown'

END AS Direction,

AST.ReasonCode,

AST.SkillTargetID,

CASE RC.ReasonText WHEN 'Undefined' THEN '' ELSE RC.ReasonText END ReasonText

FROM

(Select AgentState,

Date = CONVERT(char(10),DateTime,111),

Time = CONVERT(char(8), DateTime, 108),

Direction,

ReasonCode,

SkillTargetID

FROM Agent_State_Trace

Where DateTime >= :startDate

and DateTime <= :endDate

and SkillTargetID IN (:AgentSkillTargetID)

GROUP BY AgentState, CONVERT(char(8), DateTime, 108), CONVERT(char(10),DateTime,111), Direction, ReasonCode, SkillTargetID

) AST

LEFT OUTER JOIN (SELECT

ReasonCode,

ReasonText

FROM Reason_Code

GROUP BY ReasonCode, ReasonText

) RC

ON RC.ReasonCode=AST.ReasonCode

LEFT JOIN Agent

ON Agent.SkillTargetID=AST.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

GROUP BY

AST.SkillTargetID, Person.FirstName, Person.LastName, AST.AgentState, AST.Time,AST.Date, AST.Direction, AST.ReasonCode, RC.ReasonText

How the result looks for one Agent after your help:

[Jameson Gagnepain]

Robert,

Lack of support for LEAD has nothing to do with CUIC, it's entirely to do with the version of SQL that is running on your AW/HDS. LEAD Is a feature of SQL Server 2012 and newer. The newest SQL Server version that UCCE supports is 2008.

It's certainly possible to get the duration without LEAD, but it's not simple, and it would be VERY time-consuming to run. You would need to join AST with itself, and filter out all results that aren't the one directly after. Not an easy task, especially with the limited information available in that table. If you were only enabling state trace on one agent at a time, you could just join on RecoveryKey+1 and it would be much simpler to get that duration you're looking for.

I'd recommend trying to work with normal queries when you can (instead of Anonymous Blocks)... it's much simpler and quicker to troubleshoot them by running directly in SQL Server Management Studio, and then bring the query into CUIC when its ready.

I use Agent State Trace so rarely that I don't think I would have considered building out a report for it in CUIC... One of those things where if I need the data, I'll just run a quick query in SQL Server Management Studio. How many Agents do you have Agent State Trace enabled on? From the Admin guide:

Enabling trace can impact system performance, as it requires additional network bandwidth and database space. Typically, you use this feature for short-term tracking of specific agents. The system imposes a configuration limit on the number of agents for whom you can enable trace.

-Jameson

[roberteliasson]

Jameson,

Thanks for the explanation - I´ll skip 'Duration' then!

I´ll start working with normal queries then. Unfortunately I don´t have database access so I can´t run them in SQL Server Management Studio.

All of our Agents in several countries (around 800) have Agent State Trace Enabled. Our IT department takes care of these kind of things so unfortunately i won´t be able to affect this.

[Vinod Patil]

Hi Robert,

Thanks a lot for sharing the code.

Best regards,

Vinod

[Vinod Patil]

Hi Jameson,

I have modified the query provided by Robert in oder to have agent extensions

SELECT

(Person.FirstName + ' ' + Person.LastName) as FullName, Agent_Interval.Extension,

CASE AST.AgentState

WHEN 0 THEN 'Logged Off'

WHEN 1 THEN 'Logged On'

WHEN 2 THEN 'Not Ready'

WHEN 3 THEN 'Ready'

WHEN 4 THEN 'Talking'

WHEN 5 THEN 'Work Not Ready'

WHEN 6 THEN 'Work Ready'

WHEN 7 THEN 'Busy Other'

WHEN 8 THEN 'Reserved'

WHEN 9 THEN 'Call Initiated'

WHEN 10 THEN 'Call Held'

WHEN 11 THEN 'Active'

WHEN 12 THEN 'Paused'

WHEN 13 THEN 'Interrupted'

WHEN 14 THEN 'Not Active'

ELSE 'Unknown'

END AS AgentState,

AST.Date,

AST.Time,

CASE AST.Direction

WHEN 0 THEN ''

WHEN 1 THEN 'In'

WHEN 2 THEN 'Out'

WHEN 3 THEN 'Other In'

WHEN 4 THEN 'Other Out/Direct Preview'

WHEN 5 THEN 'Outbound Reserve'

WHEN 6 THEN 'Outbound Preview'

WHEN 7 THEN 'Outbound Predictive/Progressive'

ELSE 'Unknown'

END AS Direction,

AST.ReasonCode,

AST.SkillTargetID,

CASE RC.ReasonText WHEN 'Undefined' THEN '' ELSE RC.ReasonText END ReasonText

FROM

(Select AgentState,

Date = CONVERT(char(10),DateTime,111),

Time = CONVERT(char(8), DateTime, 108),

Direction,

ReasonCode,

SkillTargetID

FROM Agent_State_Trace

Where DateTime >= :startDate

and DateTime <= :endDate

AND SkillTargetID  in (:agent_list)

GROUP BY AgentState, CONVERT(char(8), DateTime, 108), CONVERT(char(10),DateTime,111), Direction, ReasonCode, SkillTargetID

) AST

LEFT OUTER JOIN (SELECT

ReasonCode,

ReasonText

FROM Reason_Code

GROUP BY ReasonCode, ReasonText

) RC

ON RC.ReasonCode=AST.ReasonCode

LEFT JOIN Agent

ON Agent.SkillTargetID=AST.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

LEFT JOIN Agent_Interval

ON Agent_Interval.SkillTargetID = AST.SkillTargetID

GROUP BY

AST.SkillTargetID, Person.FirstName, Person.LastName, AST.AgentState, AST.Time,AST.Date, AST.Direction, AST.ReasonCode, RC.ReasonText, Agent_Interval.Extension

but I see it displays 2 extensions though the agent is logged in with only extension 1007

Could you please help me?

Best regards,

Vinod