Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Network Math - ip and netmask convert to CIDR format.

jrobiso2
8 - Asteroid

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.

16 REPLIES 16
SeanAdams
17 - Castor
17 - Castor

:-) 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
8 - Asteroid

Unfortunately, there is a problem.

 

A test value of IP: 136.12.24.15, subnet mask of 255.255.0.0 should result in a CIDR of 136.12.0.0/16.  Instead I get 136.0.0.0/16.  The 12 is totally missing.

 

Argggghhhhhhh!!!!

jrobiso2
8 - Asteroid

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.

chvizda
8 - Asteroid

Cool Workflow.

 

I'm looking for something in the other way I have the CIDR format and wnat to have all the IP-subnets calculated.

 

Any idea how to solve this

 

Thx Steffen

jrobiso2
8 - Asteroid

I can only assume that the macro/workflow could be worked backwards to do what you want.

jrobiso2
8 - Asteroid

How does one go about posting this to the Gallery?

jrobiso2
8 - Asteroid

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?

Labels