Perl-based email traffic logger

Strontium · 26. Aug. 2024

Ein Ansatz zur Protokollierung des Mailverkehrsvolumens in einer Datenbank.

mailtrafficd.pl

Perl:

#!/usr/bin/perl
#
# The mailtrafficd program is a Perl-based email traffic logger
# that integrates with Postfix to monitor email traffic on a server.
# It records details such as the sender, recipient, timestamp,
# size of each email and the number of recipients processed by the server
# into a MySQL database.
#
# The program operates as a Milter (Mail Filter) and can be run in
# the background. It uses a persistent database connection to
# efficiently log emails. The program can also toggle terminal
# output for logging, allowing for easy monitoring or quiet operation.
#########################################################################
# Prerequisites:
#
# sudo apt install libsendmail-pmilter-perl
#
# Configure MySQL credentials:
# nano ./lib/MailTrafficD/Config.pm
#
# Postfix:
# sudo nano /etc/postfix/main.cf
#
# with RSPAMD
# smtpd_milters     = inet:localhost:11025 inet:localhost:11332
# non_smtpd_milters = inet:localhost:11025 inet:localhost:11332
#
# without RSPAMD
# smtpd_milters     = inet:localhost:11025
# non_smtpd_milters = inet:localhost:11025
#
# Restart Postfix:
# sudo service postfix restart
#
# Start Milter:
# nohup ./mailtrafficd.pl & >/dev/null &
#
# Stop Milter:
# kill $(pgrep -f mailtrafficd.pl)
#
# Folder structure:
#
# mailtrafficd/
# ├── lib/
# │   ├── MailTrafficD.pm
# │   ├── MailTrafficD/
# │   │   ├── Config.pm
# │   │   ├── DB.pm
# │   │   └── Milter.pm
# └── mailtrafficd.pl
#
# Don't run that script as root!
###############################################################
use strict;
use warnings;
use lib 'lib';
use IO::Socket::INET;
use MailTrafficD;
use MailTrafficD::Milter;
use MailTrafficD::DB;
use MailTrafficD::Config qw($milter_port);

sub check_port {
    my $port = shift;
    my $socket = IO::Socket::INET->new(
        Proto    => 'tcp',
        LocalAddr => 'localhost',
        LocalPort => $port,
        Listen    => 1,
        Reuse     => 1,
    );

    if ($socket) {
        close($socket);
        return 0;
    } else {
        return 1;
    }
}

if (check_port($milter_port)) {
    die "Port $milter_port is already in use. Program will terminate.\n";
}

# Get the database handle from MailTrafficD
my $dbh = MailTrafficD::get_dbh();

if ($dbh) {
    MailTrafficD::DB::create_table_if_not_exists($dbh);
    MailTrafficD::Milter::initialize_milter();  # Initialize the Milter
} else {
    die "Failed to connect to the database, terminating program.\n";
}

# At the end of the program, disconnect the DB handle
END {
    MailTrafficD::disconnect_dbh();
}

MailTrafficD.pm

Perl:

package MailTrafficD;

use strict;
use warnings;
use MailTrafficD::DB;

my $dbh;

sub get_dbh {
    return $dbh if $dbh;

    # Establish the connection only if it doesn't already exist
    $dbh = MailTrafficD::DB::connect(0);
    return $dbh;
}

sub disconnect_dbh {
    if ($dbh) {
        $dbh->disconnect();
        undef $dbh;
    }
}

1;

Config.pm

Perl:

package MailTrafficD::Config;

use strict;
use warnings;
use Exporter 'import';

# Export only the necessary variables
our @EXPORT_OK = qw($db_name $db_host $db_user $db_pass $enable_terminal_output $milter_port);

# Database connection details
our $db_name    = "database_name";
our $db_host    = "localhost";
our $db_user    = "database_user";
our $db_pass    = "database_password";

# Milter configuration
our $milter_port = 11025;

# Option to enable or disable terminal output
our $enable_terminal_output = 0;  # Set to 1 to enable, 0 to disable

1;

DB.pm

Perl:

package MailTrafficD::DB;

use strict;
use warnings;
use DBI;
use Try::Tiny;
use MailTrafficD::Config qw($db_name $db_host $db_user $db_pass $enable_terminal_output);

