NetTipsDB

Today I have encountered a problem, where I need to do some statistics on certain activity in my apache log. Depends on your purpose, you have to define it. Firstly I grep all the IP that I need to do the lookup using bash.

cat domain.log | cut -d' ' -f1 | sort -u > ips.txt

Now we have a list of all unique IP in ips.txt. For the country lookup, I use a free geolocation database from Maxmind. They have a database that you can download to your own host, and query it as much as your server can handle it. Let say, you have 10000 IP to lookup, and if you’re using external service, the service surely will have some limitation on how much query you can do per second or per day and it will consume a lot of bandwidth, and time

For this setup, I have downloaded GeoLite Country database, that is less accurate, but less accurate and without any updates. Well, its just enough for me. I just want some simple stats and I have more than 4 thousand IPs, I can handle 99.5% accuracy. The database was last updated on 1st October 2009.

They do provide PHP API to read the binary database. It really make our goal just a few line of code away Name the file below geoip.php

// include functions and GeoIP database.
include("geoip.inc");
$handle = geoip_open("GeoIP.dat", GEOIP_STANDARD);

$ip = stripslashes($_GET['ip']);

echo $ip.":".geoip_country_name_by_addr($handle, $ip)."\n";

// close database handler
geoip_close($handle);

// print compulsory license notice
#echo "

 -- This product includes GeoIP data created by MaxMind, available from http://maxmind.com/ --";

You should keep the last line uncommented to use it. In my case, I need to process the output with bash script. I cannot have any other text in there. Remember the ips.txt file? We have a lot of IP in there. Create another file, process.sh

#!/bin/bash
for a in `cat ips.txt`; do
        curl http://domain/geoip.php?ip=$a --user-agent "anything you like"  >> result_country.txt
done

Just run the file, ./process.sh, and you can see the processing. Once you see your shell again, you can check result_country.txt for the output. In this example, the output will be formatted like this,

XX.XX.XX.XX:Country name
XXX.XXX.XXX.XXX:Other country names
…
..

Last one, run this, more result_country.txt | cut -d’:’ -f2 | sort | uniq -c | sort -nr, you can see the stats, how many occurrences by countries. Below is the sample output.

20 Bulgaria
19 Peru
19 Hungary
19 Czech Republic
17 Malaysia
15 Turkey
15 Sweden
14 Israel
13 South Africa
13 Panama
13 Pakistan
12 Singapore
11 Lebanon
10 Greece
10 Austria

Good luck!!

No related posts.