postfix log in mysql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
k2
Reply | Threaded
Open this post in threaded view
|

postfix log in mysql

k2
Hi postfixers,

We have spam filter servers for our down, 5 of them to be exact. we use
amavisd, bitdefender & clamav for spam and virus filter.

we have a self help portal done in php/mysql for users to manage
whitelist/blacklist etc, now i want to allow users to check there email
logs to they can find if any wanted email is blocked,

so the question is, how can i log postfix to a mysql db where i can write
an interface for users to search for email and see what did the
blocking, such as rbl, amavis etc ?

ive seen some solutions to use syslog in to mysql but i was thinking
something much simpler where i will still have logs in place even if
mysql fails.

rgds


Reply | Threaded
Open this post in threaded view
|

Re: postfix log in mysql

Christian Kivalo


Am 28. August 2017 05:51:10 MESZ schrieb Kev <[hidden email]>:

>Hi postfixers,
>
>We have spam filter servers for our down, 5 of them to be exact. we use
>amavisd, bitdefender & clamav for spam and virus filter.
>
>we have a self help portal done in php/mysql for users to manage
>whitelist/blacklist etc, now i want to allow users to check there email
>logs to they can find if any wanted email is blocked,
>
>so the question is, how can i log postfix to a mysql db where i can
>write
>an interface for users to search for email and see what did the
>blocking, such as rbl, amavis etc ?
>
>ive seen some solutions to use syslog in to mysql but i was thinking
>something much simpler where i will still have logs in place even if
>mysql fails.
Most syslog daemons can write to more than one output stream so besides absorbing your logs with mysql additionally you could keep logging to file and have your logs as normal.
>rgds

--
Christian Kivalo
Reply | Threaded
Open this post in threaded view
|

Re: postfix log in mysql

James Reynolds
In reply to this post by k2
You might want to look into something like the Logstash (https://www.elastic.co/products/logstash).  

James

> On Aug 27, 2017, at 9:51 PM, Kev <[hidden email]> wrote:
>
> Hi postfixers,
>
> We have spam filter servers for our down, 5 of them to be exact. we use
> amavisd, bitdefender & clamav for spam and virus filter.
>
> we have a self help portal done in php/mysql for users to manage
> whitelist/blacklist etc, now i want to allow users to check there email
> logs to they can find if any wanted email is blocked,
>
> so the question is, how can i log postfix to a mysql db where i can write
> an interface for users to search for email and see what did the
> blocking, such as rbl, amavis etc ?
>
> ive seen some solutions to use syslog in to mysql but i was thinking
> something much simpler where i will still have logs in place even if
> mysql fails.
>
> rgds
>
>

k2
Reply | Threaded
Open this post in threaded view
|

Re[2]: postfix log in mysql

k2
> You might want to look into something like the Logstash (https://www.elastic.co/products/logstash).  

looks promising! thanks you!

On Mon, 28 Aug 2017 10:11:14 -0600
James Reynolds <[hidden email]> wrote:

> You might want to look into something like the Logstash (https://www.elastic.co/products/logstash).  
>
> James
>
> > On Aug 27, 2017, at 9:51 PM, Kev <[hidden email]> wrote:
> >
> > Hi postfixers,
> >
> > We have spam filter servers for our down, 5 of them to be exact. we use
> > amavisd, bitdefender & clamav for spam and virus filter.
> >
> > we have a self help portal done in php/mysql for users to manage
> > whitelist/blacklist etc, now i want to allow users to check there email
> > logs to they can find if any wanted email is blocked,
> >
> > so the question is, how can i log postfix to a mysql db where i can write
> > an interface for users to search for email and see what did the
> > blocking, such as rbl, amavis etc ?
> >
> > ive seen some solutions to use syslog in to mysql but i was thinking
> > something much simpler where i will still have logs in place even if
> > mysql fails.
> >
> > rgds
> >
> >


Reply | Threaded
Open this post in threaded view
|

Re: postfix log in mysql

joao
I save correlated logs from postfix (and milter daemons) in mongodb.

https://www.balabit.com/documents/syslog-ng-ose-latest-guides/en/syslog-ng-ose-guide-admin/html/configuring-destinations-mongodb.html
https://www.balabit.com/documents/syslog-ng-ose-latest-guides/en/syslog-ng-ose-guide-admin/html/configuring-pattern-databases.html

In one registry I can get all information about delivery that I need and
nothing more:

{
     "_id" : ObjectId("5991fbb0c2544d511c3a5290"),
     "TO" : "[hidden email]",
     "SUBJECT" :
"KEdSVVBPIE1QRSkgQXJxdWl2byBkZSBSZW1lc3NhIGRvIGRpYTogMTAvMDgvMjAxNw==",
     "STATUS_AT" : ISODate("2017-08-14T19:36:16.000Z"),
     "STATUS" : "sent",
     "SIZE" : 2918,
     "SERVER" : "submitter31.zyx.net",
     "SASLAUTH" : "[hidden email]",
     "RETURNPATH" : "[hidden email]",
     "RELAY_IP" : "168.0.0.0",
     "RELAY_HOST" : "relay.xxxx.com.br",
     "REASON" : "(250 OK id=1dhLEB-03037zm-MD)",
     "QUEUEID_CUST" : "None",
     "QUEUEID" : "024D781B27AC",
     "FROM" : "[hidden email]",
     "DSN" : "2.0.0",
     "DELAY" : "8.6",
     "CLIENTIP" : "177.7.7.7",
     "BEGIN_AT" : ISODate("2017-08-14T19:36:08.000Z")
}

much cleaner and simpler than logstash, either rsyslog and syslog-ng
clients connect to the log server without any other external software
and it is working very well.

Joao Reis.


On 08/29/2017 05:51 AM, Kev wrote:

>> You might want to look into something like the Logstash (https://www.elastic.co/products/logstash).
> looks promising! thanks you!
>
> On Mon, 28 Aug 2017 10:11:14 -0600
> James Reynolds <[hidden email]> wrote:
>
>> You might want to look into something like the Logstash (https://www.elastic.co/products/logstash).
>>
>> James
>>
>>> On Aug 27, 2017, at 9:51 PM, Kev <[hidden email]> wrote:
>>>
>>> Hi postfixers,
>>>
>>> We have spam filter servers for our down, 5 of them to be exact. we use
>>> amavisd, bitdefender & clamav for spam and virus filter.
>>>
>>> we have a self help portal done in php/mysql for users to manage
>>> whitelist/blacklist etc, now i want to allow users to check there email
>>> logs to they can find if any wanted email is blocked,
>>>
>>> so the question is, how can i log postfix to a mysql db where i can write
>>> an interface for users to search for email and see what did the
>>> blocking, such as rbl, amavis etc ?
>>>
>>> ive seen some solutions to use syslog in to mysql but i was thinking
>>> something much simpler where i will still have logs in place even if
>>> mysql fails.
>>>
>>> rgds
>>>
>>>
>