Splunk Search

cidrmatch between 2 csv files

munisb
Explorer

Hi,

I have two csv files where I am trying to cidrmatch between ip and subnet - but it doesn't appear to be working

test1.csv

 

 

 

host	ip
abc	192.168.1.1
def	192.168.2.2
xyz	192.168.3.3

 

 

 

 

test2.csv

 

 

 

zone	  subnet
dmz	        192.168.1.1
internet	192.168.2.0/24
management	192.168.1.0/24

 

 

 

 

SPL (returns blank)

 

 

 

| inputlookup test1.csv | lookup test2.csv subnet | where cidrmatch("192.168.1.0/24", ip) | table host ip subnet zone

 

 

 

 

whereas if I run the following - I get a match (the zone & subnet fields are blank)

 

 

| inputlookup test1.csv | lookup test2.csv subnet | where cidrmatch("192.168.1.0/24", ip) | table host ip subnet zone

 

 

 

will appreciate some guidance. Thank you

Labels (1)
Tags (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

OK...

Your first part of the search

| inputlookup test1.csv | lookup test2.csv subnet

You test1.csv has no field called subnet, so the lookup test2.csv will never match anything as the subnet field is always null. The field passed to lookup is the one in the data you - so the syntax would be 'subnet as ip'

If you run this

| inputlookup test1.csv 
| lookup test2.csv subnet as ip output subnet zone

you will see that ONLY abc will return the dmz zone - that is because a CSV file lookup requires an exact match on the data being searched, hence ip 192.168.1.1 will match the subnet of the same value.

If you want to use a CIDR match in the lookup, you can set up a lookup definition (call it test2) that sits on top of the test2.csv and you can use the advanced options to set the match type to CIDR(subnet).

Then if you run this

| inputlookup test1.csv 
| lookup test2 subnet as ip output subnet zone

i.e. using the lookup definition test2 rather than the file test2.csv, you will see it returns management for abc and internet for def. It will not return dmz for abc, as the subnet is not CIDR qualified.

If you then edit your test2.csv and change the subnet for the dmz zone to be 192.168.1.1/32

then the above search will return both dmz and management for abc as that IP is in both CIDR ranges.

You can always then filter down the smallest CIDR range to get the closest CIDR match for the combination of zone/subnet needed from the multi-valued results you get back.

 

 

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

OK...

Your first part of the search

| inputlookup test1.csv | lookup test2.csv subnet

You test1.csv has no field called subnet, so the lookup test2.csv will never match anything as the subnet field is always null. The field passed to lookup is the one in the data you - so the syntax would be 'subnet as ip'

If you run this

| inputlookup test1.csv 
| lookup test2.csv subnet as ip output subnet zone

you will see that ONLY abc will return the dmz zone - that is because a CSV file lookup requires an exact match on the data being searched, hence ip 192.168.1.1 will match the subnet of the same value.

If you want to use a CIDR match in the lookup, you can set up a lookup definition (call it test2) that sits on top of the test2.csv and you can use the advanced options to set the match type to CIDR(subnet).

Then if you run this

| inputlookup test1.csv 
| lookup test2 subnet as ip output subnet zone

i.e. using the lookup definition test2 rather than the file test2.csv, you will see it returns management for abc and internet for def. It will not return dmz for abc, as the subnet is not CIDR qualified.

If you then edit your test2.csv and change the subnet for the dmz zone to be 192.168.1.1/32

then the above search will return both dmz and management for abc as that IP is in both CIDR ranges.

You can always then filter down the smallest CIDR range to get the closest CIDR match for the combination of zone/subnet needed from the multi-valued results you get back.

 

 

 

munisb
Explorer

That did the trick Thank you.

One follow up question

in my test1.csv some of the cells have multiple IP addresses if a host has multiple NICs. Example:

1.1.1.1, 2.2.2.2
3.3.3.3, 4.4.4.4, 5.5.5.5

I tried with "*IP*" and *IP* - however that doesn't return any results for zone.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You can also make a field a wldcard field through the lookup definition, e.g. match type WILDCARD(subnet)

but not sure if you can do both wildcard and cidr for the same field, but in that case, you have to have the * characters in the lookup data.

The simplest way to do it would be to have one ip per host, so there are multiple rows for those hosts in your example.

 

munisb
Explorer

sounds like a plan - Thx. Will give the wildcard suggestion a try. Otherwise, those who stumble upon this post can also try Excel Power Query to split a row into multiple rows

Excel: Split Delimited Data into New Rows - Strategic Finance (sfmagazine.com)

Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...