Splunk Search

Search multiple fields from one lookup field

nicholascurley
Engager

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.

0 Karma
1 Solution

maciep
Champion

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)

View solution in original post

maciep
Champion

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
Engager

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...