#!/usr/bin/perl

=header
TICKET: 1094500
AUTOR: Daniel Ortiz
CRIADO EM: 13/06/2015 08:30:45

REVISÕES:
--------------------------------------------------------------
1.0 15/06/2015, Daniel Ortiz
    - Support for multiple database types

1.1 22/06/2015, Daniel Ortiz
    - Added min and max values for thesholds

1.2 - Added warning and critical thresholds

1.3 - Added -s parameter, to run queries with return as string
=cut

use Getopt::Long;
use Data::Dumper;
use warnings;
use strict;
use File::Basename;
use Switch;
use English;
use DBI;
use Nagios::Plugin;
use vars qw ($np); 

use constant{
    TRUE=> 1,
    FALSE=> 0,
    VERSION=> 1.3,
};

sub getGuideLine() {

    print <<EOF;

------------------------------------------
Manual de exemplo.
------------------------------------------

Descricao:
    
    Este plugin coleta os dados de uma tabela e agrupa os valores
    pelo bind recebido

Instalação:
    Passos para instalação:
    
    perl module English
        Instalação: yum install "perl(English)" -y

    perl module Switch;
        Instalação: yum install "perl(Switch)" -y

    perl module Nagios::Plugin;
        Instalação: yum install "perl(Nagios::Plugin)" -y

    perl module DBI;
        Instalação: yum install "perl(DBI)" -y

	perl module DBD::Sybase
		CentOS 5
		Instalação: rpm -ivh perl-DBD-Sybase-1.10-1.el5.rf.x86_64.rpm

		CentOS 6
		Instalação: rpm -ivh perl-DBD-Sybase-1.10-1.el6.rf.x86_64.rpm

Exemplos de Execução:
    
    Table:
    ----------------------------------------------
    |name|value_0|value_1|value_2|value_3|value_4|
    ----------------------------------------------
    |John|   1   |   2   |   3   |   4   |   5   |
    ----------------------------------------------
    |Carl|   7   |   8   |   9   |   10  |   11  |
    ----------------------------------------------

    ./check_values.pl -H 127.0.0.1 -u sa -p sa010203 -b John:name -t mssql -q query

EOF

    exit(OK);

}

sub setNagios() {

    $np = Nagios::Plugin->new(
        url => "www.opservices.com.br/suporte",
        shortname => "\r", # Elimina o shortname
        version => VERSION,
        license => "Developed by:\n\tOpServices\n"
            . "Author: \n\tDaniel Ortiz - daniel.ortiz\@opservices.com.br\n",
        usage => "Usage:\n\t%s -H <Host/Server> -u <username>\n"
            . "\t -p <password> -b <bind>\n"
            . "\t -t <database_type> [-s] -q <query>\n"
            . "\t -m <min_treshhold|INTEGER:INTEGER> -M <max treshold|INTEGER:INTEGER>\n"
            . "\t -w <warning> -c <critical>",
    );

    $np->add_arg(
        spec => "host|H=s",
    required => TRUE,
        help => ["Hostname or Severname"]
    );

    $np->add_arg(
        spec => "username|u=s",
    required => TRUE,
        help => ["Username to connect on database"]
    );
    
    $np->add_arg(
        spec => "password|p=s",
    required => TRUE,
        help => ["Password to connecto on database"]
    );

    $np->add_arg(
        spec => "bind|b=s",
    required => TRUE,
        help => ["Bind to collect desired data"]
    );
    
    $np->add_arg(
        spec => "type|t=s",
    required => TRUE,
        help => ["Server type"]    
    );

    $np->add_arg(
        spec => "string|s",
        help => ["Parameter needed when query only returns string values"]
    );

    $np->add_arg(
        spec => "query|q=s",
    required => TRUE,
        help => ["Query to obtain values from database"]
    );

    $np->add_arg(
        spec => "min|m=i",
     default => 0,
        help => ["Min treshold used on perfdata|Defaul=0"]
    );

    $np->add_arg(
        spec => "max|M=i",
     default => 100,
        help => ["Max treshold used on perfdata|Default=0"]
    );

    $np->add_arg(
        spec => "warning|w=s",
     default => 65,
        help => ["Warning treshold used on perfdata"]
    );
 
    $np->add_arg(
        spec => "critical|c=s",
     default => 85,
        help => ["Critical treshold used on perfdata"]
    );
 
  $np->add_arg(
        spec => "man",
        help => ["Show guideline"]
    );
   

    $np->getopts;

}

sub getResult() {
    
    my ($name, $ref) = split (":", $np->opts->bind);
    my $host = $np->opts->host;
    my $username = $np->opts->username;
    my $password = $np->opts->password;
    my $type = $np->opts->type;
    my $query = $np->opts->query;
    my $string = $np->opts->string;
    my $min = $np->opts->min;
    my $max = $np->opts->max;
    my $warning = $np->opts->warning;
    my $critical = $np->opts->critical;
    my $code;
	my $sth;
   
    my %conn = (
                oracle => "DBI:oracle",
                mssql => "DBI:Sybase",
                sybase => "DBI:Sybase",
                mysql => "DBI:mysql",

               );

    if (!$conn{$type}) {
            $np->nagios_exit(
                return_code => "UNKNOWN",
                message => "Server type not supported"
            );
        }
 
    my $dbh = DBI->connect($conn{$type}.":server=$host", $username, $password)
		or die "Can't connect to $host:  $DBI::errstr";
	
	$sth = $dbh->prepare($query)
		or die "Can't prepare statement: $DBI::errstr";

    $sth->execute;

	if ($string) {
        my $return = $sth->fetchrow_array;
        print "OK - $return\n";
        $sth->finish;
        exit(0);
    }
    
    my $hash_ref = $sth->fetchall_hashref($ref);

    if (!$$hash_ref{$name}) {
        $np->nagios_exit (
            return_code => "WARNING",
            message => "Hash_ref inválio: $name:$ref"
        );
    }

	my %hash = %{$$hash_ref{$name}};
	my $out = "$name =>";
	while (my ($key, $value) = each (%hash)) {

        if (!$warning and !$critical) {
                $out .= " ".$key.": ".$value;
                $np->add_perfdata(
                    label => $key,
                    value => $value,
				      min => $min,
                      max => $max
                );
            
        } else {
                $out .= " ".$key.": ".$value;
    	        $np->add_perfdata(
                    label => $key,
                    value => $value,
                  warning => $warning,
                 critical => $critical,
                      min => $min,
                      max => $max
                );

        }

        $code = $np->check_threshold(
                   check => $value,
                 warning => $warning,
                critical => $critical,
                );

        while ($value != $name) {
            
                $np->nagios_exit(
                   return_code => $code,
                       message => $out
                );
        }
    
	} # End While
    
}

setNagios();
getResult();
getGuideLine();
# vim: set smartindent tabstop=4 shiftwidth=4 softtabstop=4 expandtab[ENTER] #