On Tue, April 3, 2007 14:33, Mark Martinec wrote:
> Tom,
>
>
>> Perhaps a common question, but I am interested in doing some logging on
>> e-mails which gets blocked due to SPAM or VIRUS.
>>
>> I am running amavis in a cluster behind a loadbalancer, so I need some
>> central point to collect these data. Preferably the simplest way would
>> be to insert a row into a MySQL database each time a BLOCK occurs, and I
>> could then use these data to generate some statistics.
>
> Does existing SQL logging (tables: msgs, msgrcpt, maddr) not provide
> the information you need? For each message it stores a content type (Virus,
> Spam, ...), spam score, sender, recipients, timestamp,
> size, host name of the reporting amavisd, ... See README_FILES/README.sql
Obviously it does, but it's too much data to store and scan, considering
the amount of mails that comes through our system.
The optimal format would be to store date, time, server, action and spam
sore / virus name
I tried doing:
%sql_clause = (
'sel_policy' => \$sql_select_policy,
'sel_wblist' => \$sql_select_white_black_list,
'sel_adr' => undef,
'ins_adr' => undef,
'ins_msg' =>
'INSERT INTO msgs (mail_id, secret_id, am_id, time_num, time_iso, sid,'.
' policy, client_addr, size, host) VALUES (?,?,?,?,?,?,?,?,?,?)',
'upd_msg' =>
'UPDATE msgs SET content=?, quar_type=?, quar_loc=?, dsn_sent=?,'.
' spam_level=?, message_id=?, from_addr=?, subject=? WHERE mail_id=?',
'ins_rcp' => undef,
'ins_quar' => undef,
'sel_quar' => undef,
'sel_penpals' => undef,
);
But that doesn't work, just throws errors when mail comes in:
TROUBLE in process_request: sql exec: err=16, S1000, DBD::mysql::st
bind_param failed: Illegal parame
Even if it did work, it's still two SQL queries per mail, one being an
update, but that's something I can live with - I think.
--
Tom
(Resent to list, due to wrong sender mail)
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
