Agent Not Ready Detail Stock

Document created by cdnadmin on Jan 25, 2014
Version 1Show Document
  • View in full screen mode
This document was generated from CDN thread

Created by: Mike Naumkin on 19-07-2012 04:37:49 AM
I'm trying to re-create stock report "Agent Not Ready Detail" and use it with another datasource. I saved it with another name ("save as" in report definition), created parameters and fields. I had no errors, but it doesn't create all fields as in source-report.
"Agent Not Ready Detail" report's type is "anonymous block" and contains "BEGIN... END" in SQL query. This "BEGIN... END" contains a number of SELECT and UPDATE queries and i think CUIC can recognize only the first of them. But STOCK report works well with this SQL query.
 
Does anyone faced with this problem before?

Subject: RE: Agent Not Ready Detail Stock
Replied by: Dmitry Silin on 19-07-2012 10:54:15 AM
Thank you for your reply.
Yes, I logged on as administrator .. All the same with different browsers.

Now I found out that this is another problem: If I save a report definition with a different name in the same system ("save as"), it's all right (all fields are created).
But when I'm exporting this "new" report definition and try to import it to another server, the import does not throw errors, but some fields are lost.

Subject: RE: Agent Not Ready Detail Stock
Replied by: Senthil Kumar Natarajan on 19-07-2012 09:53:52 AM
are you logged in as admin? Just want to validate if is rights issue.

Also try different browser. I have seen similar issues with CUCM where it takes a command but fail to notify us "promptly" in case of script errors.

-senthil

Subject: RE: Agent Not Ready Detail Stock
Replied by: Senthil Kumar Natarajan on 19-07-2012 11:27:48 AM
why export and import? Unless its standard in the other box. If not, have you tried to create a new Rep. Def directly off the second server?

Also what fields are lost? Can you specify some? also are you running the query against the realtime/AW DB ?

Subject: RE: Agent Not Ready Detail Stock
Replied by: Senthil Kumar Natarajan on 19-07-2012 11:28:17 AM
Forgot to ask, what version of CUIC?

Subject: RE: Agent Not Ready Detail Stock
Replied by: Senthil Kumar Natarajan on 19-07-2012 12:33:26 PM
maybe its a bug like mentioned mentioned. But I do have 8.5.4 running, let me give it a try and post results when i get a chance.

Subject: RE: Agent Not Ready Detail Stock
Replied by: Dmitry Silin on 19-07-2012 11:40:55 AM
For some reasons, there are no stock reports on the second CUIC system..
Version of CUIC - 8.5(4).
When I try to create new report definition directly on the second server with SQL query from STOCK report, i have the same problem with fields.

SQL query (Agent Not Ready Detail):

BEGIN SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON SET QUOTED_IDENTIFIER ON SET ROWCOUNT 0
DECLARE @dtStartDateTime DATETIME, @dtEndDateTime DATETIME
SET @dtStartDateTime = :start_date
SET @dtEndDateTime = :end_date
SELECT aed.SkillTargetID, aed.MRDomainID, aed.LoginDateTime, aed.Event, aed.Duration, aed.ReasonCode, aed.DateTime, aed.DbDateTime, 'Suspect' = 0
INTO #ST1 FROM Agent_Event_Detail  AS aed  (nolock)
WHERE aed.DateTime >= @dtStartDateTime AND aed.DateTime <= @dtEndDateTime
AND SkillTargetID IN (:agent_list)
ORDER BY aed.SkillTargetID, aed.MRDomainID, aed.LoginDateTime, aed.Event, aed.ReasonCode

UPDATE #ST1 SET Suspect = 1
FROM #ST1 AS a
JOIN (SELECT SkillTargetID, MRDomainID, LoginDateTime FROM #ST1
  GROUP BY SkillTargetID, MRDomainID, LoginDateTime
  HAVING SUM(Event)%3>0) AS b
  ON (a.SkillTargetID = b.SkillTargetID AND a.MRDomainID = b.MRDomainID AND a.LoginDateTime = b.LoginDateTime)

UPDATE #ST1 SET Suspect = 1
FROM #ST1 AS a
JOIN (SELECT SkillTargetID, MRDomainID, LoginDateTime FROM #ST1
  GROUP BY SkillTargetID, MRDomainID, LoginDateTime
  HAVING AVG(Event)=3) AS b
  ON (a.SkillTargetID = b.SkillTargetID AND a.MRDomainID = b.MRDomainID AND a.LoginDateTime = b.LoginDateTime)
