awk weekly – Checkpoint Anti Spam statistics or viva les Open Relays


Goooood day everyone again, today I have had another fight with the spam that my client fell victim of. Once upon a time there was not so powerful UTM providing internet to not so crowded office in not so security-aware Central Europe. All would be good and well if not this problem - they could not send emails outside as the IP of the firewall entered every imaginable blacklist on the Earth. Hmm, but the firewall has AntiSpam subscription service up and running. LAN is blocked on port 25 outbound except the Exchange. Antivirus is everywhere so low chance of spam coming from LAN. In SmartView Tracker lots of SMTP rule logs in red - spam entering Exchange is blocked .

So what the ...? tcpdump with -w option for 5 minutes was all I needed to see that Exchange was open relay and kindly offered to relay spam from everyone to everywhere. To really measure the impact of the event I had to have some statistics and Checkpoint didn't help me much with that , eventhough this UTM has also SmartView Monitor license it is not suited for the task. So I exported fw.log on the UTM into text human-awk-readable format , that took some 40 mins on 300 Mb log file and produced text file of 475 Mb, and then did whatever I wanted with the data using awk. Now get some action:
Script 1 - Find all mails rejected in direction from LAN (interface Internal, remember it is UTM) to the Internet (interface External) , then gather statistics of how many mails came from what ip [less relevant here as all mails come from Exchnage, but in environment where hosts send mails directly outside it is] and show us :

# awk -F\; '/Internal to External/ && /reject/ {print $2}' ./fw.log.txt | awk ' {match($0,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/,IP); IPS[IP[0]]++ } END { for (spammer_ips in IPS) print spammer_ips " " IPS[spammer_ips]}' | sort -n -k2,2
192.168.14.12 402804

Yahooooo! In the timeframe of 28 hours there were blocked 402804 mails as spam coming from Exchange! Not bad at all - all this without any malware installed on the client side [my educated by Wireshark guess here as I dont have access to the Exchange],just amazing!

Now let's have a look at overal number of mails that was accepted and sent outside to the Internet : Script 2 - Find all mails accepted in direction from LAN (interface Internal) to the Internet (interface External) , then gather statistics of how many mails came from what ip and show us :

# awk -F\; '/Internal to External/ && /accept/ {print $2}' ./fw.log.txt | awk ' {match($0,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/,IP); IPS[IP[0]]++ } END { for (spammer_ips in IPS) print spammer_ips " " IPS[spammer_ips]}' | sort -n -k2,2
192.168.14.12 257940

Wow! in addition to 402804 mails blocked by Checkpoint firewall as spam 257940 mails were sent out as clean, given that this is a very small office hardly sending 300 mails a day we get ratio of 39% spam passing through the Checkpoint Antispam , pity . Antispam blocking rate of 61% ? In 21st century ? Wake up !

Just for statistics I also calculated how many spam emails were blocked from outside inbound: Script 3 - gather how many mails from outside coming in were rejected by Checkpoint as spam.

# awk -F\; '/External to Internal/ && /reject/ {print $2}' ./fw.log.txt | wc
# 5593 11186 112648

So only 5593 incoming spam emails and almost half a million outgoing ones - that's what I call effectiveness. Script 4 - gather statistics on blocked emails and IPs it came from:

# awk -F\; '/External to Internal/ && /reject/ {print $2}' ./fw.log.txt | awk ' {match($0,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/,IP); IPS[IP[0]]++ } END { for (spam_ips in IPS) print spam_ips " " IPS[spam_ips]}' | sort -n -k2,2 

And here are some results

93.81.26.2 75
91.121.114.1 81
220.168.57.1 87
58.9.205.2 129
122.102.101.1 149
58.137.99.7 160
189.35.231.6 189
60.248.174.6 631