#!/usr/bin/perl -w
# vim:ts=4
# Set up all the appropriate files
#
# loadlogs: Nagios log parser and mysql database loader. Steve Shipway, 2006
# www.steveshipway.org

use strict;
use DBI;
use Getopt::Std;
use vars qw/$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/;

my($DBHOST) = "nagios.company.com";
my($DBUSER) = "user";
my($DBPASS) = "password";
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);
#############################################################################
sub create_tables {
	my($sql);

	if(!$dbh) {
		$dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $DBUSER, $DBPASS, { PrintError=>0, RaiseError=>0 }) or do {
			print "Error connecting to database: $!\n";
			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);
	}

	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);

	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(32) NOT NULL default '',
  `service` varchar(32) 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);

	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;
INSERT INTO `recordtype` VALUES (1, 'HOST ALERT');
INSERT INTO `recordtype` VALUES (2, 'SERVICE ALERT');
INSERT INTO `recordtype` VALUES (101, 'HOST DOWNTIME ALERT');
INSERT INTO `recordtype` VALUES (102, 'SERVICE DOWNTIME ALERT');
INSERT INTO `recordtype` VALUES (11, 'HOST ALERT NOTIFICATION');
INSERT INTO `recordtype` VALUES (12, 'SERVICE ALERT NOTIFICATION');
INSERT INTO `recordtype` VALUES (20, 'SYSTEM');
INSERT INTO `recordtype` VALUES (30, 'EXTERNAL COMMAND');
INSERT INTO `recordtype` VALUES (41, 'HOST EVENT HANDLER');
INSERT INTO `recordtype` VALUES (42, 'SERVICE EVENT HANDLER');
INSERT INTO `recordtype` VALUES (51, 'HOST FLAPPING ALERT');
INSERT INTO `recordtype` VALUES (52, 'SERVICE FLAPPING ALERT');";
	$dbh->do($sql);
	
}
#############################################################################
sub do_help {
	print "Usage: loadlogs [-h][-d][-q][-C][-T [-X]][-t types]\n                [-D dbname][-U username -P password][-H dbhost -p dbport]\n                [logfilename ... ]\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 downtimes 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";
}

sub calc_downtime {
}

# 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:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $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(?),?,?,?,?,?,?)");
		return if(!$sth);
	}
	$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);
#	$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,$fld[7],$fld[8]);
	return $rv;
}
sub register {
	my($k,$flag) = @_;
	my(@row,$s,$id);
	if(!$dbh) {
		$dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $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) ;
		$s = $dbh->prepare("select id from loaded where filename = ?");
		$s->execute($k);
		@row = $s->fetchrow_array();
		$id = $row[0];
		if(!$id) { print "\nProblem: received $id\n"; }
		return($id);
	} else {
		$s = $dbh->prepare("update loaded set complete = 'Y' where id = ?");
		$s->execute($k);
		return 0;
	}
}
sub beendone($) {
	my($f) = $_[0];
	my(@r,$s);
	if(!$dbh) {
		$dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $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);
	@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]);
	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) ) { 
		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);
		return;
	}
	$id = register($fn,0);
	if(!$id) {
		print "\rError registering file!\n";
		return;
	}
	open LOG, "<$f";
	while( <LOG> ) {
		chomp;
		if(!/^\[(\d+)\]\s+(\S[^:]+):\s*(.*)/) {
			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' ) {
			@fld = split /;/,$arg,5;
			if($ALERTS=~/A/ or ($ALERTS=~/H/ and $fld[2] eq 'HARD')) {
			dbsave($t,51,$fld[0],"",$fld[1],$fld[3],$fld[2],$fld[4],$id);
			$j += 1;
			}
		} elsif( $type eq 'SERVICE FLAPPING ALERT' ) {
			@fld = split /;/,$arg,6;
			if($ALERTS=~/A/ or ($ALERTS=~/H/ and $fld[3] eq 'HARD')) {
			dbsave($t,52,$fld[0],$fld[1],$fld[2],$fld[4],$fld[3],$fld[5],$id);
			$j += 1;
			}
		} else {
			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);
}
####################################################################

$|=1;
getopts('dqChSTH:U:P:p:D:Xt:');

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;

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($opt_C) { calc_downtime($oldest,$newest); }

print "Actions complete ".localtime()."\n";
exit 0;