Service Operations Insight

 View Only
Expand all | Collapse all

SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

  • 1.  SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 24, 2019 10:53 AM
    Hello, so I was asked if there was a way to generate a report in SOI (or using a SQL Query ) to show for a given period of time, 
    when the alert hit the SOI console (Time Created) and then the time it was Ticketed.

    Example:
    |  Alert ID   |    Created Time   |   Ticketed Time   | 
    -----------------------------------------------------------------
    Alert001   |     9/24/19 9:01AM |    9/24/19  9:11AM


    So looking at the update history on an alert these two fields:


    I looked at the OOTB CABI reports but none give this from what I can tell. The "Alert Response and Closure" report doesn't have the time ticketed property.

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------


  • 2.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Broadcom Employee
    Posted Sep 25, 2019 07:11 AM
    Hi Daniel,

    The Alerts table store alert creation, modified, ticketid, ticket url etc details and the other table AlertHistory stores history of each alert.
    AlertID is the primary key in both tables and the 'Field' in Update History gui in the Alert console represents 'ColumnName' value in AlertHistory table.

    You may run below SQL query to get Alert Created time & Ticketed Time from both tables

    select Al.AlertID,Al.MDRAlarmID,Al.LoggedTime as "Alert Time",Ah.CreatedTime as "Ticketed Time",Ah.ColumnName from Alerts Al, AlertHistory Ah
    where al.AlertID in (select Ah.AlertID from AlertHistory)
    and Al.MDRAlarmID = 'TG05789264-39534' and Ah.ColumnName = 73762

    Hope this helps.

    Thanks
    Brahma


  • 3.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 25, 2019 12:11 PM
    Hi Brahmaiah,
    Thank you. what exactly is "and Ah.ColumnName = 73762" Does 76762 correlate to ticket event?


    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 4.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 26, 2019 03:25 AM
    Hi Dan,

    the AlertHistory table (Ah) contains entries for all kinds of updates to an Alert: queue assignment, acknowledgment, ticketing, annotations, .....
    Rather than using clear-text entries for the related action, a system with ids was introduced.
    73762 stands for "ticketed".

    Michael

    ------------------------------
    Customer Success Architect
    CA Deutschland GmbH (a Broadcom company)
    ------------------------------



  • 5.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 26, 2019 10:07 AM
    Thanks Michael.
    So I was looking more along these lines making use of the 3 tables:  AlertQueueAssignments as AQA, Alerts A, AuditRecords AR

    select AQA.AlertID,AQA.InsertedTime as "Alert Created Time",A.UserAttribute1 as Client_Name, A.UserAttribute5 as Robot, A.UserAttribute2 as UIM_Probe_Address, A.AlertDetail,AR.InternalID,AR.TimeStamp as "Time Ticketed", AR.UserName,AR.ActionDetail 

    from AlertQueueAssignments as AQA, Alerts A, AuditRecords AR

    where ((AQA.QueueID='3' and AQA.InsertedTime between '2019-08-01 00:00:00' and '2019-09-01 00:00:00') and AR.Action = '16')

    This works sorta but not in the right way. I get results but every results has the same AlertID, AlertCreated Time, and InternalId, Time Ticketed, Username and ActionDetail.


    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 6.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 26, 2019 10:35 AM

    It appears you are missing a join by an equal column-value in the three tables. 

     

    Where                  Table1.column1 = Table2.column4

    and        Table1.column2 = Table3.column5

     

    Something like the above or the "ON" syntax is needed to get the result you want.

     

    David

     

     

    David DuPre'

    Principal Services Consultant  |  Enterprise Studio

    HCL Technologies Ltd.

    404-617-3023  |  david.dupre@hcl.com  | Lookout Mountain, GA

    www.hcltech.com  | www.ca.com/services

    Planned Leave  – 6th September returning to office 16th September

     

    pic1

     






  • 7.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Broadcom Employee
    Posted Sep 26, 2019 11:04 AM
    Hi Daniel,

    Please try this one which would list out all Alerts with Ticketed for a specific Alert queue (Ex: UIM). You may change this query according to your requirement.

    SELECT dbo.AlertQueues.QueueName,AlertHistory.ColumnName,AlertHistory.CreatedTime as "Ticketed Time",Alerts.SvcDeskTicket,
    dbo.AlertQueueAssignments.AlertID,dbo.Alerts.MDRAlarmID,Alerts.DeviceID, dbo.Alerts.SituationMessage, dbo.Alerts.AlertDetail, dbo.Alerts.ModelElementID,dbo.Alerts.Active
    FROM dbo.AlertQueueAssignments INNER JOIN
    dbo.Alerts ON dbo.AlertQueueAssignments.AlertID = dbo.Alerts.AlertID INNER JOIN
    dbo.AlertQueues ON dbo.AlertQueueAssignments.QueueID = dbo.AlertQueues.QueueID INNER JOIN
    AlertHistory ON AlertHistory.AlertID = Alerts.AlertID
    where Alerts.AlertID in (select AlertHistory.AlertID from AlertHistory)
    and AlertHistory.ColumnName = 73762 and dbo.AlertQueues.QueueName = 'UIM'

    Thanks
    Brahma


  • 8.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 26, 2019 12:20 PM
    Thanks Dave. Thanks Brahmaiah. I'm almost there. 
    Dave that was it. I was missing the AR.InternalID = A.AlertID and AlertID = AQA.ID line which fixed the repeating issue. 

    Now just trying to format the Time Columns which isn't giving me the correct time difference:

    Query now is:
    ------------------------------------------

    select  A.SvcDeskTicket as "SNOW INC#", AQA.AlertID,
                 FORMAT(AQA.InsertedTime, 'MMMM dd yyyy hh:mm:ss tt' ) as "Alert Created Time",
                 FORMAT (AR.TimeStamp,'MMMM dd yyyy hh:mm:ss tt') as "Time Ticketed",
                 FORMAT((AR.TimeStamp - AQA.InsertedTime),'hh:mm:ss') as "Time Taken to Ticket",
                 A.UserAttribute1 as Client_Name, A.UserAttribute5 as Robot, A.UserAttribute2 as UIM_Probe_Address,
                 A.AlertDetail, AR.UserName,AR.ActionDetail

    from AlertQueueAssignments as AQA, Alerts A, AuditRecords AR

    where   AQA.QueueID='2'    --System Alert Queue

            and AQA.InsertedTime between '2019-08-01 00:00:00' and '2019-09-01 00:00:00' --For the Month of

            and AR.Action = '16' --Ticketed Action

            and AR.InternalID = A.AlertID and A.AlertID = AQA.AlertID --The Join of the AlertID's and Internal ID's of each table

    ORDER BY [Alert Created Time], Client_Name, Robot, UIM_Probe_Address
    ------------------------------------------

    The Time Taken to Ticket is giving weird results like:

    SNOW INC#               AlertID                Alert Created Time                       Time Ticketed                              Time Taken to Ticket
    @INC000852191        12054119        August 01 2019 01:00:02 AM        August 01 2019 01:30:08 AM       12:30:05


    Almost there.... I think I might need to use the AlertHistory to compute the time(s)





    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 9.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed

    Posted Sep 26, 2019 01:35 PM
    Edited by Daniel Blanco Sep 27, 2019 04:08 PM
    Michael or Brahma,
    Is there a list of actions that explain what each ColumnName ### means in the AlertHistory table? 

    I would want the time the alert hit a specific alert queue which would be the start time. 

    As you said the Ticketed action is = 73762

    But I see the 1st entry for an alert has 76046 but I see that this EventID can appear more than just once for an Alert. 

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------


  • 10.  RE: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
    Best Answer

    Posted Sep 27, 2019 04:08 PM
    Finally got it... This was the final SQL Query:


    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------