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

[jwalzerpitt]

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

[somesoni2 · Accepted solution]

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]

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

[somesoni2]

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

[jaxjohnny2000]

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

[snowmizer]

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]...