#!/usr/bin/perl -s
###############################################
#
#
# Description : perl script for executing a generic SQL command
#               the script retrieve the first row of the first col
#               result must be numeric
#
# Pre-req     : user need to have select right on SYS.DBA_OBJECTS view
#
# Author      : David Ligeret (david.ligeret at gmail.com)
#
###############################################
#
# Usage : perl -s check_oracle_objects -SID=<SID> -dbuser=<dbuser> -dbpassword=<dbpassword> -w=<warning> -c=<critical> [-detail=1]
#  -SID           : Oracle SID
#  -dbuser        : Oracle user
#  -dbpassword    : Oracle password
#  -w             : amount for warning
#  -c             : amount for critical
#  -detail        : list invalid objects if found
#
###############################################
# 
# History :
#   - 20061121 (ligeret) : initial release
#   - 20080306 (ligeret) : change generic script to specific function (checking objects)
#
###############################################
#
# TODO :
#
###############################################

use DBI;

# Nagios specific
use lib "/usr/local/nagios/libexec";
use utils qw(%ERRORS $TIMEOUT);
my $ERRORS =
{
	'OK'      => 0,
	'WARNING' => 1,
	'CRITICAL'=> 2,
	'UNKNOWN' => 3
};


# Globals
my $version = '0.1';

# if you already have a TNS_ADMIN environment variable do not
# comment following line
# otherwise you can uncomment and overwrite the variable if you want
# to specify the tnsnames.ora file directory
#$ENV{TNS_ADMIN} = '/usr/lib/oracle/10.2.0.3/client/network/admin';


# Functions

sub usage
{
	print qq{Usage : $0 -SID=<SID> -dbuser=<dbuser> -dbpassword=<dbpassword> -w=<warning> [-detail=1]
  -SID           : Oracle SID
  -dbuser        : Oracle user
  -dbpassword    : Oracle password
  -w             : amount for warning
  -c             : amount for critical
  -detail        : list invalid objects if found
}
}

# check user's inputs
sub check_arguments
{
	if (!$SID || !$dbuser || !$dbpassword)
	{
		print ("Invalid argument(s) !\n");
		&usage;
		exit $ERRORS->{'UNKNOWN'};
	}
}

# print help
if (defined($h))
{
	&usage;
	exit $ERRORS->{'OK'};
}

&check_arguments;


# establish DB connection
unless ($dbhandle = DBI->connect ("dbi:Oracle:$SID", $dbuser, $dbpassword))
{
	print ("CRITICAL: $DBI::errstr");
	exit $ERRORS->{'CRITICAL'};
}

# execute query
$q = "select count(*) from dba_objects where status!='VALID'";
my $statement = $dbhandle->prepare($q);
unless ($statement->execute())
{
	print ("Execution error");
	exit $ERRORS->{'UNKNOWN'};
}

# retrieve result
my @row = $statement->fetchrow_array();
$statement->finish();

my $exit_code = 0;
if (@row[0] >= $c)
{
	print "CRITICAL: ";
	$exit_code = 2;
}
elsif (@row[0] >= $w)
{
	print "WARNING: ";
	$exit_code = 1;
}
else
{
	print "OK: ";
}
my $number_of_invalid = @row[0];

my $detailed_message;
if ($detail == 1)
{
	$q = "select owner||'.'||object_name from dba_objects where status!='VALID'";
	my $statement = $dbhandle->prepare($q);
	unless ($statement->execute())
	{
		$detailed_message = "Failed to retrieve object(s) name";
	}
	else
	{
		while (@row = $statement->fetchrow_array())
		{
			$detailed_message .= @row[0]. " ";
		}
	}
}

print ($number_of_invalid." invalid object(s)");
if ($detail == 1)
{
	print (": ".$detailed_message);
}

exit $exit_code;