Splunk Search
Highlighted

Lookup one column in csv against multiple extracted fields

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 srcip and destip 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 srcip, it works.
sourcetype=traffic
logs | dedup srcip
| lookup ipLookup ip as src
ip OUTPUT ip as matchfound
| where match
found!="unmatched"

But for multiple fields, srcip and destip, it does not. Here is what I tried:
sourcetype=trafficlogs | dedup srcip, destip
| lookup ipLookup ip as src
ip, ip as destip OUTPUT ip as matchfound
| where match_found!="unmatched"

Also tried the following without success:

sourcetype=trafficlogs | dedup srcip, destip
| lookup ipLookup ip as src
ip OUTPUT ip as srcfound
| where src
found!="unmatched"
| lookup ipLookup ip as destip OUTPUT ip as destfound
| 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
Highlighted

Re: Lookup one column in csv against multiple extracted fields

SplunkTrust
SplunkTrust

Below search should help you lookup your srcip and destip 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=trafficlogs |dedup srcip,destip| eval allIp=srcip."#".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
Highlighted

Re: Lookup one column in csv against multiple extracted fields

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
Highlighted

Re: Lookup one column in csv against multiple extracted fields

SplunkTrust
SplunkTrust

try the new option which I updated above.