Garden Of Blog

SQL geeks only…

So, you have an IP address.

You also have a MySQL table of subnets, defined by columns called “ip_range_start” and “ip_range_end”.

You want to find which subnet the IP address falls within.

Perl gives you an easy way to split (using the appropriately named builtin “split”) the IP adrress into its 4 octets:

my @octets = split(/./, $ip);
my $octet3 = $octets[2];
my $octet4 = $octets[3];

(As UNH is on the 132.177* IP block, I only care about octets 3 and 4).

That’s all fine and dandy, but how the heck do you deal with those octets in SQL? No “split” to use. This is what I ended up with:

SELECT arpwatch, snmp
FROM machine_tracker.subnet_snapshots
WHERE
(
	(
		/* match the 3rd octet of the ip to the ip range */
		SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
		BETWEEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
		AND
		SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', 1) +0
	)
	AND
	(
		IF ( /* if this ip range is all on the 3rd octet, this is easy */
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
			=
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', 1) +0
		,    /* easy 4th octet match  */
			SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
			BETWEEN
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', -1) +0
			AND
			SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', -1) +0
		,  /* if this subnet spans 3rd octet values, mpt so easy */
			IF (  /* if we are at start or end of range, handling is tricky */
				SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
				=
				SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
				OR
				SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
				=
				SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', 1) +0
			,  /* are we at the start of the range? */
				IF (
					SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', 1) +0
					=
					SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', 1) +0
				,  /* the ip is at the start of the range */
					SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
					BETWEEN
					SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_start, '.', -2), '.', -1) +0
					AND
					255
				,  /* the ip is at the end of the range */
					SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
					BETWEEN
					0
					AND
					SUBSTRING_INDEX(SUBSTRING_INDEX(ip_range_end, '.', -2), '.', -1) +0
				) /* end IF */
			,  /* no problem, this is in the middle of the range */
				SUBSTRING_INDEX(SUBSTRING_INDEX(machines.ip, '.', -2), '.', -1) +0
				BETWEEN 0 AND 255
			) /* end IF */
		) /* end IF */
	)  /* end AND */
) /* end WHERE */

Not tested yet, but I think it works.

Any SQL gurus out there with a better or more efficient way to do this?