Want to know how long each alert last X days has been active? Then below report is for you.
It works on most Alerts, just change the to the name of the alert you want to report on, in the data source. You can also change the number of days back to want to report on.
Below example is for the two alerts "High Transmit Percent Utilization" and "Node is down".
The report use below SQL code:
;WITH AO1 AS
(
SELECT
AO.AlertObjectID AS AOID
,AO.EntityCaption
,AO.EntityDetailsUrl
FROM AlertConfigurations AS AC
INNER JOIN AlertObjects AS AO ON AC.AlertID=AO.AlertID
WHERE
AC.name='High Transmit Percent Utilization' -- Name of alert rule
)
,T1 as
(
SELECT
MIN(AH1.TimeStamp) AS TriggerTime
,MAX(AH1.Timestamp) AS ResetTime
,AH1.AlertActiveID
,AH1.AlertObjectID
FROM AlertHistory AS AH1
INNER JOIN AO1 ON AH1.AlertObjectID=AO1.AOID
WHERE
AH1.Timestamp>DateAdd(DAY,-30,GETDATE()) -- How many days back to check
GROUP BY AH1.AlertObjectID, AH1.AlertActiveID
)
SELECT
T1.TriggerTime
,T1.ResetTime
,DATEDIFF(MINUTE,T1.TriggerTime,T1.ResetTime) AS AlertActiveMinutes
,AO1.EntityCaption AS Caption
,AO1.EntityDetailsUrl AS DetailsUrl
FROM T1
INNER JOIN AO1 ON T1.AlertObjectID=AO1.AOID
WHERE
DATEDIFF(MINUTE,T1.TriggerTime,T1.ResetTime)>1
I also have a SWQL code that is working in SWQL Studio but for some reason does not in the report:
SELECT
MAX(AH.TimeStamp) AS TriggerTime
,MIN(AH.Timestamp) AS ResetTime
,MINUTEDIFF(MIN(AH.TimeStamp),MAX(AH.TimeStamp)) AS AlertActiveTime
-- ,AH.AlertActiveID
-- ,AH.AlertObjectID
,AH.AlertObjects.EntityCaption AS Caption
,AH.AlertObjects.EntityDetailsUrl AS DetailsURL
FROM Orion.AlertHistory AS AH
INNER JOIN
(
SELECT
AO.AlertObjectID AS AOID
FROM Orion.AlertObjects AS AO
WHERE
AO.AlertConfigurations.Name='High Transmit Percent Utilization'
) AS AO1 ON AH.AlertObjectID=AO1.AOID
GROUP BY
AH.AlertObjectID, AH.AlertActiveID,AH.AlertObjects.EntityCaption,AH.AlertObjects.EntityDetailsUrl