2 Replies Latest reply on Feb 21, 2017 7:45 AM by Steven Klassen

    custom group query

    jayshree vispute

      SELECT * FROM (

                      SELECT

                      Nodes.StatusLED,

                      Nodes.Caption,

                      Nodes.NodeID,

                      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 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