#!/usr/bin/perl
#***********************************************************************
#
# mdfdb-clean
#
# mimedefang-filter database info lister
#
# $Id: mdfdb-info.pl,v 1.27 2007/10/17 16:43:27 jonas Exp $
#
# This program may be distributed under the terms of the GNU General
# Public License, Version 2, or (at your option) any later version.
#
#***********************************************************************
#***********************************************************************
# Code...
#***********************************************************************
use strict;
use DBI;
use Date::Format;
my $sqldb;
my $sqldbd = '?';
my $sqlr;
my $max = -1;
my $all = 0;
#***********************************************************************
# Config.
#***********************************************************************
my %Features;
$Features{'Path:SPOOLDIR'} = '/var/spool/MIMEDefang';
# Add setting to config parser
my %cfgcfg = ();
$cfgcfg{'@i'}{i} = 0;
$cfgcfg{'@i'}{c} = 0;
sub add_cfg_cfg {
my ($c,$v,$d,$t,$f) = @_;
$d = '' unless (defined($d));
$t = 's' unless ($t);
$f = '' unless ($f);
$c =~ s/[-_]+//g;
$c = lc($c);
$$v = $d;
$cfgcfg{'@i'}{c} ++;
$cfgcfg{$c}{v} = $v;
$cfgcfg{$c}{t} = lc($t);
$cfgcfg{$c}{f} = lc($f);
$cfgcfg{$c}{i} = $cfgcfg{'@i'}{c};
$cfgcfg{$c}{x} = 0;
}
use vars qw($gdb_black $gdb_grey $gdb_white $gdb_host_white $nsdb_expire $rdb_expire $dc_time_window $dc_limit $out_expire $gsnd_expire);
add_cfg_cfg('gdb_black',\$gdb_black,3*60,'i');
add_cfg_cfg('gdb_grey',\$gdb_grey,72*60*60,'i');
add_cfg_cfg('gdb_white',\$gdb_white,36*24*60*60,'i');
add_cfg_cfg('gdb_host_white',\$gdb_host_white,7*24*60*60,'i');
add_cfg_cfg('nsdb_expire',\$nsdb_expire,30*24*60*60,'i');
add_cfg_cfg('rdb_expire',\$rdb_expire,30*24*60*60,'i');
add_cfg_cfg('dc_time_window',\$dc_time_window,3*60,'i');
add_cfg_cfg('dc_limit',\$dc_limit,10,'i');
add_cfg_cfg('out_expire',\$out_expire,30*24*60*60,'i');
add_cfg_cfg('gsnd_expire',\$gsnd_expire,30*24*60*60,'i');
use vars qw($smtp_cache_good $smtp_cache_fail $smtp_cache_bad);
add_cfg_cfg('smtp_cache_good',\$smtp_cache_good,60*60,'i');
add_cfg_cfg('smtp_cache_fail',\$smtp_cache_fail,10*60,'i');
add_cfg_cfg('smtp_cache_bad',\$smtp_cache_bad,60,'i');
use vars qw($expn_cache);
add_cfg_cfg('expn_cache',\$expn_cache,20*60,'i');
use vars qw($pgp_expire $pgp_expire_bad);
add_cfg_cfg('pgp_expire',\$pgp_expire,14*24*60*60,'i');
add_cfg_cfg('pgp_expire_bad',\$pgp_expire_bad,1*24*60*60,'i');
use vars qw($mx_cache_valid $mx_cache_invalid $sc_cache_valid $sc_cache_invalid $sc_cache_unknown $sc_cache_invalid_add $sc_cache_invalid_max);
add_cfg_cfg('mx_cache_valid',\$mx_cache_valid,7*24*60*60,'i');
add_cfg_cfg('mx_cache_invalid',\$mx_cache_invalid,60,'i');
add_cfg_cfg('sc_cachevalid',\$sc_cache_valid,7*24*60*60,'i');
add_cfg_cfg('sc_cacheinvalid',\$sc_cache_invalid,60*60,'i');
add_cfg_cfg('sc_cacheunknown',\$sc_cache_unknown,7*24*60*60,'i');
add_cfg_cfg('sc_cacheinvalidadd',\$sc_cache_invalid_add,60*60,'i');
add_cfg_cfg('sc_cacheinvalidmax',\$sc_cache_invalid_max,24*60*60,'i');
use vars qw ($hilo_entries);
add_cfg_cfg('hilo_entries',\$hilo_entries,0,'i');
use vars qw($vircache_local $vircache_external $virus_keep);
add_cfg_cfg('AVCacheLocal',\$vircache_local,0,'i');
add_cfg_cfg('AVCacheExternal',\$vircache_external,0,'i');
add_cfg_cfg('avc_keep',\$virus_keep,7*24*60*60,'i');
use vars qw($database_spec $database_user $database_pass);
add_cfg_cfg('database_spec',\$database_spec,'dbi:SQLite:dbname=%s/filterdata.db','p');
add_cfg_cfg('database_user',\$database_user,'','s');
add_cfg_cfg('database_pass',\$database_pass,'','s');
# Get a file path name
sub get_file_path_name {
my $f = shift;
return $f if ($f =~ /[\/\\]/);
foreach my $d (('/usr/local/etc/mimedefang/filter','/etc/mimedefang/filter',
'/usr/local/etc/mimedefang','/etc/mimedefang',
'/usr/local/etc/mail','/etc/mail', )) {
if ($d =~ /mimedefang/) {
return "$d/$f" if (-f "$d/$f");
return "$d/mimedefang-$f" if (-f "$d/mimedefang-$f");
} else {
return "$d/mimedefang-$f" if (-f "$d/mimedefang-$f");
return "$d/$f" if (-f "$d/$f");
}
}
return '';
}
# read the configuration file
sub read_cfg_params {
my @pp = @ARGV;
@ARGV = ();
foreach my $p (@pp) {
$p =~ s/^-+//;
if ($p =~ /^d(ata)?b(ase)?[-_]?s(pec|pecification)?[:=]\s*(.*?)\s*$/i) {
$database_spec = $4;
} elsif ($p =~ /^d(ata)?b(ase)?[-_]?u(sr|ser)?[:=]\s*(.*?)\s*$/i) {
$database_user = $4;
} elsif ($p =~ /^d(ata)?b(ase)?[-_]?p(ass|assword|wd)?[:=]\s*(.*?)\s*$/i) {
$database_pass = $4;
} else {
push @ARGV, $p;
}
}
}
sub read_cfg_cfg {
my $cfgfn = get_file_path_name('filter.conf');
if ($cfgfn) {
die('No filter config!') unless ($cfgfn);
$Features{'Path:CONFDIR'} = $cfgfn; $Features{'Path:CONFDIR'} =~ s/(\/filter)?\/[^\/]*$//;
die('Cannot read filter config!') unless (open(F,'<',$cfgfn));
#md_syslog('info',"Filter config: $cfgfn");
while (my $l = <F>) {
$l =~ s/[\r\n]+//gs;
next unless ($l);
next if ($l =~ /^\s*[#;]/);
if ($l =~ /^\s*(\S+)\s*?[\s:=]\s*(\S.*?)\s*$/) {
my $c = lc($1);
my $v = $2;
$c =~ s/[-_]+//g;
next unless ($c);
next if ($c =~ /^\@/);
if (defined($cfgcfg{$c})) {
if ($cfgcfg{$c}{x} && $cfgcfg{$c}{t} =~ /^m/i) {
${$cfgcfg{$c}{v}} .= ';' if (${$cfgcfg{$c}{v}} ne '');
${$cfgcfg{$c}{v}} .= $v;
} else {
${$cfgcfg{$c}{v}} = $v;
$cfgcfg{$c}{x} = 1;
}
}
}
}
close(F);
#return;
} else {
$Features{'Path:CONFDIR'} = '.';
}
read_cfg_params();
$Features{'Path:SPOOLDIR'} = '.' unless ($Features{'Path:SPOOLDIR'} && (-d $Features{'Path:SPOOLDIR'}));
my @ck = sort { $cfgcfg{$a}{i} <=> $cfgcfg{$b}{i} } keys %cfgcfg;
foreach my $c (@ck) {
next if ($c =~ /^\@/);
${$cfgcfg{$c}{v}} = ${$cfgcfg{$cfgcfg{$c}{f}}{v}} if ($cfgcfg{$c}{f} && !${$cfgcfg{$c}{v}});
}
foreach my $c (@ck) {
next if ($c =~ /^\@/);
if ($cfgcfg{$c}{t} eq 'l') {
${$cfgcfg{$c}{v}} = '' unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} =~ s/\s//g;
${$cfgcfg{$c}{v}} =~ s/\./\\./g if (${$cfgcfg{$c}{v}} !~ /\\\./);
${$cfgcfg{$c}{v}} =~ s/\@/\\\@/g if (${$cfgcfg{$c}{v}} !~ /\\\@/);
${$cfgcfg{$c}{v}} =~ s/\./\\./g if (${$cfgcfg{$c}{v}} !~ /\\\./);
${$cfgcfg{$c}{v}} =~ s/,/\|/g if (${$cfgcfg{$c}{v}} !~ /[\(\)\|\{\}]/ && ${$cfgcfg{$c}{v}} =~ /\,/);
${$cfgcfg{$c}{v}} = sprintf('(%s)',${$cfgcfg{$c}{v}}) if (${$cfgcfg{$c}{v}} !~ /[\(\)]/ && ${$cfgcfg{$c}{v}} =~ /\|/);
} elsif ($cfgcfg{$c}{t} eq 'a') {
${$cfgcfg{$c}{v}} = '' unless (${$cfgcfg{$c}{v}});
next unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} .= '@'.${$cfgcfg{'myfilterhostname'}{v}} if (${$cfgcfg{$c}{v}} =~ /^[^@]+$/);
} elsif ($cfgcfg{$c}{t} eq 't') {
${$cfgcfg{$c}{v}} = '' unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = sprintf(${$cfgcfg{$c}{v}},${$cfgcfg{'myfilterhostname'}{v}});
${$cfgcfg{$c}{v}} =~ s/[\r\n]*$/\n\n/s;
} elsif ($cfgcfg{$c}{t} eq 'b') {
${$cfgcfg{$c}{v}} = 0 unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = 0 if (${$cfgcfg{$c}{v}} =~ /^\s*(false|no?|off|0+)\s*$/);
${$cfgcfg{$c}{v}} = 1 if (${$cfgcfg{$c}{v}});
} elsif ($cfgcfg{$c}{t} eq 'i') {
${$cfgcfg{$c}{v}} = 0 unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = eval(${$cfgcfg{$c}{v}});
} elsif ($cfgcfg{$c}{t} eq 'ps') {
${$cfgcfg{$c}{v}} = $Features{'Path:SPOOLDIR'} unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = sprintf('%s/%s',$Features{'Path:SPOOLDIR'},${$cfgcfg{$c}{v}}) if (${$cfgcfg{$c}{v}} !~ /[\/\\]/);
} elsif ($cfgcfg{$c}{t} eq 'pc') {
${$cfgcfg{$c}{v}} = $Features{'Path:CONFDIR'} unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = sprintf('%s/%s',$Features{'Path:CONFDIR'},${$cfgcfg{$c}{v}}) if (${$cfgcfg{$c}{v}} !~ /[\/\\]/);
} elsif ($cfgcfg{$c}{t} eq 'p') {
${$cfgcfg{$c}{v}} = $Features{'Path:CONFDIR'} unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = sprintf(${$cfgcfg{$c}{v}},$Features{'Path:SPOOLDIR'});
} elsif ($cfgcfg{$c}{t} eq 'mpsm') {
my @pil = split(/\s*;\s*/,${$cfgcfg{$c}{v}});
for (my $i=0;$i<@pil;$i++) {
my ($fn,$ft,$fo,$x) = split(/\s*,\s*/,$pil[$i]);
$fn = sprintf('%s/%s',${$cfgcfg{'sendmailconfig'}{v}},$fn) if ($fn !~ /[\/\\]/);
$fo = '?' unless ($fo);
unless ($ft) {
if ($fn =~ /table/i) {
$ft = 'table'
} else {
$ft = 'list';
}
}
$pil[$i] = join(',',$fn,lc($ft),lc($fo));
}
${$cfgcfg{$c}{v}} = join(';',@pil);
} elsif ($cfgcfg{$c}{t} eq 'mbs') {
${$cfgcfg{$c}{v}} = 0 unless (${$cfgcfg{$c}{v}});
${$cfgcfg{$c}{v}} = 0 if (${$cfgcfg{$c}{v}} =~ /^\s*(false|no|n|off|0+)\s*$/);
${$cfgcfg{$c}{v}} = 1 if (${$cfgcfg{$c}{v}} =~ /^\s*(true|yes|y|on|\d*[1-9]\d*)\s*$/);
} else {
${$cfgcfg{$c}{v}} = '' unless (${$cfgcfg{$c}{v}});
}
}
if ($database_spec =~ /sqlite/i) {
$sqldbd = 'L';
} elsif ($database_spec =~ /mysql/i) {
$sqldbd = 'M';
}
}
#***********************************************************************
# SQL.
#***********************************************************************
sub sql_translate {
my ($cmd) = @_;
$cmd =~ s/ LIMIT -1$//;
return $cmd;
}
sub sql_command {
for (my $i=0;$i<@_;$i++) {
my $cmd = sql_translate(@_[$i]);
return 0 unless ($sqldb && $cmd);
$sqlr = $sqldb->do($cmd);
return 0 unless (defined($sqlr));
}
return 1;
}
sub sql_select_one_row {
my $cmd = sql_translate(shift);
#print "$cmd\n";
return undef unless ($sqldb && $cmd);
my $st = $sqldb->prepare("$cmd");
return undef unless ($st);
$st->execute;
my @res = $st->fetchrow_array;
$st->finish;
return \@res;
}
sub sql_select_one {
my $res = sql_select_one_row(@_);
return undef unless ($res && @{$res});
return $res->[0];
}
#***********************************************************************
# Info.
#***********************************************************************
sub rdb_list {
print "RDB\n";
my %db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM relaylist LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless (@res>1);
next unless ($res[0] && $res[1]);
next if (!$all && (($rdb_expire>0) && ($res[2] < $now-$rdb_expire)));
$res[3] = 0 unless ($res[3]);
$res[4] = 0 unless ($res[4]);
my %ri = (
a => $res[0],
w => $res[1],
t => $res[2],
s => $res[3],
h => $res[4],
);
if ($res[4] == 0 && $res[3] > 0) {
$ri{r} = 999999999999+$res[3];
} elsif ($res[3] == 0 && $res[4] > 0) {
$ri{r} = -$res[4];
} elsif ($res[4] == 0 && $res[3] == 0) {
$ri{r} = 0;
} else {
$ri{r} = $res[3]/$res[4];
$ri{r} = 999999999999 if ($ri{r} > 999999999999);
}
my $w = '?';
if ($res[0] =~ /^\d+\.\d+\.\d+\.\d+$/) {
$w = 'relay';
} elsif ($res[0] =~ /\@/) {
$w = 'sender';
} elsif ($res[0] =~ /\S+\.\S+/) {
$w = 'domain';
}
unless ($db{$w}) {
my @l = ();
$db{$w} = \@l;
}
push @{$db{$w}}, \%ri;
}
$st->finish;
return unless (%db);
my $tc = 0;
foreach my $w (keys %db) {
my $c = 0;
foreach my $i (sort { $a->{r} <=> $b->{r} } @{$db{$w}}) {
my $r = $i->{r};
if ($r >= 999999) {
$r = '>';
} elsif ($r < 0) {
$r = '<';
} else {
$r = sprintf('%.1f',$r);
}
print sprintf("%-10s %-40s %6u %6u %9s %s %3u %s %3u\n",
$w,$i->{a},$i->{h},$i->{s},$r,
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
);
$c ++;
$tc ++;
}
print sprintf("%-10s %u\n","$w:",$c);
}
print sprintf("%-10s %u\n",'rdb:',$tc);
}
sub nsdb_list {
print "NSDB\n";
my %db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM nospamlist LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless (@res>1);
next unless ($res[0] && $res[1]);
next if (!$all && (($nsdb_expire>0) && ($res[2] < $now-$nsdb_expire)));
$res[3] = 0 unless ($res[3]);
my %ri = (
a => $res[0],
w => $res[1],
t => $res[2],
c => $res[3],
);
my $w = '?';
if ($res[0] =~ /^\d+\.\d+\.\d+\.\d+$/) {
$w = 'relay';
} elsif ($res[0] =~ /\@/) {
$w = 'sender';
} elsif ($res[0] =~ /\S+\.\S+/) {
$w = 'domain';
}
unless ($db{$w}) {
my @l = ();
$db{$w} = \@l;
}
push @{$db{$w}}, \%ri;
}
$st->finish;
return unless (%db);
my $tc = 0;
foreach my $w (keys %db) {
my $c = 0;
foreach my $i (sort { $a->{c} <=> $b->{c} } @{$db{$w}}) {
if ($i->{t} == $i->{w}) {
print sprintf("%-10s %-40s %6i %s %3u\n",
$w,$i->{a},$i->{c},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
);
} else {
print sprintf("%-10s %-40s %6i %s %3u %s %3u\n",
$w,$i->{a},$i->{c},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
);
}
$c ++;
$tc ++;
}
print sprintf("%-10s %u\n","$w:",$c);
}
print sprintf("%-10s %u\n",'nsdb:',$tc);
}
sub gdb_cmp {
my $c = ($b->{c} cmp $a->{c});
$c = ($b->{a} <=> $a->{a}) unless ($c);
$c = ($b->{x} <=> $a->{x}) unless ($c);
return $c;
}
sub gdb_list {
print "GDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM greylist LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless (@res>1);
$res[0] = 0 unless ($res[0]);
$res[1] = 0 unless ($res[1]);
$res[2] = 0 unless ($res[2]);
$res[3] = 0 unless ($res[3]);
$res[4] = 0 unless ($res[4]);
my %ri = (
x => $res[0],
a => $res[1],
h => $res[2],
s => $res[3],
r => $res[4],
);
if ($now < $res[0]+$gdb_black) {
$ri{c} = 'black';
} elsif (($now < $res[0]+$gdb_grey) || (($res[1] > 0) && ($now < $res[1] + $gdb_white))) {
$ri{c} = 'white';
} elsif ($all) {
$ri{c} = '';
} else {
next;
}
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort gdb_cmp @db) {
if ($i->{a} && $i->{x}) {
print sprintf("%-6s %-90s %s %5u %s %5u\n",
$i->{c},
join(' ',$i->{h},$i->{s},$i->{r}),
time2str('%Y-%m-%d %H:%M:%S',$i->{x}),($now-$i->{x})/(60*60*24),
time2str('%Y-%m-%d %H:%M:%S',$i->{a}),($now-$i->{a})/(60*60*24),
);
} elsif ($i->{x}) {
print sprintf("%-6s %-90s %s %5u\n",
$i->{c},
join(' ',$i->{h},$i->{s},$i->{r}),
time2str('%Y-%m-%d %H:%M:%S',$i->{x}),($now-$i->{x})/(60*60*24),
);
} elsif ($i->{a}) {
print sprintf("%-6s %-90s %25s %s %5u\n",
$i->{c},
join(' ',$i->{h},$i->{s},$i->{r}),'',
time2str('%Y-%m-%d %H:%M:%S',$i->{x}),($now-$i->{x})/(60*60*24),
);
} else {
print sprintf("%-6s %s\n",
$i->{c},
join(' ',$i->{h},$i->{s},$i->{r}),
);
}
$tc ++;
}
print sprintf("%-10s %u\n",'ghdb:',$tc);
}
sub hdb_list {
print "GHDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM hostlist LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless (@res>1);
next unless ($res[0] && $res[1]);
$res[2] = 0 unless ($res[2]);
next unless ($all || ($now < $res[2]+$gdb_host_white));
my %ri = (
h => $res[0],
w => $res[1],
t => $res[2],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort { $b->{t} <=> $a->{t} } @db) {
if ($i->{t} == $i->{w}) {
print sprintf("%-40s %s %5u\n",
$i->{h},
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
);
} else {
print sprintf("%-40s %s %5u %s %5u\n",
$i->{h},
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
);
}
$tc ++;
}
print sprintf("%-10s %u\n",'ghdb:',$tc);
}
sub mxdb_cmp {
my $c = ($b->{e} cmp $a->{e});
$c = ($b->{w} <=> $a->{w}) unless ($c);
return $c;
}
sub mxdb_list {
print "MXDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM mxcheck LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless (@res>1);
next unless ($res[0] && $res[1]);
unless ($all) {
if ($res[2]) {
next unless ($now < $res[1]+$mx_cache_invalid);
} else {
next unless ($now < $res[1]+$mx_cache_valid);
}
}
$res[2] = '' unless ($res[2]);
my %ri = (
d => $res[0],
w => $res[1],
e => $res[2],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort mxdb_cmp @db) {
if ($i->{e}) {
print sprintf("%-40s %s %5u %s\n",
$i->{d},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
$i->{e},
);
} else {
print sprintf("%-40s %s %5u\n",
$i->{d},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
);
}
$tc ++;
}
print sprintf("%-10s %u\n",'mxdb:',$tc);
}
sub scdb_cmp {
my $c = ($a->{r} <=> $b->{r});
$c = ($a->{s} cmp $b->{s}) unless ($c);
$c = ($b->{w} <=> $a->{w}) unless ($c);
return $c;
}
sub scdb_list {
print "SCDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM sendercheck LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless (@res>1);
next unless ($res[0] && $res[1]);
$res[2] = 0 unless ($res[2]);
$res[3] = 0 unless ($res[3]);
$res[4] = '' unless ($res[4]);
unless ($all) {
my $ct;
if (!$res[3]) {
$ct = $sc_cache_invalid + ($sc_cache_invalid_add * ($res[2] - 1));
$ct = $sc_cache_invalid_max if ($ct > $sc_cache_invalid_max);
} elsif ($res[3] == 1) {
$ct = $sc_cache_valid;
} else {
$ct = $sc_cache_unknown;
}
next if ($now - $res[1] > $ct);
}
my %ri = (
a => $res[0],
w => $res[1],
c => $res[2],
r => $res[3],
s => $res[4],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort scdb_cmp @db) {
if ($i->{s}) {
print sprintf("%-40s %s %5u %u %u %s\n",
$i->{a},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
$i->{c},$i->{r},$i->{s},
);
} else {
print sprintf("%-40s %s %5u %u %u\n",
$i->{a},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
$i->{c},$i->{r},
);
}
$tc ++;
}
print sprintf("%-10s %u\n",'scdb:',$tc);
}
sub dcdb_cmp {
my $c = ($b->{c} <=> $a->{c});
$c = ($b->{t} <=> $a->{t}) unless ($c);
$c = ($b->{w} <=> $a->{w}) unless ($c);
return $c;
}
sub dcdb_list {
print "DCDB\n";
my @db = ();
my %dbi = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM dictionary LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[1]);
my $o = ($now < $res[0]+$dc_time_window);
next unless ($o || $all);
my $i = $dbi{$res[1]};
unless (defined($i)) {
my %i = (h=>$res[1],c=>0,t=>0,w=>0);
push @db, \%i;
$i = $#db;
$dbi{$res[1]} = $i;
}
$db[$i]->{c} ++ if ($o);
$db[$i]->{t} ++;
$db[$i]->{w} = $res[0] if ($res[0] > $db[$i]->{w});
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort dcdb_cmp @db) {
if ($all) {
print sprintf("%-15s %5u %5u %s %5u\n",
$i->{h},$i->{c},$i->{t},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
);
} else {
print sprintf("%-15s %5u %s %5u\n",
$i->{h},$i->{c},
time2str('%Y-%m-%d %H:%M:%S',$i->{w}),($now-$i->{w})/(60*60*24),
);
}
$tc ++;
}
print sprintf("%-10s %u\n",'dcdb:',$tc);
}
sub pgpdb_cmp {
my $c = ($b->{k} <=> $a->{k});
$c = ($b->{t} <=> $a->{t}) unless ($c);
return $c;
}
sub pgpdb_list {
print "PGPDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM pgpring LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[1]);
next if (!$all && (($pgp_expire>0) && ($res[1] < $now-$pgp_expire)));
next if (!$all && $res[1] eq '#' && (($pgp_expire_bad>0) && ($res[1] < $now-$pgp_expire_bad)));
$res[2] = '' unless ($res[2]);
$res[3] = '' unless ($res[3]);
$res[4] = '' unless ($res[4]);
$res[5] = '' unless ($res[5]);
my %ri = (
i => $res[0],
t => $res[1],
n => $res[2],
k => $res[3],
a => $res[4],
p => $res[5],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort pgpdb_cmp @db) {
my $kg = 'bad';
$kg = 'good' if ($i->{k} && $i->{k} ne '#');
my $s = sprintf('%-4s %-16s %s %5u',
$kg,$i->{i},
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24)
);
$s .= ' '.$i->{a} if ($i->{a});
$s .= ' '.$i->{p} if ($i->{p});
$s .= ' '.$i->{n} if ($i->{n});
print "$s\n";
$tc ++;
}
print sprintf("%-10s %u\n",'pgpdb:',$tc);
}
sub tsdb_cmp {
my $c = ($a->{e} <=> $b->{e});
$c = ($a->{t} <=> $b->{t}) unless ($c);
return $c;
}
sub tsdb_list {
print "TSDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM times LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[1]);
$res[2] = 0 unless ($res[2]);
$res[3] = 0 unless ($res[3]);
my %ri = (
i => $res[0],
t => $res[1],
e => $res[2],
g => $res[3],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
my $tcg = 0;
my $tg = 0;
foreach my $i (sort tsdb_cmp @db) {
if ($i->{e}) {
print sprintf("%s %5u %5u\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{e}),($now-$i->{e})/(60*60*24),$i->{g});
$tcg ++;
$tg += $i->{g};
} else {
print sprintf("%s %5u\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24));
}
$tc ++;
}
print sprintf(" %7.1f\n",$tg/$tcg) if ($tcg);
print sprintf("%-10s %u\n",'tsdb:',$tc);
}
sub msgldb_cmp {
my $c = ($a->{t} <=> $b->{t});
$c = ($a->{c} <=> $b->{c}) unless ($c);
return $c;
}
sub msgldb_list {
print "MSGDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM messages LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[2] && $res[3] && $res[4]);
$res[0] =~ s/[\r\n]+//gs;
$res[1] = '' unless ($res[1]);
my %ri = (
i => $res[0],
f => $res[1],
r => $res[2],
t => $res[3],
c => $res[4],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
my $tcl = 0;
if ($all) {
foreach my $i (sort msgldb_cmp @db) {
print sprintf("%10u %s %5u %-50s %s\n",
$i->{c},
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{f},$i->{r});
$tc ++;
}
print sprintf("%-10s %u\n",'msgdb:',$tc);
} else {
my @dbx = ();
my %dbx = ();
foreach my $i (@db) {
if ($dbx{$i->{i}}) {
$dbx[$dbx{$i->{i}}]->{t} = $i->{t} if ($i->{t} < $dbx[$dbx{$i->{i}}]->{t});
$dbx[$dbx{$i->{i}}]->{c} += $i->{c};
push @{$dbx[$dbx{$i->{i}}]->{f}}, $i->{f} unless ($dbx[$dbx{$i->{i}}]->{fh}{lc($i->{f})});
push @{$dbx[$dbx{$i->{i}}]->{r}}, $i->{r} unless ($dbx[$dbx{$i->{i}}]->{rh}{lc($i->{r})});
} else {
my %mi = %{$i};
$mi{f} = [$i->{f}];
$mi{r} = [$i->{r}];
$mi{fh}{lc($i->{f})} = 1;
$mi{rh}{lc($i->{r})} = 1;
push @dbx, \%mi;
$dbx{$i->{i}} = $#dbx;
}
$tc ++;
}
foreach my $i (sort msgldb_cmp @dbx) {
print sprintf("%10u %s %5u %-50s %s\n",
$i->{c},
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
join(',',@{$i->{f}}),join(',',@{$i->{r}}));
$tcl ++;
}
print sprintf("%-10s %u (%u)\n",'msgdb:',$tc,$tcl);
}
}
sub outdb_cmp {
return ($a->{t} <=> $b->{t});
}
sub outdb_list {
print "OUTDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM sentout LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[1] && $res[2]);
next if (!$all && (($out_expire>0) && ($res[5] < $now-$out_expire)));
$res[0] = '' unless ($res[0]);
$res[3] = '' unless ($res[3]);
$res[4] = '' unless ($res[4]);
$res[5] = 0 unless ($res[5]);
my %ri = (
i => $res[0],
f => $res[1],
u => $res[2],
d => $res[3],
h => $res[4],
t => $res[5],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort outdb_cmp @db) {
print sprintf("%s %5u %s %s@%s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{f},$i->{u},$i->{d},$i->{h});
$tc ++;
}
print sprintf("%-10s %u\n",'outdb:',$tc);
}
sub smtpdb_cmp {
return ($a->{t} <=> $b->{t});
}
sub smtpdb_list {
print "SMTPDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM smtpcheck LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[3] && $res[4]);
unless ($all) {
next if ($res[4] eq 'CONTINUE' && $res[3] < $now-$smtp_cache_good);
next if ($res[4] eq 'TEMPFAIL' && $res[3] < $now-$smtp_cache_fail);
next if ($res[4] eq 'REJECT' && $res[3] < $now-$smtp_cache_bad);
}
my %ri = (
f => $res[0],
r => $res[1],
h => $res[2],
t => $res[3],
o => $res[4],
m => $res[5],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort smtpdb_cmp @db) {
print sprintf("%s %5u %-15s %-8s %-40s %s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{h},$i->{o},$i->{r},$i->{f},$i->{m});
$tc ++;
}
print sprintf("%-10s %u\n",'smtpdb:',$tc);
}
sub expndb_cmp {
return ($a->{t} <=> $b->{t});
}
sub expndb_list {
print "EXPNDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM expncheck LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[2]);
next unless ($all || $res[2] >= $now-$expn_cache);
my %ri = (
o => $res[0],
n => $res[1],
t => $res[2],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort expndb_cmp @db) {
print sprintf("%s %5u %-40s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{o},$i->{n});
$tc ++;
}
print sprintf("%-10s %u\n",'expndb:',$tc);
}
sub gsnddb_cmp {
return ($a->{t} <=> $b->{t});
}
sub gsnddb_list {
print "GSNDDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM generatedsenders LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0]);
next if (!$all && (($gsnd_expire>0) && ($res[1] < $now-$gsnd_expire)));
$res[0] = '' unless ($res[0]);
$res[1] = 0 unless ($res[1]);
$res[2] = 0 unless ($res[2]);
$res[3] = 0 unless ($res[3]);
my %ri = (
e => $res[0],
t => $res[1],
r => $res[2],
b => $res[3],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort gsnddb_cmp @db) {
print sprintf("%s %5u %5u %5u %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{r},$i->{b},$i->{e});
$tc ++;
}
print sprintf("%-10s %u\n",'gsnddb:',$tc);
}
sub virus_cmp {
return ($a->{t} <=> $b->{t});
}
sub virus_list {
print "AVCDB\n";
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM virusresults LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0]);
unless ($all) {
next unless ($res[4] >= $now-$virus_keep);
if ($res[1]) {
next unless ($res[4] >= $now-$vircache_local);
} else {
next unless ($res[4] >= $now-$vircache_external);
}
}
$res[0] = '' unless ($res[0]);
$res[4] = 0 unless ($res[4]);
my %ri = (
h => $res[0],
l => $res[1],
e => $res[2],
v => $res[3],
t => $res[4],
r => $res[5],
c => $res[6],
d => $res[7],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
my $tc = 0;
foreach my $i (sort virus_cmp @db) {
print sprintf("%s %5u %-8s %-7s %01u %-20s %-15s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{l}?'local':'external',$i->{e}?'entity':'message',$i->{v},
$i->{c},$i->{d},$i->{r},$i->{h});
$tc ++;
}
print sprintf("%-10s %u\n",'avcdb:',$tc);
}
sub logs_cmp_all {
return ($a->{t} <=> $b->{t});
}
sub logs_cmp_sum {
return ($a->{h} <=> $b->{h} ? $a->{h} <=> $b->{h} : $a->{l} <=> $b->{l});
}
sub logs_list {
print "LOGSDB\n";
my $tc = 0;
if ($all) {
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT * FROM logs LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[1] && $res[2]);
my %ri = (
t => $res[1],
w => $res[2],
c => $res[3],
i => $res[4],
l => $res[5],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
foreach my $i (sort logs_cmp_all @db) {
print sprintf("%s %5u %-15s %-15s %-14s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{w},$i->{c},$i->{i},$i->{l});
$tc ++;
}
} else {
my @db = ();
my $st = $sqldb->prepare(sql_translate("SELECT logs_type,logs_cont,COUNT(*),MIN(logs_stamp),MAX(logs_stamp) FROM logs GROUP BY logs_type,logs_cont LIMIT $max"));
return unless ($st);
my $now = time();
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[0] && $res[1]);
my %ri = (
w => $res[0],
v => $res[1],
c => $res[2],
l => $res[3],
h => $res[4],
);
push @db, \%ri;
}
$st->finish;
return unless (@db);
foreach my $i (sort logs_cmp_sum @db) {
print sprintf("%s %5u %s %5u %5u %-15s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{l}),($now-$i->{l})/(60*60*24),
time2str('%Y-%m-%d %H:%M:%S',$i->{h}),($now-$i->{h})/(60*60*24),
$i->{c},$i->{w},$i->{v});
$tc += $i->{c};
}
}
print sprintf("%-10s %u\n",'logsdb:',$tc);
}
sub hilodb_select {
my $ord = shift;
my $lmt = shift;
my $dbl = shift;
my $st = $sqldb->prepare(sql_translate("SELECT hilo_id,hilo_stamp,hilo_score,hilo_info FROM hiloscores ORDER BY hilo_score $ord LIMIT $lmt"));
return unless ($st);
$st->execute;
while (my @res = $st->fetchrow_array) {
next unless ($res[1] && $res[2] && $res[3]);
my %ri = (
i => $res[0],
t => $res[1],
h => $res[2],
f => '',
r => '',
);
foreach my $l (split(/[\r\n]+/,$res[3])) {
if ($l =~ /^Mail From:\s*(.*?)$/i) {
$ri{f} .= ",$1";
} elsif ($l =~ /^RCPT To:\s*(.*?)$/i) {
$ri{r} .= ",$1";
}
}
$ri{f} =~ s/^,//;
$ri{r} =~ s/^,//;
push @$dbl, \%ri;
}
$st->finish;
}
sub hilodb_list {
print "HILODB\n";
my @db = ();
my $now = time();
return if ($max == 0);
if ($all || ($hilo_entries && $hilo_entries<0)) {
hilodb_select('DESC',$max,\@db);
} else {
return unless ($hilo_entries);
my $le = $hilo_entries;
if ($max > 0) {
my $me = sprintf('%.0f',$max/2) ;
$le = $me if ($me < $le);
}
hilodb_select('DESC',$le,\@db);
if ($max > 0) {
my $me = int($max/2);
$le = $me if ($me < $le);
}
my @dbx = ();
hilodb_select('ASC',$le,\@dbx);
push @db, reverse @dbx;
}
return unless (@db);
my $tc = 0;
foreach my $i (@db) {
print sprintf("%s %5u <%04u> %-15.3f %-55s %s\n",
time2str('%Y-%m-%d %H:%M:%S',$i->{t}),($now-$i->{t})/(60*60*24),
$i->{i},$i->{h},$i->{f},$i->{r});
$tc ++;
}
print sprintf("%-10s %u\n",'hilodb:',$tc);
}
sub hilodb_show {
my $id = shift;
print "HILDODB:$id\n";
my $st = $sqldb->prepare(sql_translate("SELECT * FROM hiloscores WHERE hilo_id=$id"));
return unless ($st);
$st->execute;
my @res = $st->fetchrow_array;
$st->finish;
return unless (@res);
print "\n";
print sprintf("%s (%u)\n",time2str('%Y-%m-%d %H:%M:%S',$res[1]),(time-$res[1])/(60*60*24));
if ($res[3]) {
print "Spam\n\n";
} else {
print "Ham\n\n";
}
print "$res[6]\n";
print "Names: $res[4]\n\n";
print "$res[5]\n";
print $res[7];
}
sub db_counts {
unless (@_) {
return db_counts(
'dcdb' => 'dictionary',
'rdb' => 'relaylist',
'nsdb' => 'nospamlist',
'gdb' => 'greylist',
'msgdb' => 'messages',
'outdb' => 'sentout',
'smtpdb' => 'smtpcheck',
'expndb' => 'expncheck',
'ghdb' => 'hostlist',
'mxdb' => 'mxcheck',
'scdb' => 'sendercheck',
'pgpdb' => 'pgpring',
'gsnddb' => 'generatedsenders',
'avcdb' => 'virusresults',
'hilodb' => 'hiloscores',
'logs' => 'logs',
'ts' => 'times',
);
}
my $c = 0;
while (@_) {
my $dbh = uc(shift @_);
my $dbn = shift @_;
next unless ($dbh && $dbn);
my $cnt = sql_select_one("SELECT count(*) FROM $dbn");
return unless (defined($cnt));
print sprintf("%-10s %u\n",$dbh,$cnt);
$c += $cnt;
}
print sprintf("%-10s %u\n",'',$c);
}
#***********************************************************************
# Main.
#***********************************************************************
read_cfg_cfg();
$sqldb = DBI->connect($database_spec,$database_user,$database_pass,{RaiseError=>0,AutoCommit=>1});
die ('Database?') unless ($sqldb);
for (my $i=0;$i<@ARGV;$i++) {
print "\n" if ($i);
my $p = $ARGV[$i];
$p =~ s/[-_+\s]//g;
if ($p =~ /^(ma)?x[=:](\d*)$/i) {
$max = $2;
$max = -1 unless ($max);
} elsif ($p =~ /^a(ll)?$/i) {
$all = 1;
} elsif ($p =~ /^r(elay)?db$/i) {
rdb_list();
} elsif ($p =~ /^no?s(pam)?db$/i) {
nsdb_list();
} elsif ($p =~ /^(g|grey)?h(ost)?db$/i) {
hdb_list();
} elsif ($p =~ /^mxdb$/i) {
mxdb_list();
} elsif ($p =~ /^s(ender)?c(heck)?db$/i) {
scdb_list();
} elsif ($p =~ /^g(r[ae]y)?db$/i) {
gdb_list();
} elsif ($p =~ /^d(c|ict)?db$/i) {
dcdb_list();
} elsif ($p =~ /^pgpdb$/i) {
pgpdb_list();
} elsif ($p =~ /^m(sg|essages?)db$/i) {
msgldb_list();
} elsif ($p =~ /^t(ime)?s(tamps?)?db$/i) {
tsdb_list();
} elsif ($p =~ /^(s|sent)?o(ut)?db$/i) {
outdb_list();
} elsif ($p =~ /^s(mtp)?db$/i) {
smtpdb_list();
} elsif ($p =~ /^e(xpn)?db$/i) {
expndb_list();
} elsif ($p =~ /^g(en(erated)?)?se?nd(ers?)?(db)?$/i) {
gsnddb_list();
} elsif ($p =~ /^a(nti)?v(ir(us)?)?c(ache)?(db)?$/i) {
virus_list();
} elsif ($p =~ /^h(i|igh)?l(o|ow)?(s|score)?db$/i) {
hilodb_list();
} elsif ($p =~ /^h(i|igh)?l(o|ow)?(s|score)?db:(\d+)$/i) {
hilodb_show($4);
} elsif ($p =~ /^l(ogs?)?db$/i) {
logs_list();
} elsif ($p =~ /^c(ounts?)?$/i) {
db_counts();
} else {
print "? $p\n";
}
}
$sqldb->disconnect();
$sqldb = undef;
(2008-01-11)