topic Re: subsearch - Busy Hour CPU usage in Splunk Search

subsearch - Busy Hour CPU usage

splunk_zen — Mon, 28 Sep 2020 13:10:06 GMT

I am running into some difficulties graphing some servers Used CPU % in the Busy Hour,

(spanning at least 2 months) for that I am trying to join a subsearch to a main search,

index=resources_reporting source=*/output/ora_queries/client07/OS_metrics.csv HOST_NAME=*sdp* | eval CpuUsedPct=USED_CPU
| convert timeformat="%y-%m-%d" ctime(_time) as day
| append
[search index=resources_reporting source="*output/ora_queries/client07/SdpTermCauses.csv" PLATFORM=P1 
| convert timeformat="%y-%m-%d" ctime(_time) as day
| stats sum(SERVICE_EVOCATIONS) as ServiceEvocationsSum by date_hour day
| eventstats max(ServiceEvocationsSum) as MaxServiceEvocationsSum by day
| where ServiceEvocationsSum=MaxServiceEvocationsSum
| rename day AS BusyHourDay, date_hour AS BusyHour
| fields BusyHourDay BusyHour
] 
| convert timeformat="%y-%m-%d" ctime(_time) as day
| chart avg(CpuUsedPct) as AvgUsedCpuPct over day
| where date_hour=BusyHour day=BusyHourDay

How can I get the AvgUsedCpuPct for each day Busy Hour ?

I've tried Ayn incremental functions suggestion but I'm really having trouble getting the where clause right.

EDIT
Ended up using

MAIN_SEARCH [search SUB_SEARCH
| fields date_month, date_mday, date_hour
]
| eval CpuUsedPct=USED_CPU
| timechart avg(CpuUsedPct) as AvgUsedCpuPct

Re: subsearch - Busy Hour CPU usage

Ayn — Wed, 23 Jan 2013 10:03:46 GMT

You're tacking the subsearch directly onto the eval command. A subsearch works very much like backticks in *NIX systems, in that it runs first of all and returns its values to the outer search. In your case, you have

... | eval CpuUsedPct=USED_CPU [search ...

which after the subsearch returns its results will expand to something like

... | eval CpuUsedPct=USED_CPU ((BusyHourDay= ...

This will of course make eval think you want to call USED_CPU as some kind of function, which won't work since there's no such function to eval.

Perhaps you meant to append the second search to the first?

... | eval CpuUsedPct=USED_CPU | append [search ...

Re: subsearch - Busy Hour CPU usage

splunk_zen — Wed, 23 Jan 2013 10:38:30 GMT

Thanks for the explanation Ayn !
Makes more sense now,
I've slightly changed the search but I am still getting the error,

Error in 'where' command: The operator at 'day=BusyHourDay' is invalid.

Re: subsearch - Busy Hour CPU usage

Ayn — Wed, 23 Jan 2013 11:02:30 GMT

I think you need to add an "AND" between your statements in the where command.

... | where date_hour=BusyHour AND day=BusyHourDay

Re: subsearch - Busy Hour CPU usage

splunk_zen — Wed, 23 Jan 2013 11:22:21 GMT

Thanks again Ayn.
the solution must be close,
but the search is now returning 0 results.

Re: subsearch - Busy Hour CPU usage

Ayn — Wed, 23 Jan 2013 14:21:53 GMT

I think you should break down your search into parts and add one command in the search pipeline at a time, so you can troubleshoot where things go wrong.

Re: subsearch - Busy Hour CPU usage

splunk_zen — Thu, 24 Jan 2013 16:44:48 GMT

I've tried a series of combinations but I'm really having trouble with the where clause. Can you point me in the right direction ?

PS: I've replaced
| stats avg(CpuUsedPct) as AvgUsedCpuPct by day, date_hour
by
| chart avg(CpuUsedPct) as AvgUsedCpuPct over day

Re: subsearch - Busy Hour CPU usage

Ayn — Fri, 25 Jan 2013 12:20:50 GMT

The thing is that when you've run the chart command, the fields you're trying to filter on do not exist anymore. The only fields you have available after the last chart command are the ones it produces - AvgUsedPct and day.