Page 1 of 1

Spammer Alert

Posted: 14 Jul 2015 02:16
by ethandee178
I have written a script that will be executed under cron.hourly.
It checks the spam from the last hour against a database of domains that are handled by your email server. It then sends you an email if someone from that domain is spamming. There is a lot of room for this to grow. But this is the simplest version i could make. There is a table under the mailscanner database called Domains. It has a column called domain that contains the list of your email server's domains.
You must have mailutils installed.
Below is the bash script.

#!/bin/bash

username=root
password=`grep MYSQLROOTPWD /etc/EFA-Config | sed 's/.*://'`
database=mailscanner
sendto=admin@mail.mail

mysql -u $username -p$password -D $database -e \
"SELECT from_address,COUNT(*) FROM maillog \
WHERE isspam=1 AND DATE_SUB(NOW(),INTERVAL 1 HOUR) <= timestamp \
AND from_domain IN (SELECT domain from Domains where 1) \
GROUP BY from_address ORDER BY COUNT(*) DESC LIMIT 50 \
INTO OUTFILE '/tmp/senders' FIELDS TERMINATED BY ',' \
ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

while read sender; do
if [ $sender != "" ]
then
user=`echo $sender | sed 's/\,.*$//g'`
offenses=`echo $sender | sed 's/.*\,//g'`
echo User: $user has recently been sending spam. $offenses messages have been reported as spam. | mail -s "Possible Account Hack" $sendto
fi
done < /tmp/senders

rm /tmp/senders

Re: Spammer Alert

Posted: 14 Jul 2015 04:03
by shawniverson
:text-bravo:

Re: Spammer Alert

Posted: 24 Jul 2015 01:42
by ethandee178
You'll want to change cur_date() to now(). Other wise it won't calculate for the hour and you'll get notices all day.

Re: Spammer Alert

Posted: 24 Jul 2015 03:25
by ethandee178
Here is a much more updated version that gives you more info in the email. The below is a bash script run hourly:

#!/bin/bash

username=root
password=`grep MYSQLROOTPWD /etc/EFA-Config | sed 's/.*://'`
database=mailscanner

########mysql query count
mysql -u $username -p$password -D $database -e \
"SELECT from_address,COUNT(*) FROM maillog \
WHERE isspam=1 AND DATE_SUB(now(),INTERVAL 1 HOUR) <= timestamp \
AND from_domain IN (SELECT domain from Domains where 1) \
GROUP BY from_address ORDER BY COUNT(*) DESC LIMIT 50 \
INTO OUTFILE '/tmp/senders' FIELDS TERMINATED BY ',' \
ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
#########end mysql query

#########mysql query list
mysql -u $username -p$password -D $database -e \
"SELECT id,from_address,to_address,clientip,sascore,date,time FROM maillog \
WHERE isspam=1 AND DATE_SUB(now(),INTERVAL 1 HOUR) <= timestamp \
AND from_domain IN (SELECT domain from Domains where 1) \
INTO OUTFILE '/tmp/spamlist' FIELDS TERMINATED BY ' ' \
ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
########end mysql query

while read sender; do

if [ $sender != "" ]
then
user=`echo $sender | sed 's/\,.*$//g' | sed 's/\"//g'`
offenses=`echo $sender | sed 's/.*\,//g' | sed 's/\"//g'`
counter=0
echo User: $user has recently been sending spam. > spammail.txt
echo $offenses messages have been reported as spam. >> spammail.txt
for item in $( cat /tmp/spamlist ); do
item=`echo $item | sed 's/\"//g'`
counter=$[$counter +1]
case $counter in
1)
Message_ID=$item
;;
2)
From_address=$item
;;
3)
To_address=$item
;;
4)
IP=$item
;;
5)
Score=$item
;;
6)
date=$item
;;
7)
time=$item
counter=0
if [ $From_address == $user ]
then
echo >> spammail.txt
echo " "ID: $Message_ID, Date: $date $time, Score: $Score >> spammail.txt
echo " "From: $From_address, To: $To_address, IP: $IP >> spammail.txt
echo >> spammail.txt
fi
;;
esac
done
cat spammail.txt | mail -s "Possible Account Hack" youremail@address.here
fi
done < /tmp/senders

