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 July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...