#!/usr/bin/perl
#
# ulog2xml - Generates a XML file with coordinates to map iptables
#            traffic on GoogleMaps
#

use strict;
use DBI;
use LWP::Simple;
use Geo::IP;

### Mysql ###
my $server = "localhost";
my $port = "3306";
my $user = "ulogd";
my $password = "password";
my $db = "ulogd";
my $table = "ulog";

#Initialize SQL
my $dbh = DBI->connect("DBI:mysql:$db:$server:$port",$user,$password)
	or die "FATAL ERROR: while connecting, $DBI::errstr";

# By default grab the last 10 minutes of traffic
my $last10m = time() - 600;

my $int;
my $ip;
my $sth  = $dbh->prepare("SELECT ip_saddr FROM ulog WHERE oob_time_sec >$last10m");
my @ips;
my $idx=0;
$sth->execute();
while ( ($int) = $sth->fetchrow_array() ) 
{
	# Convert the IP address from integer to dot-quad notation
	my $quad4       = $int % 256; $int      = int($int/256);
	my $quad3       = $int % 256; $int      = int($int/256);
	my $quad2       = $int % 256; $int      = int($int/256);
	my $quad1       = $int % 256;
	$ips[$idx++] = $quad1 . "." . $quad2 . "." . $quad3 . "." . $quad4;
}

# Generate the XML output
print "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
print "<markers entries=\"" . @ips . "\">\n";
my $ipaddr;
foreach $ipaddr(@ips) {
	my $gi = Geo::IP->open("GeoLiteCity.dat", GEOIP_STANDARD);
	my $record = $gi->record_by_name($ipaddr);
	print "<marker country_code=\"" . $record->country_code . "\" country_name=\"" . $record->country_name . "\" lng=\"" . $record->longitude . "\" lat=\"" . $record->latitude . "\"/>\n";
}
print "</markers>";
exit 0;