SWQL KPI help

Hi All,

I am looking to create a KPI that determines how long a servers CPU was above its threshold (say 80%) for last month (and another for the current month). I am trying to divide the polls that were overby all polls in the month. so far I have:

select count(1) as overload

from orion.CPULoad as CP

where nodeid = '620'

and CP.maxload > 80

AND (MONTH(TOLOCAL(cp.DateTime)) = (MONTH(TOLOCAL(ADDMONTH(-1,GETUTCDATE())))))

and

select count(1) as overload

from orion.CPULoad as CP

where nodeid = '620'

AND (MONTH(TOLOCAL(cp.DateTime)) = (MONTH(TOLOCAL(ADDMONTH(-1,GETUTCDATE())))))

How do I create a swql query that divides the first from the second query.

thanks

Matt

Find more posts tagged with

swql query

Accepted answers

jm_sysadmin

security settings on the forum are blocking the post, I sent a friend request. However I uploaded a copy to github at: sampleSWQL/sampleSWQL.txt at main · jmsysadmin/sampleSWQL

All comments

jm_sysadmin

@KITGuy I think I got this.

jm_sysadmin

security settings on the forum are blocking the post, I sent a friend request. However I uploaded a copy to github at: sampleSWQL/sampleSWQL.txt at main · jmsysadmin/sampleSWQL

KITGuy

That works thanks! and its a bit neater then what I was trying. Further question... How would I account for data rollup in this calculation? If raw is 7 days old and hourly is 35 days old, the raw would skew the average correct?

jm_sysadmin

I'll get to code in a minute. But first, let's talk about your retention settings. If you have raw for 7 days and hourly for 35 that means the first week you have (I assume) polls every five minutes, so 12ish every hour. The 2-4 week of the month for hourly you will have 24 values which are the average of the twelve you started with. And since we didn't do last 30 days, we did last month, near the end of every month, the hourly will roll up to daily at some point, averaging the 24 points into 1. If you look at the charts for the CPU peak of the last 3 months, it smooths out every time you go to a new level of roll up, and this is why. Those peaks over 80 are hard to see unless they last a long time.

If I were building a KPI, I'd either ignore the issue (using it to say that lower is better, and higher needs investigation) or I'd build my KPI timeframes to match specifically the roll up windows. For you, it would be a KPI for last 7 days, one for day 8-35, and if you care one further out.

But I also consider other ways to show the data. On the node I can set specific warning and critical thresholds. and if I have alerts set, and for business hours (which might allow things like back up times to be excluded) I can simply calculate the duration of the 'warning' and/or 'critical' alert triggered time. I have code like this lying around somewhere.

I think it matters what question you want to answer. On my domain controllers I want lower CPU than on my SolarWinds Polling engine. My SQL Servers will always have more RAM used than most things. Tailor this to answer what you need and know the story the number tell.

I tried again, but I still can't add swql here, so I updated the file at: sampleSWQL/sampleSWQL.txt at main · jmsysadmin/sampleSWQL
Edit: now able to post:

Select
    --[DayTable].[Day],
    --[DayTable].[DailyCPUAverage]
    Round( COUNT(CASE WHEN [DayTable].[DailyCPUAverage] > 80 THEN 1 END) * 100.0 / COUNT([DayTable].[Day]) , 2) AS [PercentofDailyAverageOver80Percent]
FROM (SELECT
    DateTrunc('day', CP.DateTime) AS [Day],
    Avg( CP.MaxLoad) as [DailyCPUAverage]
FROM Orion.CPULoad CP
WHERE CP.NodeID = '620'
  AND MONTHDIFF(GETDATE(), CP.DateTime) = -1
GROUP BY DateTrunc('day', CP.DateTime)
ORDER BY [Day]) as [DayTable]

Under the first example I added a 2nd. This turns all days to daily roll ups, and gives you the percent of days that averaged over 80% CPU.
I think that is not as useful as the KPI you looked at first, but I would limit that not for last month, to CPU from the last 7 days personally.

@KITGuy I hope that moves you in the right direction.