topic Re: How do you Join two searches based on lookup values? in Splunk Search

How do you Join two searches based on lookup values?

ADRIANODL — Tue, 19 Feb 2019 04:29:24 GMT

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?

Re: How do you Join two searches based on lookup values?

bangalorep — Tue, 19 Feb 2019 04:42:24 GMT

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

Re: How do you Join two searches based on lookup values?

woodcock — Tue, 19 Feb 2019 04:45:02 GMT

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

Re: How do you Join two searches based on lookup values?

ADRIANODL — Tue, 19 Feb 2019 05:30:48 GMT

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.