#!/usr/bin/perl
#
# PIX Logging Architecture
# [Kristof Philipsen]
#
# This file is part of PIX Logging Architecture
#
# PIX Logging Architecture 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.
#
# PIX Logging Architecture 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.
#
# You should have received a copy of the GNU General Public License
# along with PIX Logging Architecture; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 
use CGI;
use DBI;

#
# include configuration
#
require "conf.pl";      # General Configuration
require "subs.pl";      # Subroutines
                                                                                                                                                                                                  
db_connect();

#
# HTML SECTION
#
printtitle();

$date=`date +%Y"-"%m"-"%d`;
$date=~s/\n//g;

$stat_limit = "5";
$info_limit = "10";

$query = new CGI;
$fw = clean_input($query->param("firewall"));
$id = clean_input($query->param("id"));
$datetime = clean_input($query->param("datetime"));
$fwo = $fw;

if ($fw eq "") {
        $fw="\%";
        $fw_title="Global View (<b>All Firewalls</b>)";
} else {
        $fw_title="Custom View (<b>$fw</b>)";
}

if ($datetime eq "" || $datetime eq "YYYY-MM-DD") {
        $date=`date +%Y"-"%m"-"%d`;
        ($year,$month,$day) = split("-",$date);
        $start_year=$year;
        $start_month=$month;
        $start_day=$day;
        $start_hour="00";
        $start_min="00";
        $start_sec="00";
        $end_year=$year;
        $end_month=$month;
        $end_day=$day;
        $end_hour="23";
        $end_min="59";
        $end_sec="59";
} else {
        $date=$datetime;
        ($year,$month,$day) = split("-",$date);
        $start_year=$year;
        $start_month=$month;
        $start_day=$day;
        $start_hour="00";
        $start_min="00";
        $start_sec="00";
        $end_year=$year;
        $end_month=$month;
        $end_day=$day;
        $end_hour="23";
        $end_min="59";
        $end_sec="59";
}



if ($id eq "acceptdrop") {
 $maxvars="2";
 $tbl_fld="log_action";
 $tblval="ACCEPT:DROP";
 $legend="DROP:ACCEPT";
 $title="Drops & Accepts";
}

if ($id eq "protocols") {
 $maxvars="3";
 $tbl_fld="log_protocol";
 $tblval="TCP:UDP:ICMP";
 $legend="ICMP:UDP:TCP";
 $title="Protocol Redistribution";
}

if ($id eq "fwstat") {
 $maxvars="1";
 $tbl_fld="log_resource";
 $tblval=$fwo;
 $legend="Traffic for $fwo";
 $title="Traffic for Firewall $fwo ";
}

print <<EOF;

<script language="JavaScript">
<!-- Original script borrowed from http://web.uvic.ca/atrsweb/vikes/default.html -->
<!-- Implemented at Kwantlen 26 June 2000 DMH -->
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
if (restore) selObj.selectedIndex=0;
}
//-->
</script>


<SCRIPT LANGUAGE="JavaScript" SRC="images/CalendarPopup.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript">document.write(getCalendarStyles());</SCRIPT>

<span class="titlehead"><b>PIX Logging Architecture > Security Dashboard</b></span>
<br><br>

<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#000000" width="300" align="top" valign="top">&nbsp;&nbsp; <b><span class="main"><font color="white">PLA Statistics: $date</font></span></b></td>

<td bgcolor="#fffffff" align="right">
<form action="pla_main" method="post" align="right" valign="top" name="head">
<a href="pla_main?datetime=$date&firewall=$fwo" class="bodylink"><< Back to Global Security Dashboard</a> &nbsp; <font color="#000000">|</font> &nbsp; 
<a href="javascript:window.location.reload()" class="bodylink"><img src="images/reload.gif" align="middle" border="0"> Refresh View</img></a>&nbsp;&nbsp;
<select name="firewall" style="background-color: #5479d8; color: #ffffff;" onChange="MM_jumpMenu('parent',this,0)">
<option value="?id=$id&firewall=&datetime=$date" selected>-customize view-
<option value="?id=$id&firewall=&datetime=$date">-
<option value="?id=$id&firewall=&datetime=$date">Global Dashboard
<option value="?id=$id&firewall=&datetime=$date">-
EOF
$getAllResources="SELECT high_priority distinct log_resource from traffic_log group by log_resource";
$AllResources = $db_handle->prepare($getAllResources) or die "Couldn't prepare query '$getAllResources': $DBI::errstr\n";
$AllResources->execute();
while ($log_resource = $AllResources->fetchrow) {
print <<EOF;
<option value="?id=$id&firewall=$log_resource&datetime=$date"> $log_resource
EOF
}
print <<EOF;
</td>
</select>
</form>
</table>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#5479d8" height="6"></td>
</table>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#d9d9d9" align="right"><span class="main"><b>Security Dashboard</b> > $title Drilldown [$fw_title]</span></td>
</table>

