cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

No Frills downtime Report

I searched online for a long time to Frankenstein queries together and nothing ever worked like I wanted, so I started from scratch and put together a no-nonsense, to-the-point report for downtime.  Hope this will help someone, wish I had done this a long time ago!

 

SELECT *, CAST(SUM(OutageDurationInMinutes) / 1440 AS NVARCHAR)+' Days '+CAST((SUM(OutageDurationInMinutes) - CAST(SUM(OutageDurationInMinutes) / 1440 AS INT) * 1440) / 60 AS NVARCHAR)+'hr '+CAST((SUM(OutageDurationInMinutes)) - (CAST(SUM(OutageDurationInMinutes) / 1440 AS INT) * 1440 + CAST((SUM(OutageDurationInMinutes) - CAST(SUM(OutageDurationInMinutes) / 1440 AS INT) * 1440) / 60 AS INT) * 60) AS NVARCHAR)+'m' AS [Down Duration]

 

FROM
(
SELECT
Nodes.Caption,
StartTime.EventTime AS DownEventTime,
(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
WHERE EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
ORDER BY EndTimeTable.EventTime
) AS UpEventTime,
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)
) AS UpTimeTable
group by Caption,DownEventTime,OutageDurationInMinutes,UpEventTime
ORDER BY 'DownEventTime' Desc

1 Reply
MVP
MVP

Good stuff, but if you wanted insta-points from getting upvoted (and so folx can find it), there's a content exchange for these gems.

https://thwack.solarwinds.com/t5/NPM-Documents/tkb-p/npm-documents

0 Kudos