topic Lookup one column in csv against multiple extracted fields in Splunk Search

Lookup one column in csv against multiple extracted fields

spj2 — Mon, 28 Sep 2020 15:17:05 GMT

I have a csv file with a blacklist of domain names and IP's.
ip,domain
1.1.1.1,foo.com
2.2.2.2,bar.com

I am trying to perform a lookup for the ip in csv against the Splunk events. In the events, there are 2 extracted fields src_ip and dest_ip that I want to check against the ip in csv. If a match is found in any of these fields, I would like to set up an alert.

If I perform the following search which is looking up 1 field src_ip, it works.
sourcetype=traffic_logs | dedup src_ip
| lookup ipLookup ip as src_ip OUTPUT ip as match_found
| where match_found!="unmatched"

But for multiple fields, src_ip and dest_ip, it does not. Here is what I tried:
sourcetype=traffic_logs | dedup src_ip, dest_ip
| lookup ipLookup ip as src_ip, ip as dest_ip OUTPUT ip as match_found
| where match_found!="unmatched"

Also tried the following without success:

In the same search I would also like to look for any string that matches the value in column "domain" from the csv.

Appreciate any help!

Re: Lookup one column in csv against multiple extracted fields

somesoni2 — Mon, 28 Sep 2020 15:17:19 GMT

Below search should help you lookup your src_ip and dest_ip in the ipLookup csv file and if anyone matches, you'll have events returned from this search so you can setup alert.

sourcetype=traffic_logs | eval joinfield=1|join max=0 joinfield [|inputlookup ipLookup | fields domain | eval joinfield=1] | where LIKE(_raw,"%".domain."%")| dedup src_ip,dest_ip | lookup ipLookup ip as src_ip OUTPUT domain as src_found | lookup ipLookup ip as dest_ip OUTPUT domain as dest_found | eval shouldAlert=case(isnotnull(src_found) OR isnotnull(dest_found),"Yes",1=1,"No") | where shouldAlert="Yes"

Update

Try following

Explaination:
First combine both IP into one field. Then cross join with lookup row [will give you Count of event * count of lookup value rows]. Then search for events where the ip from lookup file is contained in combined ip field. If any match is found, you can set your alert on that.

Re: Lookup one column in csv against multiple extracted fields

spj2 — Fri, 15 Nov 2013 14:48:12 GMT

Appreciate your response. But this did not work. At this time finding a matching IP is more important than matching the domain. I also tried without using the domain portion of the search, but did not return anything. Since I am using a test csv, I know I have common IP's in both the csv and the logs.

Re: Lookup one column in csv against multiple extracted fields

somesoni2 — Fri, 15 Nov 2013 20:53:33 GMT

try the new option which I updated above.