#!/bin/bash
#
# http://support.f5.com/kb/en-us/solutions/public/11000/300/sol11370.html?sr=9518045
#


##############################################################################
#
#  Variables: You can edit these variables
#

emailRecipient=security@mycompany.com


#############################################################################
#
# Don't edit anything below this line
#

QUERYFILE1=/tmp/$$.myquery1.tmp
QUERYFILE2=/tmp/$$.myquery2.tmp
OUTPUTFILE1=/tmp/$$.myoutputfile1.tmp
OUTPUTFILE2=/tmp/$$.myoutputfile2.tmp

echo "select count(DISTINCT src_ip) as uniq_blocked, count(*) as count, dest_ip from PRX.PROXY_LOG where (unix_timestamp(now()) - 86400) <= log_time group by dest_ip order by count desc;" > $QUERYFILE1
echo "select src_ip ,dest_ip, geo_location_country_code, count(*) as blocks from PRX.PROXY_LOG where ((unix_timestamp(now()) - 86400) <= log_time) group by src_ip, dest_ip order by blocks desc limit 20;" > $QUERYFILE2

RUNMYSQL="mysql -uroot -p`perl -I/ts/packages -MF5::GenUtils -e 'print get_mysql_password().qq{\n}'` --batch "


##############################################################################
#
# Main


echo -e "Blocks\tSources\t  Target Virtual Server\n==================================" >> $OUTPUTFILE1
$RUNMYSQL < $QUERYFILE1 | tail -n +2 |
while read uniq total dstip
do
   sitename="NULL"
   sitename=`host $dstip | head -1 | cut -f5 -d' '`
   if [ "$sitename" = "3(NXDOMAIN)" ]; then
        sitename=$dstip
   fi

   echo -e "$total\t$uniq\t  $sitename" >> $OUTPUTFILE1
done


echo -e "Blocks\tGEO\tBlocked Source\t    Target Virtual Server\n==========================================" >> $OUTPUTFILE2
$RUNMYSQL < $QUERYFILE2 | tail -n +2 |
while read srcip dstip geo count
do

   sitename="NULL"
   sitename=`host $dstip | head -1 | cut -f5 -d' '`
   if [ "$sitename" = "3(NXDOMAIN)" ]; then
        sitename=$dstip
   fi

   echo -e "$count\t$geo\t$srcip\t    $sitename" >> $OUTPUTFILE2
done


emailSubj="F5 ASM daily block summary"
emailtext1="Top 20 attackers in the past 24 hours."
emailtext2="Top attacked virtual servers in the past 24 hours."
(echo -e "$emailtext1\\n\\r`cat $OUTPUTFILE2`\\n\\r\\n\\r\\n\\r$emailtext2\\n\\r`cat $OUTPUTFILE1`\\n\\r" ) | mail -s "$emailSubj" $emailRecipient

# Clean up temp files
rm /tmp/$$.*.tmp