#!/usr/bin/perl -w

# Author: Troels Arvin <troels@arvin.dk>
#
# $Date: 2014-09-02 09:32:18 +0200 (Tue, 02 Sep 2014) $
#
# ====== vvv ====== bureaucracy ====== vvv ======
# MIT style license:
#
# Copyright (c) 2009 Troels Arvin 
# 
# Permission is hereby granted, free of charge, to any person
# obtaining a copy of this software and associated documentation
# files (the "Software"), to deal in the Software without
# restriction, including without limitation the rights to use,
# copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following
# conditions:
# 
# The above copyright notice and this permission notice shall be
# included in all copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.
# ====== ^^^ ====== bureaucracy ====== ^^^ ======
#
# Requirement: The host which this plugin runs on must have DB2 client
# software and the DBD:DB2 perl-module installed:
#   perl -MCPAN -e 'install DBD::DB2'
# The Time::HiRes perl-module must also be available.

# Written with http://nagiosplug.sourceforge.net/developer-guidelines.html
# in mind. Doesn't use Nagios perl-modules, in order to minimize
# dependencies.

# Reminder: Nagios status codes:
#  0 = OK
#  1 = Warning
#  2 = Critical
#  3 = Unknown

# TODO:
#  - Allow connection by catalogued database name as alternative to
#    hostname/port.
#  - Allow GNU-style long parameters.

use strict;

use Getopt::Std;
use File::Basename;

use Time::HiRes qw(gettimeofday tv_interval);

use DBI;
use DBD::DB2;

my $dbh;

# -------------------------------------------------------------
# Helper functions
# -------------------------------------------------------------

sub usage {
    print "Usage:\n";
    print '  '.basename($0).' -H <hostname> -d <database name> [ -p <port #> ]';
    print ' [ -u <username> [ -a <password> ] ] { [ -q <SQL query statement> [ -l ] ] |';
    print " [-w <warn> ] [-c <crit> ] }\n\n";
    print "The -q parameter cannot be used together with the -w/-c parameters.\n";
    print 'If the -q parameter is used, the SQL statement must return one row ';
    print "with\ntwo columns:\n";
    print " - first column:          plugin return code\n";
    print " - second column:         status information text\n";
    print " - OPTIONAL third column: performance data\n";
    print "The -l option means: If the query fails due to a lock timeout, emit\n";
    print "a WARNING, instead of a CRITICAL state.\n";
    print "\n";
    print "warn/crit are connect-time thresholds in seconds.\n";
    exit 3; 
}

sub err {
    my $arg = shift;
    if ($dbh) {
        eval { $dbh->disconnect(); }
    }
    print STDERR "Error: $arg\n";
    exit 3;
}

sub nagios_exit {
    my $retcode = shift;
    my $msg = shift;
    print $msg;
    exit $retcode;
}

# -------------------------------------------------------------
# Sanity checks, defaults handling
# -------------------------------------------------------------

$Getopt::Std::STANDARD_HELP_VERSION = 1;
my %options;
getopts('H:h:d:p:u:a:q:w:c:l',\%options) or usage();

if (exists $options{'h'}) {
    usage();
}

if (not exists $options{'H'}) { err "No hostname indicated"; }
if (not exists $options{'d'}) { err "No database name indicated"; }

my $db = $options{'d'};
my $hostname = $options{'H'};
my $user = '';
my $pass = '';
my $query = '';
my $warn = 3;
my $crit = 30;
my $port = 50000;
my $lock_timeout_yields_warning = 0; # 0 means: return with CRITICAL if a
                                     # lock timeout occurs

if (exists $options{'u'}) { $user  = $options{'u'}; }
if (exists $options{'a'}) { $pass  = $options{'a'}; }
if (exists $options{'p'}) { $port  = $options{'p'}; }
if (exists $options{'q'}) { $query = $options{'q'}; }
if (exists $options{'w'}) { $warn  = $options{'w'}; }
if (exists $options{'c'}) { $crit  = $options{'c'}; }
if (exists $options{'l'}) { $lock_timeout_yields_warning = 1; }

