Splunk Search

How do I return values that match column in Lookup table?

jwalzerpitt
Influencer

I have an index that contains a field called user. I have a lookup file that also contains the header user, in addition to various other columns headers with other values.

How do I write a search that only returns the users that are listed in the Lookup file? I've tried the following, but I'm still seeing every user returned when I do a stats or something similiar

| lookup lookupfile user as user

Thx

0 Karma
1 Solution

somesoni2
Revered Legend

The | lookup command is data enrichment command (adds more information from lookup table to current result based on matching field). What you need is a subsearch to use lookup as filter, like this

Your base search [| inputlookup lookupfile | table user | dedup user]
| ... rest of the search

OR

your current search
| search  [| inputlookup lookupfile | table user | dedup user]

View solution in original post

somesoni2
Revered Legend

The | lookup command is data enrichment command (adds more information from lookup table to current result based on matching field). What you need is a subsearch to use lookup as filter, like this

Your base search [| inputlookup lookupfile | table user | dedup user]
| ... rest of the search

OR

your current search
| search  [| inputlookup lookupfile | table user | dedup user]

jwalzerpitt
Influencer

Thx for the clarification

If I wanted to add additional fields from the lookup file, such as Full Name, Dept #, while still matching only those user names in the lookup file, how would I perform that search?

Thx

0 Karma

somesoni2
Revered Legend

You would need to use both filter and enrichment version for that. The optimum approach would to filter first and then enrich, like this

Your base search [| inputlookup lookupfile | table user | dedup user]
 | ... rest of the search
| lookup lookupfile user OUTPUT "Full Name" "Dept #" ..all other fields
0 Karma

jaxjohnny2000
Builder

This worked perfectly!!! Thank you.

[| inputlookup lookupfile | table user | dedup user]
| ... rest of the search
| lookup lookupfile user OUTPUT "Full Name" "Dept #" ..all other fields

0 Karma

snowmizer
SplunkTrust
SplunkTrust

At first glance it seems like you're wanting to filter your results using lookupfile. By default the lookup command adds additional fields to your results. In order to filter you're probably going to want to use inputlookup in a subsearch.

Basic example:

index=abc sourcetype=abcdef [search | inputlookup lookupfile | fields user]...

Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...