I would like to create a report which shows how many times and the total hours each node has been down in the last 24 hours regardless of current status. Does anyone have SQL code to do this?
I managed to create the report. The report shows for each node the average packet loss, number of times down, and total hours down over the last 24 hours. If anyone else comes across this thread trying to do the same thing here is the code, I tried to remove all custom fields but may have missed one or two so don't be too surprised if it won't run the first time:
NetModems 24 Hour Summary
SELECT Nodes.NodeID,
Nodes.VendorIcon,
Nodes.Caption,
Nodes.GroupStatus,
ResponseTime.Packets_Lost AS 'Packets Lost',
Events.Times_Down AS 'Times Down',
MinsDownTable.HoursDown/60 AS 'Hours Down',
isnull(Events.Times_Down,0)*5+isnull(ResponseTime.Packets_Lost,0)+ isnull(MinsDownTable.HoursDown,0)/60*4 AS SortValue,
FROM Nodes
FULL OUTER JOIN
(SELECT ResponseTime.NodeID,
AVG(PercentLoss) AS Packets_Lost
FROM ResponseTime
WHERE (DateTime BETWEEN DATEADD(hour, -24, GETDATE()) AND GETDATE())
GROUP BY NodeID) ResponseTime ON Nodes.NodeID = ResponseTime.NodeID
(SELECT Events.NetworkNode,
count(EventType) AS Times_Down
FROM Events
WHERE(Events.EventTime BETWEEN DATEADD(hour, -24, GETDATE()) AND GETDATE())
AND ((Events.EventType =1))
GROUP BY NetworkNode) Events ON Events.NetworkNode = Nodes.NodeID
(SELECT NodeID,
SUM(OutageDuration) AS HoursDown
FROM
( SELECT Nodes.NodeID,
DATEDIFF(MINUTE,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 OutageDuration
FROM Events StartTime
FULL OUTER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1)
AND (StartTime.NetObjectType = 'N')
AND eventtime BETWEEN dateadd(DAY, -1, getdate()) AND getdate()
AND Nodes.Caption LIKE '%.nmd%') AS MinsDownPerIncident
GROUP BY NodeID) MinsDownTable ON Nodes.NodeID = MinsDownTable.NodeID
WHERE Nodes.Caption LIKE '%.nmd%'
GROUP BY ResponseTime.Packets_Lost,
Events.Times_Down,
MinsDownTable.HoursDown,
Nodes.NodeID,
HAVING (ResponseTime.Packets_Lost >= 2
AND ResponseTime.Packets_Lost <100)
OR Events.Times_Down >= 1
ORDER BY SortValue DESC
The time period here could be tuned up: Outage Duration Last Month
That's a good place to start and I've tried playing around with that code before. I'm still a beginner with SQL code and would like to modify this to show total time down and number of times down for each node. This report shows total time down for each time a node went down. For example a single node in this report may have several lines, one for each time it went down and show the duration of each outage but the report I want would only have one line for that node and sum the time down and count the number of times it went down.
Hi, can you tell me which ones are custom fields as I cannot get this to work and I don't have custom fields. Thank you
Hi Peters,
Thanks for your report.
I want report for devices are in down state more than 24 hours.
Can you able to share the report?.
Thanks
Aswani
I need to create a alert or mail notification for devices which are in down state more than 24hrs, Could you please guide me that how to create in solarwinds.
