Average CPU Load By Hour in Modern Dashboard SWQL Query Builder Doesn't Automatically Update Tables

jonathanlippe

Situation:

Using the SWQL Graphical Query Builder to get Average CPU status by Hour for a Table.

The day I created it, it looked good, but I noticed the next day the same data was there.

Then I looked at the dates on the SWQL builder and it was from the previous day.

The SWQL code it outputted was for a single hour on a specific day.  I want it to continuously calculate it of the last hour.

I just want a Modern Dashboard Table that displays the Average CPU Load greater than 60% of Windows Servers on a Table.

How do I do that?

SELECT TOP 10 n.Caption,aggSub1._avg 
AS Orion_CPULoad_AvgLoad_avg,n.DetailsUrl,n.Status,n.Vendor,n.LastSync 
FROM Orion.Nodes n 
LEFT JOIN ( SELECT i.NodeID,i.InstanceSiteId , avg(i.AvgLoad) AS _avg 
FROM Orion.CPULoad i 
WHERE (i.DateTime BETWEEN '2022-04-12T17:10:38.386Z' AND '2022-04-12T18:10:38.386Z') GROUP BY i.NodeID,i.InstanceSiteId ) aggSub1 ON aggSub1.NodeID=n.NodeID AND aggSub1.InstanceSiteId=n.InstanceSiteId WHERE ($aggSub1._avg > 60 AND Vendor = 'Windows')

Also note that I looked at this page with SWQL Functions but without examples I had no idea which to try or how the syntax with parenthesis and other characters needed to look.  I'm N00b.

christopher.t.jones123

@jonathanlippe, give this one a try

SELECT TOP 10 n.Caption
,aggSub1._avg AS Orion_CPULoad_AvgLoad_avg
,n.DetailsUrl
,n.Status
,n.Vendor
,n.LastSync 
FROM Orion.Nodes n 
LEFT JOIN ( SELECT 	i.NodeID
					,i.InstanceSiteId
					, avg(i.AvgLoad) AS _avg 
			FROM Orion.CPULoad i 
			WHERE (i.DateTime > ADDHOUR (-1,GETDATE())) 
			GROUP BY i.NodeID,i.InstanceSiteId ) aggSub1 ON aggSub1.NodeID=n.NodeID AND aggSub1.InstanceSiteId=n.InstanceSiteId WHERE ($aggSub1._avg > 60 AND Vendor = 'Windows')