Join (Inner or left) or Eval to join events from same source via common field

[kelie]

logs from an email server throws multiple events (each a different detail) for one email and each event has a numerical value field (MID). Each email has a unique MID

I need to extract the recipient from the events and subject containing "Password Expiring Soon" where the MID matches in each event

im trying this to get the data but its terribly slow and missing and not tabling correctly

index=email MID=* | join type=left MID [search index=email subject="Password Expiring Soon"]|join type=left MID [search index=email recipient=*]

Please assist

[3no · Accepted solution]

In large dataset or high time range, I think this method is the fastest.

First, I would recommand to find every MID where the Subject is "Password Expiring Soon" (in case of spam campaign you can have multiple). Then you'll want to find the sender and recipient for each of these MID.

From your description it should look something like this :

index=email 
[ search index = email subject="Password Expiring Soon" | table MID | format "(" "(" "" ")" "OR" ")" ] 
| stats values(subject) AS Subject, values(sender) AS sender, values(recipient) AS recipient BY MID

3no

[kelie]

heres some sample searches. stats sounds like the right path to take. The only consistent and common field is MID

[rmmiller]

If I understand your data and intent correctly, this should do the trick:

index=email MID=
| stats values(subject) AS Subject, values(recipient) AS recipient BY MID
| where NOT isnull(mvfind(Subject,"Password Expiring Soon"))

Joins are painfully inefficient and can usually be avoided using stats and filtering after aggregation. This is definitely one of those instances.

Hope that helps!
rmmiller