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

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

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!

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