Average Load Per CPU

This is sort of dove-tails with the question here: Alerts of individual CPUs in a Node but I wanted to put something fresh out with some more detail. I've put together a custom SWQL alert trigger that will respond to the latest sample of any individual CPU on a node exceeding the threshold in the where clause:

SELECT Nodes.NodeID, CPU.CPUIndex, CPU.AvgLoadFROM Orion.Nodes AS NodesJOIN Orion.CPUMultiLoad CPU ON Nodes.NodeID = CPU.NodeIDJOIN(   SELECT NodeID, MAX(TimeStampUTC) AS MaxTimeStampUTC   FROM Orion.CPUMultiLoad   GROUP BY NodeID) TBL1 ON CPU.TimeStampUTC = TBL1.MaxTimeStampUTC AND CPU.NodeID = TBL1.NodeIDWHERE Nodes.NodeID = 46-- Result:-- 46,0,0-- 46,1,0-- 46,2,11-- 46,3,11-- 46,4,29-- 46,5,29-- 46,6,52-- 46,7,52-- 46,8,0-- 46,9,0-- 46,10,29-- 46,11,29-- 46,12,11-- 46,13,11-- 46,14,64-- 47,15,64

That seems to work fine, but when I write some SQL to use in the alert action message using the same CPUMultiLoad view from the database I get only CPU 0:

SELECT CPU.CPUIndex, CPU.AvgLoadFROM CPUMultiLoad AS CPUJOIN(    SELECT NodeID, CPUIndex, MAX(TimeStampUTC) AS MaxTimeStampUTC    FROM CPUMultiLoad    GROUP BY NodeID, CPUIndex) TBL1 ON CPU.TimeStampUTC = TBL1.MaxTimeStampUTC AND CPU.NodeID = TBL1.NodeIDWHERE CPU.NodeID = 46-- Result: 0, 9

Any thoughts on why the SWQL would be happy but the SQL wouldn't? Am I beating around the wrong bush?

Thanks in advance,

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://www.loop1systems.com/

http://www.linkedin.com/in/mrxinu

Find more posts tagged with

sql

CPU

SWQL

multiple cpus

Alerting

Accepted answers

m_roberts

When I tested this I produced the same results in either SWQL or SQL (remove the Orion. from the SQL version)

Not sure if it is simply a case of including the Group By clause on the SQL:

SELECT CPU.CPUIndex, CPU.AvgLoad FROM CPUMultiLoad AS CPU JOIN (     SELECT NodeID, CPUIndex, MAX(TimeStampUTC) AS MaxTimeStampUTC     FROM CPUMultiLoad     GROUP BY NodeID, CPUIndex ) TBL1 ON CPU.TimeStampUTC = TBL1.MaxTimeStampUTC AND CPU.NodeID = TBL1.NodeID WHERE CPU.NodeID = 5GROUP BY CPU.CPUIndex, CPU.AvgLoad

All comments

silverbacksays

Maybe it's because you're crossing the streams, using SWQL for one aspect and SQL for the other.

Alternatively it could be because the value you're using is exclusive to SWQL, and SQL can't see it.

I refer to this from time to time. Some of it might be out dated, but the rest is useful:

SolarWinds SWIS API Cheat Sheet by ernest - Cheatography.com: Cheat Sheets For Every Occasion

mrxinu

Hey silverbacksays, thanks for the quick reply!

Maybe it's because you're crossing the streams, using SWQL for one aspect and SQL for the other.

Were SWQL an option for embedding the details in the email message I'd definitely go that route. Unfortunately it looks like SQL is the only game in town. I've done this kind of thing with success for other things but this one's got me stumped.

Alternatively it could be because the value you're using is exclusive to SWQL, and SQL can't see it.

Hmm, that doesn't make sense to me. Can you think of an example where that's actually the case? The API is just an abstraction layer for the SQL data - solarwinds has done a lot of work to bake it up nice (using database views, I suspect) to present it via SWQL but it's all the same information.

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://www.loop1systems.com/

http://www.linkedin.com/in/mrxinu

vfrancotecel

Does it have to create a group and put all in a single place and monitor per group?

mrxinu

Hey vfrancotecel, no the individual CPU load information is tracked on a per-node basis. The first query targets that one machine and grabs the most recent information and is able to pull the current load on each core. The odd thing is that the identically-named view in the database produces different results. Very strange.

m_roberts

When I tested this I produced the same results in either SWQL or SQL (remove the Orion. from the SQL version)

Not sure if it is simply a case of including the Group By clause on the SQL:

SELECT CPU.CPUIndex, CPU.AvgLoad FROM CPUMultiLoad AS CPU JOIN (     SELECT NodeID, CPUIndex, MAX(TimeStampUTC) AS MaxTimeStampUTC     FROM CPUMultiLoad     GROUP BY NodeID, CPUIndex ) TBL1 ON CPU.TimeStampUTC = TBL1.MaxTimeStampUTC AND CPU.NodeID = TBL1.NodeID WHERE CPU.NodeID = 5GROUP BY CPU.CPUIndex, CPU.AvgLoad

mrxinu

When I tested this I produced the same results in either SWQL or SQL (remove the Orion. from the SQL version)

Can you share the results from both queries as well? I'm wondering if you're only getting the same results because both produce a single row.

jamison.jennings

Dobbs query returns the correct results. I tested on a node with 32 CPU's.

vfrancotecel

Com certeza.

Em 11 de mar de 2016 10:48 PM, "mrxinu" <

solarwindscommunityteam@communications.solarwinds.com> escreveu:

thwack <thwack.solarwinds.com/
Average Load Per CPU
Steven Klassen
<thwack.solarwinds.com/.../mrxinu
marcou a resposta de jamison.jennings
<thwack.solarwinds.com/.../jamison.jennings
para Average Load Per CPU
<thwack.solarwinds.com/.../101196
como útil. Visualizar a resposta completa
<thwack.solarwinds.com/.../325110