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

Alteryx Designer Discussions

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

Convert IP Address to DWORD

Jasey_DePriest
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 AddressBinary 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 AddressBinary NotationInteger Notation
127.0.0.1011111110000000000000000000000012130706433
10.10.23.700001010000010100001011100000111168433415
172.12.68.12510101100000011000100010001111101-1408482179
192.168.85.7511000000101010000101010101001011-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 AddressBinary NotationInteger NotationDWORD Notation
127.0.0.10111111100000000000000000000000121307064332130706433
10.10.23.700001010000010100001011100000111168433415168433415
172.12.68.12510101100000011000100010001111101-14084821792886485117
192.168.85.7511000000101010000101010101001011-10627099413232257355

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 AddressDWORD Notation
10.10.0.0168427520
10.10.255.255168493055
10.10.23.7168433415
10.11.87.105168515433

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

Let's label them.

LabelIP AddressDWORD Notation
RangeStart10.10.0.0168427520
RangeEnd10.10.255.255168493055
Test110.10.23.7168433415
Test210.11.87.105168515433

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