rm /tmp/senders
rm /tmp/spamlist
rm spammail.txt

Re: Spammer Alert

Posted: 25 Aug 2015 08:21
by pdwalker
Useful.

Added to the wiki

Re: Spammer Alert

Posted: 20 Apr 2016 21:08
by Rob.M.P
Please advise on how I can integrate this to our E.F.A system.

Is this a new file that needs creating or the text copied into an existing file?

Re: Spammer Alert

Posted: 21 Apr 2016 12:48
by ethandee178
This is '.sh' file you must create and reference it in the cron table.
Copy the contents into a script and add execute permissions.

Re: Spammer Alert

Posted: 22 Dec 2016 19:23
by c0mputerking
I get some errors with this script maybe it is to old? I checked and i do not have Domains just like the error states should i have it?

./spamalert.sh
ERROR 1146 (42S02) at line 1: Table 'mailscanner.Domains' doesn't exist
ERROR 1146 (42S02) at line 1: Table 'mailscanner.Domains' doesn't exist
./spamalert.sh: line 72: /tmp/senders: No such file or directory
rm: cannot remove `/tmp/senders': No such file or directory
rm: cannot remove `/tmp/spamlist': No such file or directory
rm: cannot remove `spammail.txt': No such file or directory


mysql> show tables;
+-----------------------+
| Tables_in_mailscanner |
+-----------------------+
| audit_log |
| autorelease |
| blacklist |
| inq |
| maillog |
| mcp_rules |
| mtalog |
| mtalog_ids |
| outq |
| sa_rules |
| saved_filters |
| spamscores |
| user_filters |
| users |
| whitelist |
+-----------------------+
15 rows in set (0.00 sec)

Re: Spammer Alert

Posted: 22 Dec 2016 19:57
by ethandee178
This script is still working for me.
What version are you on?
The domain table is populated by the Transport settings menu under mail settings.

Re: Spammer Alert

Posted: 23 Dec 2016 05:21
by c0mputerking
I am on the latest version 3.0.1.5 and i do have a bunch of domains in the the mail settings transport settings not sure why i do not have the table in my database

Re: Spammer Alert

Posted: 23 Dec 2016 14:06
by ethandee178
:think:
If you want to pm me. I can help u look.

Re: Spammer Alert

Posted: 14 Feb 2017 17:55
by cdburgess75
I have the same issue the last guy had, no table named "Domain"

Re: Spammer Alert

Posted: 14 Feb 2017 18:07
by ethandee178
This is interesting to me.
I'm not sure if the updates have moved some tables around or how your install differs from mine.
I'd be happy to join a web meeting if you'd like to see if I can figure it out.
PM me if interested.

Re: Spammer Alert

Posted: 03 Mar 2018 21:17
by froman
same problem here.


#mysql -u root -p sdfsjgsgslgrwñelrkwre-D mailscanner -e 'SELECT from_address,COUNT(*) FROM maillog WHERE isspam=1 AND DATE_SUB(now(),INTERVAL 1 HOUR) <= timestamp AND from_domain IN (SELECT domain from Domains where 1) GROUP BY from_address ORDER BY COUNT(*) DESC LIMIT 50 INTO OUTFILE '\''/tmp/senders'\'' FIELDS TERMINATED BY '\'','\'' ENCLOSED BY '\''"'\'' LINES TERMINATED BY '\''\n'\'';'
ERROR 1146 (42S02) at line 1: Table 'mailscanner.Domains' doesn't exist

Re: Spammer Alert

Posted: 05 Mar 2018 03:29
by pdwalker
froman,

I very strongly suggest you don't publish your mysql root password in a public forum.

Please edit your post and change that password to <password>.

Re: Spammer Alert

Posted: 05 Mar 2018 17:51
by budy
But first, change it on the database!

Re: Spammer Alert

Posted: 06 Mar 2018 01:19
by froman
pdwalker wrote: 05 Mar 2018 03:29 froman,

I very strongly suggest you don't publish your mysql root password in a public forum.

Please edit your post and change that password to <password>.
it's a fake password. ;)

Re: Spammer Alert

Posted: 29 Dec 2018 20:53
by cdburgess75
This works!


#!/bin/bash

username=root
password=`grep MYSQLROOTPWD /etc/EFA-Config | sed 's/.*://'`
database=mailscanner
email=your@email.com

########mysql query count
mysql -u $username -p$password -D $database -e \
"SELECT from_address,COUNT(*) FROM maillog \
WHERE isspam=1 AND DATE_SUB(now(),INTERVAL 1 HOUR) <= timestamp \
AND from_domain IN (SELECT from_domain from maillog where 1) \
GROUP BY from_address ORDER BY COUNT(*) DESC LIMIT 50 \
INTO OUTFILE '/tmp/senders' FIELDS TERMINATED BY ',' \
ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
#########end mysql query

#########mysql query list
mysql -u $username -p$password -D $database -e \
"SELECT id,from_address,to_address,clientip,sascore,date,time FROM maillog \
WHERE isspam=1 AND DATE_SUB(now(),INTERVAL 1 HOUR) <= timestamp \
AND from_domain IN (SELECT from_domain from maillog where 1) \
INTO OUTFILE '/tmp/spamlist' FIELDS TERMINATED BY ' ' \
ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
########end mysql query

while read sender; do

if [ $sender != "" ]
then
user=`echo $sender | sed 's/\,.*$//g' | sed 's/\"//g'`
offenses=`echo $sender | sed 's/.*\,//g' | sed 's/\"//g'`
counter=0
echo User: $user has recently been sending spam. > spammail.txt
echo $offenses messages have been reported as spam. >> spammail.txt
for item in $( cat /tmp/spamlist ); do
item=`echo $item | sed 's/\"//g'`
counter=$[$counter +1]
case $counter in
1)
Message_ID=$item
;;
2)
From_address=$item
;;
3)
To_address=$item
;;
4)
IP=$item
;;
5)
Score=$item
;;
6)
date=$item
;;
7)
time=$item
counter=0
if [ $From_address == $user ]
then
echo >> spammail.txt
echo " "ID: $Message_ID, Date: $date $time, Score: $Score >> spammail.txt
echo " "From: $From_address, To: $To_address, IP: $IP >> spammail.txt
echo >> spammail.txt
fi
;;
esac
done
cat spammail.txt | mail -s "Possible Account Hack" $email
fi
done < /tmp/senders

rm -rf /tmp/senders
rm -rf /tmp/spamlist
rm -rf spammail.txt

Re: Spammer Alert

Posted: 30 Dec 2018 08:14
by henk
As Network and Systems Engineer, you should know you don't publish your real e-mail address in a script on a public forum, unless you want spam :snooty:

Re: Spammer Alert

Posted: 28 Mar 2019 13:40
by ethandee178
It appears that the Domains table served a function and was populated at one point.
Upon looking at the EFA-configure scripts, it looks like the transport section writes directly to the postfix config files.
So if you don't have a Domains table or have an out of date one like me, you can repopulate it like this:
Disclaimer: This is assuming there is no longer any function for this table which seems to be dependent on your version. Maybe we can get one of the higher ups here to confirm. If the table does exist, make a backup and test as this script will destroy it first and rebuild it.

username=root
password=`grep MYSQLROOTPWD /etc/EFA-Config | sed 's/.*://'`
database=mailscanner

### Drop table if exists
mysql -u $username -p$password -D $database -e \
"DROP TABLE IF EXISTS Domains;"

### Create New Table
mysql -u $username -p$password -D $database -e \
"CREATE TABLE Domains ( \
ID int(3) NOT NULL, \
domain varchar(100) NOT NULL DEFAULT '', \
PRIMARY KEY (ID) \
) ENGINE=MyISAM DEFAULT CHARSET=utf8;"

## add domain entries from /etc/postfix/transport
index=0
for i in `cat /etc/postfix/transport | sed 's/\#.*//g' | sed 's/\ smtp\:.*//g'`;
do
index=$((index+1))
mysql -u $username -p$password -D $database -e "insert into Domains (ID, domain) values ('$index', '$i');"
done

Re: Spammer Alert

Posted: 28 Mar 2019 15:40
by cdburgess75
henk wrote: 30 Dec 2018 08:14 As Network and Systems Engineer, you should know you don't publish your real e-mail address in a script on a public forum, unless you want spam :snooty:
Thanks :)

/dave