I currently use this downloaded SQL query for reporting why Nodes were down and for how long during the month:
SELECT
StartTime.EventTime,
Nodes.Core__Prod_or_User,
Nodes.Caption,
Nodes.Location,
StartTime.Message,
DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate() AND (Nodes.Core__Prod_or_User <> 'User')
ORDER BY Nodes.Caption ASC
I would like to modify this to show the same information for Applications/Components as well and currently have the following:
SELECT
StartTime.EventTime,
Name,
StartTime.Message,
DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN APM_Application ON StartTime.NetworkNode = APM_Application.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY Name ASC
While it works, it only shows for when the node was down. I would like to the application/Component downtime, warning and criticals. Where should I be looking for this information and how should I modify my SQL query?
