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)
)

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