<table width="100%" cellpadding="0" cellspacing="0" border="0">
<td valign="top">
<br>
<table width="220" bgcolor="black" cellpadding="0" cellspacing="0" border="0">
<td width="20" bgcolor="#ffffff">
<li type="square"></li>
</td>
<td width="200" bgcolor="#ffffff">
<span class="main">Top $stat_limit Dropped Sources</span>
</td>
<tr>
<td bgcolor="#5479d8" height="2">
</td>
<td bgcolor="#5479d8" height="2">
</td>
</table>
<table width="220" bgcolor="#fffffff" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">IP Addresses</font></span>
</td>
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">Count</font></span>
</td>
<tr>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>

EOF

##
## select log_src_ip,count(*) from traffic_log where log_action="DROP" group by log_src_ip order by count(*) desc limit 10;
##

$stats_offenders = "select log_src_ip,count(*) from traffic_log where log_action=\"DROP\" and log_time >= \"$date 00:00:00\" and log_time <= \"$date 23:59:59\" and log_resource like \"$fw\" group by log_src_ip order by count(*) desc limit $stat_limit;";
$getstats_offenders = $db_handle->prepare($stats_offenders) or die "Couldn't prepare query '$stats_offenders': $DBI::errstr\n";
$getstats_offenders->execute();

while (($log_src_ip,$count) = $getstats_offenders->fetchrow) {

print <<EOF;
<tr>
<td width="150">
<span class="main">
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&src_ip=$log_src_ip&action=drop&resource=$fw"><font color="red">$log_src_ip</font></a>
</span>
</td>
<td>
<span class="main">
$count
</span>
</td>
EOF

}

print <<EOF;
<tr>
<td bgcolor="#ffffff">
<br>
</td>
<td bgcolor="#ffffff">
<br>
</td>
</table>
<table width="220" bgcolor="black" cellpadding="0" cellspacing="0" border="0">
<td width="20" bgcolor="#ffffff">
<li type="square"></li>
</td>
<td width="200" bgcolor="#ffffff">
<span class="main">Top $stat_limit Dropped Destinations</span>
</td>
<tr>
<td bgcolor="#5479d8" height="2">
</td>
<td bgcolor="#5479d8" height="2">
</td>
</table>
<table width="220" bgcolor="#fffffff" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">IP Addresses</font></span>
</td>
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">Count</font></span>
</td>
<tr>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>
EOF


##
## select log_src_ip,count(*) from traffic_log where log_action="DROP" group by log_src_ip order by count(*) desc limit ;
##

$stats_targets = "select log_dst_ip,count(*) from traffic_log where log_action=\"DROP\" and log_time >= \"$date 00:00:00\" and log_time <= \"$date 23:59:59\"and log_message not like \"PIX-3-313001%\" and log_resource like \"$fw\" group by log_dst_ip order by count(*) desc limit $stat_limit;";
$getstats_targets = $db_handle->prepare($stats_targets) or die "Couldn't prepare query '$stats_targets': $DBI::errstr\n";
$getstats_targets->execute();

while (($log_dst_ip,$count) = $getstats_targets->fetchrow) {

print <<EOF;
<tr>
<td width="150">
<span class="main">
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&dst_ip=$log_dst_ip&action=drop&resource=$fw""><font color="red">$log_dst_ip</font></a>
</span>
</td>
<td>
<span class="main">
$count
</span>
</td>
EOF

}

print <<EOF;
<tr>
<td bgcolor="#ffffff">
<br>
</td>
<td bgcolor="#ffffff">
<br>
</td>
</table>

<table width="220" bgcolor="black" cellpadding="0" cellspacing="0" border="0">
<td width="20" bgcolor="#ffffff">
<li type="square"></li>
</td>
<td width="200" bgcolor="#ffffff">
<span class="main">Top $stat_limit Dropped Services</span>
</td>
<tr>
<td bgcolor="#5479d8" height="2">
</td>
<td bgcolor="#5479d8" height="2">
</td>
</table>
<table width="220" bgcolor="#fffffff" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#ffffff" width="60">
<span class="button"><font color="#000000">Protocol</font></span>
</td>
<td bgcolor="#ffffff" width="90">
<span class="button"><font color="#000000">Service</font></span>
</td>
<td bgcolor="#ffffff" width="70">
<span class="button"><font color="#000000">Count</font></span>
</td>
<tr>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>
EOF

##
## select log_src_ip,count(*) from traffic_log where log_action="DROP" group by log_src_ip order by count(*) desc limit 10;
##

