Apr 2009

* ApacheLogs_GeoPlot_GoogleEarth_Tobias_Marc

# Use this to see hits if you have a local website. < gets the plot from apache and then places it on a map >

> Marc's Amazing Mysql ip -> address database. its Free!!!
> Based on Tobias Script. He wrote the whole thing, i just converted it to make it work on my comp.

# Google Style Icon
# Use the style URL. It doesnt display properly on Notes.

# Setting up MYSQL
If you are on centos just do the following
yum install php-mysql mysql mysql-server
/sbin/chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

# Setting up username/password. depends on what you want. i did global.
# type mysql
mysql> CREATE USER 'dev'@'%' IDENTIFIED BY 'dev';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dev'@'%'

# Download the database from here http://blogama.org/node/58
extract it
create database ipinfo
use ipinfo

## Save this file at <some_name.pl>
## Execution is perl <some_name.pl> <access_log_Path> > <name.kml>
## Export and open the <name.kml> on google earth

use strict;
use DBI;

my %seen;
my $dbh = DBI->connect(”dbi:mysql:ipinfo”,”dev”,”dev”);
die “unable to connect to database” unless ($dbh);
# kml header
print qq{


print qq{};
print qq{};
print qq{http://maps.google.com/mapfiles/ms/icons/green-dot.png};
print qq{};
print qq{ };

# loop over access_log lines
while() {
# does it look like an access log entry?
next unless (/^(\d+)\.(\d+)\.(\d+)\.(\d+).*\[([^\]]+)\]\s+”(\S+)\s+(\S+)\s+HTTP\/\d.\d/);
# make sure we have a good ip address
next if ($a 255 || $b 255 ||
$c 255 || $d 255);
# did we see this IP already?
next if $seen{”$a.$b.$c.$d”}++;
# compute value for ipinfo lookup
# fetch ipinfo data
my($r)=$dbh->selectall_arrayref(qq{select country_code,region_code,city,latitude,longitude
from ip_group_city where ip_start <= $val order by ip_start desc limit 1});
# no iearth.google.complacemark!
next if !defined($r);
print qq{\t\t\n};
print qq{\t\t\t$a.$b.$c.$d\n};
print qq{\t\t\t#a\n};
# print qq{\t\t\t$a.$b.$c.$d\n};
print qq{\t\t\t<![CDATA[};
print qq{$method $uri from $r->[0]->[0]/$r->[0]->[1]/$r->[0]->[2] at $date};
print qq{]]>\n};
print qq{\t\t\t\n};
print qq{\t\t\t\t$r->[0]->[4],$r->[0]->[3]\n};
print qq{\t\t\t\n};
print qq{\t\t\n};
print qq{\t\t\n};
# kml trailer
print qq{\t


## have fun ;)

* Pics


* 1,4 and 6
Came across this while setting up some monitors.
1( N of 6) / (N of 6) 4 = 0.25 ( 1/4)
Eg: 166/664 = 1666/6664 = 16666/66664 etc = 1/4
There are another set of numbers that exhibit similar properties. Take a guess :)