I have the query below that I am using in Grafana that shows me all my servers Average CPU based on the City they are located in. Using New York as an example, I have 50 servers in this City so the graph displays 50 servers and their average CPU over a set time period. This graph is very busy to look at.
SELECT
DateTime,
AvgLoad as [Average CPU],
CPULoad.Node.Caption as [Server Name]
FROM Orion.CPULoad
WHERE cpuload.Node.CustomProperties.City = 'New York'
AND DateTime BETWEEN $from AND $to
ORDER BY DateTime
What I would like to do is to take the average of all 50 servers and plot just one line over time. I need the query to find out what the average of all servers in New York was at e.g. 5 minute samples 12:00, 12:05, 12:10, 12:15 and so on.. I want this to then plot just 1 average line over the set time period.
My polling rate is 1 min so all of these servers checkin at slightly different times but within the 1 minute. If I GROUP BY City (and remove server name) I see just one line but its too spiky to be any use because if server 1 was at 90% CPU at 12:01:22 and server 2 was at 2% at 12:01:35 then it draws 90% then down to 2% and so on. I need it to flatten these out and maybe sample every 1 minute or 5 minutes (or at least sum/avg the 1 or 5 minute of the 50 servers) and plot that number.
I can do this ok using the Custom Graph in Solarwinds and select Group on City and Legend on City and it creates me a nice smooth average CPU graph of 1 line for all of those 50 servers, however trying to do this with a SWQL query is not proving easy.
Can anyone help?
