0 Replies Latest reply on Mar 20, 2015 6:15 AM by luludev

    Outage for Last Month ?

    luludev

      Hello Friends.....

      Below SQL is to regenerate the report for last 30 days ,please advise how we can get for last month....thanks


      SELECT * FROM (

        SELECT

        Nodes.StatusLED,

        Nodes.Caption,

        Nodes.NodeID,

              Nodes.NetworkPlatform,

        StartTime.Message,

        StartTime.EventTime AS DownEventTime,

        (

        SELECT TOP 1 EventTime

        FROM Events AS EndTimeTable

        where EndTimeTable.EventTime >= StartTime.EventTime

        AND EndTimeTable.EventType = 5

        AND EndTimeTable.NetObjectType = 'N'

        AND EndTimeTable.NetworkNode = StartTime.NetworkNode

                              AND Nodes.NetworkPlatform = 'British Telecom'

        AND EventTime IS NOT NULL

        ORDER BY EndTimeTable.EventTime

        ) AS UpEventTime,

      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)

      ) AS UpTimeTable

      where outageDurationInMinutes IS NOT NULL

      ORDER BY Caption ASC, DownEventTime DESC