Passing parent data into subsearch

[phoenixdigital]

I have a parent search which returns

_time, key, value1 value2

Now I want to join it with a CSV file with the following format

key, startDate, endDate, internalValue

I want the subsearch to join based on key and a where startDate<_time AND endDate>_time where _time is from the parent search.

Is there a way to pass the parent searches _time through to subsearch?

Ideally I want it to be something like this

sourcetype="blah" | join key [inputlookup customer-details.csv | eval startDate=strptime(startDate, "%d/%m/%Y") | eval endDate=strptime(endDate, "%d/%m/%Y") | where startDate<_time AND endDate<_time]

Is it even possible to pass in parent data as parameters usable in the subsearch?

So I would like to the correlating internalValue to link up with the matching _time on the parent search.

[landen99]

I just executed a search looking like this:

| inputlookup stuff |eval time_min=time | eval time_max=relative_time(time, "+1d") | map maxsearches=99999 search="search index=myindex earliest=$time_min$ latest=$time_max$ [|inputlookup stuff | search time=$time$ | table field1] | join field1 [|inputlookup stuff]... "

Here we see several variables not only being passed to the map subsearch but also to a subsearch within map's subsearch. I can verify that it works. Since time is the most efficient method for reducing search time, passing time constraints through a map yields fast results for the data being searched.

Added correction: It seems that map is tricky enough with its 240s time limit. Adding subsearches easily breaks this if you aren't extremely careful, especially when you are trying to pass variables into it from the main search. This search ultimately proved to provide unreliable search results or to just flat out fail due to the complexity added with the subsearch nested into map, but without the subsearch, it seemed to work well enough if the time for each map search could be assured to never exceed its time limit.

[phoenixdigital]

More information.

The parent search

sourcetype="blah" key="12345" | timechart span=mon sum(hourlyVolume) as totalVolume, max(key) as key | table _time, key, hourlyVolume

Give me the results for each month with all the hourlyVolumes summed up for that month. _time in the results is the beginning of each month. doing the max(key) was the only way to pass through key

Now I wanted to join this with with the data in the CSV where the _time fell between startDate and endDate. This is needed in order to get out internalValue to use for further evals.

So below wont work seeing as the subsearch is performed first but you can see what I want to achieve if you imagined the subsearch was done after the parent.

sourcetype="blah" | timechart span=mon sum(hourlyVolume) as totalVolume | join key [inputlookup customer-details.csv | eval startDate=strptime(startDate, "%d/%m/%Y") | eval endDate=strptime(endDate, "%d/%m/%Y") | where startDate<_time AND endDate<_time] | eval totalCost = totalVolume*internalValue

The internalValue in the CSV changes throughout the year.

If I reverse this search and do the lookup first then subsearch the monthly summaries I will only get one result for each line in the CSV?

[Ayn]

It still looks to me like you could do this via a regular temporal (time-based) lookup. See http://docs.splunk.com/Documentation/Splunk/5.0.2/Knowledge/Addfieldsfromexternaldatasources#Set_up_...

It does depend though of your exact constraints needs. Default behaviour in a time-based lookup is that looked up values are valid from their respective time until the next occurrence of the value. Hard to explain 🙂 Hopefully the examples in the docs page I linked to can explain it more clearly.

[Ayn · Accepted solution]

No, an outer search cannot pass values into a subsearch. Subsearches run before the outer searches so they can't get values that aren't there to begin with.

I haven't had a really good look at your scenario but my initial thought is - aren't you doing this a bit backwards, using join for grabbing values from a lookup table via inputlookup? What's stopping you from using a "normal" temporal lookup instead?

[phoenixdigital]

Yep followed the instructions you linked to here
http://docs.splunk.com/Documentation/Splunk/5.0.2/Knowledge/Addfieldsfromexternaldatasources#Set_up_...

Thanks for that. Got a related question to this now but will post in another question.

[phoenixdigital]

Ah thanks for the tip that subsearches are run first.

Not sure if using the temporal lookup will work though.

The parent search actually does things a little more complex. I dumbed it down so as to not confuse the issues.

Will post more information in another answer as I cannot format text in this response.