Hi,
I have a report below which lists the node availability (anything less than 99.9%) for the last month. I would like to edit so it queries the exact same things but for the last 24 hours, and am not entirely sure how to edit the time variable correctly. All my attempts from documentation I have found on the internet have not worked. Any assistance/suggestions would be greatly appreciated.
Thanks
Scott
SELECT CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.City AS City,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
Nodes.Comments AS Comments,
Nodes.Parent_Group AS Parent_Group,
AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability
FROM
Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)
WHERE
( MONTH(DateTime) = (MONTH(GETDATE()-DAY(GETDATE())))
AND
YEAR(DateTime) = YEAR(GETDATE()-DAY(GETDATE())))
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address, Nodes.Comments, Nodes.City, Nodes.Parent_Group
HAVING AVG(DailyNodeAvailability.Availability) <= 99.9
ORDER BY AVERAGE_of_Availability
