0 Replies Latest reply on Mar 9, 2018 8:46 AM by llemieux

    Total - Monthly Average Availability Report

    llemieux

      I have seen a couple posts where other people like myself needed a report that shows an overall average availability for a specific group of nodes. Below is a SQL query I use to pull the total average availability, for the previous month, for Windows and Linux servers within our environment.

      So if you need to know the total percentage of availability for all Windows / Linux servers within your environment last month, this is it.

       

      I basically just took the SQL query SolarWinds was using in the report writer for their "Monthly Average Availability" report and doctored it to pull the exact information I needed. Just putting this out here in hopes it may help someone else out. Enjoy!

       

       

      DECLARE @startOfCurrentMonth DATETIME
      SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
      SELECT
      sub.SummaryMonth AS Month_Of,
      AVG (sub.AVERAGE_of_Availability) as Total_Average
      FROM
      (
      SELECT  TOP 10000 Nodes.VendorIcon AS Vendor_Icon,
      Nodes.Caption AS NodeName,
      Nodes.MachineType AS Machine_Type,
      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
      CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth
      FROM
      Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
      WHERE
      ( datetime >= DATEADD(month, -1, @startOfCurrentMonth) AND datetime < @startOfCurrentMonth )
      AND 
      (
         (Nodes.MachineType LIKE '%Windows%') OR
         (Nodes.MachineType LIKE '%Linux%')
      )
      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
      Nodes.VendorIcon, Nodes.Caption, Nodes.MachineType
      ) sub
      GROUP BY sub.SummaryMonth