#!/bin/sh # Copyright (C) 2009 Mark Stingley # mark AT altsec.info # http://www.altsec.info # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # See http://www.gnu.org/licenses/licenses.html#GPL or write to the # Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, # MA 02111-1307 USA # # Changelog: #Version 20090903.01 #A webserver with access to the MySQL database via ODBC can query up some useful snapshot data #to keep an eye on. #This one runs every ten minutes to highlight: # The Top 10 Internal DNS talkers # Out-of-state packets on all firewall interfaces (tcp flags != NULL) # Top 40 outbound accepts from internal hosts (less common traffic) # Top 20 inbound accepts from external hosts # Top 20 outbound drops # Top 20 inbound drops #site architecture configuration considerations # /var/www/index.html = web site index page #DNS servers: 192.168.14.52 192.168.14.53 #Local networks: 192.168. and 172.16. #isql32 -b ODBC-ID odbc-user odbc-password (replace with the appropriate database access arguments) #create some tempfiles #Apologies for the nasty script style. Other projects took me away from porting this to Perl #using the DBI modules to natively access the MySQL database. TMPFILE=`tempfile` TMPQRY=`tempfile` #this is an example of skipping the snapshot if heavy query loads are running # I had discovered that an overnight full summary was backing up the transaction queue if [ -f /tmp/allsum.run ]; then echo '<html>' > $TMPFILE echo '<head>' >> $TMPFILE echo '<meta http-equiv="refresh" content="3600;URL=index.html">' >> $TMPFILE echo '<title>[CPlogger] Firewall Snapshot - last 10 minutes</title>' >> $TMPFILE echo "<b>last update `date`</b><br>" >> $TMPFILE echo '</head>' >> $TMPFILE echo '<p> SNAPSHOT suspended... ALLSUM query running</p>' >> $TMPFILE cat $TMPFILE > /var/www/index.html exit 0 fi #start writing the html output to a temporary file that will be turned into index.html at the end echo '<html>' > $TMPFILE echo '<head>' >> $TMPFILE echo '<meta http-equiv="refresh" content="3600;URL=index.html">' >> $TMPFILE echo '<title>[CPlogger] Firewall Snapshot - last 10 minutes</title>' >> $TMPFILE echo "<b>last update `date`</b><br>" >> $TMPFILE echo '</head>' >> $TMPFILE #store the ten minute time frame in MySQL date/time format TNOW=`date "+%Y-%m-%d %H:%M"` TTHEN=`date -d "10 minutes ago" "+%Y-%m-%d %H:%M"` #Grab the top 10 talkers on the Internal DSN servers echo '<p>Top 10 Internal DNS Talkers</p>' >> $TMPFILE echo "select distinct count(*) as count,fw1src from fw1logs.fw1logs where fw1time between \"$TTHEN\" and \"$TNOW\" and fw1service = \"53\" and (fw1dst = \"192.168.14.52\" or fw1dst = \"192.168.14.53\") and (mid(fw1src,1,8) = \"192.168.\" or mid(fw1src,1,7) = \"172.16.\") group by fw1src order by count desc limit 10;" > $TMPQRY #store a blank line after each sql statement for isql echo "" >> $TMPQRY #run the query, storing the output to the temp file isql32 -b ODBC-ID odbc-user odbc-password -w < $TMPQRY >> $TMPFILE #Grab the out-of-state connections for local hosts (the internet side would be way to noisy) echo "------------------------------------------------------" >> $TMPFILE echo '<p>TCP out-of-state by Firewall Interface (local src ip)</p>' >> $TMPFILE echo "select distinct fw1orig as FW_If,count(*) as Count from fw1logs.fw1logs where fw1time between \"$TTHEN\" and \"$TNOW\" and fw1tcpflags is not null and (mid(fw1src,1,8) = \"192.168.\" or mid(fw1src,1,7) = \"172.16.\") group by FW_If order by FW_If;" > $TMPQRY echo "" >> $TMPQRY isql32 -b ODBC-ID odbc-user odbc-password -w < $TMPQRY >> $TMPFILE #Grab the top 40 outbound accepts on the Internet firewall interfaces, excluding dns,http/https, and ntp #skip some understandably noisy systems like mail servers, dns servers, etc. echo "------------------------------------------------------" >> $TMPFILE echo '<p>Top 40 accepts outbound (minus dns,http/https,ntp)</p>' >> $TMPFILE echo "select distinct count(*) as count,fw1src,fw1service,fw1proto from fw1logs.fw1logs where fw1time between \"$TTHEN\" and \"$TNOW\" and fw1action = \"accept\" and ( fw1orig = \"192.168.10.1\" or fw1orig = \"192.168.10.2\" ) and (mid(fw1src,1,8) = \"192.168.\" or mid(fw1src,1,7) = \"172.16.\") and mid(fw1dst,1,8) != \"192.168.\" and mid(fw1dst,1,7) != \"172.16.\" and mid(fw1src,1,11) != \"192.168.149\" and mid(fw1src,1,10) != \"192.168.9.\" and fw1src != \"192.168.238.17\" and fw1src != \"192.168.31.12\" and fw1src != \"192.168.4.10\" and fw1src != \"192.168.29.10\" and fw1service != \"53\" and fw1service != \"80\" and fw1service != \"443\" and fw1service != \"123\" group by fw1src,fw1service,fw1proto order by count desc limit 40;" > $TMPQRY echo "" >> $TMPQRY isql32 -b ODBC-ID odbc-user odbc-password -w < $TMPQRY >> $TMPFILE #Track the Top 20 accepts inbound from the internet (minus http/https,dns,smtp) echo "------------------------------------------------------" >> $TMPFILE echo '<p>Top 20 accepts inbound (minus http/https,dns,smtp)</p>' >> $TMPFILE echo "select distinct count(*),fw1src,fw1service,fw1proto from fw1logs.fw1logs where fw1time between \"$TTHEN\" and \"$TNOW\" and fw1action = \"accept\" and mid(fw1src,1,8) != \"192.168.\" and mid(fw1src,1,7) != \"172.16.\" and fw1service != "443" and fw1service != "80" and fw1service != "53" and fw1service != "25" group by fw1src,fw1service,fw1proto order by count(*) desc limit 20;" > $TMPQRY echo "" >> $TMPQRY isql32 -b ODBC-ID odbc-user odbc-password -w < $TMPQRY >> $TMPFILE echo "------------------------------------------------------" >> $TMPFILE #Here, the top 20 outbound drops are watched echo '<p>Top 20 drops outbound</p>' >> $TMPFILE echo "select distinct count(*),fw1src,fw1service,fw1proto from fw1logs.fw1logs where fw1time between \"$TTHEN\" and \"$TNOW\" and fw1action = \"drop\" and (mid(fw1src,1,8) = \"192.168.\" or mid(fw1src,1,7) = \"172.16.\") and (mid(fw1dst,1,8) != \"192.168.\" or mid(fw1dst,1,7) != \"172.16.\") group by fw1src,fw1service,fw1proto order by count(*) desc limit 20;" > $TMPQRY echo "" >> $TMPQRY isql32 -b ODBC-ID odbc-user odbc-password -w < $TMPQRY >> $TMPFILE echo "------------------------------------------------------" >> $TMPFILE #Lastly, keep an eye on the top 20 drops inbound from the internet echo '<p>Top 20 drops inbound</p>' >> $TMPFILE echo "select distinct count(*),fw1src,fw1service,fw1proto from fw1logs.fw1logs where fw1time between \"$TTHEN\" and \"$TNOW\" and fw1action = \"drop\" and mid(fw1src,1,8) != \"192.168.\" and mid(fw1src,1,7) != \"172.16.\" group by fw1src,fw1service,fw1proto order by count(*) desc limit 20;" > $TMPQRY echo "" >> $TMPQRY isql32 -b ODBC-ID odbc-user odbc-password -w < $TMPQRY >> $TMPFILE echo "------------------------------------------------------" >> $TMPFILE #Then, cat all the contents of the accumulated html output from isql into the web page cat $TMPFILE > /var/www/index.html #clean up by removing the temporary files rm -f $TMPQRY rm -f $TMPFILE