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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...