topic Re: Multiple lookups, with an OR in Splunk Search

Multiple lookups, with an OR

stakor — Tue, 29 Sep 2020 13:38:53 GMT

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?

Re: Multiple lookups, with an OR

DalJeanis — Wed, 12 Apr 2017 17:01:11 GMT

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"

Re: Multiple lookups, with an OR

stakor — Thu, 13 Apr 2017 19:20:32 GMT

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.