Splunk Search

How do you Join two searches based on lookup values?

Explorer

Hi folks,

I have 2 searches that return equivalent values based on the result of a lookup, as such:

Search 1

index=alpha tag=email SenderAddress=*@domain.com 
| table *Email_Address*, Name 

Search 2

index=alpha sourcetype="activities"
| spath
| search "tags{}.category_id"=USBTFR
|rex field=User_Name "(?\w{3,6})" 
|eval User_Name=upper(User_Name) 
|lookup tablename LogonID as User_Name OUTPUT *EmailAddress*

How can I join the result of the two searches based on the EmailAddress field?

0 Karma
1 Solution

Esteemed Legend

Like this:

index=alpha ((tag=email SenderAddress=*@domain.com) OR (index=alpha sourcetype="activities"))
| spath
| search "tags{}.category_id"=USBTFR OR (tag=email SenderAddress=*@domain.com)
| rex field=User_Name "(?\w{3,6})" 
| eval User_Name=upper(User_Name) 
| lookup tablename LogonID AS User_Name OUTPUT EmailAddressOther
| eval EmailAddress=coalesce(EmailAddress, EmailAddressOther)
| fields - EmailAddressOther And Perhaps Other Fields Too
| stats values(*) AS * BY EmailAddress

View solution in original post

0 Karma

Esteemed Legend

Like this:

index=alpha ((tag=email SenderAddress=*@domain.com) OR (index=alpha sourcetype="activities"))
| spath
| search "tags{}.category_id"=USBTFR OR (tag=email SenderAddress=*@domain.com)
| rex field=User_Name "(?\w{3,6})" 
| eval User_Name=upper(User_Name) 
| lookup tablename LogonID AS User_Name OUTPUT EmailAddressOther
| eval EmailAddress=coalesce(EmailAddress, EmailAddressOther)
| fields - EmailAddressOther And Perhaps Other Fields Too
| stats values(*) AS * BY EmailAddress

View solution in original post

0 Karma

Explorer

Thanks for the prompt response folks, however I'm looking to return calculated values from both indexes. I'll create another question to include it.

0 Karma

Communicator

Does using |join EmailAddress not provide the search you want?

0 Karma