#!/usr/bin/perl -w
# vim:ts=4
#
# loadlogs v2.1
# Steve Shipway, steve@steveshipway.org, 2006
#
# Load Nagios 2.x logs from rotated files into mysql database
# summarise them for future reporting.
#
# v2.1 : add better error trapping

use strict;
use Text::ParseWords;
use DBI;
use Getopt::Std;
use POSIX;
use vars qw/$opt_f $opt_s $opt_h $opt_d $opt_C $opt_q $opt_D $opt_U $opt_P $opt_H $opt_p $opt_S $opt_T $opt_X $opt_t $opt_e $opt_B $opt_E/;

my($DBHOST) = "nagios.auckland.ac.nz";
my($DBUSER) = "xxxxxxxxx";
my($DBPASS) = "xxxxxxxxx";
my($DBPORT) = 3306;
my($DBNAME) = "nagios";
my($ALERTS ) = 'AD'; # Alerts, Hardalerts, Downtime, Notify, System, Xternal, Ev
my($dbh,$sth);
my($lastkey) = 0;
my(%STATUS) = ( 0=>'OK', 1=>'WARNING', 2=>'CRITICAL', 3=>'UNKNOWN',
	'OK'=>0, 'WARNING'=>1, 'CRITICAL'=>2, 'UNKNOWN'=>3, 
	'UP'=>0, 'DOWN'=>2, 'UNREACHABLE'=>3,
    'STARTED'=>0, 'STOPPED'=>1, 'CANCELLED'=>2 );
my($oldest,$newest) = (0,0);
my(@files);
my($CONNSTR);
my($error) = 0;
my($loadedok) = 0;
#############################################################################
sub create_tables {
	my($sql,$sth);

	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or do {
			print "Error connecting to database: $!\n";
			$error += 1;
			exit 1;
		};
	}
	
	if($opt_X) {
		print "Dropping any existing tables\n";
		$sql = "drop table `loaded`";
		$dbh->do($sql);
		$sql = "drop table `logs`";
		$dbh->do($sql);
		$sql = "drop table `logsummary`";
		$dbh->do($sql);
		$sql = "drop table `logevents`";
		$dbh->do($sql);
	}

	print "Creating table 'loaded' (filenames processed log)\n";
	$sql = "CREATE TABLE `loaded` (
  `filename` char(128) NOT NULL default '',
  `date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `id` tinyint(4) unsigned NOT NULL auto_increment,
  `complete` char(1) NOT NULL default 'N',
  PRIMARY KEY  (`id`),
  KEY `ifilename` (`filename`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=1 ;
";
	$dbh->do($sql);
	if($dbh->err) { $error += 1; print "Error: ".$dbh->errstr."\n"; }

	print "Creating table 'logs' (Nagios alert logs)\n";
	$sql = "CREATE TABLE `logs` (
  `id` mediumint(9) NOT NULL auto_increment,
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `host` varchar(64) NOT NULL default '',
  `service` varchar(64) NOT NULL default '',
  `msgtype` tinyint(1) NOT NULL default '0',
  `status` ENUM('0','1','2','3') NOT NULL default '0',
  `count` tinyint(1) default NULL,
  `hard` ENUM('H','S') default NULL,
  `text` tinytext NOT NULL,
  `fileid` tinyint(4) unsigned default NULL,
  `downtime` enum('0','1') default NULL,
  `hdowntime` enum('0','1') default NULL,
  PRIMARY KEY  (`id`),
  KEY `hst` (`host`,`service`,`time`),
  KEY `ths` (`time`,`host`,`service`),
  KEY `fileid` (`fileid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=1 ;
