I have not been satisfied with the Mail Manager user interface, as some data is not included, some data is hard to read, the query capabilities are limited, and the data cannot be exported for further analysis. GDPR created lots of interest in mandatory TLS, so it was interesting to investigate how one proves that messages are being transmitted with an acceptable ciphersuite. I was also interested in the question of how often DKIM signatures are used, and whether they validate.
The log file has all of the data needed to answer these questions, but parsing it is a bit of a challenge. After slogging through the process, I decided to document my results.
My discussion will use the following example log entries. I have extracted all of the log entries related to a single message, and then sanitized the data. In an actual log file, log entries for other messages can be interspersed with each other. This means that physical proximity can be expected, but physical adjacency cannot.
Once I understood how the log entries related to each other, and how the different elements in a log message were labelled, I was able to implement code to parse the file into a structured database. I used four tables: one each for exim-in, smtpd, and exim-out, plus one for DKIM entries. Multiple data records are consolidated into a single row for each message process. Because the message formats have so little consistency, the code to do this is a bit ugly, so it is not posted. Send me a PM if you want a copy of the code, which is a SQL stored procedure and related table definitions.
Thank you for breaking down the log file format. [Y] This really helped me in writing an application that creates an overview of communication patterns from SG e-mail log files.
In case anyone is interested: https://gitlab.com/rbrt-weiler/sophos-sg-smtp-logparser
Hallo Robert and welcome to the UTM Community!
Very cool. Thanks for your contribution.
Cheers - Bob