sub connect {
    my $dbh;

    try {
        $dbh = DBI->connect(
            "DBI:mysql:database=$db_name;host=$db_host",
            $db_user, $db_pass,
            {
                'RaiseError' => 1,
                'PrintError' => 0,
                'mysql_auto_reconnect' => 1  # Enable auto-reconnect
            }
        );
        print "Successfully connected to database $db_name.\n" if $enable_terminal_output;
    } catch {
        die "Error connecting to database: $_";
    };

    return $dbh;
}

sub create_table_if_not_exists {
    my $dbh = shift;
    my $table_exists = 0;

    try {
        my $sth = $dbh->prepare("SHOW TABLES LIKE 'mail_trafficd'");
        $sth->execute();
        $table_exists = $sth->fetch();
    } catch {
        warn "Error checking for existing table 'mail_trafficd': $_" if $enable_terminal_output;
    };

    unless ($table_exists) {
        my $sql = qq{
            CREATE TABLE mail_trafficd (
                id INT AUTO_INCREMENT PRIMARY KEY,
                timestamp DATETIME NOT NULL,
                sender VARCHAR(255) NOT NULL,
                recipient VARCHAR(255) NOT NULL,
                recipient_count INT NOT NULL,
                email_size INT NOT NULL
            );
        };
        try {
            $dbh->do($sql);
            print "Table 'mail_trafficd' successfully created.\n" if $enable_terminal_output;
        } catch {
            warn "Error creating table 'mail_trafficd': $_" if $enable_terminal_output;
        };
    } else {
        print "Table 'mail_trafficd' already exists.\n" if $enable_terminal_output;
    }
}

1;

Milter.pm

Perl:

package MailTrafficD::Milter;

use strict;
use warnings;
use Sendmail::PMilter qw(:all);
use MailTrafficD;
use MailTrafficD::Config qw($enable_terminal_output $milter_port);
use POSIX qw(strftime);
use Try::Tiny;

sub initialize_milter {
    my $milter = Sendmail::PMilter->new();
  
    $milter->setconn("inet:$milter_port\@localhost");
        $milter->register('mailtrafficd', {
        envfrom => \&cb_envfrom,
        envrcpt => \&cb_envrcpt,
        header  => \&cb_header,
        body    => \&cb_body,
        eom     => \&cb_eom,
    });

    try {
        $milter->main();
    } catch {
        die "Error starting Milter service: $_";
    };
}

sub cb_envfrom {
    my ($ctx, $envfrom) = @_;
    $ctx->setpriv({ email_size => 0, envfrom => $envfrom });
    return SMFIS_CONTINUE;
}

sub cb_envrcpt {
    my ($ctx, $envrcpt) = @_;
    my $email_data = $ctx->getpriv();
    push @{$email_data->{envrcpt}}, $envrcpt;
    $ctx->setpriv($email_data);
    return SMFIS_CONTINUE;
}

sub cb_header {
    my ($ctx, $headerf, $headerv) = @_;
    my $email_data = $ctx->getpriv();
    $email_data->{email_size} += length($headerf) + length($headerv) + 4;
    $ctx->setpriv($email_data);
    return SMFIS_CONTINUE;
}

sub cb_body {
    my ($ctx, $body_chunk) = @_;
    my $email_data = $ctx->getpriv();
    $email_data->{email_size} += length($body_chunk);
    $ctx->setpriv($email_data);
    return SMFIS_CONTINUE;
}

sub cb_eom {
    my ($ctx) = @_;
    my $email_data = $ctx->getpriv();
    my $email_size = $email_data->{email_size};
    my $envfrom = $email_data->{envfrom};
    my $envrcpt = join(" ", @{$email_data->{envrcpt}});
    my $recipient_count = scalar @{$email_data->{envrcpt}};

    $envfrom =~ s/[<>]//g;
    $envrcpt =~ s/[<>]//g;

    my $dbh = MailTrafficD::get_dbh();  # Get the global DB handle
    my $datetime = strftime("%Y-%m-%d %H:%M:%S", localtime);

    try {
        my $sth = $dbh->prepare("INSERT INTO mail_trafficd (timestamp, sender, recipient, email_size, recipient_count) VALUES (?, ?, ?, ?, ?)");
        $sth->execute($datetime, $envfrom, $envrcpt, $email_size, $recipient_count);

        if ($enable_terminal_output) {
            print "Database entry added: timestamp=$datetime, sender=$envfrom, recipient=$envrcpt, email_size=$email_size bytes, recipient_count=$recipient_count\n";
        }
    } catch {
        warn "Error adding entry to the database: $_" if $enable_terminal_output;
    };

    return SMFIS_CONTINUE;
}

