[prev in list] [next in list] [prev in thread] [next in thread] 

List:       amavis-user
Subject:    Re: [AMaViS-user] custom sql logging
From:       Mark Martinec <Mark.Martinec+amavis () ijs ! si>
Date:       2006-01-27 0:32:03
Message-ID: 200601270132.04011.Mark.Martinec+amavis () ijs ! si
[Download RAW message or body]

zapster,

> Is it possible to get Amavisd-new to log to my mysql db with a
> custom sql query?
> My goal is to log all transactions that are made on my mailserver.
> Both spam, virus and "normal" mails.
> But I would like to log it in my own homemade db structure.

SQL clauses are configurable, you can adapt them to your schema
to a certain extent. Defaults are listed in amavisd.conf-default,
but you can assign (in amavisd.conf) other values to keys in %sql_clause:

%sql_clause = (
  'sel_policy' => \$sql_select_policy,
  'sel_wblist' => \$sql_select_white_black_list,
  'sel_adr' =>
    'SELECT id FROM maddr WHERE email=?',
  'ins_adr' =>
    'INSERT INTO maddr (email, domain) VALUES (?,?)',
  '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=?, dsn_sent=?, spam_level=?,'.
    ' message_id=?, from_addr=?, subject=? WHERE mail_id=?',
  'ins_rcp' =>
    'INSERT INTO msgrcpt (mail_id, rid, ds, rs, bl, wl, bspam_level,'.
    ' smtp_resp) VALUES (?,?,?,?,?,?,?,?)',
  'ins_quar' =>
    'INSERT INTO quarantine (mail_id, chunk_ind, mail_text) VALUES (?,?,?)',
  'sel_quar' =>
    'SELECT mail_text FROM quarantine WHERE mail_id=? ORDER BY chunk_ind',
);

If you need more thorough changes, you may have to tweak subroutines
save_info_preliminary and save_info_final.

  Mark


-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
[prev in list] [next in list] [prev in thread] [next in thread]