Convert IP Address to DWORD

[Jasey_DePriest]

I work with IP addresses every day and Alteryx doesn't have built-in functions to handle IP addresses. However, it has every function you can imagine to work with Integers.

The Marquee Crew gave us this lifesaver a couple of years ago: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Use-RegEx-and-build-a-Macro-to-Vali...

This macro lets you validate than an IP address is a valid IP, whether it is a public or private IP address, and can also sort them.

I had a use case that fell outside of this.

I needed to determine if an IP address was part of a range and, if it was, create two new ranges on either side of it.

For example:

• Given an IP range: 10.10.0.0 to 10.10.255.255
• Is this IP is in the range: 10.10.23.7

The dotted decimal notation we use for IP addresses is just a user-friendly way of representing a 32-bit binary number.

Using the IntToBin() function and some string concatenation, you can convert an IP to its binary equivalent. You have to PadLeft with 0 to make sure each section is a full 8 bits.

IP Address	Binary Notation
10.10.23.7	00001010000010100001011100000111

padleft(toString(intToBin(toNumber([_CurrentField_]))),8,'0')

The you just concatenate them all together to get a 32-bit binary number.

PadLeft(([Octet 1 Binary] + [Octet 2 Binary] + [Octet 3 Binary] + [Octet 4 Binary]),32,'0')

Easy.

The next step is using BinToInt() to convert the binary to an integer.

IP Address	Binary Notation	Integer Notation
127.0.0.1	01111111000000000000000000000001	2130706433
10.10.23.7	00001010000010100001011100000111	168433415
172.12.68.125	10101100000011000100010001111101	-1408482179
192.168.85.75	11000000101010000101010101001011	-1062709941

Wait a second. Why do we have negative Integers?

Standard Binary math uses a leading 1 as the indicator for negative numbers. I can't just add a leading 0 because then it wouldn't be a 32-bit Integer, it'd be a 33-bit Integer.

A 32-bit integer with a +/- is just an integer, but an unsigned, 32-bit integer is called a DWORD.

The fix is to add 2^32 (4,294,967,296) to the negative Integer which gives this chart.

IP Address	Binary Notation	Integer Notation	DWORD Notation
127.0.0.1	01111111000000000000000000000001	2130706433	2130706433
10.10.23.7	00001010000010100001011100000111	168433415	168433415
172.12.68.125	10101100000011000100010001111101	-1408482179	2886485117
192.168.85.75	11000000101010000101010101001011	-1062709941	3232257355

Okay. Now what?

Let's use the IP's I mentioned at the beginning of this post and throw in 10.11.87.105 for good measure.

IP Address	DWORD Notation
10.10.0.0	168427520
10.10.255.255	168493055
10.10.23.7	168433415
10.11.87.105	168515433

The first two are the range and the second two are the test candidates.

Let's label them.

Label	IP Address	DWORD Notation
RangeStart	10.10.0.0	168427520
RangeEnd	10.10.255.255	168493055
Test1	10.10.23.7	168433415
Test2	10.11.87.105	168515433

Now you can have a Boolean called IsInRange and have

IF [Test#] >= RangeStart and [Test#] <= RangeEnd THEN 1 ELSE 0 ENDIF

Test1 would be TRUE and Test2 would be FALSE.

I have another Macro to convert Integer back to IP address dotted decimal notation.

The actual work I do with these IPs as Integers is too complex to include here and still a work in progress. I am trying to use these number to build new ranges that exclude specific IP addresses or ranges of IP addresses. I can easily add or subtract 1 from an Integer and convert that back to IP address using these macros.

* How to determine if an IP address is valid - https://www.oreilly.com/library/view/regular-expressions-cookbook/9780596802837/ch07s16.html