WHERE Suspect = 0

SELECT SkillTargetID,MRDomainID, LoginDateTime, 'TotalLoginTime' = 0, 'TotalTimeNotReady' = 0, ReasonCode, 'ReasonCodeDuration' = SUM(Duration), Suspect
INTO #ST2
FROM #ST1
WHERE Event = 3
GROUP BY SkillTargetID, MRDomainID, LoginDateTime, ReasonCode, Suspect

UPDATE #ST2 SET TotalLoginTime = b.Duration
FROM #ST2 AS a
JOIN #ST1 AS b
ON (a.SkillTargetID = b.SkillTargetID AND a.MRDomainID = b.MRDomainID AND a.LoginDateTime = b.LoginDateTime)
WHERE b.Event = 2

UPDATE #ST2 SET TotalTimeNotReady = b.TotalTimeNotReady
FROM #ST2 AS a
JOIN (SELECT SkillTargetID, MRDomainID, LoginDateTime, 'TotalTimeNotReady' = SUM(ReasonCodeDuration) FROM #ST2
  GROUP BY SkillTargetID, MRDomainID, LoginDateTime) AS b
ON (a.SkillTargetID = b.SkillTargetID AND a.MRDomainID = b.MRDomainID AND a.LoginDateTime = b.LoginDateTime)


SELECT #ST2.SkillTargetID, #ST2.MRDomainID, Media_Routing_Domain.EnterpriseName, LoginDateTime, TotalLoginTime, TotalTimeNotReady, #ST2.ReasonCode, textReasonCode=ISNULL(ReasonText, ' ')+'['+convert(varchar, #ST2.ReasonCode)+']', ReasonCodeDuration, Suspect, FullName=Person.LastName+', '+Person.FirstName, perNotReady = ReasonCodeDuration*1.0/TotalTimeNotReady, perLogon = ReasonCodeDuration*1.0/TotalLoginTime, StartDate = CONVERT(DATETIME, @dtStartDateTime), EndDate = CONVERT(DATETIME, @dtEndDateTime)
FROM #ST2 LEFT JOIN Reason_Code ON #ST2.ReasonCode=Reason_Code.ReasonCode, Person, Agent, Media_Routing_Domain
WHERE #ST2.SkillTargetID = Agent.SkillTargetID AND Agent.PersonID = Person.PersonID AND #ST2.MRDomainID=Media_Routing_Domain.MRDomainID
ORDER BY FullName, Media_Routing_Domain.EnterpriseName, LoginDateTime, textReasonCode

DROP TABLE #ST1 DROP TABLE #ST2 END


Fields in STOCK report (as in the last SELECT in SQL query):
        EndDate                         EndDate                       Query Field DATETIME
EnterpriseName         EnterpriseName       Query Field STRING
FullName                         FullName                       Query Field STRING
LoginDateTime          LoginDateTime               Query Field DATETIME
MRDomainID                 MRDomainID               Query Field DECIMAL
perLogon                         perLogon                       Query Field DECIMAL
perNotReady                 perNotReady               Query Field DECIMAL
ReasonCode                 ReasonCode               Query Field DECIMAL
ReasonCodeDuration ReasonCodeDuration    Query Field DECIMAL
SkillTargetID                 SkillTargetID               Query Field DECIMAL
StartDate                         StartDate                       Query Field DATETIME
Suspect                         Suspect                       Query Field DECIMAL
textReasonCode         textReasonCode       Query Field STRING
TotalLoginTime                 TotalLoginTime               Query Field DECIMAL
TotalTimeNotReady         TotalTimeNotReady       Query Field DECIMAL

Fields in created report (as in the first SELECT in SQL query):
        'Suspect'           'Suspect'         Query Field DECIMAL
DateTime                 DateTime         Query Field DECIMAL
DbDateTime         DbDateTime Query Field DECIMAL
Duration                 Duration         Query Field DECIMAL
Event                 Event         Query Field DECIMAL
LoginDateTime  LoginDateTime Query Field DECIMAL
MRDomainID         MRDomainID Query Field DECIMAL
ReasonCode         ReasonCode Query Field DECIMAL
SkillTargetID         SkillTargetID Query Field DECIMAL

Subject: RE: Agent Not Ready Detail Stock
Replied by: Mike Naumkin on 19-07-2012 11:51:31 AM
I think it's a bug in CUIC 8.5(4).
In 8.5(2) all fields created when creating report directly with SQL query from stock report.