$stats_services = "select log_dst_pt,log_protocol,count(*) from traffic_log where log_action=\"DROP\" and log_time >= \"$date 00:00:00\" and log_time <= \"$date 23:59:59\" and log_resource like \"$fw\" group by log_protocol,log_dst_pt order by count(*) desc limit $stat_limit;";
$getstats_services = $db_handle->prepare($stats_services) or die "Couldn't prepare query '$stats_services': $DBI::errstr\n";
$getstats_services->execute();

while (($log_dst_pt,$log_protocol,$count) = $getstats_services->fetchrow) {

print <<EOF;
<tr>
<td width="60">
<span class="main">
<font color="red">$log_protocol</font>
</span>
</td>
<td width="90">
<span class="main">
EOF

if ($log_protocol eq "ICMP") {

print <<EOF;
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&protocol=$log_protocol&action=drop&resource=$fw""><font color="red">n/a</font></a>
EOF

} else {

print <<EOF;
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&dst_pt=$log_dst_pt&protocol=$log_protocol&action=drop&resource=$fw""><font color="red">$log_dst_pt</font></a>
EOF

}
print <<EOF;
</span>
</td>
<td width="70">
<span class="main">
$count
</span>
</td>
EOF

}


print <<EOF;
<tr>
<td bgcolor="#ffffff">
<br>
</td>
<td bgcolor="#ffffff">
<br>
</td>
</table>
<table width="220" bgcolor="black" cellpadding="0" cellspacing="0" border="0">
<td width="20" bgcolor="#ffffff">
<li type="square"></li>
</td>
<td width="200" bgcolor="#ffffff">
<span class="main">Top $stat_limit Accepted Sources</span>
</td>
<tr>
<td bgcolor="#5479d8" height="2">
</td>
<td bgcolor="#5479d8" height="2">
</td>
</table>
<table width="220" bgcolor="#fffffff" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">IP Addresses</font></span>
</td>
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">Count</font></span>
</td>
<tr>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>
EOF


##
## select log_src_ip,count(*) from traffic_log where log_action="DROP" group by log_src_ip order by count(*) desc limit 10;
##

$stats_acsources  = "select log_src_ip,count(*) from traffic_log where log_action=\"ACCEPT\" and log_time >= \"$date 00:00:00\" and log_time <= \"$date 23:59:59\" and log_resource like \"$fw\" group by log_src_ip order by count(*) desc limit $stat_limit;";
$getstats_acsources = $db_handle->prepare($stats_acsources) or die "Couldn't prepare query '$stats_acsources': $DBI::errstr\n";
$getstats_acsources->execute();

while (($log_src_ip,$count) = $getstats_acsources->fetchrow) {

print <<EOF;
<tr>
<td width="150">
<span class="main">
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&src_ip=$log_src_ip&action=accept&resource=$fw""><font color="green">$log_src_ip</font></a>
</span>
</td>
<td width="70">
<span class="main">
$count
</span>
</td>
EOF

}


print <<EOF;
<tr>
<td bgcolor="#ffffff">
<br>
</td>
<td bgcolor="#ffffff">
<br>
</td>
</table>
<table width="220" bgcolor="black" cellpadding="0" cellspacing="0" border="0">
<td width="20" bgcolor="#ffffff">
<li type="square"></li>
</td>
<td width="200" bgcolor="#ffffff">
<span class="main">Top $stat_limit Accepted Destinations</span>
</td>
<tr>
<td bgcolor="#5479d8" height="2">
</td>
<td bgcolor="#5479d8" height="2">
</td>
</table>
<table width="220" bgcolor="#fffffff" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">IP Addresses</font></span>
</td>
<td bgcolor="#ffffff">
<span class="button"><font color="#000000">Count</font></span>
</td>
<tr>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>
EOF

##
## select log_src_ip,count(*) from traffic_log where log_action="DROP" group by log_src_ip order by count(*) desc limit 10;
##

$stats_acdestinations  = "select log_dst_ip,count(*) from traffic_log where log_action=\"ACCEPT\" and log_time >= \"$date 00:00:00\" and log_time <= \"$date 23:59:59\" and log_resource like \"$fw\" group by log_dst_ip order by count(*) desc limit $stat_limit;";
$getstats_acdestinations = $db_handle->prepare($stats_acdestinations) or die "Couldn't prepare query '$stats_acdestinations': $DBI::errstr\n";
$getstats_acdestinations->execute();

while (($log_dst_ip,$count) = $getstats_acdestinations->fetchrow) {

print <<EOF;
<tr>
<td width="150">
<span class="main">
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&dst_ip=$log_dst_ip&action=accept&resource=$fw""><font color="green">$log_dst_ip</font></a>
</span>
</td>
<td width="70">
<span class="main">
$count
</span>
</td>
EOF

}


