0 Replies Latest reply on Jul 24, 2009 3:27 AM by upinsmoke

    Outage Duration Report - SQL query help needed

    upinsmoke

      Hi All,

       

      I am running a 7 day report that gives me outages for the week for main sites and also gives the outage duration. Obviously if I have nodes that have been down for over 7 days, they do not show in this report.

       

      Can anyone help me to get the nodes that are already down captured in this as well.

      I'm running Orion 9.1 SP5 and SQL 2005 Enterprise

      SQL query;

      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
      eventtime between dateadd(week, -1, getdate()) and getdate()
      AND Nodes.Bases='Base'
      AND Nodes.Type='Router'
      ORDER BY OutageDurationInMinutes DESC

       

      Thanks in advance,

       

      Ben