topic Join a search by field across indexes in Splunk Search

Join a search by field across indexes

aarichow — Thu, 25 Jul 2019 21:05:11 GMT

I have two different indexes, with the common field being username.

One index that contains phishing history data. index="phish"
One index that contains a list of usernames. index="poorpass"

I'd like to join both indexes, and match them by username. So I'll have a list of users that are in both indexes.

index="phish" status="Clicked Link" 
| eval username=email 
| rex field=username mode=sed "s/@\S+//g" 
| join host 
    [search index="poorpass" group="username"]
| table username

My search goes through with no errors, but no events are returned.

Re: Join a search by field across indexes

aarichow — Thu, 25 Jul 2019 21:07:55 GMT

To clarify, the reason I'm creating the field called username is because it's not an existing field in my index. There is a field in the index, phish, called email, which is the same as username in this case, so I just strip the email at the @ sign, giving me the username.

I'm then trying to match this field to a existing field in the index poorpass.

Re: Join a search by field across indexes

MuS — Thu, 25 Jul 2019 22:15:35 GMT

HI aarichow,

give this a try:

( index="phish" status="Clicked Link" ) OR ( index="poorpass" group="username" )
| rex field=email mode=sed "s/@\S+//g" 
| eval username=case(isnotnull(email), email, isnotnull(username), username, 1=1, "unknown")
| stats values(*) AS * by username, _time

The eval will use either email, username, or if neither is available fill the value as unknown

Hope this helps ...

cheers, MuS

Re: Join a search by field across indexes

aarichow — Fri, 26 Jul 2019 13:35:25 GMT

I ran the search, this just returns all the users that have the status="clicked link", it doesn't match it to the field in poorpass.

Re: Join a search by field across indexes

MuS — Mon, 29 Jul 2019 02:02:13 GMT

Can you please provide at least two anonymised events for each criteria? Thanks

cheers, MuS

Re: Join a search by field across indexes

woodcock — Mon, 29 Jul 2019 06:19:27 GMT

Like this:

(index="phish" status="Clicked Link") OR (index="poorpass" group="username")
| eval username=if(index="phish", email, null())
| rex field=username mode=sed "s/@\S+//g" 
| stats values(*) AS * BY host