#!/usr/bin/perl -w

use strict;
use Getopt::Std;
use DBI;

# Konfiguration / Zugangsdaten
my $username="USERNAME";
my $passwort="PASSWORT";
my $mysqlsock="mysql_socket=/pfad/zu/mysql.sock";

my $verbindungen=0;
my $verbindung="DBI:mysql:;$mysqlsock"; 
my %opts;
my $warning=0;
my $critical=0;
my $max_conn;
my $qos_max_conn = qos_check();

getopt('wc', \%opts);

my $version="0.1\n";
my $author="Tim Harsdorf / mail\@timharsdorf.de\n";
my $desc="Nagios Plugin in Perl zum auslesen der offenen MySQL Verbindungen.\nDieses Script gibt Nagios";
   $desc.=" Performacedaten zum Auswerten der offenen Datenbankverbindungen aus.\n\n";
my $syntax="./check_mysql -w XX -c XX \n\n";

while ( my ($key, $value) = each(%opts) ) {

        if ($key eq "w" && defined($value) && $value =~ /\d{1,2}/ && $value < 100) 
          { 
           $warning=$value;
          }

        if ($key eq "c" && defined($value) && $value =~ /\d{1,2}/ && $value < 100) 
          { 
           $critical=$value;
          }

     }



if ($warning >= $critical or $warning == 0 or $critical == 0 or $warning >= $qos_max_conn or $critical > $qos_max_conn) 
  { 
   usage(); 
   exit 3;
  }
   else
       { 
        my ($openconnections)=check(); 

        if ($openconnections < $warning && $openconnections < $critical)
          {
           print "OK: $openconnections Verbindungen | verbindungen=$openconnections;$warning;$critical\n";
           exit 0;
          }
       

        elsif ($openconnections >= $warning && $openconnections < $critical)
          {
           print "WARNING: $openconnections Verbindungen | verbindungen=$openconnections;$warning;$critical\n";
           exit 1;
          } 

        elsif ($openconnections > $warning && $openconnections >= $critical)
          {
           print "CRITICAL: $openconnections Verbindungen | verbindungen=$openconnections;$warning;$critical\n";
           exit 3;
          }        
        else
            {
             print "UNKNOWN: $openconnections Verbindungen | verbindungen=$openconnections;$warning;$critical\n";
             exit 4;
            }

       }

sub usage
   {
    print "Author: ".$author;
    print "Version: ".$version;
    print $desc;
    print $syntax;
   }

sub check
   {
    my $dbh = DBI->connect( "$verbindung","$username","$passwort") || die 
    "Database connection not made: $DBI::errstr";

    my $sql = qq{show processlist};
    my $sth = $dbh->prepare( $sql );
    $sth->execute();

     while(my @ergebnis=$sth->fetchrow_array)
        {
         #print $ergebnis[0]." ".$ergebnis[1]." ".$ergebnis[2]." ".$ergebnis[3]." \n";
         $verbindungen++;
        }
    $sth->finish();

    $dbh->disconnect();
   
    return $verbindungen;
   }

sub qos_check
   {
    my $dbh = DBI->connect( "$verbindung","$username","$passwort") || die
    "Database connection not made: $DBI::errstr";

    my $sql = qq{select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME = "max_connections"};
    my $sth = $dbh->prepare( $sql );
    $sth->execute();

     while(my @ergebnis=$sth->fetchrow_array)
        {
         $max_conn=$ergebnis[0];
        }
    $sth->finish();
    $dbh->disconnect();
   
    return $max_conn;
   }