Custom report for alert duration

rinat.kirimov

Hi,

I'm trying to come up with a SWQL query to show triggered alerts in the past 24 hours and how long each alert lasted and then sort them by severity level. So far, I can't seem to find a field that tracks each alert duration. I can see under AlertActive.TriggeredDateTime we can calculate the duration of the current alert. But is it possible to do retrospectively?

Any guidance in here would be appreciated!

DaHaole

Aloha....

For this first ask, the location is at the end of the query, just before the semi-colon.  The second parameter for DATEADD is the number of hours before you want, you can also change the first parameter to day if you want it in the last 24 hours (or any number of days).

) AS dtl_tickets
WHERE StartDateTime > DATEADD(hour, -8, GETDATE())

To do the second part, I went and put another wrapper on it (I know there is a better way but for quick sake did it this way).  In this case I did both the StartDateTime is within the last 8 hours and it took longer than 15 minutes to close:

SELECT AlertActiveID,
[Alert Name],
[Triggered Node],
[Alert Source],
[Alert Note],
[Alert Limitation Category],
TriggeredCount,
[Severity Name],
StartDateTime,
AcknowledgedDateTime,
AcknowledgedBy,
ClosureDateTime,
SystemResetDateTime,
ClearedDateTime,
ClosureType,
TimeToAcknowledge,
TimeToClosure
FROM
(
SELECT DISTINCT
AlertActiveID,
[Alert Name],
[Triggered Node],
[Alert Source],
[Alert Note],
[Alert Limitation Category],
TriggeredCount,
[Severity Name],
StartDateTime,
AcknowledgedDateTime,
AcknowledgedBy,
ClosureDateTime,
SystemResetDateTime,
ClearedDateTime,
CASE
WHEN(ClearedDateTime IS NOT NULL)
THEN 'Manual Reset'
WHEN(SystemResetDateTime IS NOT NULL)
THEN 'Closed'
WHEN(ClosureDateTime IS NULL)
THEN 'Still Active'
ELSE 'Object Deleted'
END AS ClosureType,
CASE
WHEN(AcknowledgedDateTime IS NULL)
THEN NULL
ELSE DATEDIFF(MINUTE, StartDateTime, AcknowledgedDateTime)
END AS TimeToAcknowledge,
CASE
WHEN(ClearedDateTime IS NOT NULL)
THEN DATEDIFF(MINUTE, StartDateTime, ClearedDateTime)
WHEN(SystemResetDateTime IS NOT NULL)
THEN DATEDIFF(MINUTE, StartDateTime, SystemResetDateTime)
WHEN(DATEDIFF(MINUTE, ClosureDateTime, GETDATE()) < 5)
THEN NULL
ELSE DATEDIFF(MINUTE, StartDateTime, ClosureDateTime)
END AS TimeToClosure
FROM
(
SELECT DISTINCT
AlertHistoryView.AlertActiveID,
AlertHistoryView.Name AS [Alert Name],
AlertObjects.RelatedNodeCaption AS [Triggered Node],
AlertObjects.EntityCaption AS [Alert Source],
AlertObjects.AlertNote AS [Alert Note],
AlertHistoryView.Category AS [Alert Limitation Category],
AlertHistoryView.TriggeredCount,
CASE
WHEN(AlertHistoryView.Severity = 2)
THEN 'Critical'
WHEN(AlertHistoryView.Severity = 1)
THEN 'Warning'
WHEN(AlertHistoryView.Severity = 3)
THEN 'Serious'
WHEN(AlertHistoryView.Severity = 0)
THEN 'Informational'
WHEN(AlertHistoryView.Severity = 4)
THEN 'Notice'
ELSE 'Undefined'
END AS [Severity Name],
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, dt_Created.StartDateTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS StartDateTime,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, dt_Acknowledged.AcknowledgedDateTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS AcknowledgedDateTime,
dt_Acknowledged.AcknowledgedBy,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, dt_Cleared.ClearedDateTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ClearedDateTime,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, dt_SystemReset.SystemResetDateTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS SystemResetDateTime,
CASE
WHEN(dt_SystemReset.SystemResetDateTime IS NOT NULL)
THEN CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, dt_SystemReset.SystemResetDateTime), DATENAME(TzOffset, SYSDATETIMEOFFSET())))
WHEN(dt_Cleared.ClearedDateTime IS NOT NULL)
THEN CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, dt_Cleared.ClearedDateTime), DATENAME(TzOffset, SYSDATETIMEOFFSET())))
ELSE NULL
END AS ClosureDateTime
FROM AlertHistoryView
INNER JOIN AlertObjects ON AlertHistoryView.AlertObjectID = AlertObjects.AlertObjectID
INNER JOIN
(
SELECT AlertActiveID,
MAX(TimeStamp) AS LastUpdateDateTime
FROM AlertHistoryView AS ahv
GROUP BY AlertActiveID
) AS dt_LastUpdate ON AlertHistoryView.AlertActiveID = dt_LastUpdate.AlertActiveID
INNER JOIN Nodes n ON AlertObjects.RelatedNodeId = n.NodeID
LEFT OUTER JOIN
(
SELECT AlertActiveID,
MIN(TimeStamp) AS ClearedDateTime
FROM AlertHistoryView AS ahv
WHERE(EventTypeWord = 'Cleared')
GROUP BY AlertActiveID
) AS dt_Cleared ON AlertHistoryView.AlertActiveID = dt_Cleared.AlertActiveID
LEFT OUTER JOIN
(
SELECT AlertActiveID,
MIN(TimeStamp) AS SystemResetDateTime
FROM AlertHistoryView AS ahv
WHERE(EventTypeWord = 'Reset')
GROUP BY AlertActiveID
) AS dt_SystemReset ON AlertHistoryView.AlertActiveID = dt_SystemReset.AlertActiveID
LEFT OUTER JOIN
(
SELECT AlertActiveID,
MIN(TimeStamp) AS AcknowledgedDateTime,
AccountID AS AcknowledgedBy
FROM AlertHistoryView AS ahv
WHERE(EventTypeWord = 'Acknowledged')
GROUP BY AlertActiveID,
AccountID
) AS dt_Acknowledged ON AlertHistoryView.AlertActiveID = dt_Acknowledged.AlertActiveID
LEFT OUTER JOIN
(
SELECT AlertActiveID,
MIN(TimeStamp) AS StartDateTime
FROM AlertHistoryView AS ahv
WHERE(EventTypeWord = 'Triggered')
GROUP BY AlertActiveID
) AS dt_Created ON AlertHistoryView.AlertActiveID = dt_Created.AlertActiveID
) AS dtl_tickets
) AS dtl_finaltickets
WHERE StartDateTime > DATEADD(hour, -8, GETDATE())
AND TimeToClosure > 15;