I have the below query that I have been using to get cpu and memory utilization. However, I recently had a request to change it add in a field for both cpu and memory that shows a sustained high usage. So, what I need is to show the max value of the highest average over multiple data pulls or over a period of time. What I'm thinking is to average out the data in 20 minutes averages or two consecutive data pulls whichever is easier, then give the max of this. So, if I had 4 data values, it would need to be to be able to compare 1 to 2, 2 to 3, 3 to 4. Any ideas?
SELECT
Caption,
count_cpuIndex AS CPUCount,
max_maxLoad AS MaxCPU,
avg_avgLoad AS AvgCPU,
ROUND((TotalMemory / 1073741824),0) AS MemCount,
ROUND(max_maxMemoryUsedPct * 100, 2) AS MaxMem,
ROUND(avg_avgPercentMemoryUsed, 2) AS AvgMem
FROM
(SELECT
caption,
nodeid
FROM
Nodes) n INNER JOIN
(SELECT
nodeid,
COUNT(DISTINCT CPUIndex) AS count_cpuIndex
FROM
CPUMultiLoad_Detail
GROUP BY
nodeid) d ON
n.NodeID = d.NodeID INNER JOIN
(SELECT
nodeid,
TotalMemory,
MAX(MaxLoad) AS max_maxLoad,
AVG(AvgLoad) AS avg_avgLoad,
MAX(MaxMemoryUsed / TotalMemory ) AS max_maxMemoryUsedPct,
AVG(AvgPercentMemoryUsed) AS avg_avgPercentMemoryUsed
FROM
CPULoad
WHERE
Datetime >= DATEADD(MONTH,datediff(MONTH,0,getdate())-1,0) AND
Datetime < DATEADD(MONTH,datediff(MONTH,0,getdate()),0)
GROUP BY
nodeid,
TotalMemory) l ON
n.NodeID = l.NodeID