1 Reply Latest reply on Jul 10, 2017 1:25 PM by jkrenzien

    Help creating custom average availability report

    aomoding

      Hello, I need some help

      I'm trying to create a custom report from report writer where I can get the: Average monthly Node availability for nodes/hosts that are less than or equal to 95%. The query below only gives me results where the availability and NOT the Average Availability is less than 95%. Please help?


      SELECT  TOP 10000 CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth,
      Nodes.VendorIcon AS Vendor_Icon,
      Nodes.Caption AS NodeName,
      AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

      FROM
      Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)


      WHERE
      ( DateTime BETWEEN 42854 AND 42884.9999884259 )
      AND 
      (
        (Nodes.DeviceType = 'router') AND
        (DailyNodeAvailability.Availability)  < 95)
      )

        • Re: Help creating custom average availability report
          jkrenzien

          From the SQL side of things you could just select from the results and remove the 95 from the original query. It should work, I think. But I am a SQL beginner that has made a couple things work.

           

          SELECT *

          FROM

          (

          SELECT  TOP 10000 CONVERT(DateTime,
          LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
          101) AS SummaryMonth,
          Nodes.VendorIcon AS Vendor_Icon,
          Nodes.Caption AS NodeName,
          AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

          FROM
          Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)


          WHERE
          ( DateTime BETWEEN 42854 AND 42884.9999884259 )
          AND 
          (
            (Nodes.DeviceType = 'router')
          )

          ) myTable

          WHERE AVERAGE_of_Availability < 95