#!/usr/bin/perl -w
#
# Nagios plugin to run an arbitrary query against a database, and test the 
#   number of rows returned. 
#   By default, it returns CRITICAL if no rows are returned, and OK otherwise.
#

use strict;
use File::Basename;
use DBI;
use Number::Tolerant;
use Nagios::Plugin::Getopt;
use Nagios::Plugin 0.1303;

my $ng = Nagios::Plugin::Getopt->new(
  usage =>  qq(Usage: %s [-v] -q <query> [-w <warn-count>] [-c <crit-count>]
                                 [-d <dsn>] [-u <user>] [-p <pass>]\n),
  version => '0.04',
  url => 'http://www.openfusion.com.au/labs/nagios/',
  blurb => 'Plugin to run an arbitrary query against a db and test no. of rows returned.',
  extra => qq(Warning and critical rowcount ranges may be either single numbers or ranges.
Ranges may be specified using the following syntaxes:
- explicit ranges (hyphen- or colon-separated) e.g. 50-100, 50:100
- open-ended ranges (hyphen- or colon-separated) for greater-than-or-equal-to 
  and less-than-or-equal-to tests e.g. 50-, :100
- signed numbers, for greater-than-or-equal-to and less-than-or-equal-to tests
  e.g. +50, -100
It is fine to use open-ended ranges for both warning and critical rowcounts -
critical rowcounts are checked first.

It is recommended that you set 'dsn', 'user', and 'pass' variables in a 
section of your plugins.cfg file and load them via the 'auth' argument above.
This provides better security than passing your credentials on the command 
line.),
);

$ng->arg(
  spec => "query|q=s",
  help => q(-q, --query
   Query to run against the database, which should return at least one row.),
  required => 1);
$ng->arg(
  spec => "warning|w=s",
  help => qq(-w, --warning=STRING
   Exit with WARNING status if rowcount is in this range (see below).
   Default: none.));
$ng->arg(
  spec => "critical|c=s",
  help => qq(-c, --critical=STRING
   Exit with CRITICAL status if rowcount is in this range (see below).
   Default: %s.),
  default => 0);
$ng->arg(
  spec => "authkey|auth|a=s",
  help => q(-a, --auth, --authkey
   Auth key, used to lookup dsn/user/pass details from plugins.cfg.
   Default: plugin name.));
$ng->arg(
  spec => "dsn|d=s",
  help => q(-d, --dsn
   Database DSN indentifier e.g. dbi:Pg:dbname=foo;host=dbserver, etc.));
$ng->arg(
  spec => "user|u=s",
  help => q(-u, --user
   Username to use to login to the database.));
$ng->arg(
  spec => "pass|p=s",
  help => q(-p, --pass
   Password to use to login to the database.));
$ng->arg(
  spec => "name|n=s",
  help => q(-n, --name
   Name/description to use for query in results message.));

$ng->getopts;

# ----------------------------------------------------------------------------
# Subroutines

# Map our supported range syntaxes to a Number::Tolerant range
sub make_range
{
  my ($range) = @_;
  # Explicit range
  if ($range =~ m/(\d+)\s*[-:]\s*(\d+)/) {
    return tolerance("$1" => to => "$2");
  }
  # Less-than range
  elsif ($range =~ m/\s*[-:]\s*(\d+)/) {
    return tolerance("$1" => 'or_less');
  }
  # Greater-than range
  elsif ($range =~ m/(\d+)\s*[-:]\s*/ || $range =~ m/\+(\d+)/) {
    return tolerance("$1" => 'or_more');
  }
  elsif ($range =~ m/(\d+)/) {
    return $1;
  }
  else {
    nagios_exit("UNKNOWN", "bad range specification '$range'");
  }
}

# ----------------------------------------------------------------------------

my $np = Nagios::Plugin->new;

my $dsn = $ng->dsn;
my $user = $ng->user;
my $pass = $ng->pass;
if (! $dsn || ! $user || ! $pass) {
  my $auth = $ng->authkey || $np->shortname;
  $np->nagios_die('auth undefined') unless $auth;
  my $config = $np->load_config(section => $auth);
  $dsn ||= $config->{dsn};
  $user ||= $config->{user};
  $pass ||= $config->{pass};
}
my @bad = ();
for my $req (qw(dsn user pass)) {
  push @bad, "missing $req" unless eval "\$$req";
}
$np->nagios_die(join ' ', @bad) if @bad;

alarm($ng->timeout);

# Execute query
my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 })
  or $np->nagios_exit(CRITICAL, "Database connect failed: " . $DBI::errstr);
my $res;
eval { $res = $dbh->selectall_arrayref($ng->query) };
$np->nagios_exit(CRITICAL, "Query failed: " . $dbh->errstr) if $@;
$dbh->disconnect;

# Query successful - evaluate results
my $count = scalar(@$res);
my $range = make_range($ng->critical);
print "+ critical range is $range\n" if $ng->verbose;
my $name = $ng->name ? $ng->name . ' ' : '';
$np->nagios_exit(CRITICAL, "${name}query returned $count rows (critical is '" . $ng->critical . "')") 
  if $count == $range;
if (defined $ng->warning) {
  $range = make_range($ng->warning);
  print "+ warning range is $range\n" if $ng->verbose;
  $np->nagios_exit(WARNING, "${name}query returned $count rows (warning is '" . $ng->warning . "')") 
    if $count == $range;
}
$np->nagios_exit(OK, "${name}query returned $count rows");


# arch-tag: 9031f039-b394-4e28-849e-0c28c815dea0
# vim:ft=perl:ai:sw=4