";
	$dbh->do($sql);
	if($dbh->err) { $error += 1; print "Error: ".$dbh->errstr."\n"; }

	print "Creating table 'recordtype' (Nagios log entry types)\n";
	$sql = "CREATE TABLE `recordtype` (
  `msgtype` int(1) NOT NULL default '0',
  `desc` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`msgtype`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
	$dbh->do($sql);
	if($dbh->err) { $error += 1; print "Error: ".$dbh->errstr."\n"; }
	
	print "Populating table recordtype with default values\n";
	$sql = "INSERT INTO `recordtype` VALUES(?,?);";
	$sth = $dbh->prepare($sql);
	$sth->execute(1,'HOST ALERT');
	$sth->execute(2, 'SERVICE ALERT');
	$sth->execute(101, 'HOST DOWNTIME ALERT');
	$sth->execute(102, 'SERVICE DOWNTIME ALERT');
	$sth->execute(111, 'CURRENT HOST STATE');
	$sth->execute(112, 'CURRENT SERVICE STATE');
	$sth->execute(11, 'HOST ALERT NOTIFICATION');
	$sth->execute(12, 'SERVICE ALERT NOTIFICATION');
	$sth->execute(20, 'SYSTEM');
	$sth->execute(30, 'EXTERNAL COMMAND');
	$sth->execute(41, 'HOST EVENT HANDLER');
	$sth->execute(42, 'SERVICE EVENT HANDLER');
	$sth->execute(51, 'HOST FLAPPING ALERT');
	$sth->execute(52, 'SERVICE FLAPPING ALERT');
	
	print "Creating table 'logsummary' (Nagios log daily summaries)\n";
	$sql = "CREATE TABLE `logsummary` (
  `date` date NOT NULL default '0000-00-00',
  `host` varchar(64) NOT NULL default '',
  `service` varchar(64) NOT NULL default '',
  `ok` mediumint(8) unsigned NOT NULL default '0',
  `oks` mediumint(8) unsigned NOT NULL default '0',
  `okhs` mediumint(8) unsigned NOT NULL default '0',
  `warning` mediumint(8) unsigned NOT NULL default '0',
  `warnings` mediumint(8) unsigned NOT NULL default '0',
  `warninghs` mediumint(8) unsigned NOT NULL default '0',
  `critical` mediumint(8) unsigned NOT NULL default '0',
  `criticals` mediumint(8) unsigned NOT NULL default '0',
  `criticalhs` mediumint(8) unsigned NOT NULL default '0',
  `unknown` mediumint(8) unsigned NOT NULL default '0',
  `unknowns` mediumint(8) unsigned NOT NULL default '0',
  `unknownhs` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`date`,`host`,`service`),
  UNIQUE KEY `hsd` (`host`,`service`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; ";
	$dbh->do($sql);
	if($dbh->err) {$error += 1;  print "Error: ".$dbh->errstr."\n"; }

	print "Creating table 'logevents' (Nagios log individual events)\n";
	$sql = "CREATE TABLE `logevents` (
  `id` mediumint(9) NOT NULL auto_increment,
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `host` varchar(64) NOT NULL default '',
  `service` varchar(64) NOT NULL default '',
  `status` ENUM('0','1','2','3') NOT NULL default '0',
  `text` tinytext NOT NULL default '',
  `downtime` enum('0','1') NOT NULL default '0',
  `hdowntime` enum('0','1') NOT NULL default '0',
  `duration` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `hst` (`host`,`service`,`time`),
  KEY `ths` (`time`,`host`,`service`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; ";
	$dbh->do($sql);
	if($dbh->err) { $error += 1; print "Error: ".$dbh->errstr."\n"; }
}
#############################################################################
sub do_help {
	print "Usage: loadlogs [-f][-h][-d][-q][-C][-T [-X]][-t types][-e days]\n                [-D dbname][-U username -P password][-H dbhost -p dbport]\n                [[-B begind/m/y][-E endd/m/y]][logfilename ... ]\n";
	print "       loadloags -s [-d]\n";
	print " -X: Drop tables first(DANGER!)\n -T: create tables in database\n -S: ignore Soft alert logs\n -d: debug mode\n -q: quiet mode - no progress counters\n -C: calculate summary records for loaded records (for ALL records if no files given)\n";
	print " -t: Specify log types.  Default is 'AD'\n     A=All alerts, H=Hard alerts, D=Downtime, N=Notifications, S=System,\n     X=External commands, E=Event handlers, F=Flapping alerts\n";
	print " -e: expire old entries older than this many days\n";
	print " -s: give current database status\n";
	print " -d: give more details\n";
	print " -f: force: load even if its already been done\n";
}

sub daystart($) { # return the nearest midnight...
	my($t) = $_[0];
	my(@gt,$nt);
	@gt = localtime($t);
	$nt = mktime(0,0,0,@gt[3..8]);
	return $nt;
}
sub dayend($) { # return the nearest midnight...
	my($t) = $_[0];
	my(@gt,$nt);
	@gt = localtime($t);
	$nt = mktime(0,0,0,@gt[3..8]) + (24*3600);
	return $nt;
}

my($s2) = "";
sub write_day_summary($$) {
	my($d,$svcptr) = @_;
	my($rv,$x);

#	print "\rDate is $d\n";
	# write a set of summary records for this day.
	# erase any existing ones.  $d is yyyy-mm-dd
	print " Writing..." unless($opt_q);

	if(!$s2) {
		$s2 = $dbh->prepare("insert into `logsummary` values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
	}
	$dbh->do("delete from `logsummary` where `date` = '$d'");
#	print " [     ][     ]";
	$x = ".";
	foreach my $h ( keys %$svcptr ) {
#		printf "\b\b\b\b\b\b\b\b\b\b\b\b\b%5.5s][     ]",$h;
		if(!$opt_q) {
		print "$x\b"; if( $x eq '.' ) { $x = 'O'; } else { $x = '.'; }
		}
		foreach my $s ( keys %{$svcptr->{$h}} ) {
#			printf "\b\b\b\b\b\b%5.5s]",$s;
			$s2->execute($d,$h,$s,
				$svcptr->{$h}{$s}{counters}{"0" },
				$svcptr->{$h}{$s}{counters}{"0D"}+
				$svcptr->{$h}{$s}{counters}{"0DH"},
				$svcptr->{$h}{$s}{counters}{"0H"},
				$svcptr->{$h}{$s}{counters}{"1" },
				$svcptr->{$h}{$s}{counters}{"1D"}+
				$svcptr->{$h}{$s}{counters}{"1DH"},
				$svcptr->{$h}{$s}{counters}{"1H"},
				$svcptr->{$h}{$s}{counters}{"2" },
				$svcptr->{$h}{$s}{counters}{"2D"}+
				$svcptr->{$h}{$s}{counters}{"2DH"},
				$svcptr->{$h}{$s}{counters}{"2H"},
				$svcptr->{$h}{$s}{counters}{"3" },
				$svcptr->{$h}{$s}{counters}{"3D"}+
				$svcptr->{$h}{$s}{counters}{"3DH"},
				$svcptr->{$h}{$s}{counters}{"3H"});
			if($s2->err) {
				print("\nInsert failed: ".$s2->errstr."\n") ;
			} elsif($s2->rows != 1) {
				print("\nInsert failed: $h/$s [$d]\n") ;
			}
		}
	}
	print " \b" unless($opt_q);
	print "\b\b\b\b\b\b\b\b\b\b\b           \b\b\b\b\b\b\b\b\b\b\b" 
		unless($opt_q);
}

# Print the status
sub do_status() {
	my(@r,$s,$c,$d,$f);
	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or do {
			print "\rError connecting: $!\n";
			$error += 1;
			return "Error connecting";
		};
	}

	# Count the loaded files
	$s = $dbh->prepare("select `filename`,UNIX_TIMESTAMP(`date`),`id`,`complete` from `loaded`");
	$s->execute();	
	if($s->err) { $error += 1;print "Error: ".$s->errstr."\n"; }
	$c = 0; $d = 0;
	while( @r = $s->fetchrow_array() ) {
		$c+=1; 
		if($r[1]>$d) { $d = $r[1]; $f = $r[0]; }
		if($opt_d) { print $r[0]." ".localtime($r[1])." [".$r[3]."]\n"; }
	}
	print "$c files have been loaded.\n";
	print "Last file was $f on ".localtime($d)."\n" if($f);
	print "\n";

	# Check actual log records
	$s = $dbh->prepare("select count(*) C,UNIX_TIMESTAMP(max(`time`)) T,UNIX_TIMESTAMP(min(`time`)) MINT from `logs`");
	$s->execute();	
	if($s->err) { $error += 1;print "Error: ".$s->errstr."\n"; }
	@r = $s->fetchrow_array();
	print "There are ".$r[0]." log records.\n";
	print "- The earliest from   ".localtime($r[2])."\n";
	print "- The most recent for ".localtime($r[1])."\n";

	# check log summaries
	$s = $dbh->prepare("select count(*) C,UNIX_TIMESTAMP(max(`date`)) T,UNIX_TIMESTAMP(min(`date`)) MINT from `logsummary`");
	$s->execute();	
	if($s->err) { $error += 1;print "Error: ".$s->errstr."\n"; }
	@r = $s->fetchrow_array();
	print "There are ".$r[0]." log summary records.\n";
	if(!$r[2]) {
		print "- Some have an invalid date!\n";
	} else {
		print "- The earliest from   ".localtime($r[2])."\n";
	}
	print "- The most recent for ".localtime($r[1])."\n" if($r[1]);

}

# Write a special record for this particular status block
# the ptr gives the details for h/s
my($s3)='';
sub cleanoutages($) {
	my($d) = $_[0];
	print " clearing.." unless($opt_q);
	$dbh->do("delete from logevents where `time` >= '$d' and `time` < DATE_ADD('$d',INTERVAL 1 DAY)");
	
	print "\b\b\b\b\b\b\b\b\b\b\b           \b\b\b\b\b\b\b\b\b\b\b" 
		unless($opt_q);
}
sub writeoutage($$$) {
	my($h,$s,$ptr) = @_;
	my($status,$downtime,$hdowntime);
	my($lstatus,$ltext);

	return if($ptr->{'lstatus'} =~ /^[U0]/); # dont need to log OK  or unavail
	return if(!$ptr->{'ltime'});

#	print "writing ".localtime($ptr->{ltime})."\n";
	if(!$s3) {
		$s3 = $dbh->prepare("insert into `logevents`(`time`,`host`,`service`,`status`,`text`,`downtime`,`hdowntime`,`duration`) values (FROM_UNIXTIME(?),?,?,?,?,?,?,?) ");
	}
	$status = $downtime = $hdowntime = 0;
	$lstatus = $ptr->{'lstatus'};
	return if($lstatus!~/^([123])(D?)(H?)/);
	return if($ptr->{'time'}<=$ptr->{'ltime'}); # zero interval??
	$status = $1 if($1);
	$downtime = 1  if($2);
	$hdowntime = 1 if($3);
	$ltext = $ptr->{'ltext'};
	$ltext = "(null)" if(!$ltext);
#	print "\r[$lstatus] [".$ptr->{status}."] $downtime $hdowntime\n"
#		if($lstatus=~/[DH]/);
	$s3->execute( $ptr->{ltime} , $h, $s, $status, 
		$ltext, $downtime, $hdowntime, 
		($ptr->{'time'}-$ptr->{'ltime'})
	);
	print "\rError writing record: ".$s3->errstr."\n" if($s3->errstr);
	print "\rRecord was not written!\n" if($s3->rows!=1);
}


sub calc_summary($$) {
	my($fromtime,$totime) = @_;
	my($sql,$sth,$row);	
	my($lastday) = 0;
	my($lasttime) = 0;
	my($t,$h,$s,$stat,$lstat,$ltime,$time,$c,$cd);
	my($ct) = time;
	my(@lt);
	my($lastseentime);
	my(%svc) = (); # current status
	# if totime=0 then make it the end of the db.  If fromtime=0 then
	# its the start of the db.  If it's not, we'll need to wind back
	# at least a day or more to get the previous status.
	# fromtime and totime need to be changed to be the top of the day.
	$fromtime = daystart($fromtime) if($fromtime);
	$totime   = dayend($totime)     if($totime);

	$c = $cd = 0;

	# at the end, we should write the current status of each host and
	# each service into the logs db for the use of the next.

	print "Processing summaries from "
		.($fromtime?localtime($fromtime):"beginning")." to "
		.($totime?localtime($totime):"end")."\n";

	# find out the list of hostnames/servicenames, and prep the %svc hash

	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or return "Error connecting to database";
	}

	# prep the service names, just in case
	$sql = "select `host_name`, `service_description` from `nagiosservice`";
	print "\nSummarising data: reading services   " unless($opt_q);
	$sth = $dbh->prepare($sql);
	print "\rSummarising data: running SQL        " unless($opt_q);
	$sth->execute();
	print "\rSummarising data: loading services   " unless($opt_q);
	while( $row = $sth->fetchrow_hashref() ) {
		$h = $row->{host_name};
		$s = $row->{service_description};
		$svc{$h}{$s}{status} = 'U';
		$svc{$h}{""}{status} = 'U';
		$svc{$h}{$s}{counters} = { 
			"U"=>0, "UH"=>0, "UD"=>0, "UDH"=>0,
			"0"=>0, "0H"=>0, "0D"=>0, "0DH"=>0,
			"1"=>0, "1H"=>0, "1D"=>0,
			"2"=>0, "2H"=>0, "2D"=>0,
			"1DH"=>0, "2DH"=>0, "3DH"=>0,
			"3"=>0, "3H"=>0, "3D"=>0 };
		$svc{$h}{""}{counters} = { 
			"U"=>0, "UH"=>0, "UD"=>0, "UDH"=>0,
			"0"=>0, "0H"=>0, "0D"=>0, "0DH"=>0,
			"1"=>0, "1H"=>0, "1D"=>0,
			"2"=>0, "2H"=>0, "2D"=>0,
			"1DH"=>0, "2DH"=>0, "3DH"=>0,
			"3"=>0, "3H"=>0, "3D"=>0 };
	}

	# Now we process the actual log entries
	$sql = "select *, date(`time`) d, unix_timestamp(`time`) u from `logs` WHERE 1 ";
	$sql .=  " and `time` >= FROM_UNIXTIME(".($fromtime-(48*3600)).") " if($fromtime);
	$sql .=  " and `time` <= FROM_UNIXTIME($totime) " if($totime);
	$sql .= " and `msgtype` in ( 1, 2, 101, 102, 111, 112 ) order by `time`";

	print "\rSummarising data: preparing SQL      " unless($opt_q);
	$sth = $dbh->prepare($sql);
	print "\rSummarising data: running SQL        " unless($opt_q);
	$sth->execute();
	print "\rSummarising data: processing records " unless($opt_q);
	while( $row = $sth->fetchrow_hashref() ) {
		next if($row->{hard} and $row->{hard} eq 'S');
		$c++;	
		if(!$opt_q) {
			if(!$c or (time-$ct)>1) { $ct=time;
				printf "\b\b\b\b\b\b\b\b\b%8d)",$c;
			}
		}
		# check for day change
		if($lastday ne $row->{d}) {
			# day change!
			if(!$opt_q){
				printf "\rSummarising data: %s (%8d)" ,$row->{d}, $c;
				$ct = time;
			}
			# calculate this time point
#			printf " %10.10s/%10.10s=%2.2s","","","" if(!$opt_q);
			foreach $h (keys %svc) {
				foreach $s ( keys %{$svc{$h}} ) {
#					printf "\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b %10.10s/%10.10s=%2.2s",$h,$s,($svc{$h}{$s}{status}?$svc{$h}{$s}{status}:"?") if(!$opt_q);
					$svc{$h}{$s}{status} = '0' if(!defined $svc{$h}{$s}{status});
					$svc{$h}{$s}{lstatus} = $svc{$h}{$s}{status};
					$svc{$h}{$s}{ltime} = $svc{$h}{$s}{'time'};
					$svc{$h}{$s}{ltext} = $svc{$h}{$s}{'text'};
					$svc{$h}{$s}{'time'} = daystart($row->{u});
					$svc{$h}{$s}{counters}{$svc{$h}{$s}{lstatus}}
						+= $svc{$h}{$s}{'time'}-$svc{$h}{$s}{ltime}
						if($svc{$h}{$s}{ltime});
					writeoutage($h,$s,$svc{$h}{$s})
						if($svc{$h}{$s}{ltime}
						and $svc{$h}{$s}{ltime}>=$fromtime) ;
				}
			}
			# if >fromtime, then write this day to db
			write_day_summary($lastday,\%svc) 
				if($lastday and $lasttime>=$fromtime) ;
			# reset all counters
			foreach $h (keys %svc) {
				foreach $s ( keys %{$svc{$h}} ) {
					$svc{$h}{$s}{counters} = { 
						"U"=>0, "UH"=>0, "UD"=>0, "UDH"=>0,
						"0"=>0, "0H"=>0, "0D"=>0, "0DH"=>0,
						"1"=>0, "1H"=>0, "1D"=>0,
						"2"=>0, "2H"=>0, "2D"=>0,
						"1DH"=>0, "2DH"=>0, "3DH"=>0,
						"3"=>0, "3H"=>0, "3D"=>0 };
				}
			}
			# reset lastday
			$lastday = $row->{d};
			$c = 0;
			$cd++; $ct=time;
			cleanoutages($lastday) 
				if($row->{u}>=$fromtime) ;
		}

		# update current statuses for this record
		$lastseentime = $row->{u};
		$h = $row->{host}; next if(!$h);
		$s = $row->{service}; $s = "" if(!$s);
		$svc{$h}{""}{"exists"} = 1; # creates hashes implicitly
		$svc{$h}{$s}{"exists"} = 1; # creates hashes implicitly
		$lstat = $svc{$h}{$s}{status}; $lstat = "0" if(!defined $lstat);
		$t = $row->{msgtype};
		if(($t == 1) or ($t == 2) or ($t == 111) or ($t == 112)) { # alert
			$stat = $row->{status};
		} elsif(($t == 101) or ($t == 102)) { # svc downtime
			$stat = $svc{$h}{$s}{status}; $stat = '0' if(!defined $stat);
			$stat =~ s/[DH]//g;
			if( $row->{status} ) { # 0=start, 1=end, 2=cancel
				$svc{$h}{$s}{downtime} = 0;
			} else {
				$svc{$h}{$s}{downtime} = 1;
			}
#			print "\rDowntime signal\n";
			if(!$s) { # change ALL the services
				foreach my $ss ( keys %{$svc{$h}} ) {
					next if(!$ss);
					$svc{$h}{$ss}{status} = '0' 
						if(!defined $svc{$h}{$ss}{status});
					$svc{$h}{$ss}{lstatus} = $svc{$h}{$ss}{status};
					$svc{$h}{$ss}{status} =~ s/[DH]//g;
					if($svc{$h}{$ss}{downtime}) { $svc{$h}{$ss}{status}.='D'; }
					if($svc{$h}{""}{downtime}) { $svc{$h}{$ss}{status}.='H' ; }
					$svc{$h}{$ss}{ltime} = $svc{$h}{$ss}{'time'};
					$svc{$h}{$ss}{ltext} = $svc{$h}{$ss}{'text'};
					$svc{$h}{$ss}{'time'} = $row->{u};
					$svc{$h}{$ss}{counters} = { 
						"U"=>0, "UH"=>0, "UD"=>0, "UDH"=>0,
						"0"=>0, "0H"=>0, "0D"=>0, "0DH"=>0,
						"1"=>0, "1H"=>0, "1D"=>0, "1DH"=>0,
						"2"=>0, "2H"=>0, "2D"=>0, "2DH"=>0,
						"3"=>0, "3H"=>0, "3D"=>0, "3DH"=>0 }
						if(!defined  $svc{$h}{$ss}{counters} );
					$svc{$h}{$ss}{counters}{$svc{$h}{$ss}{lstatus}}
						+= $svc{$h}{$ss}{'time'}-$svc{$h}{$ss}{ltime}
						if($svc{$h}{$ss}{ltime});
					writeoutage($h,$ss,$svc{$h}{$ss})
						if($svc{$h}{$s}{ltime} 
						and $svc{$h}{$ss}{ltime}>=$fromtime) ;
				}
			}
		}  else { next; }
		if($svc{$h}{$s}{downtime}) { $stat .= "D" ; }
		if($s and $svc{$h}{""}{downtime}) { $stat .= "H" ; }
		$svc{$h}{$s}{lstatus} = $lstat;
		$svc{$h}{$s}{status} = $stat;
		$svc{$h}{$s}{ltime} = $svc{$h}{$s}{'time'};
		$svc{$h}{$s}{ltext} = $svc{$h}{$s}{'text'};
		$svc{$h}{$s}{text} = $row->{text} if($row->{text});
		$svc{$h}{$s}{'time'} = $row->{u};
		$svc{$h}{$s}{counters} = { 
			"U"=>0, "UH"=>0, "UD"=>0, "UDH"=>0, "0DH"=>0,
 			"0"=>0, "0H"=>0, "0D"=>0, "1"=>0, "1H"=>0, "1D"=>0,
			"2"=>0, "2H"=>0, "2D"=>0, "3"=>0, "3H"=>0, "3D"=>0,
			"1DH"=>0, "2DH"=>0, "3DH"=>0 }
			if(!defined  $svc{$h}{$s}{counters} );
		$svc{$h}{""}{counters} = { 
			"U"=>0, "UH"=>0, "UD"=>0, "UDH"=>0, "0DH"=>0,
 			"0"=>0, "0H"=>0, "0D"=>0, "1"=>0, "1H"=>0, "1D"=>0,
			"2"=>0, "2H"=>0, "2D"=>0, "3"=>0, "3H"=>0, "3D"=>0,
			"1DH"=>0, "2DH"=>0, "3DH"=>0 }
			if(!defined  $svc{$h}{""}{counters} );
		$svc{$h}{$s}{counters}{$lstat} +=
			$svc{$h}{$s}{'time'}-$svc{$h}{$s}{ltime}
			if($svc{$h}{$s}{ltime});
		writeoutage($h,$s,$svc{$h}{$s})
			if($svc{$h}{$s}{ltime} and $svc{$h}{$s}{ltime}>=$fromtime) ;
		$lasttime = $row->{u};
	}
	# Now we are at the end, write the last record.
	# first, calculate last count
	$lastseentime = $totime if(!$lastseentime);
	$lastseentime = dayend($lastseentime);
	foreach $h (keys %svc) {
		foreach $s ( keys %{$svc{$h}} ) {
			$svc{$h}{$s}{status} = '0' if(!defined  $svc{$h}{$s}{status});
			$svc{$h}{$s}{lstatus} = $svc{$h}{$s}{status};
			$svc{$h}{$s}{ltext} = $svc{$h}{$s}{'text'};
			$svc{$h}{$s}{ltime} = $svc{$h}{$s}{'time'};
			$svc{$h}{$s}{'time'} = $lastseentime; # $totime;
			$svc{$h}{$s}{counters}{$svc{$h}{$s}{lstatus}}
				+= $svc{$h}{$s}{'time'}-$svc{$h}{$s}{ltime}
				if($svc{$h}{$s}{ltime}
					and($svc{$h}{$s}{'time'}-$svc{$h}{$s}{ltime})<(24*3600)
				);
			writeoutage($h,$s,$svc{$h}{$s});
		}
	}
	write_day_summary($lastday,\%svc) ;
	$sth->finish;
	print "\nSummarising complete.\n" if(!$opt_q);
}

