topic Re: mstats with host subquery in Splunk Search

mstats with host subquery

matthewwhittle — Tue, 03 Nov 2020 01:47:00 GMT

Hi all!

I have this query which gets me the list of hosts

stuff stuff stuff | rename host as host_changed | dedup host_changed | table host_changed

it works beautifully.

Now I have this other query

| mstats prestats=true avg(load.*) WHERE (`sai_metrics_indexes`) AND host=lalalala by host span=1m | timechart span=1m avg(load.longterm) AS Longterm by host

which also works perfectly

Now, what I want to do, it effectively combine the two, but I cannot seem to get the syntax right

| mstats prestats=true avg(load.*) WHERE (`sai_metrics_indexes`) AND host in [search stuff stuff stuff | rename host as host_changed | dedup host_changed | table host_changed] by host span=1m | timechart span=1m avg(load.longterm) AS Longterm by host

Thoughts? Thanks!

Re: mstats with host subquery

richgalloway — Tue, 03 Nov 2020 14:46:43 GMT

Run the subsearch by itself with "| format" appended to it. You should get something that looks like

(host="foo" OR host="bar" OR host="baz")

Add that to the main search to get

| mstats prestats=true avg(load.*) WHERE (`sai_metrics_indexes`) AND host in (host="foo" OR host="bar" OR host="baz")

and you should see the problem. The string returned by the subsearch makes no sense in the context of the main search. The solution is to modify one or both searches so the result is good SPL.

| mstats prestats=true avg(load.*) WHERE (`sai_metrics_indexes`) AND [search stuff stuff stuff | rename host as host_changed | return host_changed] by host span=1m | timechart span=1m avg(load.longterm) AS Longterm by host

Re: mstats with host subquery

matthewwhittle — Wed, 04 Nov 2020 17:42:19 GMT

Hi richgalloway,

Your response is very appreciated. When I tried your suggestion below, I got the error

"Term based search is not supported"

Re: mstats with host subquery

matthewwhittle — Wed, 04 Nov 2020 17:48:06 GMT

Ah, but a theme off that variation works, taking the approach of modifying the mstats query

| mstats prestats=true avg(load.*) WHERE (`sai_metrics_indexes`) AND

[search stuff stuff stuff

| format] by host span=1m

| timechart span=1m avg(load.longterm) AS Longterm by host