2 Replies Latest reply on Feb 16, 2017 11:33 PM by th3rush

    Down devices for the previous month - and for how long

    th3rush

      Hi Reporting World,

       

      I'm trying to make a report to show the down time of devices from the previous month and the time it was down - we have the standard downtime report however that covers interfaces which just won't work.

       

      The expectation is to marry this report up with an availability report to show what was down and why availability isn't 100%.

       

      I have been working on this code however I can't get the right results:

       

      SELECT

        NodesData.Caption,

        NodesData.IP_Address,

        NetObjectDowntime.DateTimeFrom,

       

        (CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, getdate()) / 1440) AS nvarchar(200)) + ':' +

        CAST(((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, getdate()) / 60) % 24) AS nvarchar(200)) + ':' +

        CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom,  getdate()) % 60) AS nvarchar(200))) AS OutageInDHM

       

      FROM

        NetObjectDowntime INNER JOIN

        NodesData

          ON NetObjectDowntime.NodeId = NodesData.NodeID INNER JOIN

        NodesCustomProperties

          ON NetObjectDowntime.NodeId = NodesCustomProperties.NodeID

       

       

      WHERE

      NetObjectDowntime.DateTimeFrom BETWEEN  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, -0, getdate()) , 0)) AND

              NetObjectDowntime.EntityType like '%Nodes' AND

              NetObjectDowntime.DateTimeUntil IS NOT NULL AND

        (CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, NetObjectDowntime.DateTimeUntil) / 1440) AS nvarchar(200)) + ':' +

        CAST(((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, NetObjectDowntime.DateTimeUntil) / 60) % 24) AS nvarchar(200)) + ':' +

        CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom,  NetObjectDowntime.DateTimeUntil) % 60) AS nvarchar(200))) NOT LIKE '0:0:0%'

      ORDER BY

         NetObjectDowntime.DateTimeFrom ASC

       

      I'm not particularly good with SQL and have got some help from the SQL guy in my office around the date stuff, still not convinced it's working correctly though.

       

      Any help would be appreciated Not sure if this is the right approach however it's the only table with downtime so it seems to be the logical place to pull the results from.

       

      Cheers,

       

      Garreth.