Shell based GeoIP log file analysis

I needed to make statistics on the users' countries for one of our application, based on the ip addresses stored in a log file.

I used only shell commands for this extraction.

1. Download Geoip C tools from MaxMind to get command line info: http://www.maxmind.com/download/geoip/api/c/

You can choose the latest version of the tool.

1. Build the geoip tools

Uncompress the downloaded archive.

Build the software with the following commands:

The two following lines are needed on MacOSX (Mountain Lion), to avoid the build error: 'sed: RE error: illegal byte sequence'

$ export LC_COLLATE='C'
$ export LC_CTYPE='C'

You can then build the software

$ ./configure && make
$ sudo make install

1. Download a database file:

You need to download an IP database file from MaxMind. I used the free GeoIP Lite database: http://dev.maxmind.com/geoip/geolite

I used the simple GeoLite Country database file. This give me the database file GeoIP.dat.

1. Query a single IP address

   $ geoiplookup -f GeoIP.dat 33.12.126.143
   GeoIP Country Edition: FR, France

I can then cut unwanted parts:

$ geoiplookup -f GeoIP.dat 93.20.226.243 | cut -f2 -d ','| sed -e 's/^[ \t]*//'
France

You can then use that in more complex commands (Note: Locale is for unicode support in country names):

$ export LC_ALL=C
$ sort -t '|' -k7 myfile.log  | cut -f7 -d '|' | uniq > ip.txt
$ IPS=`cat ip.txt`
$ for i in $IPS; do geoiplookup -f GeoIP.dat $i ; done | cut -f2 -d ','| sed -e 's/^[ \t]*//' > countries.txt

This give you a line per country for each IP address.

You can then use your Bash shell knowledge to refine at will. Enjoy !