Network Math - ip and netmask convert to CIDR format.

I have data which has a column for the IP address and one for the Netmask. So, for example, 192.168.2.25 and 255.255.255.0.

I need to calculate the CIDR format of that (in this case it would be 192.168.2.0/32).

I have been presented a list of "isolated subnets" in CIDR format, so I need to create a new column with the CIDR format value and from there I can do comparisons against the isolated subnet list to see if the client is in one of those subnets.

Obviously the netmask is not always going to be 255.255.255.0. We own multiple class A networks, so as you can guess there is a lot of data and a lot of partial subnet (anywhere from 6 clients to 254x254 clients per subnet).

Any help would be greatly appreciated.

Help

Accepted answers

SeanAdams

:-) I'm very stoked that you were able to take the beginning piece and take it across the finish line. Nicely done!

As you say, this is very useful as a gallery tool - well worth posting. The other person who may be interested in this one is @JoeM - Joe puts together the weekly challenges every week, and this really is an interesting challenge because it forces you to engage with binary manipulation functions.

Thanks for posting your solution, nicely done!

Sean

jrobiso2

Found it. A type in the SecondOctet BinaryAnd calculation. I had the AND function doing the 2nd of the IP AND the 3rd of the subnet instead of the 2nd of the subnet.

Fix attached.

CIDR Address Calc.yxmd

All comments

SeanAdams

Hey @jrobiso2

This sounds like a great challenge and I'm dead keen to help - unfortunately I don't know much about the translation from an external IP + subnet mask to a CIDR format.

if you're able to mock up a few working input & outputs (in a text input in an Alteryx flow like @JoeM does with the weekly challenges with the basic logic of CIDR translation) and attach it to this thread, I'd love to tackle this with you and see if we can get to a solution

Good news is that we have a whole weekend ahead to tackle this :-)

Looking forward to seeing your reply

Sean

jrobiso2

This is pretty much my working reference for this:

https://www.youtube.com/watch?v=PnApdHjU8sg

jrobiso2

Added another section that can go from CIDR format netmask to long form.

Question for all: As part of a "does this IP address go in this subnet", there's a section where I have to take the base IP and the netmask, both in digital form, and BinaryAND them together. Currently, the BinaryAND really only works on 1 and 0, not entire 32 character long binary strings (or even 8 character, for that matter).

Can anyone think of an easy way to cycle through each character (in both values) and do a BinaryAND on each, and then join, so the final output is a new 32 character binary?

CIDR Address Calc.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335