Spammer Alert

Questions and answers about how to do stuff
Post Reply
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Spammer Alert

Post by ethandee178 » 14 Jul 2015 02:16

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
Last edited by ethandee178 on 24 Jul 2015 03:03, edited 1 time in total.

User avatar
shawniverson
Posts: 3029
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: Spammer Alert

Post by shawniverson » 14 Jul 2015 04:03

:text-bravo:
Version eFa 4.0.2 now available!

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 24 Jul 2015 01:42

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.

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 24 Jul 2015 03:25

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

User avatar
pdwalker
Posts: 1212
Joined: 18 Mar 2015 09:16

Re: Spammer Alert

Post by pdwalker » 25 Aug 2015 08:21

Useful.

Added to the wiki

Rob.M.P
Posts: 26
Joined: 28 Jan 2014 23:01

Re: Spammer Alert

Post by Rob.M.P » 20 Apr 2016 21:08

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?

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 21 Apr 2016 12:48

This is '.sh' file you must create and reference it in the cron table.
Copy the contents into a script and add execute permissions.

c0mputerking
Posts: 29
Joined: 26 Aug 2014 23:47

Re: Spammer Alert

Post by c0mputerking » 22 Dec 2016 19:23

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)

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 22 Dec 2016 19:57

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.

c0mputerking
Posts: 29
Joined: 26 Aug 2014 23:47

Re: Spammer Alert

Post by c0mputerking » 23 Dec 2016 05:21

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

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 23 Dec 2016 14:06

:think:
If you want to pm me. I can help u look.

cdburgess75
Posts: 48
Joined: 11 Jun 2014 21:43

Re: Spammer Alert

Post by cdburgess75 » 14 Feb 2017 17:55

I have the same issue the last guy had, no table named "Domain"

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 14 Feb 2017 18:07

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.

froman
Posts: 21
Joined: 29 Aug 2017 03:41

Re: Spammer Alert

Post by froman » 03 Mar 2018 21:17

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

User avatar
pdwalker
Posts: 1212
Joined: 18 Mar 2015 09:16

Re: Spammer Alert

Post by pdwalker » 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>.

budy
Posts: 74
Joined: 10 Sep 2017 07:33

Re: Spammer Alert

Post by budy » 05 Mar 2018 17:51

But first, change it on the database!

froman
Posts: 21
Joined: 29 Aug 2017 03:41

Re: Spammer Alert

Post by froman » 06 Mar 2018 01:19

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. ;)

cdburgess75
Posts: 48
Joined: 11 Jun 2014 21:43

Re: Spammer Alert

Post by cdburgess75 » 29 Dec 2018 20:53

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
Last edited by cdburgess75 on 28 Mar 2019 15:40, edited 1 time in total.

henk
Posts: 442
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Spammer Alert

Post by henk » 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:

ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 » 28 Mar 2019 13:40

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

cdburgess75
Posts: 48
Joined: 11 Jun 2014 21:43

Re: Spammer Alert

Post by cdburgess75 » 28 Mar 2019 15:40

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

Post Reply