Splunk Search

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

dionrivera
Communicator

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

Labels (2)
0 Karma

woodcock
Esteemed Legend

Like this:

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

0 Karma

dionrivera
Communicator

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

0 Karma

woodcock
Esteemed Legend

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

dionrivera
Communicator

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

0 Karma

woodcock
Esteemed Legend

Any user that starts with "mike" would match.

0 Karma

tscroggins
Influencer

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...