| 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?
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
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
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.
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?
Can we have some sample values from IP address in your events and subnet values from your lookup?
Any ideas? I put some samples of how the data looks
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 ]
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