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

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

For an introduction to the new site, click here. And see here for current known issues.

login duration (total login) is incorrect in Agent Not Ready Detail

Hi All,


We have deployed the PCCE 10, and CUIC 10, and downloaded Cisco reporting template CCE_AF_Historical template.


When we checked the report login duration show as some 0-2037:0-31:0-48 (not readable value is coming all agents).


Since we are using cisco default template we didnt change anything on the sql query. i here with attached the sql query 


what i have understood from this issue is , it is not valditating the Event valuein the following area



AgentLogOutDetails(ALODSkillTargetID, ALODLoginDateTime, TotalLoginTime, ALODEvent) AS
(Select aed.SkillTargetID,  
  TotalLoginTime = DATEDIFF(ss,LoginDateTime, DateTime),
From AgentNotReadyDetails aed 
WHERE Event = 2),
SELECT SkillTargetID = ANRDU.SkillTargetID, 
  LoginDateTime = ANRDU.LoginDateTime,
    TotalLoginTime = CASE WHEN ALOD.ALODEvent = 2 THEN ALOD.TotalLoginTime ELSE DATEDIFF(ss,LoginDateTime, (CASE WHEN DATEDIFF(ss, :end_date, (Select NowTime From Controller_Time)) > 0 THEN :end_date ELSE (Select NowTime From Controller_Time) END)) END,
  TotalNotReadyTime = AgentNotReadyTotal.NotReadyTime,
  ReasonCode = ANRDU.ReasonCode, 
  textReasonCode = ISNULL(Reason_Code.ReasonText, ' ')+'['+convert(varchar, ANRDU.ReasonCode)+']', 
  ReasonCodeDuration = ANRDU.ReasonCodeDuration, 
  FullName = Person.LastName+', '+Person.FirstName, 
  perNotReady = CASE WHEN ISNULL(AgentNotReadyTotal.NotReadyTime,0) = 0 THEN 0*1.0 ELSE ISNULL(ANRDU.ReasonCodeDuration,0)*1.0/ISNULL(AgentNotReadyTotal.NotReadyTime,0) END,
  perLogon = ANRDU.ReasonCodeDuration*1.0/(CASE WHEN ALOD.ALODEvent = 2 THEN ALOD.TotalLoginTime ELSE DATEDIFF(ss,LoginDateTime, (Select NowTime From Controller_Time)) END),
  StartDate = CONVERT(DATETIME, :start_date), 
  EndDate = CONVERT(DATETIME, :end_date) 
FROM AgentNRDuration ANRDU 
LEFT JOIN Reason_Code ON ANRDU.ReasonCode=Reason_Code.ReasonCode
     LEFT JOIN  AgentLogOutDetails ALOD ON (ANRDU.SkillTargetID = ALOD.ALODSkillTargetID
AND ANRDU.LoginDateTime = ALOD.ALODLoginDateTime)
LEFT JOIN AgentNotReadyTotal ON ANRDU.SkillTargetID = AgentNotReadyTotal.SkillTargetID,
Person (nolock),
Agent (nolock),
WHERE Agent.PersonID = Person.PersonID 
  AND Agent.SkillTargetID = ANRDU.SkillTargetID
  AND Media_Routing_Domain.MRDomainID = ANRDU.MRDomainID 
ORDER BY FullName, 



Does any come across this issue please guide us how to resolve it, since it is affecting the customer performance badly.


with Regards,






Hi,I just tried to run the


I just tried to run the attached SQL query and it's alright. I guess there might be a problem with presenting the values. Did you contact Cisco yet (you wrote you did not touch the report definition).


Hi,We found the issue, after


We found the issue, after grouping the ANRDU.LoginDateTime it looses milliseconds, so it is not matching with following left join 


LEFT JOIN  AgentLogOutDetails ALOD ON (ANRDU.SkillTargetID = ALOD.ALODSkillTargetID
AND ANRDU.LoginDateTime = ALOD.ALODLoginDateTime)
so i rectified it with subracting the milliseconds from ALOD.ALODLoginDateTime now it is working fine
any one got idea why it is neglecting the milliseconds when we do grouping datetime?

Both ALOD and ANRDU in that

Both ALOD and ANRDU in that query get their LoginDateTime values from Agent_Event_Detail. One is looking for Event=2 (not ready), the other looks for Event=3 (log out). If the query isn't finding matching LoginDateTime values for those two Event types, then you may have an underlying database issue.


There's also likely an issue with the WHERE statement on AgentNotReadyDetails... I've seen that method of interval calculation in Cisco AED reports before and recall there being issues with it. If I remember right, it sometimes puts Not Ready events into the wrong interval (like 5pm instead of 4:30pm for example). More details here:




It sounds like the query is

It sounds like the query is giving you a negative time value of about -85 days. I see a few ways this could happen:

  • The Agent logout events in AED (Event=2) are somehow getting DateTime values that are earlier than the login times
  • NowTime in table Controller_Time is earlier than the agent login times. (this seems more likely)


So, I suggest looking at the Agent_Event_Detail table on the AW/HDS to ensure that the DateTime values on Event=2 lines are later than the login times... and also check the NowTime in table Controller_Time to ensure it is correct.

Here's the NowTime query:

SELECT [NowTime]
FROM [Controller_Time]


And here's the appropriate query for AED (should return 0 results if DateTime/LoginDateTimes are correct):

SELECT [DateTime]
FROM [Agent_Event_Detail]
WHERE Event=2 AND DateTime<LoginDateTime



CreatePlease login to create content