Subject: RE: Agent Not Ready Detail Stock
Replied by: Mike Naumkin on 20-07-2012 03:13:51 AM
Yes, sure, try it on your system - just create new report with SQL query above and check fields.
I'll try to use another "BEGIN... END" in another report definition - it will help to see, does CUIC detects that statement at all.

If it's a bug, i'll create service request in TAC.

Subject: RE: Agent Not Ready Detail Stock
Replied by: Carmen Logue on 23-07-2012 10:30:59 AM
I checked with the development team and this is a bug in 8.5(4).  Sorry!

We have fixed it in CUIC 9.0 which we expect to be available end of August.  (and as a reminder, can be deployed with Contact Center Enterprise 8.x)

Subject: RE: Agent Not Ready Detail Stock
Replied by: Senthil Kumar Natarajan on 23-07-2012 10:44:11 AM
Forgot to update. I had a similar issue where no fields are created for me. Looks like Carmen just confirmed its a bug.

Subject: RE: Agent Not Ready Detail Stock
Replied by: Mike Naumkin on 23-07-2012 10:46:14 AM
Thanks for the information!
We found workaround - we will use stored procedure in custom database, which will receive data from UCCE databases.

Subject: RE: Agent Not Ready Detail Stock
Replied by: Michael Jezierski on 10-08-2012 11:30:48 AM
Thanks for the information!
We found workaround - we will use stored procedure in custom database, which will receive data from UCCE databases.

 
Here is a normal SQL query that returns the same data as Cisco's query but it doesn't use temp tables so it validates and creates the proper fields successfully.
Thanks to Ed Umansky for his assistance with optimizing this query.
 
 
 

SELECT
aed.SkillTargetID, 
aed.MRDomainID, 
FullName = p.LastName+', '+p.FirstName,
mrd.EnterpriseName,
aed.LoginDateTime, 
TotalLoginTime = ISNULL(tl.Duration,0), 
tnr.TotalTimeNotReady,
aed.ReasonCode, 
textReasonCode = ISNULL(rc.ReasonText,'')+'['+CONVERT(VARCHAR,aed.ReasonCode)+']',  
ReasonCodeDuration = SUM(aed.Duration),
perNotReady =
CASE 
WHEN ISNULL(tnr.TotalTimeNotReady,0) = 0 THEN 0 
ELSE ISNULL((SUM(aed.Duration) * 1.0) / tnr.TotalTimeNotReady, 0) 
END,
perLogon =
CASE 
WHEN ISNULL(tl.Duration,0) = 0 THEN 0 
ELSE ISNULL((SUM(aed.Duration) * 1.0) / tl.Duration, 0)
END
 
FROM
Agent_Event_Detail AS aed (nolock)
 
LEFT JOIN  
Agent_Event_Detail tl (nolock) 
  ON aed.SkillTargetID = tl.SkillTargetID AND aed.MRDomainID = tl.MRDomainID AND aed.LoginDateTime = tl.LoginDateTime AND tl.Event = 2
 
INNER JOIN  
(
select aed3.SkillTargetID, aed3.MRDomainID, aed3.LoginDateTime, SUM(aed3.Duration) AS TotalTimeNotReady
FROM Agent_Event_Detail aed3 (nolock) 
WHERE aed3.Event = 3
GROUP BY SkillTargetID, MRDomainID, LoginDateTime
) AS tnr
ON tnr.SkillTargetID = aed.SkillTargetID AND tnr.MRDomainID = aed.MRDomainID AND tnr.LoginDateTime = aed.LoginDateTime
 
LEFT JOIN Reason_Code rc 
ON aed.ReasonCode = rc.ReasonCode
 
INNER JOIN Agent a
ON aed.SkillTargetID = a.SkillTargetID
 
INNER JOIN Person p
ON a.PersonID = p.PersonID
 
INNER JOIN Media_Routing_Domain mrd
ON mrd.MRDomainID = aed.MRDomainID
 
WHERE aed.Event = 3
 
GROUP BY aed.SkillTargetID, aed.MRDomainID, mrd.EnterpriseName, p.FirstName, p.LastName, aed.LoginDateTime, tl.Duration, 
tnr.TotalTimeNotReady, aed.ReasonCode, rc.ReasonText
 
ORDER BY p.LastName + ', ' + p.FirstName, mrd.EnterpriseName, aed.LoginDateTime, ISNULL(rc.ReasonText,'')+'['+CONVERT(VARCHAR,aed.ReasonCode)+']'

 
 

Attachments

    Outcomes