Sort by IP address

So you have a list of rows from ... a CSV? A spreadsheet? What ever the source, let's say there's some chunk of data that you would like to put into order, sorting by the IP address field for each row.

Lexical sort is one place to start, but it will probably leave you frustrated. Why is that? Well, 1 comes before 2, but so does 10 and 100.

???? ?????? ???????????? ?????????????? ?? ????????????, ?????? ?????? ?????????? ?????????????? ???? ???? ?? ????????.

As with many lessons in IT, there's more than one way to solve this. Remember my friend ??????? Let's find a way to drive this nail with one of my favorite hammers.

Head over to your Linux or Mac OS command line environment. Let's start by grinding through a file of comma-separated values (CSV) using awk. Set the field separator to comma like so: awk -F, and feed it the first three rows of our CSV:

10.10.100.10,aa-bb-cc-dd-ee-ff
10.20.100.20,11-22-33-44-55-66
10.30.100.30,a1-b2-c3-d4-e5-f6        

One component of awk that I find to be ... well, awkward, is the built-in function split. I can break up the quad-dot IPv4 address using split like so:

split($1,ip,".")        

This is a busy line of code, behind the scenes. For one, the variable ip is created as an array, with the first entry starting at index 1 (not 0). When you run split against the first field of the first row of our CSV (recall that awk places fields into numbered variables using the built-in FS variable that we've just set to ",") it would yield the following entries:

ip[1]=10
ip[2]=10
ip[3]=100
ip[4]=10        

You can set arbitrary variables on the command line using the -v switch. Setting the output field separator (OFS) to comma, we get CSV in, CSV out.

Convolution alert! The code gets a bit thick here ... (line feeds added for readability)

awk -F, -v OFS=, '{split($1,ip,".");
printf "%03d%03d%03d%03d ",ip[1],ip[2],ip[3],ip[4];
print $1,$2}' input.csv | \
sort -n | \
awk '{print $NF}'        

So that's awk piped into sort piped into yet another awk. The first invocation of awk strips out the "dot" from the quad-dot IPv4 address, then pads the individual octets with leading zeroes. The output then pipes through sort (set the numeric flag), then pipe into a second invocation of awk to strip out the temporary numeric field.

Break open the pipeline to inspect the first row of the first awk's output before we send it into sort, it looks like this:

010010100010 10.10.100.10,aa-bb-cc-dd-ee-ff        

That first awk invocation sure takes a whole lot of typing for a sort routine. Is there a simpler way? What happens if you set down the hammer and slowly back away ... ? Just like Dorothy's slippers, the answer was right there in the yellow brick pipeline, all along!

awk -F, '{print $1,$0}' input.csv | sort -nt . -k 1234 | awk '{print $NF}' > sort-by-ip.csv        

Now we pivot to use awk for its strengths, and sort for its strengths. Let awk split up each row of the csv into fields, then use sort to ... well, sort.

Command line switches can be combined behind a single hyphen in most Unix shells.

Look at the man page for sort, and you learn that -t sets the field separator, -k sets which fields to sort by, and in which order. Did you catch the -n? Command line switches can be combined behind a single hyphen in most Unix shells. Sneaky, huh?

Notice that the first invocation of awk prints out field 1, then field 0. Field 0 means the original record before field separation logic is applied. The second invocation of awk only prints out the last field, $NF - meaning, the pipeline's output is the original input, in IP sorted order.

I'll tip my hat to Perl on my way out of this tutorial, and as for the rest of the problem solution for this iteration, I leave as an exercise for the reader. Create yourself an alias, aton, and point it to this little Perl script:

#!/usr/bin/perl
use warnings;
use strict; 
use Socket q/inet_aton/;
my $ip = shift || die;
die "Unrecognized format: $ip" unless (inet_aton($ip));
print unpack("N",inet_aton($ip)),"\n";        

Now when you invoke your alias with aton 10.10.100.10, your output is 168453130. What kinds of damage could you do with this building block?