Total - Monthly Average Availability Report

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