topic inputlookup in subsearch to filter by one column and to output back the corresponding values of another column to main search in Splunk Search

inputlookup in subsearch to filter by one column and to output back the corresponding values of another column to main search

yepyepyayyooo — Wed, 30 Sep 2020 03:23:34 GMT

Okay so this question has never been asked or answered before so here goes...Hoping someone can assist.

index="ironport" 
    [ inputlookup exfil_filenames 
    | fields file_name ]
| table file_name matching_criteria

The above query has a lookup which has 2 columns: file_name, matching_criteria. The only difference between them is the items in matching_criteria do not have asterisks. Example:

file_name   matching_criteria
*hello*        hello
*world*        world

Currently the query returns files that match the lookup field "file_name" but in addition to that I also need the corresponding matching criteria value to be associated and returned in the main search. So this is what I'd like to see as the result:

| table file_name matching_criteria

file_name     matching_criteria
hello_file.xls     hello
world_bank.virus     world
SSN_dump.zip     SSN

God speed! Hopefully I explained it clearly. Thanks in advanced.

Re: inputlookup in subsearch to filter by one column and to output back the corresponding values of another column to main search

aberkow — Tue, 17 Dec 2019 22:20:57 GMT

Are you familiar with the lookup command, and is there a reason that doesn't work for you? If you check out the docs here https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Lookup, it does essentially what you want (join/lookup on a value, and optionally OUTPUT any fields you want).

Example that should work for you:

index="ironport"
| lookup exfil_filenames file_name OUTPUT matching_criteria
|table file_name matching_criteria

Alternatively and perhaps more performantly,

index="ironport" 
         [ inputlookup exfil_filenames 
         | fields file_name ]
| lookup exfil_filenames file_name OUTPUT matching_criteria
| table file_name matching_criteria

You also don't need the wildcards in the csv, there is an option in the lookup configuration that allows you do wildcard a field when doing lookup matches: Settings -> Lookups -> Lookup definitions -> filter to yours -> click it -> advanced options -> Match type -> WILDCARD(file_name).

Hope this helps!

Re: inputlookup in subsearch to filter by one column and to output back the corresponding values of another column to main search

yepyepyayyooo — Wed, 30 Sep 2020 03:24:19 GMT

aberkow thank you for your response. I have tried both examples you have provided. In both scenarios the file_name column populates results however the matching_criteria column is blank; not displaying the matching value.

Re: inputlookup in subsearch to filter by one column and to output back the corresponding values of another column to main search

yepyepyayyooo — Wed, 30 Sep 2020 03:24:21 GMT

I should point out that unlike file_name, matching_criteria is not a field in the index. It's only a column header in the lookup table.

Re: inputlookup in subsearch to filter by one column and to output back the corresponding values of another column to main search

yepyepyayyooo — Thu, 19 Dec 2019 14:25:04 GMT

Update:

The reason it wasn't working was because I had wildcard (asterisks) in the lookup file. When I removed those and used the advanced features method aberkow recommended instead, the matching_criteria column returned results.

Splunk gremlin