Subnet calculation using pure mysql

You can easily aggregate your records by subnets using mysql thanks to bitwise operators, an inet_aton (ascii to number function) and some thinking...

Here you go:

	-- SQLfiddle: http://www.sqlfiddle.com/#!2/e88de/2
	-- Create a table and add some records
	create table somelogs
	(
	some_ip varchar(25),
	log varchar(50)
	);
	insert into somelogs
	values
	("10.0.0.1", "class A"),
	("10.0.0.2", "Same /24 as the previous record"),
	("192.168.122.9", "Class C"),
	("127.0.0.1", "Home");
	-- Run a query that does subnet calculation
	select distinct
	some_ip,
	-- Subnet at /24 (32 - 8)
	-- Basically, we are converting the IP into a number (32 bit)
	-- Then the cast/pow business generates the subnet mask
	-- Then we `and` the two to generate the network id :)
	inet_ntoa(inet_aton(some_ip) & cast((pow(2, 32) - pow(2, 8)) as UNSIGNED)) as subnet,
	log
	from
	somelogs;
	-- Profit!
	-- SOME_IP SUBNET LOG
	-- 10.0.0.1 10.0.0.0 class A
	-- 10.0.0.2 10.0.0.0 Same /24 as the previous record
	-- 192.168.122.9 192.168.122.0 Class C
	-- 127.0.0.1 127.0.0.0 Home

view raw subnet_calc_mysql.sql hosted with ❤ by GitHub