mstats with host subquery

[matthewwhittle]

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!

[richgalloway · Accepted solution]

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

[matthewwhittle]

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

[matthewwhittle]

Hi richgalloway,

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

"Term based search is not supported"