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 »

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: 3573
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: Spammer Alert

Post by shawniverson »

:text-bravo:
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post 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.
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post 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
User avatar
pdwalker
Posts: 1444
Joined: 18 Mar 2015 09:16

Re: Spammer Alert

Post by pdwalker »

Useful.

Added to the wiki
Rob.M.P
Posts: 26
Joined: 28 Jan 2014 23:01

Re: Spammer Alert

Post 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?
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post 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.
c0mputerking
Posts: 29
Joined: 26 Aug 2014 23:47

Re: Spammer Alert

Post 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)
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post 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.
c0mputerking
Posts: 29
Joined: 26 Aug 2014 23:47

Re: Spammer Alert

Post 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
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post by ethandee178 »

: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 »

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 »

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 »

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: 1444
Joined: 18 Mar 2015 09:16

Re: Spammer Alert

Post 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>.
budy
Posts: 74
Joined: 10 Sep 2017 07:33

Re: Spammer Alert

Post by budy »

But first, change it on the database!
froman
Posts: 21
Joined: 29 Aug 2017 03:41

Re: Spammer Alert

Post 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. ;)
cdburgess75
Posts: 48
Joined: 11 Jun 2014 21:43

Re: Spammer Alert

Post 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
Last edited by cdburgess75 on 28 Mar 2019 15:40, edited 1 time in total.
henk
Posts: 500
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Spammer Alert

Post 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:
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
ethandee178
Posts: 42
Joined: 26 May 2015 16:28

Re: Spammer Alert

Post 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
cdburgess75
Posts: 48
Joined: 11 Jun 2014 21:43

Re: Spammer Alert

Post 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
Post Reply