Loading ...
Sorry, an error occurred while loading the content.
 

improving logging

Expand Messages
  • mancyborg@gmail.com
    Hi is it possible to log what postfix does to mysql ? For example each email sent or received, with sender and receiver, date, status, file length. And even
    Message 1 of 10 , May 26, 2014
      Hi is it possible to log what postfix does to mysql ?
      For example each email sent or received, with sender and receiver, date, status, file length.
      And even better, is it possible to integrate that with BL / SA / AV
      in order to have on the same database row also the blacklist check, spam score and antivirus info ?

      Thanks for your attention,
      regards and have a nice day,
      Mike
    • Robert Schetterer
      ... postfix logs to syslog , syslog may log to sql http://www.rsyslog.com/doc/rsyslog_mysql.html http://loganalyzer.adiscon.com/ more advanced
      Message 2 of 10 , May 26, 2014
        Am 26.05.2014 15:53, schrieb mancyborg@...:
        > Hi is it possible to log what postfix does to mysql ?

        postfix logs to syslog , syslog may log to sql

        http://www.rsyslog.com/doc/rsyslog_mysql.html
        http://loganalyzer.adiscon.com/

        more advanced

        http://graylog2.org/


        > For example each email sent or received, with sender and receiver, date, status, file length.
        > And even better, is it possible to integrate that with BL / SA / AV
        > in order to have on the same database row also the blacklist check, spam score and antivirus info ?
        >
        > Thanks for your attention,
        > regards and have a nice day,
        > Mike
        >



        Best Regards
        MfG Robert Schetterer

        --
        [*] sys4 AG

        http://sys4.de, +49 (89) 30 90 46 64
        Franziskanerstraße 15, 81669 München

        Sitz der Gesellschaft: München, Amtsgericht München: HRB 199263
        Vorstand: Patrick Ben Koetter, Marc Schiffbauer
        Aufsichtsratsvorsitzender: Florian Kirstein
      • Ansgar Wiechers
        ... Postfix logs to syslog and syslog (rsyslog at least) can be configured to write to MySQL instead of files. See [1] for details. [1]
        Message 3 of 10 , May 26, 2014
          On 2014-05-26 mancyborg@... wrote:
          > Hi is it possible to log what postfix does to mysql ?
          > For example each email sent or received, with sender and receiver,
          > date, status, file length.
          > And even better, is it possible to integrate that with BL / SA / AV
          > in order to have on the same database row also the blacklist check,
          > spam score and antivirus info ?

          Postfix logs to syslog and syslog (rsyslog at least) can be configured
          to write to MySQL instead of files. See [1] for details.

          [1] http://www.rsyslog.com/doc/rsyslog_mysql.html

          Regards
          Ansgar Wiechers
          --
          "Abstractions save us time working, but they don't save us time learning."
          --Joel Spolsky
        • Robert Schetterer
          ... also you may have a look to http://logstash.net/ http://8pussy.org/ http://www.postfix.org/addon.html ... Logfile analysis ... Best Regards MfG Robert
          Message 4 of 10 , May 26, 2014
            Am 26.05.2014 16:14, schrieb Robert Schetterer:
            > Am 26.05.2014 15:53, schrieb mancyborg@...:
            >> Hi is it possible to log what postfix does to mysql ?
            >
            > postfix logs to syslog , syslog may log to sql
            >
            > http://www.rsyslog.com/doc/rsyslog_mysql.html
            > http://loganalyzer.adiscon.com/
            >
            > more advanced
            >
            > http://graylog2.org/

            also you may have a look to

            http://logstash.net/
            http://8pussy.org/
            http://www.postfix.org/addon.html
            ...
            Logfile analysis
            ...


            >
            >
            >> For example each email sent or received, with sender and receiver, date, status, file length.
            >> And even better, is it possible to integrate that with BL / SA / AV
            >> in order to have on the same database row also the blacklist check, spam score and antivirus info ?
            >>
            >> Thanks for your attention,
            >> regards and have a nice day,
            >> Mike
            >>
            >
            >
            >
            > Best Regards
            > MfG Robert Schetterer
            >



            Best Regards
            MfG Robert Schetterer

            --
            [*] sys4 AG

            http://sys4.de, +49 (89) 30 90 46 64
            Franziskanerstraße 15, 81669 München

            Sitz der Gesellschaft: München, Amtsgericht München: HRB 199263
            Vorstand: Patrick Ben Koetter, Marc Schiffbauer
            Aufsichtsratsvorsitzender: Florian Kirstein
          • mancyborg@gmail.com
            On Mon, 26 May 2014 16:17:14 +0200 ... Hi yes sure but that would result in having 20 database rows for each email, since that is what happens in the syslog,
            Message 5 of 10 , May 26, 2014
              On Mon, 26 May 2014 16:17:14 +0200
              Ansgar Wiechers <lists@...> wrote:

              > On 2014-05-26 mancyborg@... wrote:
              > > Hi is it possible to log what postfix does to mysql ?
              > > For example each email sent or received, with sender and receiver,
              > > date, status, file length.
              > > And even better, is it possible to integrate that with BL / SA / AV
              > > in order to have on the same database row also the blacklist check,
              > > spam score and antivirus info ?
              >
              > Postfix logs to syslog and syslog (rsyslog at least) can be configured
              > to write to MySQL instead of files. See [1] for details.
              >
              > [1] http://www.rsyslog.com/doc/rsyslog_mysql.html
              >
              > Regards
              > Ansgar Wiechers
              > --
              > "Abstractions save us time working, but they don't save us time learning."
              > --Joel Spolsky


              Hi yes sure but that would result in having 20 database rows for each email, since that is what happens in the syslog,
              each component (postfix, spamassassin, amavis, policyd, ...) writes few lines in the syslog for each email sent or received.

              Also, if aggregating, that information must be correlated.

              Well I guess the answer is no, perhaps qmail does it better ?


              Thanks for supporting,
              regards and have a nice day,
              Mike
            • Klaipedaville on Google
              Create custom logs, instruct postfix to log to your custom logs by giving it the correct new path (instead of the default syslog), then copy-paste the original
              Message 6 of 10 , May 26, 2014
                Create custom logs, instruct postfix to log to your custom logs by giving it the correct new path (instead of the default syslog), then copy-paste the original mysql tables into your custom created logs then add as many custom rows, sql commands as you please or just create totally brand-new databases and tables and there you go, you may log absolutely anything you please, even the current temperature on Mars Smile if that’s your preference. Syslog can also just write to mysql directly, simply add / extend the original with your custom rows. I do custom logging for Dovecot but just in plain text files (not in mysql) for simplicity.
                 
                Regards,
                Dennis.
                 
                 
                Sent: Monday, May 26, 2014 17:14
                Subject: Re: improving logging
                 
                Am 26.05.2014 15:53, schrieb mancyborg@...:
                > Hi is it possible to log
                what postfix does to mysql ?

                postfix logs to syslog , syslog may log to sql

                http://www.rsyslog.com/doc/rsyslog_mysql.html
                http://loganalyzer.adiscon.com/

                more advanced

                http://graylog2.org/


                > For example each email sent
                or received, with sender and receiver, date, status, file length.
                > And
                even better, is it possible to integrate that with BL / SA / AV
                > in order
                to have on the same database row also the blacklist check, spam score and antivirus info ?
                >
                > Thanks for your attention,
                > regards and
                have a nice day,
                > Mike
                >



                Best Regards
                MfG Robert Schetterer

                --
                [*] sys4 AG

                http://sys4.de, +49 (89) 30 90 46 64
                Franziskanerstraße 15, 81669 München

                Sitz der Gesellschaft: München, Amtsgericht München: HRB 199263
                Vorstand: Patrick Ben Koetter, Marc Schiffbauer
                Aufsichtsratsvorsitzender: Florian Kirstein
              • lists@rhsoft.net
                ... *you* need to correlate that that s why the queue-id exists in the logs and if there are serveral servers talking to each other you get as last line in
                Message 7 of 10 , May 26, 2014
                  Am 26.05.2014 16:31, schrieb mancyborg@...:
                  > Hi yes sure but that would result in having 20 database rows for each email, since that is what happens in the syslog,
                  > each component (postfix, spamassassin, amavis, policyd, ...) writes few lines in the syslog for each email sent or received.
                  >
                  > Also, if aggregating, that information must be correlated.
                  > Well I guess the answer is no, perhaps qmail does it better?

                  *you* need to correlate that

                  that's why the queue-id exists in the logs and if there
                  are serveral servers talking to each other you
                  get as last line in your own log even the queue-id
                  of the destination

                  this *can not* be in one line because that is just how
                  email works - a message is accepted, queued, forwarded
                  to filters and back, tried several times if the detsination
                  is not available yet

                  so there is per definition no process knowing the
                  whole flow of a mail from A to Z and given how
                  email works the first queue line may be written
                  now and the final line (sent or bounced) 5 days
                  later
                  ___________________________________________

                  [root@srv-rhsoft:~]$ cat maillog | grep 3gcMzr47KVzBr0x
                  May 26 04:43:04 srv-rhsoft postfix/smtpd[19441]: 3gcMzr47KVzBr0x: client=*********
                  May 26 04:43:04 srv-rhsoft postfix/cleanup[19446]: 3gcMzr47KVzBr0x: message-id=<3gcMzq5pg3z1LHc@********>
                  May 26 04:43:04 srv-rhsoft postfix/qmgr[28478]: 3gcMzr47KVzBr0x: from=**********, size=3265, nrcpt=1 (queue active)
                  May 26 04:43:04 srv-rhsoft postfix/lmtp[19447]: 3gcMzr47KVzBr0x: to=********, relay=127.0.0.1[127.0.0.1]:24,
                  delay=0.44, delays=0.16/0.04/0/0.25, dsn=2.0.0, status=sent (215 Recipient ******* OK)
                  May 26 04:43:04 srv-rhsoft postfix/qmgr[28478]: 3gcMzr47KVzBr0x: removed
                • Robert Schetterer
                  ... i followed some presentation for graylog2 a few weeks ago with big logs prefilter ist your friend, after all it depends in how deep you want to go in
                  Message 8 of 10 , May 26, 2014
                    Am 26.05.2014 16:31, schrieb mancyborg@...:
                    > On Mon, 26 May 2014 16:17:14 +0200
                    > Ansgar Wiechers <lists@...> wrote:
                    >
                    >> On 2014-05-26 mancyborg@... wrote:
                    >>> Hi is it possible to log what postfix does to mysql ?
                    >>> For example each email sent or received, with sender and receiver,
                    >>> date, status, file length.
                    >>> And even better, is it possible to integrate that with BL / SA / AV
                    >>> in order to have on the same database row also the blacklist check,
                    >>> spam score and antivirus info ?
                    >>
                    >> Postfix logs to syslog and syslog (rsyslog at least) can be configured
                    >> to write to MySQL instead of files. See [1] for details.
                    >>
                    >> [1] http://www.rsyslog.com/doc/rsyslog_mysql.html
                    >>
                    >> Regards
                    >> Ansgar Wiechers
                    >> --
                    >> "Abstractions save us time working, but they don't save us time learning."
                    >> --Joel Spolsky
                    >
                    >
                    > Hi yes sure but that would result in having 20 database rows for each email, since that is what happens in the syslog,
                    > each component (postfix, spamassassin, amavis, policyd, ...) writes few lines in the syslog for each email sent or received.
                    >
                    > Also, if aggregating, that information must be correlated.

                    i followed some presentation for graylog2 a few weeks ago
                    with big logs prefilter ist your friend, after all it depends
                    in how deep you want to go in logging and analyse

                    with smaller setups logcheck, pflogsumm and manual grep log files etc is
                    mostly enough

                    >
                    > Well I guess the answer is no, perhaps qmail does it better ?

                    no idea, i quit with qmail years ago

                    >
                    >
                    > Thanks for supporting,
                    > regards and have a nice day,
                    > Mike
                    >



                    Best Regards
                    MfG Robert Schetterer

                    --
                    [*] sys4 AG

                    http://sys4.de, +49 (89) 30 90 46 64
                    Franziskanerstraße 15, 81669 München

                    Sitz der Gesellschaft: München, Amtsgericht München: HRB 199263
                    Vorstand: Patrick Ben Koetter, Marc Schiffbauer
                    Aufsichtsratsvorsitzender: Florian Kirstein
                  • mancyborg@gmail.com
                    On Mon, 26 May 2014 16:43:45 +0200 ... Thanks for the info, appreciated. Just wondering, is there any packaged / commercial version of the email stack on
                    Message 9 of 10 , May 26, 2014
                      On Mon, 26 May 2014 16:43:45 +0200
                      "lists@..." <lists@...> wrote:

                      >
                      > Am 26.05.2014 16:31, schrieb mancyborg@...:
                      > > Hi yes sure but that would result in having 20 database rows for each email, since that is what happens in the syslog,
                      > > each component (postfix, spamassassin, amavis, policyd, ...) writes few lines in the syslog for each email sent or received.
                      > >
                      > > Also, if aggregating, that information must be correlated.
                      > > Well I guess the answer is no, perhaps qmail does it better?
                      >
                      > *you* need to correlate that
                      >
                      > that's why the queue-id exists in the logs and if there
                      > are serveral servers talking to each other you
                      > get as last line in your own log even the queue-id
                      > of the destination
                      >
                      > this *can not* be in one line because that is just how
                      > email works - a message is accepted, queued, forwarded
                      > to filters and back, tried several times if the detsination
                      > is not available yet
                      >
                      > so there is per definition no process knowing the
                      > whole flow of a mail from A to Z and given how
                      > email works the first queue line may be written
                      > now and the final line (sent or bounced) 5 days
                      > later
                      > ___________________________________________
                      >
                      > [root@srv-rhsoft:~]$ cat maillog | grep 3gcMzr47KVzBr0x
                      > May 26 04:43:04 srv-rhsoft postfix/smtpd[19441]: 3gcMzr47KVzBr0x: client=*********
                      > May 26 04:43:04 srv-rhsoft postfix/cleanup[19446]: 3gcMzr47KVzBr0x: message-id=<3gcMzq5pg3z1LHc@********>
                      > May 26 04:43:04 srv-rhsoft postfix/qmgr[28478]: 3gcMzr47KVzBr0x: from=**********, size=3265, nrcpt=1 (queue active)
                      > May 26 04:43:04 srv-rhsoft postfix/lmtp[19447]: 3gcMzr47KVzBr0x: to=********, relay=127.0.0.1[127.0.0.1]:24,
                      > delay=0.44, delays=0.16/0.04/0/0.25, dsn=2.0.0, status=sent (215 Recipient ******* OK)
                      > May 26 04:43:04 srv-rhsoft postfix/qmgr[28478]: 3gcMzr47KVzBr0x: removed


                      Thanks for the info, appreciated.

                      Just wondering, is there any 'packaged' / 'commercial' version of the email stack on linux, suitable for an ISP with around 3K email accounts ?


                      Best Regards,
                      Mike
                    • Robert Schetterer
                      ... there are a few enterprise mail implementations based on postfix dovecot etc http://kolab.org/ http://www.zarafaserver.de/ use google for more some good
                      Message 10 of 10 , May 26, 2014
                        Am 26.05.2014 17:03, schrieb mancyborg@...:
                        > On Mon, 26 May 2014 16:43:45 +0200
                        > "lists@..." <lists@...> wrote:
                        >
                        >>
                        >> Am 26.05.2014 16:31, schrieb mancyborg@...:
                        >>> Hi yes sure but that would result in having 20 database rows for each email, since that is what happens in the syslog,
                        >>> each component (postfix, spamassassin, amavis, policyd, ...) writes few lines in the syslog for each email sent or received.
                        >>>
                        >>> Also, if aggregating, that information must be correlated.
                        >>> Well I guess the answer is no, perhaps qmail does it better?
                        >>
                        >> *you* need to correlate that
                        >>
                        >> that's why the queue-id exists in the logs and if there
                        >> are serveral servers talking to each other you
                        >> get as last line in your own log even the queue-id
                        >> of the destination
                        >>
                        >> this *can not* be in one line because that is just how
                        >> email works - a message is accepted, queued, forwarded
                        >> to filters and back, tried several times if the detsination
                        >> is not available yet
                        >>
                        >> so there is per definition no process knowing the
                        >> whole flow of a mail from A to Z and given how
                        >> email works the first queue line may be written
                        >> now and the final line (sent or bounced) 5 days
                        >> later
                        >> ___________________________________________
                        >>
                        >> [root@srv-rhsoft:~]$ cat maillog | grep 3gcMzr47KVzBr0x
                        >> May 26 04:43:04 srv-rhsoft postfix/smtpd[19441]: 3gcMzr47KVzBr0x: client=*********
                        >> May 26 04:43:04 srv-rhsoft postfix/cleanup[19446]: 3gcMzr47KVzBr0x: message-id=<3gcMzq5pg3z1LHc@********>
                        >> May 26 04:43:04 srv-rhsoft postfix/qmgr[28478]: 3gcMzr47KVzBr0x: from=**********, size=3265, nrcpt=1 (queue active)
                        >> May 26 04:43:04 srv-rhsoft postfix/lmtp[19447]: 3gcMzr47KVzBr0x: to=********, relay=127.0.0.1[127.0.0.1]:24,
                        >> delay=0.44, delays=0.16/0.04/0/0.25, dsn=2.0.0, status=sent (215 Recipient ******* OK)
                        >> May 26 04:43:04 srv-rhsoft postfix/qmgr[28478]: 3gcMzr47KVzBr0x: removed
                        >
                        >
                        > Thanks for the info, appreciated.
                        >
                        > Just wondering, is there any 'packaged' / 'commercial' version of the email stack on linux, suitable for an ISP with around 3K email accounts ?

                        there are a few "enterprise" mail implementations based on postfix
                        dovecot etc

                        http://kolab.org/
                        http://www.zarafaserver.de/

                        use google for more


                        some good how to build

                        https://www.exratione.com/2012/05/a-mailserver-on-ubuntu-1204-postfix-dovecot-mysql/

                        3k is big but not that much.....

                        >
                        >
                        > Best Regards,
                        > Mike
                        >



                        Best Regards
                        MfG Robert Schetterer

                        --
                        [*] sys4 AG

                        http://sys4.de, +49 (89) 30 90 46 64
                        Franziskanerstraße 15, 81669 München

                        Sitz der Gesellschaft: München, Amtsgericht München: HRB 199263
                        Vorstand: Patrick Ben Koetter, Marc Schiffbauer
                        Aufsichtsratsvorsitzender: Florian Kirstein
                      Your message has been successfully submitted and would be delivered to recipients shortly.