[Nagiosplug-devel] new oracle plugins

[Dietmar Ruzicka]

Hi,

sorry I sent an old version of check_oracle_cache_hit_ratio!
the ratio should be > than the threshold.

Dietmar
-------------- next part --------------
#!/usr/bin/perl -w

use DBI;
use strict;
use Getopt::Long;
&Getopt::Long::config('bundling');

my $PROGNAME = "check_oracle_cache_hit_ratio";

my $status;

#
# parameters
my $hostname;
my $instance;
my $user;
my $password;
my $warning;
my $critical;
my $opt_h; 
my $opt_V;

my $dbh;
my @db_block_gets;
my @consistent_gets;
my @physical_reads;
my $stmt_db_block_gets;
my $stmt_consistent_gets;
my $stmt_physical_reads;
my $result;

my %ERRORS=('OK'=>0,'WARNING'=>1,'CRITICAL'=>2,'UNKNOWN'=>3,'DEPENDENT'=>4);

$status = GetOptions(   "V"   => \$opt_V, "version"    => \$opt_V,
                        "h"   => \$opt_h, "help"       => \$opt_h,
                        "u=s" =>\$user, "user=s" => \$user,
                        "p=s" =>\$password, "password=s",\$password,
                        "i=s" =>\$instance, "instance=s" => \$instance,
                        "H=s" => \$hostname, "hostname=s" => \$hostname,
                        "w=i" =>\$warning, "warning=i" => \$warning,
                        "c=i" =>\$critical, "critical=i" => \$critical
		     );

if ($status == 0)
{
    print_help();
    exit $ERRORS{'OK'};
}

if ($opt_V) {
    print "$PROGNAME, Revision: 0.2\n";
    exit $ERRORS{'OK'};
}

if ($opt_h) {
    print_help();
    exit $ERRORS{'OK'};
}

if (!defined($hostname)){
    usage();
    exit $ERRORS{"UNKNOWN"};
}

if (!defined($instance)){
    usage();
    exit $ERRORS{"UNKNOWN"};
}

if (!defined($user)){
    usage();
    exit $ERRORS{"UNKNOWN"};
}

if (!defined($password)){
    usage();
    exit $ERRORS{"UNKNOWN"};
}


if (!defined($warning)){
    usage();
    exit $ERRORS{"UNKNOWN"};
}

if (!defined($critical)){
    usage();
    exit $ERRORS{"UNKNOWN"};
}

if ($warning > $critical)
{
    print "Error: Warning Value greater than Critical Value! $warning% > $critical%!\n";
    exit $ERRORS{"UNKNOWN"};
}

#
# sql select-statements 
$stmt_db_block_gets="select value from sys.v_\$sysstat where name in(\'db block gets\')";
$stmt_consistent_gets="select value from sys.v_\$sysstat where name in(\'consistent gets\')";
$stmt_physical_reads="select value from sys.v_\$sysstat where name in(\'physical reads\')";

#
# connecting to database
$dbh = DBI->connect("dbi:Oracle:host=".$hostname.";sid=".$instance, $user, $password);

if (!defined($dbh))
{
    print "Critical: Cannot connect to Oracle! $DBI::errstr\n";
    exit $ERRORS{"CRITICAL"};
}

#
# executing query
@db_block_gets = $dbh->selectrow_array($stmt_db_block_gets);
if (defined $DBI::err)
{
    print "Error: $DBI::errstr\n";    
    $dbh->disconnect;
    exit $ERRORS{"CRITICAL"};
}
@consistent_gets = $dbh->selectrow_array($stmt_consistent_gets);
if (defined $DBI::err)
{
    print "Error: $DBI::errstr\n";    
    $dbh->disconnect;
    exit $ERRORS{"CRITICAL"};
}
@physical_reads = $dbh->selectrow_array($stmt_physical_reads);
if (defined $DBI::err)
{
    print "Error: $DBI::errstr\n";    
    $dbh->disconnect;
    exit $ERRORS{"CRITICAL"};
}

#
# disconnect;
$dbh->disconnect;

#
# calculating result
if ((!defined($physical_reads[0])) || (!defined($consistent_gets[0])) || (!defined($db_block_gets[0])))
{
    print "Unknown: Value missing!\n";
    exit $ERRORS{"UNKNOWN"};
}
$result = (1 - $physical_reads[0] / ($db_block_gets[0] + $consistent_gets[0])) * 100;

# cut to dd.d
$result = int ($result*10);
$result = $result / 10;

#
# critical if result < required
if ($result < $critical) {
    print "Critical: cache hit ratio: $result % warning: $warning critical: $critical \n";
    exit $ERRORS{"CRITICAL"};
}

#
# warning if result < required
if ($result < $warning) {
    print "Warning: cache hit ratio: $result % warning: $warning critical: $critical\n";
    exit $ERRORS{"WARNING"};
}

#
# ok if result => required
print "Information: cache hit ratio: $result % warning: $warning critical: $critical\n";
exit $ERRORS{"OK"};

sub usage {
    print "\nMissing arguments!\n";
    print "\n";
    print "usage: \n";
    print "$PROGNAME -H <HOSTNAME> -i <instance> -u <user> -p <password> -w <warning> -c <critical>\n";
    print "Copyright (C) 2002 Dietmar Ruzicka druzicka\@cubit.at\n";
    print "$PROGNAME comes with ABSOLUTELY NO WARRANTY\n";
    print "This programm is licensed under the terms of the ";
    print "GNU General Public License\n";
    print "\n\n";
    exit $ERRORS{"UNKNOWN"};
}


sub print_help {
    print "$PROGNAME plugin for Nagios monitors current\n";
    print "cache hit ratio\n";
    printf "\nrequires an oracle user with the following properties:\n";
    printf "\tcreate user nagios identified by nagios;\n";
    printf "\tgrant connect to nagios;\n";
    printf "\tgrant select on V_\$SYSSTAT to nagios;\n";
    print "\nUsage:\n";
    print "   -H (--hostname)   Hostname to query - (required)\n";
    print "   -i (--instance)   Oracle Instance - (required)\n";
    print "   -u (--user)       Oracle user - (required)\n";
    print "   -p (--password)   Oracle user password - (required)\n";
    print "   -w (--warning)    Warning threshold for cache hit ratio - (required)\n";
    print "   -c (--critical)   Warning threshold for cache hit ratio - (required)\n";
    print "   -V (--version)    Plugin version\n";
    print "   -h (--help)       usage help \n\n";
    print "$PROGNAME, Revision: 0.2\n";
    exit $ERRORS{"UNKNOWN"};
}