cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

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
0 Replies