print <<EOF;
<tr>
<td bgcolor="#ffffff">
<br>
</td>
<td bgcolor="#ffffff">
<br>
</td>
</table>
<table width="220" bgcolor="black" cellpadding="0" cellspacing="0">
<td width="20" bgcolor="#ffffff">
<li type="square"></li>
</td>
<td width="200" bgcolor="#ffffff">
<span class="main">Top $stat_limit Accepted Services</span>
</td>
<tr>
<td bgcolor="#5479d8" height="2">
</td>
<td bgcolor="#5479d8" height="2">
</td>
</table>
<table width="220" bgcolor="#fffffff" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#ffffff" width="60">
<span class="button"><font color="#000000">Protocol</font></span>
</td>
<td bgcolor="#ffffff" width="90">
<span class="button"><font color="#000000">Service</font></span>
</td>
<td bgcolor="#ffffff" width="70">
<span class="button"><font color="#000000">Count</font></span>
</td>
<tr>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>
<td bgcolor="#5479d8" height="1">
</td>

EOF

## select log_src_ip,count(*) from traffic_log where log_action="DROP" group by log_src_ip order by count(*) desc limit 10;
##

$ac_stats_services = "select log_dst_pt,log_protocol,count(*) from traffic_log where log_action=\"ACCEPT\" and log_time >= \"$date 00:00:00\" and log_time <= \"$date 23:59:59\" and log_resource like \"$fw\" group by log_dst_pt,log_protocol order by count(*) desc limit $stat_limit;";
$getac_stats_services = $db_handle->prepare($ac_stats_services) or die "Couldn't prepare query '$ac_stats_services': $DBI::errstr\n";
$getac_stats_services->execute();

while (($log_dst_pt,$log_protocol,$count) = $getac_stats_services->fetchrow) {

print <<EOF;
<tr>
<td width="60">
<span class="main">
<font color="green">$log_protocol</font>
</span>
</td>
<td width="90">
<span class="main">
EOF

if ($log_protocol eq "ICMP") {

print <<EOF;
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&protocol=$log_protocol&action=accept&resource=$fw""><font color="green">n/a</font></a>
EOF

} else {

print <<EOF;
<a href="pix_search_result_traffic?date_type=userdef&start_year=$start_year&start_month=$start_month&start_day=$start_day&start_hour=$start_hour&start_min=$start_min&start_sec=$start_sec&end_year=$end_year&end_month=$end_month&end_day=$end_day&end_hour=$end_hour&end_min=$end_min&end_sec=$end_sec&dst_pt=$log_dst_pt&protocol=$log_protocol&action=accept&resource=$fw""><font color="green">$log_dst_pt</font></a>
EOF

}
print <<EOF;
</span>
</td>
<td width="70">
<span class="main">
$count
</span>
</td>
EOF

}
# select log_time,log_resource,log_message,log_description from info_log group by log_time desc limit 5;

print <<EOF;
</table>
</td>
<td width="10">
</td>
<td align="top" valign="top">
<br>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td align="left"><li type="square"><span class="main">Last 24 Hours $title</span></li></td>
</table>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#5479d8" height="2"></td>
</table>
<br>
<img src="stats/pla-grapher?interval=hours&num=24&firewall=$fwo&maxvars=$maxvars&tblfld=$tbl_fld&tblval=$tblval&legend=$legend&datetime=$date&compare=1" alt="Accepts & Drops"></img>
<br><br>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td align="left"><li type="square"><span class="main">Last 7 Days $title</span></li></td>
</table>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#5479d8" height="2"></td>
</table>
<br>
<img src="stats/pla-grapher?interval=days&num=7&date_shift=0&firewall=$fwo&maxvars=$maxvars&tblfld=$tbl_fld&tblval=$tblval&legend=$legend&datetime=$date&compare=1" alt="Day of the Week"></img>
<br><br>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td align="left"><li type="square"><span class="main">Last 30 Days $title</span></li></td>
</table>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#5479d8" height="2"></td>
</table>
<br>
<img src="stats/pla-grapher?interval=days&num=30&date_shift=0&firewall=$fwo&maxvars=$maxvars&tblfld=$tbl_fld&tblval=$tblval&legend=$legend&datetime=$date&compare=1"></img>
<br><br>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td align="left"><li type="square"><span class="main">Last 12 Months $title</span></li></td>
</table>
<table width="100%" bgcolor="white" cellpadding="0" cellspacing="0" border="0">
<td bgcolor="#5479d8" height="2"></td>
</table>
<br>
<img src="stats/pla-grapher?interval=months&num=12&date_shift=1&firewall=$fwo&maxvars=$maxvars&tblfld=$tbl_fld&tblval=$tblval&legend=$legend&datetime=$date&compare=1"></img>
<br><br>
</table>
</td>
</table>
EOF

