I need to create a report that finds servers that did not alert on our critical CPU alert, greater that 90% for an hour. So I created a report that averages CPU into an hour and only returns nodes and data time that the CPU was over 85 but under 90%. Here is my SQL from the report.
This is the our put I get from one node. You will see that I get multiple data points from an hour, why?
22-Jan-08 08:07 AM 81 %
22-Jan-08 07:57 AM 83 %
22-Jan-08 07:47 AM 81 %
22-Jan-08 07:27 AM 82 %
22-Jan-08 07:17 AM 81 %
22-Jan-08 06:57 AM 83 %
28-Jan-08 07:52 PM 82 %
SQL
SELECT TOP 10000 Convert(DateTime,Floor(Cast(DateTime as Float)),0) AS SummaryDate,
Nodes.DNS AS DNS,
CPULoad.DateTime AS Date,
AVG(CPULoad.AvgLoad) AS AVERAGE_of_AvgCPULoad
FROM
Nodes INNER JOIN CPULoad ON (Nodes.NodeID = CPULoad.NodeID)
WHERE
( DateTime BETWEEN 39444 AND 39475 )
AND
(
(Nodes.Report LIKE '%win%') AND
(
(CPULoad.AvgLoad > 80) AND
(CPULoad.AvgLoad < 85))
)
GROUP BY Convert(DateTime,Floor(Cast(DateTime as Float)),0),
Nodes.DNS, CPULoad.DateTime
ORDER BY SummaryDate ASC, 3 DESC
