Siguiendo las ideas de Federico esta tarde la he dedicado a hacer un script para el /etc/cron.hourly. Funciona a partir de la base de datos del mailwatch y manda alertas cuando un usuario manda muchos mails por hora, o a muchos remitentes. Si alguien lo quiere aprovechar aquí lo tiene.
#!/bin/bash
# Query Mailwatch database looking for mail abuses, from authenticated senders and relayed servers.
##################
##### CONFIG #####
##################
# This threshold configure the maximum number of recipients per time period
# Max Recipients per mail
TO_THRESHOLD_TO=25
# Max Number of mails for the same account that reach $TO_THRESHOLD_TO
TO_THRESHOLD_FROM=7
TO_INTERVAL="1 HOUR"
# This threshold configure the maximum number of emails for the same account per time period
# Max Mails per account
FROM_THRESHOLD=25
FROM_INTERVAL="1 HOUR"
# Mails to scan:
# Relayed ips: like webmail server or secondary mail server.
REGEX_RELAY_IPS='"78.254.514.*"'
# Authenticated Senders
REGEX_AUTH_HEADER='"Received:.*Authenticated sender: .*"'
# Exceptions for Mailman, Mailer-Daemon and Nagios.
REGEX_MAILMAN_HEADER='"\nX-Mailman-Version: 2.1.11\n"'
# Email alert configuration
PERIOD="Hourly"
# MySQL Connection Options (not implemented)
#MYSQL_HOST=""
#MYSQL_DB=""
#MYSAL_USER=""
#MYSQL_PASS=""
##################
### END CONFIG ###
##################
# Retrieving mailwatch information
sql_result_to=$(mysql mailscanner -e "SET @mytime=(SELECT DATE_FORMAT(SUBDATE(CURRENT_TIMESTAMP(),INTERVAL $TO_INTERVAL),'%H:%i:%s')); SET @mydate=(SELECT DATE_FORMAT(SUBDATE(CURRENT_TIMESTAMP(),INTERVAL $TO_INTERVAL),'%Y-%m-%d')); SELECT from_address, subject FROM maillog WHERE ((date = @mydate AND time > @mytime) OR date > @mydate) AND LENGTH(to_address) - LENGTH(REPLACE(to_address, '@', '')) > $TO_THRESHOLD_TO AND (headers REGEXP $REGEX_AUTH_HEADER OR clientip REGEXP $REGEX_RELAY_IPS) AND NOT headers REGEXP $REGEX_MAILMAN_HEADER ORDER BY from_address;")
sql_result_from=$(mysql mailscanner -e "SET @mytime=(SELECT DATE_FORMAT(SUBDATE(CURRENT_TIMESTAMP(),INTERVAL $FROM_INTERVAL),'%H:%i:%s')); SET @mydate=(SELECT DATE_FORMAT(SUBDATE(CURRENT_TIMESTAMP(),INTERVAL $FROM_INTERVAL),'%Y-%m-%d')); SELECT from_address, subject FROM maillog WHERE ((date = @mydate AND time > @mytime) OR date > @mydate) AND (headers REGEXP $REGEX_AUTH_HEADER OR clientip REGEXP $REGEX_RELAY_IPS) AND NOT headers REGEXP $REGEX_MAILMAN_HEADER AND from_address!=$NAGIOS_FROM AND NOT headers REGEXP $REGEX_MAILER_DAEMON ORDER BY from_address;");
# Formatting the sql output
result_to=$(echo "$sql_result_to"|awk {'print $1'}|grep -v "^from_address.*subject$"|uniq -c);
result_from=$(echo "$sql_result_from"|awk {'print $1'}|grep -v "^from_address.*subject$"|uniq -c);
# Looking for abuses
spamers_to=""
while read line; do
mails=$(echo "$line"|awk {'print $1'});
if [ $mails -gt $TO_THRESHOLD_FROM ]; then
# Subject is only orientative and not necessarily the most repeated, simply is the first one.
posible_subject=$(echo "$sql_result_to"|grep "${line#*' '}"|head -n1)
spamers_to=$(echo -e "${spamers_to}\n$mails $posible_subject");
fi;
done << EOF
$(echo "$result_to");
EOF
spamers_from=""
while read line; do
mails=$(echo "$line"|awk {'print $1'});
if [ $mails -gt $FROM_THRESHOLD ]; then
# Subject is only orientative and not necessarily the most repeated, simply is the first one.
posible_subject=$(echo "$sql_result_from"|grep "${line#*' '}"|head -n1)
spamers_from=$(echo -e "${spamers_from}\n$mails $posible_subject");
fi;
done << EOF
$(echo "$result_from");
EOF
# Sending mail alert
if [[ "$spamers_to" != "" || $spamers_from != "" ]]; then
(
echo "Subject: MAIL ABUSE REPORT - $MAIL_SERVER - $PERIOD report"
echo "To: $EMAIL"
echo ""
if [ "$spamers_to" != "" ]; then
echo "During the last $TO_INTERVAL the following senders have sent at least $TO_THRESHOLD_FROM e-mails to more than $TO_THRESHOLD_TO recipients."
echo "$spamers_to"
echo ""
echo ""
fi;
if [ "$spamers_from" != "" ]; then
echo "During the last $FROM_INTERVAL the following senders have sent at least $FROM_THRESHOLD e-mails."
echo "$spamers_from"
echo ""
fi;
echo ""
) | /usr/sbin/sendmail $EMAIL
fi;
exit 0;
pd. como veis no me avergüenzo de mi pésimo nivel del inglez :P