Hello!
Looking for a bit of help/advice on best methods for reporting on downtime of nodes on a monthly basis. We have in the past used the report generator to run the following SQL;
SELECT TOP 10000 Nodes.NodeID AS NodeID,Nodes.VendorIcon AS Vendor_Icon,Nodes.Caption AS NodeName,AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,Nodes.DeviceType AS DeviceTypeFROMNodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)WHERE( DateTime > '2016-07-01'AND DateTime < '2016-07-31' )AND ( (Nodes.WAN = 1) AND (Convert(Char,DateTime,108) > '07:30') AND (Convert(Char,DateTime,108) < '17:30') AND (DATEPART(weekday, DateTime) <> 1))GROUP BY Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.DeviceTypeORDER BY 3 ASC
However, upon running the report for the last month we noticed some of the figures looked worse than the actual general feel of downtime. I then started investigating the data in the SQL table Nodes - only to realise after 7 days of data its truncated from a poll result every minute, to a poll result every hour. This obviously upsets the AVG calculation.
Has anyone got any suggestions on how a more accurate figure could be calculated? We are running the above across 50 nodes, so anything that doesn't require too much 'after the fact' calculations would be good!
Thanks in advance.
Mike
