Splunk Search

Multiple lookups, with an OR

stakor
Path Finder

I am looking to use lookups in an OR for a search. Roughly what I want to do is:

<search>
((if IP_From_BAD_IP matches destination_IP) OR (if IP_From_BAD_IP matches source_IP))

I am extracting the IPs as below:

<main_search>
[|inputlookup BAD_IP.csv|table ip_address | rename ipaddress as destination_ip]

Clearly, doing:

<main_search>
[|inputlookup BAD_IP.csv|table ip_address | rename ipaddress as destination_ip]
[|inputlookup BAD_IP.csv|table ip_address | rename ipaddress as source_ip]

Will not work, as there is an implied AND. Not sure how to extract the lists of IPs to match the source_IP and destination_IP, with an OR. Anyone have any guidance?

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Here's one way...

<main search giving source_ip and destination_ip>
| join type=left destination_ip [| inputlookup BAD_IP.csv| table ip_address | rename ip_address as ip_address1 | eval destination_ip = ip_address1]
| join type=left source_ip [| inputlookup BAD_IP.csv| table ip_address | rename ip_address as ip_address2 | eval source_ip = ip_address2]
| where isnotnull(ip_address1) OR isnotnull(ip_address2) 

... here's another...

<main search giving source_ip and destination_ip>
| search 
    [|inputlookup BAD_IP.csv 
    | eval destination_ip=ip_address 
    | eval source_ip = ip_address 
    | table source_ip destination_ip
    | format "(" "(" OR ")" OR ")" 
    ]

The latter method should only be used when the csv is pretty small, since the section of code in square brackets [...] expands to

( 
  ( destination_ip="001.001.001.001" OR source_ip="001.001.001.001" ) OR 
  ( destination_ip="002.002.002.002" OR source_ip="002.002.002.002" ) OR 
   ...
 )

...and a third method, probably more efficient than the above two, but beware the record limits on append...

<main search giving source_ip and destination_ip>
| eval ip_address = mvappend(source_ip, destination_ip)
| eval IsDetail="Yes"
| append 
    [|inputlookup BAD_IP.csv 
     | eval IsBadIP = "Yes"
     | table ip_address IsBadIP
     ]
| eventstats max(IsBadIP) as IsBadIP by ip_address
| where IsBadIP=="Yes" AND IsDetail=="Yes"

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Here's one way...

<main search giving source_ip and destination_ip>
| join type=left destination_ip [| inputlookup BAD_IP.csv| table ip_address | rename ip_address as ip_address1 | eval destination_ip = ip_address1]
| join type=left source_ip [| inputlookup BAD_IP.csv| table ip_address | rename ip_address as ip_address2 | eval source_ip = ip_address2]
| where isnotnull(ip_address1) OR isnotnull(ip_address2) 

... here's another...

<main search giving source_ip and destination_ip>
| search 
    [|inputlookup BAD_IP.csv 
    | eval destination_ip=ip_address 
    | eval source_ip = ip_address 
    | table source_ip destination_ip
    | format "(" "(" OR ")" OR ")" 
    ]

The latter method should only be used when the csv is pretty small, since the section of code in square brackets [...] expands to

( 
  ( destination_ip="001.001.001.001" OR source_ip="001.001.001.001" ) OR 
  ( destination_ip="002.002.002.002" OR source_ip="002.002.002.002" ) OR 
   ...
 )

...and a third method, probably more efficient than the above two, but beware the record limits on append...

<main search giving source_ip and destination_ip>
| eval ip_address = mvappend(source_ip, destination_ip)
| eval IsDetail="Yes"
| append 
    [|inputlookup BAD_IP.csv 
     | eval IsBadIP = "Yes"
     | table ip_address IsBadIP
     ]
| eventstats max(IsBadIP) as IsBadIP by ip_address
| where IsBadIP=="Yes" AND IsDetail=="Yes"
0 Karma

stakor
Path Finder

Thank you. Let me give this a go, and I will respond on the thread. For whatever reason, I did not see this answer. Sorry about that.

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 ...