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?

I work, and once graduated from, the University of New Hampshire, where the Dimond Library played a rather important role in my life. While a student, I had a work study job in the library for awhile, as an assistant to a couple of research librarians (one, then the other). It was there that I was first introduced to making simple web pages, and the use of a digital scanner to put images on the web. Man, that web thing was cool, and I even got to have my own space at pubpages.unh.edu.

This was in 1996. Even then it seemed clear where all this digital stuff was going. The musty old books I still had to truck from one building to another never smelled mustier as I imagined what this web thing was going to do to libraries. Now, more than a decade later, there is pretty clear evidence that the traditional library is slowly being replaced by the internet.

Now that I work at Computing and Information Services (hmmm… even the latter half of our department name is moving in on the library’s territory), I can see more evidence of this shift. One of our initiatives this year is the establishment of the “Dimond Academic Commons” complete with an IT Support Center. The motto for the “DAC” was voted on and turned out to be “Integrating Learning and Technology”. Of course, the word technology is defined as “the application of scientific knowledge for practical purposes”, so this motto could just as easily be “Integrating Learning and Doing” or even “Learn To Do Something, Jack”… but I didn’t get to vote. Probably a good thing.

So little by little, silicon will replace wood pulp over at Dimond Library and every other library in the world. Sure, there will be books for years to come, but those interested in preserving a legacy in writing these days will do so in the digital domain. Books can burn, rot, and can’t be copied very easily. They are “bound” (I kill me) to be obsolete sooner or later.

Still, I plan to keep the “Book Learnin’” section here in the Garden of Blog alive and well, even if, in my later years, the word “book” becomes an arcane symbol for the act of reading.

0714070826.jpg

Originally uploaded by Marcus Del Greco.

This one gives me flashbacks. So accurate, so funny.