Network Math - ip and netmask convert to CIDR format.

[jrobiso2]

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.

[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

[SeanAdams]

got it - so I've attached a quick flow that does this for you.

Summary of how it works:

-Splits subnet mask into octets

- Converts each to binary

- brings them into a single long binary string

- Looks for the first 0 - and that's the CIDR number

- then appends to the IP.

You can use a similar method to get host ID; broadcast range; and address range (low & high)

If this works - would you mind marking this thread as solved - if not, just reply with any follow-on questions or challenges - and if possible add in some test data to a text-input in the flow in your reply.

[jrobiso2]

I'm on jury duty this week, but I'll give it a test Monday. Assuming everything works, we'll mark it as solved :-)

I am so grateful for you taking the time to work this out! THANK YOU!

[jrobiso2]

There is an error :-(

For example, the first entry in your test data: 102.2.15.2 netmask 255.0.0.0

The subnet CIDR should be 102.0.0.0/8, not 102.2.15.2/8.  I think what you've provided does correctly calculate the CIDR notation for that particular IP, but I'm hoping for the subnet's CIDR. 

I guess I should have stated more clearly that the intent is to determine the subnet's IP range in CIDR notation so that I can then compare that to a list of CIDR subnets to see if the PC is part of one of those subnets.  If you ask a large company for a list of their subnets, it'll come in a list of Base IP/mask (CIDR) format. So 102.2.15.2 netmask 255.0.0.0 becomes 102.0.0.0/8 because 102.0.0.0 is the base for the subnet. Then I'd take that result and see if it exists in the list of subnets.

An online resource to check the results is at http://www.subnet-calculator.com/cidr.php. I'm

[jrobiso2]

I found a location that gives a really good look at the binary handling:  http://www.xarg.org/tools/subnet-calculator/

Example:

IP Address: Subnet Mask: Wildcard Mask: Network Address: Broadcast Address: First Host: Last Host: Hosts per Net:

235.110.46.146	11101011.01101110.00101110.10010010
255.255.255.192	11111111.11111111.11111111.11000000
0.0.0.63	00000000.00000000.00000000.00111111
235.110.46.128/26	11101011.01101110.00101110.10000000 (Class D)
235.110.46.191	11101011.01101110.00101110.10111111
235.110.46.129	11101011.01101110.00101110.10000001
235.110.46.190	11101011.01101110.00101110.10111110
62

So I guess what I'm looking for is what is labelled above as the "Network Address" (where it says "Class D") or really, subnet address in CIDR.

[jrobiso2]

I found this at https://networkengineering.stackexchange.com/questions/7106/how-do-you-calculate-the-prefix-network-... ===============================================

Calculating the Network Address:

The network address is the logical AND of the respective bits in the binary representation of the IP address and network mask. Align the bits in both addresses, and perform a logical AND on each pair of the respective bits. Then convert the individual octets of the result back to decimal.

Logical AND truth table:

128.42.5.4      in binary: 10000000 00101010 00000101 00000100
255.255.248.0   in binary: 11111111 11111111 11111000 00000000
                           ----------------------------------- [Logical AND]
                           10000000 00101010 00000000 00000000 ------> 128.42.0.0

As you can see, the network address of 128.42.5.4/21 is 128.42.0.0

===========================================================================

So I've repeated what you did, but with the IP address, then merged the data back together. But at this point, I'm stuck on how to do the logical AND operation.

You've done the tough work of finding the CIDR mask value (in the above case, 21), so the task at hand is just the above logical AND operation to determine the base network address of the subnet.  Any ideas?

I know that if we can figure this out, many people will benefit from this work - you've basically created a base set of tools that can do most everything those programmatic and online calculators can do, if we can solve this last part.

[jrobiso2]

Did it!  Yay!!

A little search of the community for some Binary Math functionality and I found the Binary AND solution.

I don't know how to post pics (tried adding an image and 10 minutes later I'm still waiting for a tiny image to upload) - so solution is just attached.

Shall we put this in the Gallery, somehow, for anyone to use?

[jrobiso2]

Updated version - this one also calculates the Broadcast Address.

So, with IP and subnet mask input, this calculates the Network Address in CIDR format and the Broadcast Address.