1 Reply Latest reply on Jul 19, 2017 2:21 PM by osborne_graham

    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

        • Re: Report for Node downtime, over the last 7 days
          osborne_graham

          I think you are asking for an availability report for all nodes over past week. If so, create a custom table and enter 'node' type and 'node name'->'contains'->'sol'

          And click 'add to layout'

           

          Click 'edit table' then click the plus symbol to add a column. Select:

          • Node
          • Average Availability
          • Timestamp

           

          If you have a custom field you want to group by (node function, site location etc) add this too then also select it on the 'group results by' at the bottom.

           

          Expand 'average availability'

          Select 'custom format' for the display setting and enter '0.00 %' in the empty field.

          Select 'Average' for data aggregation (this is important).

           

          Expand the timestamp column and tick 'hide from view' then under the 'date/time column in this table is' select 'timestamp' and sample interval 'week'.

           

          Click 'submit'

           

          You will see next to the datasource drop down you now have a 'from' from down. Select 'last 7 days'.

           

          You will now see something like mine below (I grouped by a custom field which specifies the device funtion and also added a response time column):