topic Re: Lookup usage question in Splunk Search

How to correlate a field from a query to a field from a lookup file?

dionrivera — Mon, 10 Apr 2023 16:10:18 GMT

Hello. I've been watching a few lookup videos but they mostly concentrate on extracting data from a lookup file. None of them are addressing a case where you have to correlate a field from a query to a field from a lookup file. Here is my example. I have a query (index=web username=mike) I would like to pull Mike's email from a emaillookup.csv file so that my final table result looks like below.

username email

mike mike@yahoo.com

So far, I have tried index=web username=mike | lookup emaillookup.csv email OUTPUT username with no success

Re: Lookup usage question

tscroggins — Thu, 06 Apr 2023 17:24:38 GMT

Hi,

Try swapping your input and output fields:

index=web username=mike | lookup emaillookup.csv username output email

The lookup command takes the form:

| lookup <lookup_name> <lookup_field_name> [as <event_field_name>] output <lookup_field_name_1> [as <event_field_name_1>] [<lookup_field_name_2> [as <event_field_name_2>] ...]

Re: Lookup usage question

woodcock — Thu, 06 Apr 2023 19:57:03 GMT

Like this:

index="web" AND username="mike"
| lookup emaillookup.csv nameFieldInLookupFIle AS username OUTPUT email

Re: Lookup usage question

dionrivera — Fri, 07 Apr 2023 16:09:49 GMT

Thank you. I figured out my problem.

On the query I was trying to use username=mike and trying to reference the name mike in my emaillookup.csv lookup table. However, the name in the lookup table was in the form of mike@my-site.com . I had to regex the "@my-site.com" from the name mike in order to reference mike. Once I was referencing mike on both the query and the lookup table, I was able to pull the fields I needed.

Thanks for both of your recommendations

Re: Lookup usage question

woodcock — Fri, 07 Apr 2023 16:34:11 GMT

You can create a lookup definition and use "WILDCARD(user)" and make it "mike*" and it will match either.

Re: Lookup usage question

dionrivera — Wed, 12 Apr 2023 17:18:56 GMT

So, if I had more than one user, could I use WILDCARD(user*)?

Re: Lookup usage question

woodcock — Wed, 12 Apr 2023 17:26:56 GMT

Any user that starts with "mike" would match.