How filter results from the outer search in join subsearches?

[BradL]

I'm trying to monitor a set of hosts that run a batch process, and I want to produce output that dynamically identifies the hosts according to filter key words, then join that list of hosts with subsearches that look for the specific events of interest. For example:

index=Idx filter1 filter2 | stats min(_time) as initial_time by host | convert ctime(first_time) | table host, first_time | join type=left host [ | search index=eng_etm [ index=Idx filter1 filter2 |  stats count by host | table host | return 2000 host ] starttag1 starttag2 | convert ctime(_time) as start_time | table host, start_time | sort 0 host | dedup host ] | join type=left host [| search index=Idx [ index=Idx filter1 filter2 |  stats count by host | table host | return 2000 host ] endtag1 endtag2 | table _time, host | stats min(_time) as end_time by host | convert ctime(end_time) ]

Returns something like this:

host   initial_time      start_time        end_time
1      8/1/16 00:00:00   8/2/16 00:01:00   8/2/16 00:01:21
2      8/1/16 00:00:00   8/2/16 00:01:00   
3      8/1/16 00:00:00      
4      8/1/16 00:00:00   8/2/16 00:01:00   8/2/16 00:01:21

This is actually the output I want, but I don't like having to constantly restate the subsearch in each join statement. Is there a simpler way to write this search to allow the subsearch to get the host list from the outer search?

[MuS · Accepted solution]

Hi BradL,

No, since a sub search is run before the main search there is no way for a sub search to get information back from a main search.

Maybe you should look at this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to get an idea how it can be done without sub search and its limits or the March session of 2016 virtual .conf here http://wiki.splunk.com/Virtual_.conf

cheers, MuS

[BradL]

P.S> I worked around this by running the outer query into "outputlookup" creating a temporary file and then reusing the result that way. not nearly as clean or friendly as a single search, but functional.

[Runals]

You could throw it in a macro so that you wouldn't have to retype it over and over. That way at least in your mind you aren't repeating the same subquery(ies) 😃

[BradL]

@MuS. Ok, thanks

[MuS]

@BradL , I converted it to an answer - feel free to accept it if your question was answered - thanks 🙂