Agent Not Ready Detail Stock

Version 1
    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)+']'