topic Re: Calculate where criteria with value from a subsearch in Splunk Search

Calculate where criteria with value from a subsearch

jheiselman — Tue, 18 May 2021 17:36:49 GMT

I'm sure this has been asked before, but nothing I'm coming up with for searches against this forum have proved useful.

I want to check for Windows hosts where the number of Context Switches/sec is higher than a calculated amount. That calculation needs to take into account the number of processors on the system.

To get the number of processors, I found that I can run the following search:
index="perfmon" sourcetype="Perfmon:CPU" instance!="_Total" | stats dc(instance) AS NumProcessors by host

To get the number of Context Switches/sec, it's as easy as:
index="perfmon" sourcetype="Perfmon:System" counter="Context Switches/sec"

And I want to limit the events in the context switches query to where Value = 5000 * NumProcessors. I thought a subsearch might be the way, but I can't seem to get that to work. This is something like what I want, but it doesn't work because the subsearch usage is wrong.

index="perfmon" sourcetype="Perfmon:System" counter="Context Switches/sec"
| stats avg(Value) AS avg_cs by host
| where avg_cs > (5000 * [search index="perfmon" host=$host$ sourcetype="Perfmon:CPU" instance!="_Total" | stats dc(instance) AS NumProcessors by host])

Re: Calculate where criteria with value from a subsearch

jheiselman — Tue, 18 May 2021 18:16:45 GMT

Got it to work for me.

Re: Calculate where criteria with value from a subsearch

bowesmana — Tue, 18 May 2021 23:32:39 GMT

When using appendcols, you need to be 100% certain that the number of rows in your outer search and the number of rows in the subsearch are the same, otherwise the columns will not necessarily line up with the correct, in your case, host.

It is sometimes better to use append, as below, which means less sorting and no issue with missing host data

index="perfmon" sourcetype="Perfmon:System" counter="Context Switches/sec" | stats avg(Value) AS avg_cs by host | append [ search index="perfmon" sourcetype="Perfmon:CPU" instance!="_Total" | stats dc(instance) AS NumProcessors by host | eval threshold=(5000*NumProcessors) ] | stats values(*) as * by host | where !isnull(threshold) AND avg_cs>=threshold | rename avg_cs AS "Context Switches/sec" | fields host, "Context Switches/sec", threshold | sort host

This basically creates data set 1 and data set 2, with the common host field.

The stats values(*) as * by host, collapses and joins the columns for each host. Where there is no threshold, if for example, the subsearch does not have data for the search, the isnull() check handles that case.

Re: Calculate where criteria with value from a subsearch

jheiselman — Wed, 19 May 2021 16:00:27 GMT

@bowesmana wrote:
When using appendcols, you need to be 100% certain that the number of rows in your outer search and the number of rows in the subsearch are the same, otherwise the columns will not necessarily line up with the correct, in your case, host.

Understood. Part of what I've excluded is a couple of lookups done to ensure that the host list is always the same and in the same order.