#!/usr/local/bin/perl
#
# Copyright (c) 2003 Jeremy Kister
# Author: Jeremy Kister <argus-devel at jeremykister.com>
# Date: 2003-Aug-14 13:34 (EDT)
# sqlnotify.pl -- Argus method to send notifications to sql server (works for MSSQL and MySQL <with dsn change>)
# :set tabstop=4 makes me prettier
#
# in argus config:
#
# notify: sql:server1.example.net
# nolotsmsgs:     yes
#
# Method "sql" {
#         command: /export/scripts/sqlnotify.pl %R
#         send:   %M - %T
# }
#
#
#
# Table structure for table `argus_alarms`
#
#CREATE TABLE `argus_alarms` (
#  `reported_time` char(19) NOT NULL default '',
#  `cleared_time` char(19) default NULL,
#  `location` varchar(40) default NULL,
#  `host` varchar(255) default NULL,
#  `alarm_description` varchar(64) NOT NULL default '',
#  `priority` tinyint(1) unsigned NOT NULL default '0',
#  `argus_id` int(5) unsigned NOT NULL default '0',
#  PRIMARY KEY (`argus_id`)
#) TYPE=MyISAM AUTO_INCREMENT=37 ;


use DBI;

die "syntax error: no hostname given\n" unless($server = shift);

$dbun = 'username';
$dbpw = 'password';

$dbh = DBI->connect("DBI:mysql:host=$server;database=isp", $dbun, $dbpw) || die "cannot connect to sql server: $server\n";

while(<STDIN>){
	#  the lotsnotify down message prints date only once (makes us .* at the end)
	if(/^(\d+)\s(.+)\s(is|are)\s(UP|DOWN|OK|NOTOK)(\s-\s)?(.*)/){
		#31771 Top:Test:jeremy-03:FTP_jeremy-03.example.net is DOWN - 08/Aug 12:24
		#31772 Top:Test:jeremy-03:FTP_jeremy-03.example.net is UP - 08/Aug 12:25
		#31771 Top:Essential_Services:Recursive-west:NS_All are NOTOK - 08/Aug 12:24 (self-defined messagedn:)
		$argus_id=$1;
		$object=$2;
		$plural=$3;
		$status=$4;
		$time=$6;
	
		if($object =~ /(.+):(\S+)_(\S+)/){
			$misc=$1;
			$service=$2;
			$host=$3;
			# 10 = bvn down, 9 = site down, 7 = important service, ...
			if($misc =~ /World_Reachability/){
				$priority=10;
			}elsif($misc =~ /Essential_Services/){
				$priority=7;
			}elsif($misc =~ /Routers/){
				$priority=6;
			}elsif($misc =~ /Upstream_Connections/){
				$priority=5;
			}elsif($misc =~ /Customer_Connectivity/){
				$priority=2;
			}elsif($misc =~ /Test/){
				$priority=1;
			}else{
				$priority=3;
			}
		}else{
			mailerror("cannot parse object: $object -- all: $_");
			next;
		}
		
		if($time =~ /^(\d+)\/(\S+)\s(.+)/){
			$day=$1;
			$mon=$2;
			$tod=$3;
			if($mon eq "Jan"){ $month="01";
			}elsif($mon eq "Feb"){ $month="02";
			}elsif($mon eq "Mar"){ $month="03";
			}elsif($mon eq "Apr"){ $month="04";
			}elsif($mon eq "May"){ $month="05";
			}elsif($mon eq "Jun"){ $month="06";
			}elsif($mon eq "Jul"){ $month="07";
			}elsif($mon eq "Aug"){ $month="08";
			}elsif($mon eq "Sep"){ $month="09";
			}elsif($mon eq "Oct"){ $month="10";
			}elsif($mon eq "Nov"){ $month="11";
			}elsif($mon eq "Dec"){ $month="12";
			}else{
				# we dont want to die, because we want the notice (just in case)
				$mon="???";
			}
			chomp($year = `date +%Y`);
			$date = "$month/$day/$year $tod:00";
		}else{
			chomp($date = `date "+%m/%d/%Y %H:%M:%S"`);
		}
		
		if($host =~ /^(.{2,3})-/){
			$acronym = $1;
			if($acronym eq "hor"){ $location="Horsham";
			 }elsif($acronym eq "bos"){ $location="Boston";
			 }elsif($acronym eq "lic"){ $location="LIC";
			 }elsif($acronym eq "syr"){ $location="Syracuse";
			 }elsif($acronym eq "man"){ $location="Manhatten";
			 }elsif($acronym eq "sj"){ $location="San Jose";
			}
		}
		unless(defined($location)){
			if($object =~ /(LIC|Boston|Syracuse|Horsham|Manhatten|San Jose)/){
				$location = $1;
			}
		}
		
		if($status =~ /(DOWN|NOTOK)/){
			# make sure its not a dupe (from a re-notify)
			$sql = "SELECT reported_time FROM argus_alarms WHERE argus_id = " . $dbh->quote($argus_id);
			$sth = $dbh->prepare($sql);
			$sth->execute;
			$row=$sth->fetchrow_hashref;
			$dbreported_time=$row->{reported_time};
			if($dbreported_time eq $date){
				# it is a dupe
				next;
			}
			$sql = 'INSERT INTO argus_alarms ';
			$sql .= '(reported_time,location,host,alarm_description,priority,argus_id) VALUES (';
			$sql .= $dbh->quote($date) . "," . $dbh->quote($location) . "," . $dbh->quote($host) . "," . $dbh->quote($service);
			$sql .= ",$priority," . $dbh->quote($argus_id) . ")";
		}elsif($status =~ /(UP|OK)/){
			$sql = "UPDATE argus_alarms SET cleared_time = " . $dbh->quote($date) . " WHERE ";
			$sql .= "host = " . $dbh->quote($host) . " AND alarm_description = " . $dbh->quote($service);
			$sql .= " AND cleared_time IS NULL";
		}else{
			mailerror("dont know status: $status -- all: $_");
			next;
		}
		
		$sth = $dbh->prepare($sql);
		$sth->execute;
		$dbh->disconnect;
	
	}else{
		mailerror("cannot parse: $_");
	}
}

sub mailerror {
	open (S, "|/usr/lib/sendmail -t") || die "cannot fork sendmail: $!\n";
	print S "To: jkister\@example.net\n\n",
		"@_\n";
	close S;
}