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:

Pull a list of IP addresses from your apache logs (you can get the list from anywhere, of course). Strip the duplicates (using uniq ) Use host to get the DNS entry for the IP Use the default geoiplookup to get the country for the IP. Use geoiplookup with the city file passed to it to get the city (and other info) for the IP. 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.

, which gives you the command.

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. ::