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
------------------------------
Daniel Blanco
Enterprise Tools Architect
Alphaserve Technologies
------------------------------
Original Message:
Sent: 09-26-2019 11:04 AM
From: Brahma Addepalli
Subject: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
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
Original Message:
Sent: 09-26-2019 10:07 AM
From: Daniel Blanco
Subject: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
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
Original Message:
Sent: 09-26-2019 03:24 AM
From: MICHAEL BOEHM
Subject: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
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)
Original Message:
Sent: 09-25-2019 12:10 PM
From: Daniel Blanco
Subject: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
Hi Brahmaiah,
Thank you. what exactly is "and Ah.ColumnName = 73762" Does 76762 correlate to ticket event?
------------------------------
Daniel Blanco
Enterprise Tools Architect
Alphaserve Technologies
Original Message:
Sent: 09-25-2019 07:11 AM
From: Brahma Addepalli
Subject: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
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
Original Message:
Sent: 09-24-2019 10:53 AM
From: Daniel Blanco
Subject: SOI Report or SQL Query to show time Alert Hit console versus time Ticketed
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
------------------------------