Splunk Search

Lookup one column in csv against multiple extracted fields

spj2
New Member

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:

sourcetype=traffic_logs | dedup src_ip, dest_ip
| lookup ipLookup ip as src_ip OUTPUT ip as src_found
| where src_found!="unmatched"
| lookup ipLookup ip as dest_ip OUTPUT ip as dest_found
| where dest_found!="unmatched"

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!

Tags (2)
0 Karma

somesoni2
Revered Legend

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

sourcetype=traffic_logs |dedup src_ip,dest_ip| eval allIp=src_ip."#".dest_ip| eval joinfield=1|join max=0 joinfield [|inputlookup ipLookup | eval joinfield=1] | where LIKE(allIp,"%".ip."%")

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.

0 Karma

somesoni2
Revered Legend

try the new option which I updated above.

spj2
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Splunk Classroom Chronicles: Training Tales and Testimonials (Episode 3)

Welcome back to Splunk Classroom Chronicles, our ongoing blog series that pulls back the curtain on Splunk ...

Operationalizing TDIR: Building a More Resilient, Scalable SOC

Optimizing SOC workflows with a unified, risk-based approach to Threat Detection, Investigation, and Response ...

Almost Too Eventful Assurance: Part 1

Modern IT and Network teams still struggle with too many alerts and isolating issues before they are notified. ...