The awesome article by Leon (The Ultimate CPU Alert) does the trick to alert us on real high CPU VMs.
But sometimes we just want to know about it without alerting. Just make sure you have the "Processor Queue Length" across all VMs.
SELECT
n.Caption as [Node]
,n.DetailsURL as [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
,n.CPULoad as [CPULoad]
,c.CurrentStatistics.ComponentStatisticData AS [CPU Q Len]
,case when c.CurrentStatistics.ComponentStatisticData > c2.CPUCount then '>'
when c.CurrentStatistics.ComponentStatisticData < c2.CPUCount then '<'
else '=' end as [Comp]
,c2.CPUCount AS[cpuCOUNT]
from Orion.nodes n
join Orion.APM.Application a on a.NodeID=n.NodeID
join Orion.APM.Component c on c.ApplicationID=a.ID
INNER join (select c1.NodeID, COUNT(c1.CPUIndex) as CPUCount
from (select DISTINCT CPUMultiLoad.NodeID, CPUMultiLoad.CPUIndex from Orion.CPUMultiLoad) c1
group by c1.NodeID) c2 on n.NodeID = c2.NodeID
where c.ComponentName= 'Processor Queue Length'
AND c.CurrentStatistics.ComponentStatisticData > c2.CPUCount
AND N.CPULoad > 90
order by n.CPULoad desc
Thanks
Amit
