topic Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file? in Splunk Search

How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

sai21 — Tue, 29 Sep 2020 14:03:26 GMT

| set union [search index=*_place_holder sourcetype=placeholder | fields src_ip | where src_ip!="N/A"| rename src_ip as c_ip |fields c_ip ] [searchindex=*_place_holder sourcetype=placeholder | fields dest_ip |where dest_ip!="N/A"|rename dest_ip as c_ip|fields c_ip ] | table c_ip |dedup c_ip|lookup lookupfile.csv SUBNET as c_ip OUTPUT SUBNET_NAME

That's the search I have so far. It does combine the two fields src_ip and dest_ip in to c_ip successfully. But now I need to find a way to compare c_ip with the SUBNET field in lookupfile.csv. Matching the first 3 octets would be the best way for me, but i'm not sure how to go about this. Also, how would I modify the search so the results show Matches and Non-Matches a two separate fields?

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

somesoni2 — Fri, 12 May 2017 16:48:30 GMT

You would need to setup a lookup definition (needs conf file access) on your search head to enable CIDR match. Have a look at this post for detailed instructions:-

https://answers.splunk.com/answers/5916/using-cidr-in-a-lookup-table.html

You can skip the step of automatic lookup.

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

sai21 — Mon, 15 May 2017 16:19:55 GMT

the SUBNET field in my lookup file doesn't have the subnet mask next to the next ip address like it is shown in that example. Can I still use CIDR match?

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

somesoni2 — Mon, 15 May 2017 16:21:47 GMT

Can we have some sample values from IP address in your events and subnet values from your lookup?

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

sai21 — Mon, 15 May 2017 17:11:33 GMT

c_ip example: 198.241.211.29
SUBNET value example: 198.241.211.0

another pair:

c_ip: 10.121.3.248

SUBNET: 10.121.3.0

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

sai21 — Wed, 17 May 2017 14:01:30 GMT

Any ideas? I put some samples of how the data looks

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

DalJeanis — Wed, 17 May 2017 16:05:46 GMT

simple enough - if you only want to match the first 3 octets, then create your lookup table with only the first three octets as the key, and only use the first three octets to look it up.

table  mylookup.csv

subnet_key  result_ip
10.121.3      10.121.3.0

incoming c_ip: 10.121.3.248 

| rex field=c_ip "^(?<subnet_key1>\d+\.\d+\.\d+)\.\d*"
| lookup mylookup.csv subnet_key as subnet_key1 OUTPUT  result_ip as result_ip1

Run-anywhere example. First run this...

 | makeresults 
 | eval subnet_key="10.121.3" 
 | eval result_ip="10.121.3.0" 
 | table subnet_key result_ip
 | outputlookup mylookup.csv

... with this result table...

subnet_key   result_ip 
10.121.3     10.121.3.0

...then run this...

| makeresults 
| eval c_ip="10.121.3.248" 
| rex field=c_ip "^(?<subnet_key1>\d+\.\d+\.\d+)\.\d*"
| lookup mylookup.csv subnet_key as subnet_key1 OUTPUT  result_ip as result_ip1
| table c_ip subnet_key1 result_ip1

... with this result output...

c_ip          subnet_key1  result_ip1 
10.121.3.248  10.121.3     10.121.3.0

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

sai21 — Wed, 17 May 2017 18:34:34 GMT

The problem is, I can't modify the lookup file SUBNET field to only have the first 3 octets. Can i use a rex field to get the first 3 octets of SUBNET to use as a new field? If so, where would this be in the query? After the lookup command?

| set union [search index=*_place_holder sourcetype=placeholder | fields src_ip | where src_ip!="N/A"| rename src_ip as c_ip |fields c_ip ] [search index=*_place_holder sourcetype=placeholder | fields dest_ip |where dest_ip!="N/A"|rename dest_ip as c_ip|fields c_ip ] | table c_ip |dedup c_ip|rex field=c_ip "^(?<subnet_key1>\d+\.\d+\.\d+)\.\d*"|lookup lookupfile.csv SUBNET as subnet_key1 OUTPUT  result_ip as result_ip1

that gives me this output:

c_ip                          result_ip     subnet_key1
10.43.117.129                               10.43.117

Re: How to edit my search to match an IP from Data to a Subnet IP in a lookup file?

somesoni2 — Wed, 17 May 2017 18:39:14 GMT

This can be a workaround, but won't be efficient cause usage of join.

| set union [search index=*_place_holder sourcetype=placeholder | fields src_ip | where src_ip!="N/A"| rename src_ip as c_ip |fields c_ip ] [searchindex=*_place_holder sourcetype=placeholder | fields dest_ip |where dest_ip!="N/A"|rename dest_ip as c_ip|fields c_ip ] | table c_ip |dedup c_ip|join c_ip [|inputlookup lookupfile.csv | eval c_ip=replace(SUBNET,"(\d+\.\d+\.\d+)\.\d+","\1") | table c_ip SUBNET_NAME ]