1 Reply Latest reply on Mar 29, 2017 1:27 PM by nwetherwax

    downtime reports (planned and unplanned) for a specific group

    nwetherwax

      We are currently using the built in downtime report within report writer to view which nodes have gone down and for how long within the past month.

       

      I am trying to create two separate reports that accomplish a similar task but for a specific group.  We have a group named "Critical Nodes".  I would like (1) a report that shows the downtime events for nodes within this group while managed (unplanned downtime) within the past month (30 days).  I would also like (2) a report showing the downtime duration for the nodes within this group while the nodes were unmanaged (planned downtime).

       

      I'm not much of a SQL person but i believe a simple modification to the query should help me at least accomplish task1.  The current query for the built in downtime report (listing all nodes, not specifically for the desired group) is as follows:

       

      SELECT * FROM (

        SELECT

        Nodes.StatusLED,

        Nodes.Caption,

        Nodes.NodeID,

        Nodes.Device_Type,

        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 datepart(MONTH, DownEventTime) = datepart(MONTH,GetDate())

      ORDER BY Caption ASC, DownEventTime DESC

       

       

      I would like like to filter it so that the results are selected FROM Containers WHERE NAME ='Critical Nodes'

       

      any help would be appreciated, thanks.