cidrmatch between 2 csv files

[munisb]

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

[bowesmana · Accepted solution]

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]

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.

[bowesmana]

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]

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)