Join a search by field across indexes

[aarichow]

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.

[woodcock]

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

[MuS · Accepted solution]

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

[aarichow]

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.

[MuS]

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

cheers, MuS

[aarichow]

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.