How to use field value from outer query in inputlookup?

[Thulasinathan_M]

Hi,

I'm trying to find whether a lookup file is available or not. If yes, I want to use the same file, if not I want to use different file, so far with some helps, I've written below query, the eval fileName if condition is working fine, in the stats I could see the correct results(desired files I'm looking for). 

But I'm wondering whether I could use the filename in makeresults and search for lookup file. Could someone please assist. Thanks in advance.

index=main sourcetype="dummySource"  events
| stats by EventCode
| append [ | inputlookup states.csv | stats count as isAvailable ]
| stats sum(isAvailable) as available, values(EventCode) as EventCode
| eval fileName = if(available > 0, "1.csv", "2.csv")
| stats values(available) as available values(EventCode) as EventCode by fileName
| join type=left fileName 
   [| inputlookup [ | makeresults 
    | eval search=fileName
    | table search ]]

[Thulasinathan_M]

Thanks @ITWhisperer, glad to know the sub-search runs first.
The inner inputlookup gives me the correct fileNames, but when I try it with either of options neither worked, any suggestions on what I'm doing wrong please.

Option1:
| inputlookup [| makeresults
[| inputlookup geo_attr_us_states.csv
| stats count as isAvailable
| eval fileName = if(isAvailable > 0, "1.csv", "2.csv")
| table fileName]]

Option 2:
| inputlookup [| makeresults
[| inputlookup geo_attr_us_states.csv
| stats count as isAvailable
| eval fileName = if(isAvailable > 0, "1.csv", "2.csv")
| table fileName]
| return $fileName]

[ITWhisperer · Accepted solution]

Try something like this

| inputlookup geo_attr_us_states.csv
| stats count as isAvailable
| eval fileName = if(isAvailable > 0, "1.csv", "2.csv")
| eval lookup="| inputlookup ".fileName
| map search="| makeresults | map search="$lookup$

[Thulasinathan_M]

Hi @ITWhisperer ,

Sorry,  I misunderstood my existing flow and it's I've to add a field  'env' value from the main search. As I'm a newbie to splunk couldn't find a solution for this, could you please kindly assist.

index=main sourcetype=java ErrorCode=400 env=prod
| join type=left ErrorCode
[| inputlookup [| makeresults 
        | eval search="Errors".env.strftime(now(),"%m%d").".csv" 
        | table search]
| stats count as isAvailable
| eval fileName = if(isAvailable > 0, "Errors".env.strftime(now(),"%m%d").".csv", "Errors".env.strftime(relative_time(now(), "-1d"),"%m%d").".csv")
| eval lookup="| inputlookup ".fileName
| map search="| makeresults | map search="$lookup$]

[ITWhisperer]

From your main search, env=prod so why not just use that string in the lookup file name?

[Thulasinathan_M]

Thanks, working now.!!!

[Thulasinathan_M]

@ITWhisperer  Based on the source the env values get change, from the results I add a new field as 'env' using rex and then have to use the field value to differentiate the files specific to each env.

[Thulasinathan_M]

Thank you, it did the trick 🙂

[ITWhisperer]

Essentially, you can't pass values from the outer search to the inner search, this is because, in general, the inner search is executed before the outer search.

One exception to this is the map command. However, the search which is executed for each event, replaces the event with its results.

You may be able to use this by doing the test first and use inputlookup to load the relevant csv file, then append your main search as a subsearch, then use stats to join your result to event from the lookup.