search

bnchdan / MailLog2MySQL

MailLog2MySQL is a Python script for parsing and storing email server log (from Dovecot and Postfix) data into a MySQL database. This can be useful for monitoring email traffic, analyzing email server performance, and generating reports. Inspired by mysqmail-dovecot-logger and mysqmail-postfix-logger with some improvements and an API interface.
0 stars 0 forks source link

more examples? #2

Open DavidGoodwin opened 1 year ago

DavidGoodwin commented 1 year ago

any chance of a unit test or perhaps documentation showing how a semi-standard postfix maillog gets broken up into database record(s)?

e.g. given something like : 

Sep 21 12:37:57 mail postfix/smtpd[3679404]: DA5C280C1: client=localhost[127.0.0.1], orig_queue_id=C1C978075, orig_client=mail-sgaapc01olkn2021.outbound.protection.outlook.com[40.92.53.21]
Sep 21 12:37:57 mail postsrsd[3679405]: srs_forward: <sender@outlook.com> rewritten as <SRS0=QD0P=FF=outlook.com=sender@mydomain.co.uk>
Sep 21 12:37:57 mail postsrsd[3679405]: srs_forward: <SRS0=QD0P=FF=outlook.com=sender@mydomain.co.uk> not rewritten: Valid SRS address for <sender@outlook.com>
Sep 21 12:37:57 mail postfix/cleanup[3679400]: DA5C280C1: message-id=<some-message-identiifer@something.apcprd03.prod.outlook.com>
Sep 21 12:37:58 mail postfix/smtpd[3679404]: disconnect from localhost[127.0.0.1] ehlo=1 xforward=1 mail=1 rcpt=1 data=1 quit=1 commands=6
Sep 21 12:37:58 mail postfix/smtp[3679401]: C1C978075: to=<sales@mydomain.co.uk>, relay=127.0.0.50[127.0.0.50]:10024, delay=1.9, delays=0.29/0/0/1.6, dsn=2.0.0, status=sent (250 2.0.0 from MTA(smtp:[127.0.0.1]:10025): 250 2.0.0 Ok: queued as DA5C280C1)
Sep 21 12:37:58 mail postfix/qmgr[3119543]: C1C978075: removed
Sep 21 12:37:58 mail postfix/virtual[3679408]: DA5C280C1: to=<david@mydomain.co.uk>, orig_to=<sales@mydomain.co.uk>, relay=virtual, delay=0.76, delays=0.72/0.01/0/0.03, dsn=2.0.0, status=sent (delivered to maildir)
Sep 21 12:37:58 mail postfix/qmgr[3119543]: DA5C280C1: removed

it would produce a log message(s) like X and Y ...

bnchdan commented 1 year ago

for:

Sep 22 04:04:15 mail postfix/cleanup[50522]: QueueID: message-id=<MsgID>
Sep 22 04:04:15 mail postfix/qmgr[861]: QueueID: from=<user@test.ro>, size=7028, nrcpt=1 (queue active)
Sep 22 04:04:15 mail postfix/local[50524]: QueueID: to=<user@test.ro>, relay=local, delay=0.12, delays=0.12/0/0/0, dsn=2.0.0, status=sent
Sep 22 04:04:15 mail postfix/qmgr[861]: QueueID: removed

output:

*************************** 1. row ***************************
              id: 681
           month: Sep
             day: 22
            hour: 04
         mail_to: user
  mail_to_domain: test.ro
       mail_from: user
mail_from_domain: test.ro
          status: sent
           msgid: MsgID

https://github.com/bnchdan/MailLog2MySQL/blob/main/test.sh