#!/usr/local/bin/perl
use Socket;
# 
# This is a perl script to check the status of Oracle instances. Add it to your ext procs
# and it will parse the bb-hosts looking for oracle(....) keywords, parse out the data
# in the () to get the sid, and optionaly user=xxxx,pass=yyyy, and use DBI/DBD to 
# connect and select data from the instance. Green if it works, yellow if it connect
# but select fails, and red if it can't connect.
#
# Let me know what you think, and definitly send me updates if you improve the code,
# since it is a first draft. 
#
# Joe Bryant
# HiUCImOn@Yahoo.com
#
if (!exists $ENV{BBHOME}) {
	my $work,$dir;					# we need local vars
	($work,$script)=$0=~/(.*?)\/?([^\/]*)$/;	# now strip out the dir and our name
	$work="$work/../";				# we assume a subdir of BBHOME
	chomp($dir=`pwd`);				# change to BBHOME
	chdir($work);					#   to get
	chomp($work=`pwd`);				#   the real dir name
	$ENV{BBHOME}=$work;				# now set BBHOME to something real
	chdir($dir);					# and retunr to our dir
}
if (!exists $ENV{BBTMP}) {				# only run if not set
	foreach (`cd $BBHOME;. etc/bbdef.sh;set`) {
		chomp;						# drop EOL
		($var,$val)=/^\s*(.*?)\s*=\s*(.*)/;		# get var and value
		$ENV{$var}=$val;				# and set
	}
}
# all your env is now set up, just like in a shell.
#
# Lets process bb-hosts into a hash first for easy reference
#
$keyword='oracle';
open(IN,"$ENV{BBHOSTS}");			# open bb-hosts
foreach (<IN>) {				# read contents
	chomp;					# trim EOL
	next if (/^\s*#/);			# Skip comments
	($ip,$host,$pound,@parms)=split;	# Split into pieces
	$name=lc($host);			# force lower case to make finding easier
	next unless ($pound eq '#');		# Skip if token 3 isn't a '#'
	foreach $parm (@parms) {		# Process all the parms
		$bbhosts{"$name~$parm"}=$parm;	# and store as keys in %bbhosts "$name~$parm"
		$bbhost{"$name~$parm"}=$host;	# and store as keys in %bbhosts "$name~$parm"
		$bbhostsIP{"$ip~$parm"}=$parm;	# and store as keys in %bbhosts "$name~$parm"
	}
}
close(IN);					# and close
foreach $key (sort grep /~$keyword\b/sig,keys %bbhosts) {
	($host,$parms)=$key=~/^(.*)~$keyword\((.*)\)/;
	$name=$bbhost{"$key"};
	%temp=();
	foreach (split(/,/,$parms)) {
		if (/=/) {
			$_=lc($_);
			($var,$val)=split(/=/);
			$temp{$var}=$val;
		} else {
			$sid=$_;
		}
	}
	foreach (keys %temp) {
		$parms{"$sid~$_"}=$temp{$_};
	}
	$host{$sid}=$name;
}
$hosts=join(' ',keys %host);
#
# OK, this is code that uses another script of mine to get the oracle user and password 
# for an instance. You can contact me if you would like this script, or replace the code 
# with whatever you want to use. If you code the user=,pass= in the bb-hosts for every instance
# then you only need to change this to a "foreach (keys %host) {" and you'll be OK
#
foreach (`OraclePassword $hosts`) {
	($sid,$user,$pass)=split;
	$user{$sid}=$user;
	$pass{$sid}=$pass;
	$user{$sid}=$parms{"$sid~user"} if exists $parms{"$sid~user"};
	$pass{$sid}=$parms{"$sid~pass"} if exists $parms{"$sid~pass"};
}
use DBI;
$SQL='select * from dual';
foreach (sort keys %host) {
	print "-->$_<-->$host{$_}<-->$user{$_}<-->$pass{$_}<--\n";
	$host=$host{$_};
	$user=$user{$_};
	$pass=$pass{$_};
	$inst=$_;
	if(my $dbConn = DBI->connect("dbi:Oracle:$inst", $user, $pass)) {
		$result="$inst\nConnect successful";
		my $dbCommand = $dbConn->prepare($SQL);
		if($dbCommand->execute()) {
			$color='GREEN';
			$result.="\nExecute successful\n".$SQL."\n".BuildResultAsTabulatedString($dbCommand);
		} else {
			$color='YELLOW';
			$result.="\nExecute failed\n".$SQL."\n ddue to error: $DBI::errstr";
		}
	} else {
		$color='RED';
		$result="Connect failed: $DBI::errstr";
	}
	print "$result\n";
	ReportToBB($host,$inst,$color,$result);
}

exit;


sub BuildResultAsTabulatedString {
	my($Result)= @_ ;
	
	my @Row;
	my $output = '';
	
	# Count rows returned
	my $i=0;
	# For each row
	while (@Row = $Result->fetchrow_array()) {
		# For each cell
		for my $Cell (@Row) {
			# append the cell to the output
			$output .= "$Cell\t";
		} 
		# Write the end of row
		$output .= "\n";
		$i++;
	}
	
	# Print the number of rows
	$output .= "\t$i rows.\n";
	
	# Return the string with the tabulated data
	return $output ;
}

sub ReportToBB {
	my($HostName, $inst, $color, $status) = @_ ;
	($inst)=split(/\./,$inst);
	# Substitute dots by commas in the host name
	$HostName =~ s/\./,/g;
	# Build the command to report to Big Brother
	$color=lc($color);
	my $MyCmd= "$ENV{BB} $ENV{BBDISP} ".'"'."status $HostName.$inst $color ".localtime(time).' '.$status.'"';
	# For debugging purposes
	# Execute the command.
	print "$MyCmd\n";
	print `$MyCmd`."\n";
}