3 # Copyright (c) 2010-2012, 2014, 2015, The Trusted Domain Project.
6 # Script to age out OpenDMARC aggregate report data
24 my $progname = basename
($0);
25 my $version = "1.3.1";
39 my $def_dbhost = "localhost";
40 my $def_dbname = "opendmarc";
41 my $def_dbuser = "opendmarc";
42 my $def_dbpasswd = "opendmarc";
43 my $def_dbport = "3306";
50 my $dbscheme = "mysql";
58 ### NO user-serviceable parts beyond this point
63 print STDERR
"$progname: usage: $progname [options]\n";
64 print STDERR
"\t--alltables expire rows from all tables\n";
65 print STDERR
"\t--dbhost=host database host [$def_dbhost]\n";
66 print STDERR
"\t--dbname=name database name [$def_dbname]\n";
67 print STDERR
"\t--dbpasswd=passwd database password [$def_dbpasswd]\n";
68 print STDERR
"\t--dbport=port database port [$def_dbport]\n";
69 print STDERR
"\t--dbuser=user database user [$def_dbuser]\n";
70 print STDERR
"\t--expire=days expiration time, in days [$def_maxage]\n";
71 print STDERR
"\t--help print help and exit\n";
72 print STDERR
"\t--verbose verbose output\n";
73 print STDERR
"\t--version print version and exit\n";
76 # parse command line arguments
77 my $opt_retval = &Getopt
::Long
::GetOptions
('alltables!' => \
$alltables,
78 'dbhost=s' => \
$dbhost,
79 'dbname=s' => \
$dbname,
80 'dbpasswd=s' => \
$dbpasswd,
81 'dbport=s' => \
$dbport,
82 'dbuser=s' => \
$dbuser,
83 'expire=i' => \
$maxage,
84 'help!' => \
$helponly,
85 'verbose!' => \
$verbose,
86 'version!' => \
$showversion,
89 if (!$opt_retval || $helponly)
105 print STDOUT
"$progname v$version\n";
110 if (!defined($dbhost))
112 if (defined($ENV{'OPENDMARC_DBHOST'}))
114 $dbhost = $ENV{'OPENDMARC_DBHOST'};
118 $dbhost = $def_dbhost;
122 if (!defined($dbname))
124 if (defined($ENV{'OPENDMARC_DB'}))
126 $dbname = $ENV{'OPENDMARC_DB'};
130 $dbname = $def_dbname;
134 if (!defined($dbpasswd))
136 if (defined($ENV{'OPENDMARC_PASSWORD'}))
138 $dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
142 $dbpasswd = $def_dbpasswd;
146 if (!defined($dbport))
148 if (defined($ENV{'OPENDMARC_PORT'}))
150 $dbport = $ENV{'OPENDMARC_PORT'};
154 $dbport = $def_dbport;
158 if (!defined($dbuser))
160 if (defined($ENV{'OPENDMARC_USER'}))
162 $dbuser = $ENV{'OPENDMARC_USER'};
166 $dbuser = $def_dbuser;
170 if (!defined($maxage))
172 if (defined($ENV{'OPENDMARC_MAXAGE'}))
174 $maxage = $ENV{'OPENDMARC_MAXAGE'};
178 $maxage = $def_maxage;
185 print STDERR
"$progname: invalid expiration time\n";
195 print STDERR
"$progname: started at " . localtime() . "\n";
198 my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
199 ";host=" . $dbhost . ";port=" . $dbport;
201 $dbi_h = DBI
->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError
=> 0 });
202 if (!defined($dbi_h))
204 print STDERR
"$progname: unable to connect to database: $DBI::errstr\n";
210 print STDERR
"$progname: connected to database\n";
219 print STDERR
"$progname: expiring messages older than $maxage day(s)\n";
222 $dbi_s = $dbi_h->prepare("DELETE FROM messages WHERE date <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY)");
223 $rows = $dbi_s->execute($maxage);
226 print STDERR
"$progname: DELETE failed: " . $dbi_h->errstr;
235 print STDOUT
"$progname: no rows deleted\n";
239 print STDOUT
"$progname: $rows row(s) deleted\n";
249 $dbi_s = $dbi_h->prepare("SELECT MIN(id) FROM messages");
250 if (!$dbi_s->execute)
252 print STDERR
"$progname: SELECT failed: " . $dbi_h->errstr;
258 while ($dbi_a = $dbi_s->fetchrow_arrayref())
260 $minmsg = $dbi_a->[0];
264 # We might have emptied the messages table
268 if (!defined($minmsg))
270 $dbi_s = $dbi_h->prepare("SELECT COUNT(id) FROM messages");
271 if (!$dbi_s->execute)
273 print STDERR
"$progname: SELECT failed: " . $dbi_h->errstr;
279 while ($dbi_a = $dbi_s->fetchrow_arrayref())
281 $rowcount = $dbi_a->[0];
286 if (defined($rowcount) && $rowcount == 0)
288 $dbi_s = $dbi_h->prepare("TRUNCATE TABLE signatures");
291 print STDERR
"$progname: TRUNCATE failed: " . $dbi_h->errstr;
307 print STDERR
"$progname: expiring signatures on expired messages (id < $minmsg)\n";
310 $dbi_s = $dbi_h->prepare("DELETE FROM signatures WHERE message < ?");
311 $rows = $dbi_s->execute($minmsg);
314 print STDERR
"$progname: DELETE failed: " . $dbi_h->errstr;
323 print STDOUT
"$progname: no rows deleted\n";
327 print STDOUT
"$progname: $rows row(s) deleted\n";
335 # Expire request data
340 print STDERR
"$progname: expiring request data older than $maxage days\n";
343 $dbi_s = $dbi_h->prepare("DELETE FROM requests WHERE lastsent <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY) AND NOT lastsent = '1971-01-01 00:00:01'");
344 $rows = $dbi_s->execute($maxage);
347 print STDERR
"$progname: DELETE failed: " . $dbi_h->errstr . "\n";
356 print STDOUT
"$progname: no rows deleted\n";
360 print STDOUT
"$progname: $rows row(s) deleted\n";
370 print STDERR
"$progname: expiring unneeded domain data\n";
373 $dbi_s = $dbi_h->prepare("DELETE FROM domains WHERE id NOT IN
374 (SELECT DISTINCT domain FROM requests) AND id NOT IN
375 (SELECT DISTINCT from_domain FROM messages) AND id NOT IN
376 (SELECT DISTINCT env_domain FROM messages) AND id NOT IN
377 (SELECT DISTINCT policy_domain FROM messages) AND id NOT IN
378 (SELECT DISTINCT domain FROM signatures)");
379 $rows = $dbi_s->execute();
382 print STDERR
"$progname: DELETE failed: " . $dbi_h->errstr . "\n";
391 print STDOUT
"$progname: no rows deleted\n";
395 print STDOUT
"$progname: $rows row(s) deleted\n";
401 print STDERR
"$progname: expiring unneeded IP data\n";
404 $dbi_s = $dbi_h->prepare("DELETE FROM ipaddr WHERE id NOT IN
405 (SELECT DISTINCT ip FROM messages)");
406 $rows = $dbi_s->execute();
409 print STDERR
"$progname: DELETE failed: " . $dbi_h->errstr . "\n";
418 print STDOUT
"$progname: no rows deleted\n";
422 print STDOUT
"$progname: $rows row(s) deleted\n";
428 print STDERR
"$progname: expiring unneeded reporter data\n";
431 $dbi_s = $dbi_h->prepare("DELETE FROM reporters WHERE id NOT IN
432 (SELECT DISTINCT reporter FROM messages)");
433 $rows = $dbi_s->execute();
436 print STDERR
"$progname: DELETE failed: " . $dbi_h->errstr . "\n";
445 print STDOUT
"$progname: no rows deleted\n";
449 print STDOUT
"$progname: $rows row(s) deleted\n";
460 print STDERR
"$progname: terminating at " . localtime() . "\n";