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

[yepyepyayyooo]

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.

[yepyepyayyooo]

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

[aberkow · Accepted solution]

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!

[yepyepyayyooo]

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.

[yepyepyayyooo]

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.