0 Replies Latest reply on May 14, 2013 2:08 AM by jawad1979

    SQL Help Needed in custom Downtime Report




      I have the below SQL code that can show the sum of node downtime, however, I would like to filter the SQL statement so that only nodes that are members of a specific node group "name" can show up.

      Appreciate if anyone can help:


      Select NodeCaption, IsNull(Sum(DownTime),0) As DownTime From (


            Nodes.Caption As NodeCaption,   

        DATEDIFF(Mi, StartTime.EventTime,

            (SELECT TOP 1


                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 Downtime

        FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

        WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')

        AND eventtime between dateadd(month, -1, getdate()) and getdate()

      ) dt

      Group By NodeCaption

      Order By NodeCaption


      Thanks in Advanced.