Reporting

Lookup Table: Link more than one field (sender, receiver) from each event to a look up table value (email, firstname, lastname)

tristamaltizo
New Member

We have email events and want to link sender and receiver email addresses to the user data in a lookup table.

Events:
sender = user1@email.com
receiver = user2@email.com

Lookup Table:
user1@email.com, firstname, lastname, city, state
user2@email.com, firstname, lastname, city, state

What is an efficient search to get to the following search result:

Sender Email Sender State Receiver Email Receiver State
user1@email.com CA user2@email.com NY

Thanks for any input!

0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this (assuming both sender and receiver fields are appearing in same event)

your base search | table sender receiver | lookup yourEmailLookup.csv email as sender OUTPUT state as "Sender State"   | lookup yourEmailLookup.csv email as receiver OUTPUT state as "Receiver State"  | rename sender as "Sender Email" receiver as "Receiver Email" | table "Sender Email" "Sender State" "Receiver Email" "Receiver State"

View solution in original post

0 Karma

somesoni2
Revered Legend

Try something like this (assuming both sender and receiver fields are appearing in same event)

your base search | table sender receiver | lookup yourEmailLookup.csv email as sender OUTPUT state as "Sender State"   | lookup yourEmailLookup.csv email as receiver OUTPUT state as "Receiver State"  | rename sender as "Sender Email" receiver as "Receiver Email" | table "Sender Email" "Sender State" "Receiver Email" "Receiver State"
0 Karma

tristamaltizo
New Member

Hi @somesoni2 (or whoever else can provide input).....

With the following lookup data, I'm pulling the mgr*userid and would like to search for a match to the userid* in the same lookup file order to get their firstname and lastname. How would I include this in the search? Would it be another lookup command?

... | lookup yourEmailLookup.csv email as sender OUTPUT MGRID as sender_mgrid | lookup yourEmailLookup.csv USERID as sender_mgrid OUTPUT FIRSTNAME as "Sender Mgr Firstname" LASTNAME as "Sender Mgr Lastname"

USERID, EMAIL, FIRSTNAME, LASTNAME, MGRID
userid1, user1@email.com, firstname, lastname, mgr1userid
userid2, user2@email.com, firstname, lastname, mgr2userid

0 Karma

somesoni2
Revered Legend

Try something like this

your base search | table sender receiver | lookup yourEmailLookup.csv email as sender OUTPUT state as "Sender State"   MGRID as sender_mgrid   | lookup yourEmailLookup.csv USERID as sender_mgrid OUTPUT FIRSTNAME as "Sender Mgr Firstname" LASTNAME as "Sender Mgr Lastname" | lookup yourEmailLookup.csv email as receiver OUTPUT state as "Receiver State"  | rename sender as "Sender Email" receiver as "Receiver Email" | table "Sender Email" "Sender State" "Sender Mgr Firstname"  "Sender Mgr Lastname"  "Receiver Email" "Receiver State"
0 Karma

tristamaltizo
New Member

I'm getting the names now. Thanks @somesoni2 !

Wish there was a more simpler way as I'm wondering if there is a performance hit with these lookups, other than feeding the info directly into Splunk. But, for now, this will do. Thanks again!

0 Karma

somesoni2
Revered Legend

Yes, lookup command can be expensive if it has to be applied on lot of rows. But if you base search OR part of search before the lookup has proper filter and aggregation commands (such as stats etc), it reduces the number of rows to lookup and performance would be better.

0 Karma

tristamaltizo
New Member

Ok got it Thanks again @somesoni2

0 Karma

tristamaltizo
New Member

That appears to work! Thanks @somesoni2

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...