The Ultimate Masculinity Test: A Laser Pointer

My buddy at work asked me if I could find some location information for a
list of IPs. I knew of the
GeoIP / GeoLite
project(s), so I said yes and then proceeded to put together the following
quick hack in bash.

Here’s what it does:

1. Pull a list of IP addresses from your apache logs (you can get the list
   from anywhere, of course).

2. Strip the duplicates (using uniq)

3. Use host to get the DNS entry for the IP

4. Use the default geoiplookup to get the country for the IP.

5. Use geoiplookup with the city file passed to it to get the city (and
   other info) for the IP.

6. Output the whole thing into a .csv file that will import instantly into
   Excel.

#!/usr/bin/env bash
cat /var/log/apache2/ | awk '{print $1}' > ips.txt
uniq ips.txt > uniques.txt
IPS='cat uniques.txt'
echo "" > ./ipinfo.csv
for i in $IPS
do
    echo "$i,'host $i | awk '{print $5}'','geoiplookup $i | cut -d "," -f2 
| sed -e 's/^[ t]//'','geoiplookup -f /usr/share/GeoIP/GeoLiteCity.dat 
$i | cut -d "," -f3 | sed -e 's/^[
 t]//'','geoiplookup -f /usr/share/GeoIP/GeoLiteCity.dat $i | cut -d
 "," -f4 | sed -e 's/^[ t]*//''" >> ipinfo.csv
done

[ The backticks have been changed to single quotes so it would render
correctly.
Here’s
the original file. ]

Here’s what the output looks like:

193.110.229.12,host12-193-110-229.limes.com.pl.,Poland,82,Gdansk
    189.20.216.229,3(NXDOMAIN),Brazil,27,São Paulo
    81.192.159.138,ll81-2-138-159-192-81.ll81-2.iam.net.ma.,Morocco,07,Casablanca
    189.20.216.229,3(NXDOMAIN),Brazil,27,São Paulo
    76.27.75.237,c-76-27-75-237.hsd1.ut.comcast.net.,United States,UT,South Jordan
    189.20.216.229,3(NXDOMAIN),Brazil,27,São Paulo
    123.125.66.70,3(NXDOMAIN),China,22,Beijing
    70.183.232.136,wsip-70-183-232-136.pn.at.cox.net.,United States,FL,Pensacola
    66.249.70.108,crawl-66-249-70-108.googlebot.com.,United States,CA,Mountain View
    193.212.60.77,3(NXDOMAIN),Norway,01,Fornebu
    189.20.216.229,3(NXDOMAIN),Brazil,27,São Paulo
    193.110.229.12,host12-193-110-229.limes.com.pl.,Poland,82,Gdansk
    83.16.251.58,ajr58.internetdsl.tpnet.pl.,Poland,82,Gdansk
    193.110.229.12,host12-193-110-229.limes.com.pl.,Poland,82,Gdansk
    212.247.189.113,3(NXDOMAIN),Sweden,25,Västerås

Setup

So there are a few quick things you need before this will work:

• geoip, which gives you the geopiplookup command.

  • The
    GeoLiteCity.dat file manually, which you need to put somewhere. I put it next to the default one
    that comes with geoip, which is in /usr/share/GeoIP/.

  • ensure the paths in your environment match the paths in the script.

Of course, if I were really cool I’d use a real programming language and one
of the APIs, but this is quick, dirty and effective. I’m thinking about
building a rails-based web service for doing it. If anyone’s interested or
has any comments on this one, let me know in the comments or send me a mail
at daniel@danielmiessler.com. ::