I have managed to get something together which is proving very useful to me in my environment.
I dubbed it repeat offenders as I have a need to know which machines are continually hitting high on memory and CPU usage, as this helps me identify which machines are under continuous load - the report identifies how many times a machine exceeds a threshold set on CPU and Memory, and reports on the amount of counts (Hits).
SELECT C.NodeID, N.IP_Address, N.Caption, N.SysName, N.MachineType, COUNT(1) AS [Hits], COUNT(1) * 10 AS [MinutesOnLoad],
MIN(C.DateTime) AS [FirstHit], MAX(C.DateTime) AS [LastHit]
FROM dbo.CPULoad_Detail_20160331 AS C WITH (NOLOCK)
INNER JOIN dbo.Nodes AS N WITH (NOLOCK)
ON C.NodeID = N.NodeID
WHERE C.AvgLoad >= 80
GROUP BY C.NodeID, N.IP_Address, N.Caption, N.SysName, N.MachineType
HAVING COUNT(1) > 2
ORDER BY COUNT(1) DESC
Remember to change the date - FROM dbo.CPULoad_Detail_20160331 AS C WITH (NOLOCK)
Also if you would like to change this to report on Memory - change: WHERE AvgPercentMemoryUsed >= 80 And you can change the threshold in my case, I am using 80% on both Memory and CPU.
Perhaps this will be useful for some of you, and I think this would be nice to have built in.