Tracking which account is sending spam on a plesk server

This is not an easy task without knowing a few tricks as the log files are not an awful lot of help.   Providing you have the Plesk grey listing switched on and you know a little SQL and PHP the task is not that hard.

sqlite3 /var/lib/plesk/mail/greylist/data.db 'select * from data'

The above command will provide you with a list of senders, recipients and IP address.  I have written a couple of scripts which monitor this database every 5 minutes and extracts spammer signatures which then get emailed to me.  I usually catch them within 10-15 minutes of starting their run these days.

Here is a list of the columns in the database;

sqlite> PRAGMA table_info(data);
0|remoteIP|VARCHAR(39)|1||0
1|mailFrom|VARCHAR(255)|1||0
2|rcptTo|VARCHAR(255)|1||0
3|blockedCount|INTEGER|1||0
4|passCount|INTEGER|1||0
5|creationTime|INTEGER|1||0
6|lastUpdate|INTEGER|1||0
7|greyExpires|INTEGER|1||0

Using the above and some data from the headers from one of the spam emails you can quickly extract the sender.

If the spammer is changing the from address so it doesn’t match an account on the server you can filter the logs as follows;

cat /usr/local/psa/var/log/maillog | grep "smtp_auth" | awk '/logged in from/ {print $8"\t"$14}' | sort -u -k1 | awk '{ print $1 }' | sort | uniq -c |  sed -e 's/^[ \t]*//' | awk '$1 >= 10'

This command is for qmail and it filters out senders who have logged into your server,  it then cleans up the list, sorts it, counts by IP and only lists those who have logged in more than 10 times.  You can modify this line to suite your needs.

For postfix this command can give you a few clues;

grep 'failed mail authenticatication attempt for user\|SASL PLAIN authentication failed:\|SASL LOGIN authentication failed:\|Invalid mail address' /usr/local/psa/var/log/maillog

Remember you can also view the password of accounts on a plesk server by using the following command.

/usr/local/psa/admin/bin/mail_auth_view