#!/usr/bin/perl
# $Id: check_ecm 220 2011-12-09 21:31:04Z mwall $
# check_ecm - nagios plugin to monitor brultech ecm data collection
#   by Matthew Wall
#
# Copyright (c) 2011 Matthew Wall, all rights reserved
#
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notice and this permission notice appear in all copies.
#
#
# This plugin checks the latest entry in a mysql database to determine whether
# data from a Brultech ECM have been collected.  Indicate warning or critical
# if the last update was older than warn or crit thresholds, respectively.
# If not in a warning or critical state, the plugin reports ECM metrics as
# performance data.
#
# This is designed to be used with the ecmread.py data logger.  The default
# update interval for ecmread is 60 seconds.  The default Nagios check interval
# is 300 seconds (5 minutes).
#
# The plugin expects to find a database with the following schema:
#
#   +--------------+---------+------+-----+---------+----------------+
#   | Field        | Type    | Null | Key | Default | Extra          |
#   +--------------+---------+------+-----+---------+----------------+
#   | id           | int(11) | NO   | PRI | NULL    | auto_increment | 
#   | ecm_serial   | int(11) | YES  |     | NULL    |                | 
#   | ch1_ws       | int(11) | YES  |     | NULL    |                | 
#   | ch2_ws       | int(11) | YES  |     | NULL    |                | 
#   | aux1_ws      | int(11) | YES  |     | NULL    |                | 
#   | aux2_ws      | int(11) | YES  |     | NULL    |                | 
#   | aux3_ws      | int(11) | YES  |     | NULL    |                | 
#   | aux4_ws      | int(11) | YES  |     | NULL    |                | 
#   | aux5_ws      | int(11) | YES  |     | NULL    |                | 
#   | time_created | int(11) | YES  |     | NULL    |                | 
#   +--------------+---------+------+-----+---------+----------------+
#
#
# Examples
#
# check for database updates and emit performance data, warn if over 10 minutes
#   check_ecm -w 10 -c 20
#
# check for database updates but do not emit performance data
#   check_ecm --no-ecm-data
#
# Revision History
#   0.1 09dec11
#    * initial release
#
# Prerequisites
#   mysql database with ecm data
#   DBI::mysql
#   ecmread data collector
#   brultech ECM hardware
#
# to install DBI:mysql on debian:
#   apt-get install libdb-mysql-perl

use strict;
use POSIX qw(strftime);

my $STATE_OK = 0;
my $STATE_WARN = 1;
my $STATE_CRIT = 2;
my $STATE_UNKNOWN = 3;
my @STATESTR = ( 'OK', 'WARN', 'CRITICAL', 'UNKNOWN' );

my $PROG = 'check_ecm';

# should we emit weather observations as performance data?
my $emitedata = 1;

# minutes without update until we warn
my $mwarn = 30;

# minutes without update until we go critical
my $mcrit = 120;

# which ecm devices should we query?  if none are specified we use the latest
my $serials = q();

# database parameters
my $dbhost = 'localhost';
my $dbname = 'ecm';
my $dbtable = 'ecm';
my $dbuser = 'ecmuser';
my $dbpass = 'ecmpass';

while($ARGV[0]) {
    my $arg = shift;
    if ($arg eq '-w') {
        $mwarn = shift;
    } elsif ($arg eq '-c') {
        $mcrit = shift;
    } elsif ($arg eq '--no-ecm-data') {
        $emitedata = 0;
    } elsif ($arg eq '--ecm-serials') {
        $serials = shift;
    } elsif ($arg eq '--db-user') {
        $dbuser = shift;
    } elsif ($arg eq '--db-passwd') {
        $dbpass = shift;
    } elsif ($arg eq '--db-table') {
        $dbtable = shift;
    } elsif ($arg eq '--db-database') {
        $dbname = shift;
    } elsif ($arg eq '--db-host') {
        $dbhost = shift;
    } elsif ($arg ne q()) {
        if ($arg ne '--help') {
            print "unknown argument '$arg'\n";
        }
        print "usage: $PROG [options]\n";
        print "options include:\n";
        print "  --help\n";
        print "  --no-ecm-data\n";
        print "  --ecm-serials sn1[,sn2[,sn3[,...]]]\n";
        print "  -w minutes               $mwarn\n";
        print "  -c minutes               $mcrit\n";
        print "  --db-user username       $dbuser\n";
        print "  --db-passwd password     $dbpass\n";
        print "  --db-table tablename     $dbtable\n";
        print "  --db-database dbname     $dbname\n";
        print "  --db-host hostname       $dbhost\n";
        exit $STATE_UNKNOWN;
    }
}

