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!
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
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
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
Hi richgalloway,
Your response is very appreciated. When I tried your suggestion below, I got the error
"Term based search is not supported"