[Maia-users] HOW TO: Delete High Score SPAM (amavis $sa_quarantine_cutoff_level fix)

[Michiel van den Berg]

Nice, this was exactly what I was looking for !!

the feature that should be implemented in the next version in my opionion !

Michiel

Sebastian A. Aresca wrote:
> I've testing Maia MailGuard for 2 weeks and I found the problem:
> "Too many emails on SPAM quarantine".
> So I built this little script and then cron it to remove records with score greater than X.
>
> ###################################################################################################################################
> #!/bin/bash
>
> echo -n "Deleting records ... "
> mysql -v -D maia -u root -B -e "DELETE FROM maia_mail_recipients WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_sa_rules_triggered WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_viruses_detected WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_banned_attachments_found WHERE mail_id IN (SELECT * FROM HighScore)"
> mysql -v -D maia -u root -B -e "DELETE FROM maia_mail WHERE score > 20"
> echo "OK"
>
> ###################################################################################################################################
>
> This should remove all the spam with score greater than 20.
>
> Before run it you should:
>
> - Login to your mysql:
> # mysql -u root (-p -> if you have set a password for root)
> - Select maia data base:
> mysql> use maia;
> - IMPORTANT: create score index.
> mysql> CREATE INDEX maia_mail_idx_score ON maia_mail (score);
> - Create HighScore view.
> mysql> CREATE VIEW HighScore AS SELECT `maia_mail`.`id` AS `id` FROM `maia_mail` WHERE (`maia_mail`.`score` > 15);
>
>
> Creating index in large database could take a long time. About 10 minutes with 50.000 records running over a P IV dual core 2Gb Ram.
>
> Try it.
>
> Sebastian A. Aresca
>
> _______________________________________________
> Maia-users mailing list
> Maia-users at renaissoft.com
> http://www.renaissoft.com/mailman/listinfo/maia-users
>