if (
        (exists $options{'w'} or exists $options{'c'})
    and
        (exists $options{'q'} or exists $options{'l'})
   ) {
    err "the -w/-c parameters cannot be used with the -q/-l parameters";
}

if (not $warn =~ m/^-?[\d.]+$/) {
    err "non-numeric value for warn parameter requested";
}

if (not $crit =~ m/^-?[\d.]+$/) {
    err "non-numeric value for crit parameter requested";
}

if ($warn > $crit) {
    err "warn > crit";
}

# -------------------------------------------------------------
# Actual connection handling
# -------------------------------------------------------------
my $conn_string = "dbi:DB2:DATABASE=$db; HOSTNAME=$hostname; PORT=$port; ".
    'PROTOCOL=TCPIP;';
if ($user ne '') {
    $conn_string = $conn_string." UID=$user;";
}
if ($pass ne '') {
    $conn_string = $conn_string." PWD=$pass;";
}

# db2_txn_isolation=SQL_TXN_READ_UNCOMMITTED:
#   minimize risk of Nagios introducing or being blocked by locks
#
# PrintError=0:
#   otherwise, an error situation may reveal passwords through an error message
my %conn_attr = (
    db2_txn_isolation => DBD::DB2::Constants::SQL_TXN_READ_UNCOMMITTED,
    PrintError => 0
);

my $t0 = [gettimeofday];
unless ($dbh = DBI->connect($conn_string, $user, $pass, \%conn_attr)) {
    if ($query eq '') {
        # The script is being used to check connection-setup; in the
        # light of that, a connection setup failure is a critical error:
        nagios_exit(2,"Connection failed with error: $DBI::errstr");
    } else {
        # The script is being used to check some state of data.
        # In the light of that, the connection failure is treated
        # as an undefined state (supposedly, a separate check is 
        # keeping an eye on mere connection setups):
        nagios_exit(3,"Connection failed with error: $DBI::errstr");
    }
}
my $elapsed = tv_interval ($t0, [gettimeofday]);

my $perf=
    "time=${elapsed}s" . 
    (-1 == $warn ? ';' : ';'.sprintf('%f',$warn)) .
    (-1 == $crit ? ';' : ';'.sprintf('%f',$crit)) .
    ';0.000000'
;

# -------------------------------------------------------------
# Final work: Decide if the situation is good, or not.
# -------------------------------------------------------------
$query =~ s/^\s*$//g;
if ($query eq '') {
    $dbh->disconnect();
    if ($elapsed > $crit) {
        nagios_exit(2,"DB2 CRITICAL: Connect time too high - $elapsed sec. ".
            "connect time|$perf\n");
    }
    if ($query eq '' and $elapsed > $warn) {
        nagios_exit(1,"DB2 WARNING: Connection time high - $elapsed sec. ".
            "connect time|$perf\n");
    }
    nagios_exit(0,"DB2 OK: Connect time $elapsed sec.|$perf\n");
} else {
    my $stmt;
    $stmt = $dbh->prepare($query) or
        err "Could not prepare SQL query; error code: " .$stmt->state(). 
        "; error mesage: " . $stmt->errstr();
    if (! $stmt->execute()) {
        if ($lock_timeout_yields_warning) {
            $dbh->disconnect();
            nagios_exit(1,'Aborted due to lock timeout');
        } else {
            err "Could not execute SQL query; error code: " .$stmt->state(). 
                "; error mesage: " . $stmt->errstr();
        }
    }
    my @firstrow = $stmt->fetchrow_array() 
            or err "Could not fetch array; error code: " .$stmt->state(). 
                "; error mesage: " . $stmt->errstr();
    my $cols = scalar(@firstrow);
    if ($cols != 2 and $cols != 3) {
        err "SQL query returned result with $cols columns (expected two or three)\n";
    }
    my $retcode = $firstrow[0];
    my $msg = $firstrow[1];
    if ($cols == 3) {
        $msg .= '|'.$firstrow[2];
    }
    if ($stmt->fetchrow_array()) {
        err "Query returned more than one row";
    }
    $dbh->disconnect();
    nagios_exit($retcode,$msg);
}