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

[tristamaltizo]

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!

[somesoni2 · Accepted solution]

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"

[tristamaltizo]

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

[somesoni2]

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"

[tristamaltizo]

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!

[somesoni2]

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.

[tristamaltizo]

Ok got it Thanks again @somesoni2

[tristamaltizo]

That appears to work! Thanks @somesoni2