Skip to main content

You are here

How to sort or group on CIDR

I found myself having to detect user download accesses for a client with the user possibly using different IP's within the same classless subnet. The user's IP is recorded as part of the session, and the client then updates the netmask using CIDR notation. The IP is stored as varchar(15) and the netmask may be stored as int or char(2) with a default value of 32.

CREATE TABLE logtable (
.... ,
ip varchar(15) default "0.0.0.0",
netmask int default 32,
.... )

MySQL has two functions, INET_ATON and INET_NTOA which convert dotted quad notation to an integer and back. It is easy to sort by IP using INET_ATON(ip). However grouping by subnet requires an understanding of the bitwise operators in MySQL.

When two numbers are compared using the MySQL bitwise operators, the numbers and the result are cast to bigint (64 bits). To create a bit mask for the network address, which is 32 bits, we must first shift the mask the required number of bits, then we have to mask the lower 32 bits to create the bit mask for the subnet:

Thus the SQL becomes:

SELECT ip,
INET_NTOA((INET_ATON("255.255.255.255") << (32 - netmask))
& INET_ATON("255.255.255.255")) AS bitmask,
INET_NTOA(INET_ATON(ip)
& ((INET_ATON("255.255.255.255") << (32 - netmask))
& INET_ATON("255.255.255.255"))) AS subnet
FROM logtable;

For an IP of 123.123.123.15 and a netmask of 24, this will give:

+----------------+---------------+---------------+
| ip | bitmask | subnet |
+----------------+---------------+---------------+
| 123.123.123.15 | 255.255.255.0 | 123.123.123.0 |
+----------------+---------------+---------------+

The SQL might be a little more readable, and a tad more efficient, if we substitute the actual integer:

INET_ATON("255.255.255.255") = 4294967295

SELECT ip,
INET_NTOA((4294967295 << (32 - netmask))
& 4294967295) AS bitmask,
INET_NTOA(INET_ATON(ip)
& ((4294967295 << (32 - netmask))
& 4294967295)) AS subnet
FROM logtable;

Link to this page

To refer others to this page copy the following link code and paste it into your page, blog, text or email.

Premium Drupal Themes by Adaptivethemes