Splunk Search

Using a subquery result in 'IN' clause

revathiram
Engager

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.

Labels (1)
0 Karma
1 Solution

to4kawa
Ultra Champion

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])

View solution in original post

to4kawa
Ultra Champion

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])
Get Updates on the Splunk Community!

Your Voice Matters! Help Us Shape the New Splunk Lantern Experience

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...

Community Content Calendar, October Edition

Welcome to the October edition of our Community Spotlight! The Splunk Community is a treasure trove of ...