1 Reply Latest reply on Oct 26, 2015 8:54 AM by ladames

    I am currently trying to write a report to show the up time of a custom property in this case it is SiteName. For each site we have a primary and backup routing system. We do not have the dependency set to show a site up if one or the other router is on.

    ladames

      In this case it is SiteName. For each site we have a primary and backup routing system. We do not have the dependency set to show a site up if one or the other router is on. so I am trying to manually get the data.

       

      So far I have. The problem is in some sites the backup has been down 100% of the time but the site has been running fine with the primary router. So the 0% is making the up time of the site lower than what it should be.

       

      SELECT  TOP 10000

      Nodes.NodeID AS NodeID,

      Nodes.Caption AS NodeName,

      Nodes.IP_Address AS IP_Address,

      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,

      Nodes.DeviceRole AS DeviceRole,

      Nodes.SiteName AS SiteName,

      CONVERT(DateTime,

      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

      101) AS SummaryMonth

       

      FROM

      Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

       

       

      WHERE

      ( DateTime BETWEEN 42003 AND 42298 )

      AND 

      (

        (Nodes.DeviceRole = '3g backup router') OR

        (Nodes.DeviceRole = '3g primary router') OR

        (Nodes.DeviceRole = '4g primary router') OR

        (Nodes.DeviceRole = '4g backup router') OR

        (Nodes.DeviceRole = 'vsat backup router') OR

        (Nodes.DeviceRole = 'vsat primary router') OR

        (Nodes.DeviceRole = 'wan router')

      )

       

       

      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

      Nodes.NodeID, Nodes.Caption, Nodes.IP_Address, Nodes.DeviceRole, Nodes.SiteName

       

       

      ORDER BY 2 ASC ,SummaryMonth ASC, SiteName