#!/usr/bin/perl -w use strict; use DBI; use DBD::SQLite; use Date::Calc qw(:all); use DateTime; use CGI qw(:all); my $q = new CGI; my %params = $q->Vars; my $db_name = "/home/wjw/bin/auth_log_analysis.sqlite3"; my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name","","") or die DBI::errstr; my (%results_s, %results_m); my $report_start = get_report_start($params{report_length}); #retrieve the start time of the report #based on the variable handed in. # a hash of queries to be run. the has key is intended to be descriptive enough # to be usefull as titles on a web page. These have single results and go into # the %results_s hash my %qs_s = ( "num_runs" => qq(select count(*) from run_log where run_time_end >= '$report_start'), "num_new_users" => qq(select count(*) from users where create_time > '$report_start'), "num_new_attempts" => qq(select count(*) from attempts where timestamp > '$report_start'), "last_first_line" => qq(select last_log_first_line from run_log where eid = (select max(eid) - 1 from run_log)), "this_first_line" => qq(select last_log_first_line from run_log where eid = (select max(eid) from run_log)), "time_prev_run" => qq(select run_time_end from run_log where eid = (select max(eid) from run_log)) ); # These are queries that return multiple results which are then stored in the # %results_m hash my %qs_m = ( "results_$params{report_length}" => qq(select user, port, ip, timestamp from try1 where timestamp > '$report_start' order by timestamp), "new_ports" => qq(select port from ports where create_time > '$report_start' order by port), "new_users" => qq(select user from users where create_time > '$report_start' order by user), "new_ip" => qq(select ip from ip where create_time > '$report_start'), "who_is" => qq(select ip, entry, timestamp from who_is w join ip i on i.eid = w.ip_id where i.create_time > '$report_start' order by ip_id) ); #do the multi-result queries foreach my $key (keys %qs_m) { my $qs = $qs_m{$key}; my $sth = $dbh->prepare($qs); $sth->execute(); while (my @row = $sth->fetchrow_array()) { my $cnt = scalar(@row); if (scalar(@row) == 1) { push @{ $results_m{$key} }, @row; } elsif(scalar(@row) > 1) { push @{ $results_m{$key} }, join(" ", @row); } } } # do the single result queries foreach my $key (keys %qs_s) { my $qs = $qs_s{$key}; my $sth = $dbh->prepare($qs); $sth->execute(); while (my $row = $sth->fetch()) { $results_s{$key} = @$row[0]; } } if ($results_s{last_first_line} = $results_s{this_first_line}) { $results_s{new_file} = "False"; #print "same log\n"; #debug } $dbh->disconnect(); output(); exit; ################################################################################ sub get_report_start() { my $report_length = shift(@_); if($report_length eq "") { $report_length = -24; } # default to 24 hour report $report_length = $report_length * -1; #negate hours to work with Date::Calc my ($year,$month,$day,$hour,$min,$sec) = Date::Calc::Add_Delta_DHMS(Today_and_Now(), 0,$report_length, 0, 0); #format to iso-8601 form yyyy-mm-ddThh:mm:ss if (length($day) < 2) { $day = "0" . $day; } if (length($month) < 2) { $month = "0" . $month; } if(length($hour) < 2) { $hour = "0" . $hour; } if(length($min) <2 ) { $min = "0" . $min; } if(length($sec) <2 ) { $sec = "0" . $sec; } my $now = "$year\-$month\-$day" ."T" . "$hour\:$min\:$sec"; return $now; } ################################################################################ sub output() { print $q->header(); my $output = qq(\n\n\n\t\n); $output = $output . qq( ); #Output summary table $output = $output . qq(
\t\n); my $table_single_vals = qq(
Summary Values
New File Logged Runs New Users New Attempts Prev Run Time
$results_s{new_file} $results_s{num_runs} $results_s{num_new_users} $results_s{num_new_attempts} $results_s{time_prev_run}
); $output = $output . $table_single_vals; $output = $output . qq(
); my ($s_who_is, $s_attempts, $s_ip, $s_ports, $s_users); foreach my $key ( keys %results_m) { my @tmp = @{$results_m{$key}}; my $count = scalar(@tmp); #Output new IP found if ($key eq "new_ip") { $s_ip = qq(
\n); for my $i (0..scalar(@tmp)) { $tmp[$i] = qq(); } foreach my $i (0..scalar(@tmp) - 1) { $s_ip = $s_ip . qq($tmp[$i]\n); } $s_ip = $s_ip . qq(
IP's Used ($count)
IP
$tmp[$i]
\n); # $output = $output . qq($s_ip
); } #Output new ports found if ($key eq "new_ports") { $s_ports = qq(
\n); for my $i (0..scalar(@tmp)) { $tmp[$i] = qq(); } foreach my $i (0.. scalar(@tmp) - 1) { $s_ports = $s_ports . qq($tmp[$i]\n); } $s_ports = $s_ports . qq(
Ports Added ($count)
Port
$tmp[$i]
\n); # $output = $output . qq($s_ports); } #Output new users found if ($key eq "new_users") { $s_users = qq(
\n); for my $i (0..scalar(@tmp) - 1) { $tmp[$i] = qq(); } foreach my $i (0.. scalar(@tmp)) { $s_users = $s_users . qq($tmp[$i]\n); } $s_users = $s_users . qq(
Uname(s) Added ($count)
Uname
$tmp[$i]
\n); } #Output who_is if ($key eq "who_is") { $s_who_is = qq(
\n); for my $i (0..scalar(@tmp) - 1) { $tmp[$i] =~ s/\n/
/g; $tmp[$i] =~ s/->/---->/g; } for my $i (0..scalar(@tmp)) { $s_who_is = $s_who_is . qq(); } $s_who_is = $s_who_is . qq(
Who_is (Results $count)
WhoIs
$tmp[$i]
); } # Output attempts to table if ($key eq "results_$params{report_length}") { $s_attempts = qq(
); for my $i (0..scalar(@tmp)) { my @str = split(" ",$tmp[$i]); chomp(@str); my $newstr = qq(); foreach my $str (@str) { $str = qq(); $newstr = $newstr . $str; } $newstr = $newstr . qq(); $tmp[$i] = $newstr; } for my $i (0..scalar(@tmp)) { $s_attempts = $s_attempts . qq($tmp[$i]\n); } $s_attempts = $s_attempts . qq(
Attempts Last $params{report_length} Hours ($count)
User Port IP Timestamp
$str
\n); } } my $output_end = qq(); $output = $output . $s_ip . $s_users . $s_ports . $s_attempts . $s_who_is . $output_end; print $output; }