0 Replies Latest reply on Jul 19, 2017 3:04 AM by asafsqlhelp

    Report for Node downtime, over the last 7 days

    asafsqlhelp

      Hi guys,

       

      I'm pretty new at Thwack. I need to get a report for Node downtime, over the last 7 days,

      and I need the filter only to show the Contains Node "sol" results and not all the nodes,

      in addition, the report need to contains the Scheduled Maintenance work....

       

       

      The report below in one that I found at the web that match my needs...

      #####Please please please can u help to to add the features mentioned above?#####

       

       

      SELECT * FROM (

          SELECT

          Nodes.StatusLED,

          Nodes.Caption,

          Nodes.NodeID,

          Nodes.MachineType,

          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 AND outageDurationInMinutes >= '15'

      AND datepart(hour, DownEventTime) >= 8

      AND datepart(hour, UpEventTime) >= 8

      AND datepart(hour, DownEventTime) <= 17

      AND datepart(hour, UpEventTime) <= 17

      ORDER BY Caption ASC, DownEventTime DESC