##
#!/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(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(Ports Added ($count) Port $tmp[$i]
\n);
# $output = $output . qq($s_ports);
}
#Output new users found
if ($key eq "new_users") {
$s_users = qq(Uname(s) Added ($count) Uname $tmp[$i]
\n);
}
#Output who_is
if ($key eq "who_is") {
$s_who_is = qq(Who_is (Results $count)
WhoIs
/g;
$tmp[$i] =~ s/->/---->/g;
}
for my $i (0..scalar(@tmp)) {
$s_who_is = $s_who_is . qq($tmp[$i]
);
}
# Output attempts to table
if ($key eq "results_$params{report_length}") {
$s_attempts = qq(
Attempts Last $params{report_length} Hours ($count)
User
Port
IP
Timestamp
);
for my $i (0..scalar(@tmp)) {
my @str = split(" ",$tmp[$i]);
chomp(@str);
my $newstr = qq();
foreach my $str (@str) {
$str = qq($str );
$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(
\n);
}
}
my $output_end = qq();
$output = $output . $s_ip . $s_users . $s_ports . $s_attempts . $s_who_is . $output_end;
print $output;
}
####
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE "attempts" (
"eid" INTEGER PRIMARY KEY AUTOINCREMENT,
"ip_id" INTEGER NOT NULL,
"user_id" INTEGER NOT NULL,
"port_id" INTEGER NOT NULL,
"timestamp" TEXT NOT NULL
, "create_time" TEXT);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE ip (
"eid" INTEGER PRIMARY KEY AUTOINCREMENT,
"IP" TEXT NOT NULL
, "create_time" TEXT);
CREATE TABLE ports (
"eid" INTEGER PRIMARY KEY AUTOINCREMENT,
"port" INTERGER NOT NULL,
"create_time" TEXT);
CREATE TABLE users (
"eid" INTEGER PRIMARY KEY AUTOINCREMENT,
"user" TEXT NOT NULL,
"create_time" TEXT);
CREATE TABLE who_is (
"eid" INTEGER PRIMARY KEY AUTOINCREMENT,
"entry" TEXT NOT NULL,
"ip_id" INTEGER NOT NULL,
"timestamp" TEXT NOT NULL
, "create_time" TEXT);
CREATE TABLE "run_log" (
"eid" INTEGER PRIMARY KEY AUTOINCREMENT,
"run_time_start" TEXT,
"run_time_end" TEXT,
"input_file" TEXT,
"last_log_first_line" TEXT,
"last_line_read" INTEGER
);
CREATE VIEW try1 as
select u.user, p.port, i.ip, a.timestamp, a.create_time
from attempts a join
ports p on a.port_id = p.eid join
ip i on i.eid = a.ip_id join
users u on u.eid = a.user_id
order by a.timestamp;