Splunk Search

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

dionrivera
Path Finder

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
Path Finder

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
Path Finder

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!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...