Feature request
Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6301
Printed Date: 05 January 2026 at 9:02am
Topic: Feature request
Posted By: jerbo128
Subject: Feature request
Date Posted: 19 November 2007 at 4:27pm
|
I know that this has been asked before, BUT....
It would be really helpful if there were a way that we could harvest IP's from the blacklist cache.
I am envisioning a table where the you have IP, count, last date.
I would create a script that dropped all entires with dates older than 30 days. All entries with count greater than 10 would be added to my blacklist.
Just My 2 cents
Jeremy
|
Replies:
Posted By: atifghaffar
Date Posted: 21 November 2007 at 4:01pm
Jeremy,
I have some scripts to do that.
The info is in the logfiles.
My scripts run on linux (the log files are available via a NAS to both SF and my management box)
The scripts are written in perl and i am quiet certain that they will run without much changes on windows.
if you are interested, i will post them.
best regards
Atif
-------------
best regards
Atif
|
Posted By: jerbo128
Date Posted: 21 November 2007 at 5:25pm
|
Atif,
That would be great. You can PM me, or email jerbo128 at hot Mail
Thanks
Jeremy
|
Posted By: atifghaffar
Date Posted: 21 November 2007 at 6:14pm
Jeremy, here is the script.
You will need the perl modules
File::Tail
DBI
DBD::Mysql
POSIX
#!/usr/bin/perl
# spamfilter_log_watch.pl
# Script to watch Spamfilter logfiles and extract balcklist attemps and log to database
# Author: Atif Ghaffar <atif.ghaffar@gmail.com>
# Tables required
# for connection logging, just to see which country you are getting most connections from
#
# CREATE TABLE `connections` (
# `id` bigint(20) NOT NULL auto_increment,
# `ip` char(15) NOT NULL,
# `date` date NOT NULL,
# `time` time NOT NULL,
# `country` char(255) NOT NULL,
# PRIMARY KEY (`id`),
# KEY `ip_idx` (`ip`),
# KEY `date_idx` (`date`),
# KEY `time_idx` (`time`)
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
# blacklists
# To log attempts after SF has blacklisted the ip address
#
# CREATE TABLE `blacklists` (
# `id` int(11) NOT NULL auto_increment,
# `ip` varchar(15) default NULL,
# `date` date NOT NULL,
# `time` time NOT NULL,
# PRIMARY KEY (`id`),
# UNIQUE KEY `same_record_idx` (`ip`,`date`,`time`),
# KEY `date_idx` (`date`)
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1
#
#
# Define your database parameters here
#
$dbuser="username";
$dbpass="password";
$dbhost="database server";
$dbname="database name";
# You may have more than 1 Spamfilters running and logging to their own directories
# The logs can be centralized using a NAS or SMB share
# Note it is assumed that the logfile is in format YYYYMMDD.log
# for example 20071225.log
#
@logdirs=qw(
/path/to/logfile/directory1
/path/to/logfile/directory2
);
# END OF CONFIG
$|=1;
use File::Tail;
use POSIX qw(strftime);
use DBI;
our $dsn="DBI:mysql:database=$dbname;host=$dbhost";
our $dbh=DBI->connect_cached($dsn, $dbuser, $dbpass, {AutoCommit => 1, RaiseError => 1});
$sth=$dbh->prepare("insert ignore into blacklists (ip, date, time) values (?,?,?)");
$connection_sth=$dbh->prepare("insert into connections(date, time, ip, country) values (?,?,?,?)");
$today = strftime "%Y%m%d", localtime;
@logfiles=();
foreach (@logdirs){
push @logfiles, "$_/$today.log";
}
$debug=0;
print "Tailing: ", @logfiles, "\n";
foreach (@logfiles) {
push(@files,File::Tail->new(name=>"$_",debug=>$debug));
}
while (1) {
($nfound,$timeleft,@pending)=File::Tail::select(undef,undef,undef,$timeout,@files);
unless ($nfound) {
# timeout - do something else here, if you need to
} else {
foreach (@pending) {
$line=$_->read;
chomp $line;
if ( $line=~/Originating country/) {
($date, $time)=getDateTime($line);
($ip, $country)=$line=~m!.*?Connection from: ([0-9\.]+).*?Originating country : (.*)!;
$country=~s/\s*$//;
$country=~s/^\s*//;
if ($country and $country ne "N/A") {
print "Connection Logging: $date, $time, $ip $country\n";
$connection_sth->execute($date, $time, $ip, $country);
}
}
next unless $line=~/IP is in local blacklist cache/;
($date, $time)=getDateTime($line);
($date, $time, $ip)=$line=~m!^(\d\d/\d\d/\d\d)\s\s*?(\d\d:\d\d:\d\d).*?Disconnecting: ([\d\.]*)!;
($month, $day, $year)=split("/", $date);
$year+=2000;
$date="$year-$month-$day";
print $_->{"input"}. " $date $time $ip\n";
$sth->execute($ip, $date, $time);
}
}
}
sub getDateTime {
my $line=shift;
my $date;
my $time;
($date, $time)=$line=~m!^(\d\d/\d\d/\d\d)\s\s*?(\d\d:\d\d:\d\d).*!;
my ($month, $day, $year)=split("/", $date);
$year+=2000;
$date="$year-$month-$day";
return ($date, $time);
}
__END__
-------------
best regards
Atif
|
Posted By: atifghaffar
Date Posted: 21 November 2007 at 6:39pm
PS: Please not, if you want to to the connection logging also, make sure you have lots of space on your db.
For me, I have 138 million rows in 1 month and about 60GB just for the connections table.
-------------
best regards
Atif
|
|