Tuesday, June 9, 2009

SPAM statistics for your country, HOWTO

This how to get the spam level for specific country from one of the well known spam Black lists:
To get the daily black listed ips you have to register in CBL: http://cbl.abuseat.org/
They are using rsync to distribute the ip list.

to download the list after registration:
/usr/bin/rsync -av rsync://rsync.cbl.abuseat.org/cbl/list.txt /feeds/cbl/list.txt
once you download the list.txt, use a perl script to resolve and filter the country code and query the whois db for network name, the below perl script is using Net::Whois::IP and IP::Country::Fast libraries.
use this command to redirect the output to EG.txt
# perl geo.perl >EG.txt
geo.perl:
use Net::Whois::IP qw(whoisip_query);
use IP::Country::Fast;
my $reg = IP::Country::Fast->new();
my $search_options =["NetName","OrgName"];
my $tt=0;
my @tyy;
open (list, "/feeds/cbl/list.txt")||die "couldn't open the file!";
while ($record = <> )
{

chomp $record;
$cc=$reg->inet_atocc($record);
if($cc eq "EG")
{
$ip=$record;
my $response = whoisip_query($ip,"",$search_options);
foreach (sort keys(%{$response}))
{
$res=$_;
$res1=$response->{$_};

if ($res=~ /netname/) {print $ip . " "; print $res1 . "\n";}
$tyy[$tt] = "$_ $response->{$_} \n";
$tt++;
}}}
close(list);

Now you have EG.txt file will all IPs from Egypt with network name from whois db, you can then upload it to mysql database for further processing:
First create the database cbl and create the table list:
use cbl;
CREATE TABLE list (
date TIMESTAMP NOT NULL,
ip varchar(80) NOT NULL default '',
netname varchar(80) NOT NULL default ''
);


Then upload the file to the database:
#/usr/bin/mysql -u root -ppassword < /feeds/cbl/commands commands file: use cbl; LOAD DATA INFILE '/feeds/cbl/EG.txt' INTO TABLE list FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' (ip,netname,date) ; Now you have the data in the database, you can apply any sort of reporting on it. I am using php and utilizing Google charts API, here is one example:


No comments: