Using a subquery result in 'IN' clause

[revathiram]

Hi,

I have a query like below which would return a list of host names.

index=osmetrics flock=xxx source=ps PID=1
| lookup xxx.csv host
| stats latest(ELAPSED) as last_reboot by host 
| eval reboot_days=if(like(last_reboot, "%-%"), mvindex(split(last_reboot, "-"),0), 0)
| search reboot_days=0
| fields host | rename host as search

---------------------

Result:

search

----------

host 1

host 2

host 3

I want to use the above query results as a sub-query like below:

host IN [ index=osmetrics flock=xxx source=ps PID=1
| lookup xxx.csv host
| stats latest(ELAPSED) as last_reboot by host 
| eval reboot_days=if(like(last_reboot, "%-%"), mvindex(split(last_reboot, "-"),0), 0)
| search reboot_days=0
| fields host | rename host as search ]  
| timechart count by abcd

which is

host IN ( "host 1","host 2","host 3" ) 
| timechart count by abcd

Please help me with the query to format the output of query 1 like ( "host 1","host 2","host 3" ) and use it as sub-query in query 2.

[to4kawa · Accepted solution]

sample:

| makeresults count=10 
| eval host="host".random() % 10 
| search host IN ( 
    [| makeresults count=10 
    | eval host="host".random() % 10 
    | table host 
    | stats values(eval("\"".host."\"")) as search delim="," 
    | nomv search])