Splunk Search

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

yepyepyayyooo
New Member

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.

Tags (1)
0 Karma
1 Solution

aberkow
Builder

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!

View solution in original post

0 Karma

yepyepyayyooo
New Member

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

0 Karma

aberkow
Builder

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!

0 Karma

yepyepyayyooo
New Member

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.

0 Karma

yepyepyayyooo
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...