# save: time, type, host, svc, status, count, hard, txt, id
sub dbsave($$$$$$$$$) {
	my(@fld) = @_;
	my($type,$status,$count,$hard,$text,$svc,$rv,$t);
	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or return "Error connecting to database";
	}
	if(!$sth) {
		$sth = $dbh->prepare("insert into `logs`(`host`,`service`,`time`,`msgtype`,`status`,`count`,`hard`,`text`,`fileid`) values(?,?,FROM_UNIXTIME(?),?,?,?,?,?,?)");
		if(!$sth) { print "\nUnable to define insert!\n"; exit(1); }
	}
	$t = $fld[0];
	$oldest = $t if($oldest>$t or !$oldest); $newest = $t if($newest<$t);
	$status = $hard = undef;
	$hard = "H" if($fld[6] and $fld[6]=~/HARD/);
	$hard = "S" if($fld[6] and $fld[6]=~/SOFT/);
	$status = $STATUS{$fld[4]} if($fld[4]);
	$count = $fld[5]; $count = 0 if(!$count);
	$text = $fld[7]; #$text = "(null)" if(!$text);
#	$type = 0;
#	foreach ( keys %TYPES ) { 
#		if( $fld[1] =~ /$_/ ) { $type = $TYPES{$_}; last; } }
	$type = $fld[1];
	$svc = $fld[3]; $svc = "" if(!$svc);
	$rv = $sth->execute($fld[2],$svc,$t,$type,$status,$count,$hard,$text,$fld[8]);
	if($sth->err) { 
		print "\nWrite error: ".$sth->errstr."\nUnable to load log file, exiting.\n";
		print "[".(join ",",@fld)."]\n";
		exit(1);
	}
	return $rv;
}
sub register {
	my($k,$flag) = @_;
	my(@row,$s,$id);
	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or return "Error connecting to database";
	}
	if(!$flag) {
		$s = $dbh->prepare("insert into `loaded`(`filename`,`date`) values(?,NOW())");
		$s->execute($k) ;
		if($s->err) {
			print "\nError inserting into loaded table: ".$s->errstr."\n";
			return 0;
		}
		$s = $dbh->prepare("select `id` from `loaded` where `filename` = ?");
		$s->execute($k);
		if($s->err) {
			print "\nError querying loaded table: ".$s->errstr."\n";
			return 0;
		}
		@row = $s->fetchrow_array();
		$id = $row[0];
		if(!$id) { print "\nProblem: received an error inserting into loaded table\n"; }
		return($id);
	} else {
		$s = $dbh->prepare("update `loaded` set `complete` = 'Y' where `id` = ?");
		$s->execute($k);
		if($s->err) {
			print "\nError updating loaded table: ".$s->errstr."\n";
			return 0;
		}
		return 0;
	}
}
sub beendone($) {
	my($f) = $_[0];
	my(@r,$s);
	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or do {
			print "\rError connecting: $!\n";
			return "Error connecting";
		};
	}
	$s = $dbh->prepare("select `filename`,`date`,`id`,`complete`  from `loaded` where `filename` = ?");
	$s->execute($f);
	if($s->err) {
		print "\rError querying loaded table: ".$s->errstr."\n";
		return 0;
	}
	@r = $s->fetchrow_array();
	return 0 if(!$r[2]);
	return $r[1] if($r[3] eq 'Y');
	if($opt_q) {
		print "This file previously failed loading. Clearing old data.\n";
	} else {
		print "\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\bPREVIOUSLY FAILED\nClearing old data....\n";
	}
	$s = $dbh->prepare("delete from `logs` where `fileid` = ?");
	$s->execute($r[2]);
	if($s->err) {
		print "\rUnable to clear old records: ".$s->errstr."\n";
		return 0;
	}
	$s = $dbh->prepare("delete from `loaded` where `filename` = ?");
	$s->execute($f);
	if($s->err) {
		print "\rUnable to clear old 'loaded' record: ".$s->errstr."\n";
	}
	print "Reloading data:       0 (      0)" unless($opt_q);
	return 0;
}
sub parselog($) {
	my($f) = $_[0];
	my($t,$type,@fld,$arg,$fn);
	my($i) = 0; my($j) = 0;
	my($ct) = time;
	my($id,$pd);
	return if(! -r $f);
	$fn = $f; $fn =~ s/^.*\///;
	if( $pd = beendone($fn) and !$opt_f) { 
		print "\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\bALREADY LOADED ($pd)\n" unless($opt_q); 
		print "$f already loaded on $pd\n" if($opt_q);
		$error += 1;
		return;
	}
	$id = register($fn,0);
	if(!$id) {
		print "\rError registering file!\n";
		$error += 1;
		return;
	}
	open LOG, "<$f";
	while( <LOG> ) {
		chomp;
		if(!/^\[(\d+)\]\s+(\S[^:]+):\s*(.*)/) {
#			print "$_\n";
			if(/^\[(\d+)\]\s+(.*)/ and $ALERTS=~/S/) {
				dbsave($1,20,'','',0,undef,undef,$2,$id);
			}
			next;
		}
		$i += 1;
		if(( time - $ct ) > 1) { 
			$ct = time; printf "\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b%7d (%7d)",$i,$j unless($opt_q); }
		$t = $1; $type = $2; $arg = $3;
# save: time, type, host, svc, status, count, hard, txt, id
		if($type eq 'SERVICE ALERT') {
			@fld = split /;/,$arg,6;
			if($ALERTS=~/A/ or ($ALERTS=~/H/ and $fld[3] eq 'HARD')) {
				dbsave($t,2,$fld[0],$fld[1],$fld[2],$fld[4],$fld[3],$fld[5],$id);
				$j += 1;
			}
		} elsif( $type eq 'HOST ALERT' ) {
			@fld = split /;/,$arg,5;
			if($ALERTS=~/A/ or ($ALERTS=~/H/ and $fld[2] eq 'HARD')) {
				dbsave($t,1,$fld[0],"",$fld[1],$fld[3],$fld[2],$fld[4],$id);
				$j += 1;
			}
		} elsif( $type eq 'HOST DOWNTIME ALERT' ) {
			if($ALERTS=~/D/){
				@fld = split /;/,$arg,3;
				dbsave($t,101,$fld[0],"",$fld[1],undef,undef,$fld[2],$id);
				$j += 1;
			}
		} elsif( $type eq 'SERVICE DOWNTIME ALERT' ) {
			if($ALERTS=~/D/) {
				@fld = split /;/,$arg,4;
				dbsave($t,102,$fld[0],$fld[1],$fld[2],undef,undef,$fld[3],$id);
				$j += 1;
			}
		} elsif( $type eq 'SERVICE NOTIFICATION' ) {
			if($ALERTS=~/N/) {
				@fld = split /;/,$arg,6;
				dbsave($t,12,$fld[1],$fld[2],$fld[3],undef,undef,$fld[5],$id);
				$j += 1;
			}
		} elsif( $type eq 'HOST NOTIFICATION' ) {
			if($ALERTS=~/N/) {
				@fld = split /;/,$arg,5;
				dbsave($t,11,$fld[1],'',$fld[2],undef,undef,$fld[4],$id);
				$j += 1;
			}
		} elsif( $type eq 'SERVICE EVENT HANDLER' ) {
			if($ALERTS=~/E/) {
				@fld = split /;/,$arg,6;
				dbsave($t,42,$fld[0],$fld[1],$fld[2],$fld[4],$fld[3],$fld[5],$id);
				$j += 1;
			}
		} elsif( $type eq 'HOST EVENT HANDLER' ) {
			if($ALERTS=~/E/) {
				@fld = split /;/,$arg,5;
				dbsave($t,41,$fld[0],"",$fld[1],$fld[3],$fld[2],$fld[4],$id);
				$j += 1;
			}
		} elsif( $type eq 'EXTERNAL COMMAND' ) {
			if($ALERTS=~/X/) {
				dbsave($t,30,'','',0,undef,undef,$arg,$id);
				$j += 1;
			}
		} elsif( $type eq 'HOST FLAPPING ALERT' ) {
			if($ALERTS=~/F/) {
				@fld = split /;/,$arg,3;
				dbsave($t,51,$fld[0],"",$fld[1],undef,undef,$fld[2],$id);
				$j += 1;
			}
		} elsif( $type eq 'SERVICE FLAPPING ALERT' ) {
			if($ALERTS=~/F/) {
				@fld = split /;/,$arg,4;
				dbsave($t,52,$fld[0],$fld[1],$fld[2],undef,undef,$fld[3],$id);
				$j += 1;
			}
		} elsif($type eq 'CURRENT SERVICE STATE') {
			@fld = split /;/,$arg,6;
			dbsave($t,112,$fld[0],$fld[1],$fld[2],$fld[4],$fld[3],$fld[5],$id);
			$j += 1;
		} elsif( $type eq 'CURRENT HOST STATE' ) {
			@fld = split /;/,$arg,5;
			dbsave($t,111,$fld[0],"",$fld[1],$fld[3],$fld[2],$fld[4],$id);
			$j += 1;
		} else {
#			print "\r$type?                                \n";
			dbsave($t,20,'','',0,undef,undef,"$type: $arg",$id)
				if($ALERTS=~/S/);
		}
# save: time, type, host, svc, status, count, hard, txt, id
	}
	close LOG;
	printf "\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b%7d (%7d) COMPLETE\n",$i,$j 
		unless($opt_q);
	register($id,1);
	$loadedok += 1;
}
sub do_expire($) {
	my($days) = $_[0];
	my($s);

	if($days < 8) {
		print "\rError: days of $days is too few for it to be safe!\n";
		return "Too few days";
	}
	if(!$dbh) {
		$dbh = DBI->connect("DBI:$CONNSTR", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or do {
			print "\rError connecting: $!\n";
			$error += 1;
			return "Error connecting";
		};
	}
	print "Expiring old logs\n" unless($opt_q);
	$s = $dbh->prepare("delete from `logs` where  `time` < FROM_UNIXTIME(?)");
	$s->execute(time - ($days*24*3600));
	if($s->err) { print "Error: ".$s->errstr."\n"; }
#	print "Expiring old summary logs\n" unless($opt_q);
#	$s = $dbh->prepare("delete from `logsummary` where  `date` < FROM_UNIXTIME(?)");
#	$s->execute(time - ($days*24*3600));
#	if($s->err) { print "Error: ".$s->errstr."\n"; }
	return 0;
}
####################################################################

$|=1;
getopts('fe:dqChSTH:U:P:p:D:Xt:B:E:s');

if($opt_h) { do_help(); exit 0; }
$DBHOST = $opt_H if($opt_H);
$DBUSER = $opt_U if($opt_U);
$DBPASS = $opt_P if($opt_P);
$DBPORT = $opt_p if($opt_p);
$DBNAME = $opt_D if($opt_D);
$ALERTS = $opt_t if($opt_t); $ALERTS =~ s/A/H/g if($opt_S);
if( $ARGV[0] and $ARGV[0]=~/^-/ ) { do_help(); exit 0; }
@files = @ARGV;

# Database connection string -- DBI format.
# If you have something other than mysql, you may be able to change this
$CONNSTR = "mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT";

if($opt_s) { do_status; exit (0); }

print "Starting processing ".localtime()."\n";
print "Not loading soft alert logs\n" if($opt_S);

create_tables() if($opt_T);

print "Processing files\n" if(@files);
foreach ( @files ) {
	print "$_\nLoading data:       0 (      0)" unless($opt_q);
	print "Processing $_\n" if($opt_q);
	parselog($_);
}
if($error and !$loadedok) { # we loaded nothing due to error so stop
	print "Stopping because of error.\n";
	exit 1;
}
if($opt_B) {
	if($opt_B =~ /(\d\d?)\/(\d\d?)\/(\d?\d?\d?\d)/ ) {
		$oldest = mktime(0,0,0,$1,$2-1,(($3<100)?($3+100):($3-1900)),0,0,-1);
	} elsif($opt_B =~ /(\d\d?\d?\d?)-(\d\d?)-(\d\d?)/ ) {
		$oldest = mktime(0,0,0,$3,$2-1,(($1<100)?($1+100):($1-1900)),0,0,-1);
	} else {
		print "\nBegin option ($opt_B) not recognised.  Using default.\n";
	}
}
if($opt_E) {
	if($opt_E =~ /(\d\d?)\/(\d\d?)\/(\d?\d?\d?\d)/ ) {
		$newest = mktime(0,0,0,$1,$2-1,(($3<100)?($3+100):($3-1900)),0,0,-1);
	} elsif($opt_E =~ /(\d\d?\d?\d?)-(\d\d?)-(\d\d?)/ ) {
		$newest = mktime(0,0,0,$3,$2-1,(($1<100)?($1+100):($1-1900)),0,0,-1);
	} else {
		print "\nEnd option ($opt_E) not recognised.  Using default.\n";
	}
}
if($error) {
	print "Stopping because of error.\n";
	exit 1;
}
if($opt_C) { calc_summary($oldest,$newest); }
if($error) {
	print "Stopping because of error.\n";
	exit 1;
}
if($opt_e) { do_expire($opt_e); }
if($error) {
	print "Stopping because of error.\n";
	exit 1;
}

print "Actions complete ".localtime()."\n";
exit 0;