if ($mwarn > $mcrit) {
    print "$STATESTR[$STATE_UNKNOWN] - warn threshold must be less than critical threshold\n";
    exit $STATE_UNKNOWN;
}

my $msg = q();
my $perfdata = q();
my $now = time;
my $state = $STATE_OK;

my $rval = eval { require DBI; };
if (defined $rval && $rval == 1) {
    my $dbh = DBI->connect("dbi:mysql:$dbname", "$dbuser", "$dbpass",
                           {RaiseError => 1, AutoCommit => 0});
    if ($dbh) {
        my %info;
        if ($serials eq q()) {
            $info{single} = getinfo($dbh, $now, $mwarn, $mcrit, 'single');
        } else {
            foreach my $sn (split ',', $serials) {
                $sn =~ s/^\s//g;
                $sn =~ s/\s$//g;
                $info{$sn} = getinfo($dbh, $now, $mwarn, $mcrit, $sn);
            }
        }
        $dbh->disconnect;

        foreach my $sn (sort keys %info) {
            if ($info{$sn}->{state} > $state) {
                $state = $info{$sn}->{state};
            }
            $msg .= '; ' if $msg ne q();
            $msg .= $info{$sn}->{msg};
	    $perfdata .= ' ' if $perfdata ne q();
            $perfdata .= $info{$sn}->{perfdata};
        }
    } else {
        $state = $STATE_UNKNOWN;
        $msg = 'database connection failed';
    }
} else {
    $state = $STATE_UNKNOWN;
    $msg = 'DBI is not installed';
}

print "$STATESTR[$state] - $msg";
print "|$perfdata" if $perfdata ne q();
print "\n";

exit $state;


sub getinfo {
    my ($dbh, $now, $mwarn, $mcrit, $sn) = @_;

    my %info;
    my $query = q();
    if ($sn eq 'single') {
        $query = "select * from $dbtable where time_created = (select max(time_created) from $dbtable)";
    } else {
        $query = "select * from $dbtable where time_created = (select max(time_created) from $dbtable where ecm_serial=$sn) and ecm_serial=$sn";
    }

    my $sth = $dbh->prepare($query);
    $sth->execute();
    my $row = $sth->fetchrow_hashref();
    if ($row) {
        my $snlabel = $row->{ecm_serial};
        my $updated = $row->{time_created};
        my $deltaS = $now - $updated;
        my $deltaM = $deltaS / 60;
        my $mstr = q();
        if ($deltaS > 60) {
            $mstr = sprintf("%.0f minutes", $deltaM);
        } else {
            $mstr = sprintf("%.2f seconds", $deltaS);
        }
        my $dstr = strftime "%H:%M:%S %d %b %Y", localtime $updated;
        $info{msg} = "last update of $row->{ecm_serial} was $mstr ago at $dstr";
        $info{perfdata} = "${snlabel}_lastupdate=$deltaM;$mwarn;$mcrit";
        if ($deltaM > $mcrit) {
            $info{state} = $STATE_CRIT;
        } elsif ($deltaM > $mwarn) {
            $info{state} = $STATE_WARN;
        } else {
            $info{state} = $STATE_OK;
            if ($emitedata) {
                foreach my $field (keys %$row) {
                    next if $field eq 'id' ||
                        $field eq 'ecm_serial' ||
                        $field eq 'time_created';
                    if ($row->{$field} ne q()) {
                        $info{perfdata} .= " ${snlabel}_$field=$row->{$field}";
                    }
                }
            }
        }
    } else {
        $info{state} = $STATE_UNKNOWN;
        $info{msg} = 'database query returned no records for $sn';
    }
    return \%info;
}