7 Replies Latest reply on Nov 16, 2011 7:30 AM by Marlbs

    Reporting on Node Outage in Minutes with specified time problem?

    beckyk193

      Hi,

       

      I am trying to create a report that details when a node goes down, how long it was down for for a specified time frame - between 07:30 and 21:00 for the last 7 days. I have used the following sql statement that I have found on other posts but the time doesnt seem to work.

      I also only want to report on outages greater than 10 minutes - which I'm not sure how to do.

      Any ideas?

      SELECT
      StartTime.EventTime,
      Nodes.Caption,
      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
      AND EndTime.EventTime > 15
      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(day, -7, getdate()) and getdate()
       )
      AND
      (
      (CONVERT(Char, EventTime, 108)> '0730') AND
      (CONVERT(Char, EventTime, 108)< '2100')
      )
      ORDER BY Nodes.Caption ASC