NPM ALERT HISTORY ADVANCED SQL REPORTING (TIME COALESCE / GROUP / EVENTS)
How do we Create a report that uses the NPM 12 AlertHistoryView Table (instead of the Events Table) to show the TIME DIFFERENCE between a 'Trigger' and a 'Reset' of an Alert?
Here are 2 completely different examples from showing SQL coding that I would need to change Tables and Fields. This gets the ball rolling for me.
1)
--
SELECT TOP 10000 * FROM
(
SELECT
AlertActiveID,
EntityNetObjectID as ENOid,
EventTypeWord,
StartTime.TimeStamp AS DownTime,
(
SELECT TOP 1 AlertHistoryID
FROM AlertHistoryView AS EndTimeTable
where EndTimeTable.TimeStamp >= StartTime.TimeStamp
AND EndTimeTable.EventTypeWord = 'Triggered'
AND EndTimeTable.AlertActiveID = StartTime.AlertActiveID
AND TimeStamp IS NOT NULL
ORDER BY EndTimeTable.TimeStamp
) AS DownID,
(
SELECT TOP 1 EventTypeWord
FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
) AS UpMsg,
(
SELECT TOP 1 TimeStamp
FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
) AS UpTime,
(
SELECT TOP 1 AlertHistoryID
FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
) AS UpID,
DATEDIFF(Mi, StartTime.TimeStamp,
(
SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
)
) AS OutageMinutes,
Cast(Coalesce(
DATEDIFF(Mi, StartTime.TimeStamp,
(
SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
)
) / 60.0000, 0 ) as float
) AS OutageHrs,
floor( Cast(Coalesce(
DATEDIFF(Mi, StartTime.TimeStamp,
(
SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
)
) / 60, 0 ) as float)
) AS OH,
Cast(Coalesce((
DATEDIFF(Mi, StartTime.TimeStamp,
(
SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime
where EndTime.TimeStamp > StartTime.TimeStamp
AND EndTime.EventTypeWord = 'Reset'
AND EndTime.AlertActiveID = StartTime.AlertActiveID
ORDER BY EndTime.TimeStamp
)
) % 60) , 0 ) as float
) AS OHM
FROM AlertHistoryView StartTime
WHERE (StartTime.EventTypeWord = 'Triggered' )
) AS UpTimeTable
where OutageMinutes IS NOT NULL
ORDER BY AlertActiveID, DownTime DESC
---------------------------------------------------------------------- derived from ( Node Downtime Reports )
2) NPM 11.5 AlertHistory (since there is no AlertHistoryView to work with in this version)
I was able to do a successful subtract script. But, didn't have the right tables to at home to test with. Also, I never finished the group script.
Here is one sample sql script I figured out. My NPM is version 11.5 so tables are different.
You will have to change table name and column names.
****
SELECT
-- ThisLog.*,
Thislog.TimeStamp,
-- Distinct
Convert(datetime, Floor(Cast((ThisLog.TimeStamp) as Float)),0) AS SummaryDate,
DatePart(minute,COALESCE(ThisLog.TimeStamp - PrevLog.TimeStamp, 0)) as TimeDiff,
ThisLog.AlertActiveID
FROM [dbo].[AlertHistory] AS ThisLog
LEFT JOIN
[dbo].[AlertHistory] AS PrevLog
ON PrevLog.AlertActiveID = ThisLog.AlertActiveID
AND PrevLog.TimeStamp =
(
SELECT MAX(TimeStamp)
FROM [dbo].[AlertHistory]
WHERE
AlertActiveID = ThisLog.AlertActiveID
and
TimeStamp < ThisLog.TimeStamp
)
where ThisLog.AlertActiveID = '2'
order by timestamp desc
****
ref KB: sql coalesce cmd +
https://msdn.microsoft.com/en-us/library/ms174420.aspx
Message was edited by: Marc christian Fobair