We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

alteryx Community

# Alteryx Designer Discussions

## Convert IP Address to DWORD

5 - Atom

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

0 REPLIES 0
Labels