Search multiple fields from one lookup field

[nicholascurley]

I'm trying to format a search in which I have a lookup with one column, this column includes malicious email addresses, "indicator" is the field. Now I would like my search to return any events that either the "recipient" or "sender" fields match "indicator". I'm not sure how to write that query though without renaming my "indicator" field to one or the other. This is what I have so far

index=msexchange [inputlookup blocklist.csv |fields indicator |format]  indicator=*
|table _time, indicator, subject

I know indicator=* will not return any results because non of my events contain that field, at the moment of writing this though, I'm wondering if eval will work for this? If I find a solution I'll post my results..

Thanks in advance, let me know if you have any questions.

[maciep · Accepted solution]

I'd probably build out the logic in the subsearch and just return it. Maybe something like this, where you build a comma separated list of addresses from your lookup and then build the condition using the IN operator for your check and finally return the entire condition back to the main search.

index=msexchange [
   | inputlookup blocklist.csv
   | eval indicator = "\"" . indicator . "\""
   | stats values(indicator) as indicator
   | eval indicator = mvjoin(indicator,",")
   | eval condition = "sender IN (" . indicator . ") OR recipient IN (" .indicator . ")"
   | return $condition
]

I think you could also do this after the fact with the lookup command.

index=msexchange 
| lookup blocklist.csv indicator AS sender OUTPUT indicator as found_sender_indicator
| lookup blocklist.csv indicator AS recipient OUTPUT indicator as found_recipient_indicator
| where isnotnull(found_sender_indicator) OR isnotnull(found_recipient_indicator)

[nicholascurley]

The first search works like a charm! I do have one question, do you know of a way to also include the indicator field in the results? Something along where the below search would yield what I'm looking for. I.e. I would not only like to see the information about the email (sender/recipient/subject) but also what specifically flagged the alert (the indicator).
|table _time, indicator, sender, recipient, subject