DMARC aggregate report to DB parser tool.
© 2016, GPLv2, Sander Smeenk, [email protected]
This tool will help with automatic parsing of DMARC RUA 'Aggregate Reports' to a MySQL database.
It can monitor a Maildir, for instance, and process reports that come in on the fly.
This script can monitor a directory for changes to files. It does this
by utilising the Filesys::Notify::Simple Perl module. This
module will, by itself, monitor a directory by performing directory
scans periodically. You can get more efficient monitor methods by
installing the matching implementation for your OS:
| OS | Module |
|---|---|
| Linux | Linux::Inotify2 |
| OSX | Mac::FSEvents |
| BSD | Filesys::Notify::KQueue |
| Windows | Win32::ChangeNofity |
You'll also need the following Perl modules installed:
| Perl Module | Ubuntu / Debian Package |
|---|---|
| Filesys::Notify::Simple | libfilesys-notify-simple-perl |
| MIME::Parser | libmime-tools-perl |
| PerlIO::gzip | libperlio-gzip-perl |
| Getopt::Std | perl-modules |
| MIME::Words | libmime-tools-perl |
| XML::Simple | libxml-simple-perl |
| DBD::mysql | libdbd-mysql-perl |
| DBI | libdbi-perl |
And the 'gzip' (usually default) and 'unzip' (usually
non-default) packages/binaries. Make sure your version of unzip supports
the -p option to make unzip extract files to pipe, no messages.
Or adjust the code accordingly to your unzip binary. ;)
ng-rddmarc [opts] <filenames ...>
ng-rddmarc [opts] <directories ...>
Options:
-h show this text
-s show MySQL create table statements
-d nn print debug info, nn = level,
-1 = all, 10 = sensible, 11 = xml dumps
-r replace existing reports in database
-w block and monitor for new files in path(s) to process
-n do not initially scan files in path(s) specified
-m detect Maildir: process files and monitor in new/ dir,
move completed to cur/ dir.
-c if path is Maildir, also process cur/ on startup
-C xx/yy Use file 'xx/yy' as configuration file.
Not specifying the -C option will try these in order:
$HOME/.ng-rddmarc.conf
/etc/ng-rddmarc/ng-rddmarc.conf
The following 'conf:' lines can be stored in a configuration file:
-U xxx database user conf: $opt_U = "username";
-P xxx database password conf: $opt_P = "password";
-N xxx database name conf: $opt_N = "dbname";
-H xxx database hostname conf: $opt_H = "hostname";
-p enable persistent database connection
defaults to connect for each report processed (0)
conf: $opt_p = 1;
See the contrib/dot_ng-rddmarc.conf file for example
config. Place it somewhere the script expects it (see --help)
or use the -C parameter to point to your file and configure
the database connection. Configuration options specified via command
line override those specified in the configuration file.
A valid database environment is needed for operation.
Default startup behaviour is to process all files specified on the
command line and those found within directories specified on the
command line. There will be no recursion into subdirectories.
Use the -n option to prevent this script from scanning for
files within directories on startup. It will then only process files
explicitly specified on the command line.
The tool will try to distinguish between XML formatted report files, with
the first five bytes <?xml, and assume all other files are
MIME compatible message/rfc822-formatted 'email files', as
commonly found in Maildir structures, where the XML-report is a properly
formatted base64 encoded attachment.
When a Maildir/ structure is specified, and -m is used, by
default only the new/ directory is scanned for files to
process. The files will be moved to cur/ after successful
processing. If you also want to scan and process all files in
cur/ on startup, you can use the -c
option. Using -w with -m will cause the
new/ directory to be monitored for events, even if you
specified the parent directory on the commandline.
Use the -w flag in combination with at least one directory
on the command line to enable 'waiting for new events' in said
directory. You could run ng-rddmarc as a daemon and process
messages automatically as it 'watches' your (-m)Maildir/ for
new files to process and move to cur/ (marking it 'Old
Mail' in your mail client).
Other non-Maildir/ directories may be mixed with Maildir/ type directories and will be processed and watched as normal directories.
The -d flag enables verbose output. There is no real logic
to the verbose levels at this moment. A value of 10 shows
everything but dumps of the parsed XML-bodies and is most useful if
something is going awry.
This script was developed with a MySQL database as backend. But it uses
Perl DBI and it should thus be trivial to change to a different database
backend. The MySQL create statements can be obtained by running
ng-rddmarc -s. See this simplistic example on how to create
the database:
$ ssh root@databaseserver
$ mysql
mysql> CREATE DATABASE dmarc DEFAULT CHARACTER SET utf8;
mysql> GRANT ALL ON dmarc.* TO 'dmarc'@'%' IDENTIFIED BY 'yourplainpassword';
mysql> FLUSH PRIVILEGES;
mysql> exit
Bye
$ ng-rddmarc -s | mysql -u dmarc -p -h databaseserver dmarcIt is always safer to use ng-rddmarc -s to obtain the most up-to-date SQL-schema.
CREATE TABLE report (
serial int(10) unsigned NOT NULL AUTO_INCREMENT,
mindate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
maxdate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
domain varchar(255) NOT NULL,
org varchar(255) NOT NULL,
reportid varchar(255) NOT NULL,
PRIMARY KEY (serial),
UNIQUE KEY domain (domain,reportid)
) charset=utf8;
CREATE TABLE rptrecord (
db_idx int(10) unsigned NOT NULL AUTO_INCREMENT,
serial int(10) unsigned NOT NULL,
ip int(10) unsigned,
ip6 binary(16),
rcount int(10) unsigned NOT NULL,
disposition enum('none','quarantine','reject'),
reason varchar(255),
dkimdomain varchar(255),
dkimresult enum('none','pass','fail','neutral','policy','temperror','permerror'),
spfdomain varchar(255),
spfresult enum('none','neutral','pass','fail','softfail','temperror','permerror'),
PRIMARY KEY (db_idx),
KEY serial (serial,ip),
KEY serial6 (serial,ip6)
) charset=utf8;We all love examples.
Considering a valid configuration file exists...
$ ng-rddmarc -m /home/user/Maildir/.site.dmarc-rua/new
$ ng-rddmarc -m /home/user/Maildir/.site.dmarc-rua/cur
$ ng-rddmarc -m /home/user/Maildir/.site.dmarc-rua/All of these do the same, they scan the messages in new/
and move them to cur/ since -m was used and
this was in fact a Maildir/-structure.
$ ng-rddmarc -w -m /home/user/Maildir/.site.dmarc-rua/This will process all files in new/ and move them to
cur/ when finished. It will then wait for new files to
appear in the new/ directory and process them, indefinitely.
You could run this from init/upstart/systemd as a 'service'.
$ ng-rddmarc -r ./report.xmlThis processes the ./report.xml file, replacing
records in the database if any exists, and quits.
$ ng-rddmarc /opt/dump/xmls/Processes all files in /opt/dump/xmls/, skips
reports that are already in the database, and quits.
$ ng-rddmarc -w /opt/dir ./messagefile.emlProcesses all files in /opt/dir and
./messagefile.eml, after this it will wait for new files in
/opt/dir indefinitely.
... etc.
© 2016, GPLv2, Sander Smeenk [email protected]
Some parts of this script are literal copies of, or are roughly based
on, work by Taughannock Networks, licensed as follows:
Copyright 2012-2013, Taughannock Networks. All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.