2 Replies Latest reply on Feb 9, 2009 1:01 PM by jordana

    Outage Report Nodes Down More Than 15 Minutes.


         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.

      DATEDIFF(Mi, StartTime.EventTime,
      (SELECT TOP 1
      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?