topic Re: How do I return values that match column in Lookup table? in Splunk Search

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

jwalzerpitt — Wed, 13 Dec 2017 16:18:04 GMT

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

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

snowmizer — Wed, 13 Dec 2017 16:40:06 GMT

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

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

somesoni2 — Wed, 13 Dec 2017 16:40:53 GMT

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

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

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

jwalzerpitt — Wed, 13 Dec 2017 16:54:04 GMT

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

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

somesoni2 — Wed, 13 Dec 2017 17:04:56 GMT

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

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

jaxjohnny2000 — Fri, 02 Aug 2019 17:10:08 GMT

This worked perfectly!!! Thank you.