1;

nowayback · 26. Aug. 2024

oder du nutzt einfach rspamd... da haste die statistiken direkt im dashboard

Strontium · 27. Aug. 2024

Ja den RSPAMD hab ich eh installiert, aber dort sehe ich unter "Status", "Throughput" oder "History" nicht wie viel Emailtraffic eine Domain macht.

Beziehungsweise ist die ISPConfig-Tabelle "mail_traffic" bei mir immer leer und folglich auch unter "Email | Mailbox Traffic" alles leer.

Till · 27. Aug. 2024

Vielleicht müssen wir mal sehen wie und wo Rspamd die Statistik speichert (Redis?) und ob man diese dann von ISPConfig auslesen lassen kann. Denn einen zusätzlichen Milter Filter würde ich nach Möglichkeit gern vermeiden.

Till · 27. Aug. 2024

Habe mal etwas geschaut, ich denke es gibt da zwei besser Lösungen. Die erste wäre dass wir das Logformat von rspamd anpassen, so dass es die Größe der Emails entält, dann könnten wir das aus dem rspamd.log raus parsen. Die Alternative wäre dass wir ein Lua Script für Rspamd schreiben, welches ein Traffic.log schreibt.

Till · 27. Aug. 2024

ChatGPT

A conversational AI system that listens, learns, and challenges

chatgpt.com

Strontium · 9. Sep. 2024

Zitat von Till:
zusätzlichen Milter Filter würde ich nach Möglichkeit gern vermeiden

Stimmt.

Aber wenn man den Milter um die Felder "Header" und "Body" erweitert hat man eine Kopie der ein- und ausgehenden Mails in der MySQL-Tabelle.

Zitat von Till:
Logformat von rspamd anpassen, so dass es die Größe der Emails entält

Der ISPConfig Autoinstaller hat mir das Logformat von Rspamd in der Datei /etc/rspamd/logging.inc so installiert:

Code:

level = "info";
log_format =<<EOD
id: <$mid>,$if_qid{ qid: <$>,}$if_ip{ ip: $,}$if_user{ user: $,}$if_smtp_from{ from: <$>,}
(default: $is_spam ($action): [$scores] [$symbols_scores_params]),
len: $len, time: $time_real, dns req: $dns_req,
digest: <$digest>$if_smtp_rcpts{, rcpts: <$>}$if_mime_rcpts{, mime_rcpts: <$>}$if_filename{, file: $}$if_forced_action{, forced: $}$if_settings_id{, settings_id: $}
EOD

Eigentlich alles da zum Parsen von /var/log/rspamd/rspamd.log. Man muss nur darauf achten dass logrotate die Inodes der Logdatei beibehält, dies geschieht mit der Direktive "copytruncate" in /etc/logrotate.d/rspamd.

Dann kann man mit der Datei "rspamdlogger.pl" die Logdatei parsen und in der MySQL-Tabelle "mail_rspamd" in Echtzeit mitloggen und damit Mailtrafficstatistiken berechnen.

rspamdlogger.pl

Perl:

#!/usr/bin/perl
use strict;
use warnings;
use File::Tail;
use DBI;

#############################
# Prerequisites
#
#     sudo apt install libfile-tail-perl
#     sudo apt install libdbi-perl libdbd-mysql-perl
#
#############################
#     cat /etc/logrotate.d/rspamd
#       .
#       copytruncate
#       .
########################
# Start
#     nohup sudo ./rspamdlogger.pl > /dev/null 2>&1 &
#
# Stop
#     sudo pkill -f rspamdlogger.pl
########################

# MySQL connection details
my $db_name = "database_name";
my $db_host = "localhost";
my $db_user = "database_user";
my $db_pass = "database_password";

