Hello,
I've been using the outage report I found in the forums. It works great for grabbing all the data we need, except I'm returning hundreds of pages. Every day almost all the nodes on our network go down for anywhere from 1-10 minutes because of large files that are transferred. So I need the following query to only show nodes that have were down for at least 15 minutes. That way the report will only show real outages and skip the daily morning outages.
SELECT
StartTime.EventTime,
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
Nodes.WhatIsIt = 'Router'
ORDER BY StartTime.EventTime DESC
I've tried everything I can think of, but unfortunately my SQL skills aren't up to par. Can someone please help me out?
