Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
Hum, this trigger idea seems better and less time of coding! From: dbmail-dev-boun...@dbmail.org [mailto:dbmail-dev-boun...@dbmail.org] On Behalf Of Jake Anderson Sent: sexta-feira, 19 de Junho de 2009 14:07 To: DBMAIL Developers Mailinglist Subject: Re: [Dbmail-dev] New idea 2, log received messages by lmtpd Jorge Bastos wrote: 1-So for now I'm going to see a way to get this info from postfix, whether grep the logs or something else. 2-the views are important, 'cause if the message was received by postfix and not by dbmail, something eat it! After wake up this morning I was thinking, the view's won't work, 'cause if I want to see an email from one month ago, and the user uses POP3, that email won't exist anymore on the database. Apart from the postfix log, for my case, email will only be eat by clamd is it has virus. That's why I think the best way is to make dbmail-lmtpd log this. What do you say? ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev Rather than the view, perhaps create another table in the same format then use a trigger that fires when a new mail is inserted to SELECT foo INTO that table for posterity. I can see benefit to that and its an optional extra that people can turn on at will and with some tweaking can be made specific down to the individual user level. If all the info is in the database then a "stats" application is going to be easy to hack up and if you were really keen you could have a "top" style application grouping by domains etc showing bandwidth per minute etc. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
Jorge Bastos wrote: 1-So for now I'm going to see a way to get this info from postfix, whether grep the logs or something else. 2-the views are important, 'cause if the message was received by postfix and not by dbmail, something eat it! After wake up this morning I was thinking, the view's won't work, 'cause if I want to see an email from one month ago, and the user uses POP3, that email won't exist anymore on the database. Apart from the postfix log, for my case, email will only be eat by clamd is it has virus. That's why I think the best way is to make dbmail-lmtpd log this. What do you say? ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev Rather than the view, perhaps create another table in the same format then use a trigger that fires when a new mail is inserted to SELECT foo INTO that table for posterity. I can see benefit to that and its an optional extra that people can turn on at will and with some tweaking can be made specific down to the individual user level. If all the info is in the database then a "stats" application is going to be easy to hack up and if you were really keen you could have a "top" style application grouping by domains etc showing bandwidth per minute etc. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
> > And it's more easier for sure, I just don't know how to code it. > > Why easier? "less /var/log/mail" is quicker than login to db and select > I guess? Ok true but, not better when you go to search something, that's the capabilities of having a DB and a flat file. > > I'm with you on this feature, but your arguments are ... irreproducible > for me. It's a english problem, I don't know how to express myself better :P ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
On Freitag 19 Juni 2009 Jorge Bastos wrote: > But, I think it's much more robust I never had problems with postfix logs. > and secure (in the way that we get the right data) > to this log's in dbmail-lmtpd than greping the > postfix logs. I don't understand this. You mean, you got other data if postfix logs and into dbmail? > And it's more easier for sure, I just don't know how to code it. Why easier? "less /var/log/mail" is quicker than login to db and select I guess? I'm with you on this feature, but your arguments are ... irreproducible for me. mfg zmi -- // Michael Monnerie, Ing.BSc- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
> On Freitag 19 Juni 2009 Jorge Bastos wrote: > > That's why I think the best way is to make dbmail-lmtpd log this. > > What do you say? > > As long as we don't loose e-mails on the way from postfix to dbmail- > lmtpd, there's not much urge to implement this IMHO. > The only thing ATM that makes this interesting is to log the subject, > as you can search for that. But I don't need that really, it's just > something I miss a bit in the postfix log. > What could also be nice is to log where the e-mail was originally sent > "to", because sometimes with aliases and BCC spammers can hide where > they sent the mail to, and I can't see anymore the original e-mail they > used. Hum I understand your point of view. But, I think it's much more robust and secure (in the way that we get the right data) to this log's in dbmail-lmtpd than greping the postfix logs. And it's more easier for sure, I just don't know how to code it. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
On Freitag 19 Juni 2009 Jorge Bastos wrote: > That's why I think the best way is to make dbmail-lmtpd log this. > What do you say? As long as we don't loose e-mails on the way from postfix to dbmail- lmtpd, there's not much urge to implement this IMHO. The only thing ATM that makes this interesting is to log the subject, as you can search for that. But I don't need that really, it's just something I miss a bit in the postfix log. What could also be nice is to log where the e-mail was originally sent "to", because sometimes with aliases and BCC spammers can hide where they sent the mail to, and I can't see anymore the original e-mail they used. One more thing that would make it nice is I could do simple stats per domain, to see how many e-mail addresses are active, how many mails and how many bytes received... that's the most interesting thing I could get out of this - and that could make it worthy. After all, it wouldn't use a lot of space or hit performance, so a +1 from me. If developers have time of course ;-) mfg zmi -- // Michael Monnerie, Ing.BSc- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 signature.asc Description: This is a digitally signed message part. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
> 1-So for now I'm going to see a way to get this info from postfix, > whether grep the logs or something else. > 2-the views are important, 'cause if the message was received by > postfix and not by dbmail, something eat it! > After wake up this morning I was thinking, the view's won't work, 'cause if I want to see an email from one month ago, and the user uses POP3, that email won't exist anymore on the database. Apart from the postfix log, for my case, email will only be eat by clamd is it has virus. That's why I think the best way is to make dbmail-lmtpd log this. What do you say? ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
> Jorge, you are one lazy bum. > :P Sorry Paul! > All of this information (except the delivery status) is already there > in > the database for you to retrieve. After taking a look at this, and think a bit more, this doesn't quite solve my problem/what I want. One important part is to get the information from postfix if the message was really received by him. 1-So for now I'm going to see a way to get this info from postfix, whether grep the logs or something else. 2-the views are important, 'cause if the message was received by postfix and not by dbmail, something eat it! > > datetime (dbmail_physmessage.internal_date) > from (dbmail_fromfield) > to (dbmail_tofield) > subject (dbmail_subjectfield) > msg_size (dbmail_physmessage.rfcsize) > dest_user (dbmail_mailbox.owner_idnr) > status (delivered/deleted by a sieve script/something else) > > So try this: > > create view received_messages as > select u.userid,p.internal_date,p.rfcsize, > s.subjectfield,f.fromfield,t.tofield > from dbmail_physmessage p > join dbmail_subjectfield s on s.physmessage_id=p.id > join dbmail_fromfield f on f.physmessage_id=p.id > join dbmail_tofield t on t.physmessage_id=p.id > join dbmail_messages m on m.physmessage_id=p.id > join dbmail_mailboxes b on m.mailbox_idnr=b.mailbox_idnr > join dbmail_users u on b.owner_idnr=u.user_idnr; > > And so a select on the new view. Will work just fine on (> 2.2 and < > 2.3.6) with a minor modification: > > create view received_messages as > select u.userid,p.internal_date,p.rfcsize, > s.subjectfield,f.fromaddr,t.toaddr > from dbmail_physmessage p > join dbmail_subjectfield s on s.physmessage_id=p.id > join dbmail_fromfield f on f.physmessage_id=p.id > join dbmail_tofield t on t.physmessage_id=p.id > join dbmail_messages m on m.physmessage_id=p.id > join dbmail_mailboxes b on m.mailbox_idnr=b.mailbox_idnr > join dbmail_users u on b.owner_idnr=u.user_idnr; > > So make sure you drop this view before upgrading to 2.3.6! > > And no: I *really* don't want to keep tabs on messages > deleted/forwarded > by sieve! Grep the logs! > > > > Jorge Bastos wrote: > > Hi, > > > > John & Paul, > > > > > > > > I talked about this in the past, funny is that if I had this today it > > were very handy, I had one client that needed this confirmation, of a > > certain email was received by the server cause someone told him that > > had sent it and it wasnt delivered. > > > > > > > > The idea is to create a table and log the received messages by LMTPD, > > something like: > > > > > > > > datetime,from,to,subject (if possible),msg_size,dest_user,status > > (delivered/deleted by a sieve script/something else) > > > > > > > > this should have a parameter to switch on/off in dbmail.conf also. > > > > > > > > John, could you give a hand on this? I have no idea how to do it :P > > > > > > > > > > > > Jorge, > > > > > > - > --- > > > > ___ > > Dbmail-dev mailing list > > Dbmail-dev@dbmail.org > > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev > > > -- > > Paul Stevens paul at nfg.nl > NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 > The Netherlandshttp://www.nfg.nl > ___ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] New idea 2, log received messages by lmtpd
Jorge, you are one lazy bum. All of this information (except the delivery status) is already there in the database for you to retrieve. datetime (dbmail_physmessage.internal_date) from (dbmail_fromfield) to (dbmail_tofield) subject (dbmail_subjectfield) msg_size (dbmail_physmessage.rfcsize) dest_user (dbmail_mailbox.owner_idnr) status (delivered/deleted by a sieve script/something else) So try this: create view received_messages as select u.userid,p.internal_date,p.rfcsize, s.subjectfield,f.fromfield,t.tofield from dbmail_physmessage p join dbmail_subjectfield s on s.physmessage_id=p.id join dbmail_fromfield f on f.physmessage_id=p.id join dbmail_tofield t on t.physmessage_id=p.id join dbmail_messages m on m.physmessage_id=p.id join dbmail_mailboxes b on m.mailbox_idnr=b.mailbox_idnr join dbmail_users u on b.owner_idnr=u.user_idnr; And so a select on the new view. Will work just fine on (> 2.2 and < 2.3.6) with a minor modification: create view received_messages as select u.userid,p.internal_date,p.rfcsize, s.subjectfield,f.fromaddr,t.toaddr from dbmail_physmessage p join dbmail_subjectfield s on s.physmessage_id=p.id join dbmail_fromfield f on f.physmessage_id=p.id join dbmail_tofield t on t.physmessage_id=p.id join dbmail_messages m on m.physmessage_id=p.id join dbmail_mailboxes b on m.mailbox_idnr=b.mailbox_idnr join dbmail_users u on b.owner_idnr=u.user_idnr; So make sure you drop this view before upgrading to 2.3.6! And no: I *really* don't want to keep tabs on messages deleted/forwarded by sieve! Grep the logs! Jorge Bastos wrote: > Hi, > > John & Paul, > > > > I talked about this in the past, funny is that if I had this today it > were very handy, I had one client that needed this confirmation, of a > certain email was received by the server ‘cause someone “told” him that > had sent it and it wasn’t delivered. > > > > The idea is to create a table and log the received messages by LMTPD, > something like: > > > > datetime,from,to,subject (if possible),msg_size,dest_user,status > (delivered/deleted by a sieve script/something else) > > > > this should have a parameter to switch on/off in dbmail.conf also. > > > > John, could you give a hand on this? I have no idea how to do it :P > > > > > > Jorge, > > > > > ___ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev -- Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlandshttp://www.nfg.nl ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
[Dbmail-dev] New idea 2, log received messages by lmtpd
Hi, John & Paul, I talked about this in the past, funny is that if I had this today it were very handy, I had one client that needed this confirmation, of a certain email was received by the server 'cause someone "told" him that had sent it and it wasn't delivered. The idea is to create a table and log the received messages by LMTPD, something like: datetime,from,to,subject (if possible),msg_size,dest_user,status (delivered/deleted by a sieve script/something else) this should have a parameter to switch on/off in dbmail.conf also. John, could you give a hand on this? I have no idea how to do it :P Jorge, ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