# Create database connection
my $dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { RaiseError => 1, PrintError => 0, mysql_auto_reconnect => 1 })
    or die "Unable to connect to the MySQL database: $DBI::errstr";

# Create table if not exists
my $create_table_sql = qq{
    CREATE TABLE IF NOT EXISTS mail_rspamd (
        id INT AUTO_INCREMENT PRIMARY KEY,
        timestamp VARCHAR(255),
        qid VARCHAR(255),
        ip VARCHAR(255),
        user VARCHAR(255),
        `from` VARCHAR(255),
        len INT,
        time DECIMAL(10,3),
        dns_req INT,
        rcpts TEXT,
        rcpts_count INT,
        mime_rcpts TEXT,
        mime_rcpts_count INT
    )
};
$dbh->do($create_table_sql) or die "Unable to create table: $DBI::errstr";

# Path to the Rspamd log file
my $log_file_path = "/var/log/rspamd/rspamd.log";

# Regex to parse the relevant information
my $log_pattern = qr/^(?<timestamp>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}).*qid:\s<(?<qid>[^>]+)>,\sip:\s(?<ip>[\d\.:a-fA-F]+),(?:\suser:\s(?<user>[^,]+),)?\sfrom:\s<(?<from>[^>]+)>,.*len:\s(?<len>\d+),\stime:\s(?<time>[0-9\.]+)ms,\sdns\ req:\s(?<dns_req>\d+),.*\srcpts:\s<(?<rcpts>[^>]+)>,\smime_rcpts:\s<(?<mime_rcpts>[^>]+)>/;

# Error handling for opening the log file
eval {
    my $file = File::Tail->new(name => $log_file_path, maxinterval => 1, interval => 1, tail => 0, reset_tail => 0 )
        or die "Error opening the log file $log_file_path: $!";

    print "Monitoring the file $log_file_path...\n";

    my $insert_sql = qq{
        INSERT INTO mail_rspamd (timestamp, qid, ip, user, `from`, len, time, dns_req, rcpts, rcpts_count, mime_rcpts, mime_rcpts_count)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    };
    my $sth = $dbh->prepare($insert_sql) or die "Unable to prepare SQL statement: $DBI::errstr";

    while (my $line = $file->read) {
        if ($line =~ $log_pattern) {
            # Set default values if fields are undefined
            my $timestamp = $+{timestamp} // '-';
            my $qid = $+{qid} // '-';
            my $ip = $+{ip} // '-';
            my $user = $+{user} // '-';
            my $from = $+{from} // '-';
            my $len = $+{len} // '-';
            my $time = $+{time} // '-';
            my $dns_req = $+{dns_req} // '-';
            my $rcpts = $+{rcpts} // '-';
            my $mime_rcpts = $+{mime_rcpts} // '-';

            # Replace commas with spaces
            $rcpts =~ s/,/ /g;
            $mime_rcpts =~ s/,/ /g;

            # Count the recipients
            my $rcpts_count = scalar split ' ', $rcpts;
            my $mime_rcpts_count = scalar split ' ', $mime_rcpts;

            # Insert data into MySQL
            $sth->execute($timestamp, $qid, $ip, $user, $from, $len, $time, $dns_req, $rcpts, $rcpts_count, $mime_rcpts, $mime_rcpts_count)
                or die "Unable to execute SQL statement: $DBI::errstr";

            # Output for confirmation
            print "Inserted into DB: Timestamp: $timestamp, QID: $qid, IP: $ip, User: $user, From: $from, Len: $len, Time: $time, DNS_Req: $dns_req, Rcpts: $rcpts, Rcpts_count: $rcpts_count, MIME_Rcpts: $mime_rcpts, MIME_rcpts_count: $mime_rcpts_count\n";
        }
    }
};

# Error handling
if ($@) {
    print "An error occurred: $@\n";
}

# Close the database connection
$dbh->disconnect();

Perl-based email traffic logger

Strontium

Member

nowayback

Well-Known Member

Strontium

Member

Till

Administrator

Till

Administrator

Till

Administrator

ChatGPT

Strontium

Member

Werbung

Wir schützen deine Privatsphäre