Merge remote-tracking branch 'riseup/master' into HEAD

Conflicts:
	manifests/apache.pp
	manifests/base.pp
	manifests/defaults/templates.pp
	manifests/defaults/vars.pp
	manifests/init.pp
	manifests/irc_bot.pp
	manifests/nsca/client.pp
	manifests/nsca/server.pp
	manifests/service.pp
	manifests/service/mysql.pp
	templates/irc_bot/nsa.cfg.erb

2 files changed, 606 insertions, 86 deletions

diff --git a/files/irc_bot/riseup-nagios-server.pl b/files/irc_bot/riseup-nagios-server.pl
index 7880dde..5d65e6d 100644
--- a/files/irc_bot/riseup-nagios-server.pl
+++ b/files/irc_bot/riseup-nagios-server.pl
@@ -4,6 +4,16 @@
 # a simple IRC bot which dispatches messages received via local domain sockets
 # ##############################################################################
 
+
+######
+## THIS NEEDS TO BE PORTED TO THE NEW FRAMEWORKS!
+##
+## STICKY POINTS: the addfh() function doesn't exist in BasicBot or POE::Component::IRC
+##
+## people suggested we use Anyevent::IRC and POE::Kernel ->select_read and POE::Wheel namespace
+##
+## in the meantime, inspiration for extensions can be found here: http://svn.foswiki.org/trunk/WikiBot/mozbot.pl
+
 use strict;
 use File::Basename;
 
@@ -56,7 +66,8 @@ sub new {
 	my $self = {
 		socket => undef,
 		irc => undef,
-		conn => undef
+		conn => undef,
+		commandfile => undef,
 	};
 
 	return bless($self, __PACKAGE__);
@@ -101,7 +112,11 @@ sub socket_has_data {
     my $self = shift;
     
     $self->{socket}->recv(my $data, 1024);
-    $self->{conn}->privmsg($CFG::Nsa{'channel'}, $data);
+    if ($CFG::Nsa{'usenotices'}) {
+	$self->{conn}->notice($CFG::Nsa{'channel'}, $data);
+    } else {
+	$self->{conn}->privmsg($CFG::Nsa{'channel'}, $data);
+    }
 }
 
 sub irc_on_connect {
@@ -111,6 +126,70 @@ sub irc_on_connect {
 	$self->join($CFG::Nsa{'channel'});
 }
 
+sub irc_on_msg {
+    my ($self, $event) = @_;
+    my $data = join(' ', $event->args);
+    #my $nick = quotemeta($nicks[$nick]);
+    my $to = $event->to;
+    #my $channel = &toToChannel($self, @$to);
+    #my $cmdChar = $commandChar{default}; # FIXME: should look up for CURRENT channel first!
+    #if ( exists $commandChar{$channel} ) { $cmdChar = $commandChar{$channel}; }
+    my $msg = parse_msg($event);
+    if (defined($msg)) {
+      #$self->privmsg($event->nick, "alright, sending this message to nagios, hope it figures it out: $msg");
+    } else {
+      $self->privmsg($event->nick, "can't parse $data, you want 'ack host service comment'\n");
+    }
+}
+
+sub irc_on_public {
+    my ($self, $event) = @_;
+    my $data = join(' ', $event->args);
+    my $to = $event->to;
+    if ($data =~ s/([^:]*):\s+//) {
+      if ($1 eq $CFG::Nsa{'nickname'}) {
+        my $msg = parse_msg($event);
+        if (defined($msg)) {
+          #$self->privmsg($event->to, "alright, sending this message to nagios, hope it figures it out: $msg");
+        } else {
+          $self->privmsg($event->to, "can't parse $data, you want 'ack host service comment'\n");
+        }
+      } else {
+        #print STDERR "ignoring message $data, not for me (me being $1)\n";
+      }
+    } else {
+      #print STDERR "ignoring message $data\n";
+    }
+}
+
+sub parse_msg {
+    my $event= shift;
+    my $data = join(' ', $event->args);
+    my $msg;
+    if ($data =~ m/([^:]*:)?\s*ack(?:knowledge)?\s+([a-zA-Z0-9\-\.]+)(?:\s+([-\w\.]+)(?:\s+([\w\s]+))?)?/) {
+      #print STDERR "writing to nagios scoket ". $CFG::Nsa{'commandfile'} . "\n";
+      open(my $cmdfile, ">", $CFG::Nsa{'commandfile'}) || die "Can't open Nagios commandfile: $CFG::Nsa{'commandfile'}!\n";
+      my $host = $2;
+      my ($service, $comment) = (undef, "no comment (from irc)");
+      if ($4) {
+        $service = $3;
+	$comment = $4;
+      } elsif ($3) {
+        $service = $3;
+      }
+      my $user = $event->nick;
+      $msg = '[' . time() . '] ';
+      if (defined($service)) {
+        $msg .= "ACKNOWLEDGE_SVC_PROBLEM;$host;$service;1;1;1;$user;$comment\n";
+      } else {
+        $msg .= "ACKNOWLEDGE_HOST_PROBLEM;$host;1;1;1;$user;$comment\n";
+      }
+      print {$cmdfile} $msg;
+      close($cmdfile);
+    }
+    return $msg;
+}
+
 # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
 my $bot = &new;
@@ -141,6 +220,9 @@ $bot->{conn} = $bot->{irc}->newconn (
 
 $bot->{conn}->add_global_handler(376, \&irc_on_connect);
 $bot->{conn}->add_global_handler('nomotd', \&irc_on_connect);
+$bot->{conn}->add_global_handler('msg', \&irc_on_msg);
+$bot->{conn}->add_global_handler('public', \&irc_on_public);
+#$bot->{conn}->add_global_handler('notice', \&irc_on_msg);
 $bot->{irc}->addfh($bot->{socket}, \&socket_has_data, 'r', $bot);
 
 while ($running) {
diff --git a/files/plugins/check_mysql_health b/files/plugins/check_mysql_health
index f0599f2..9292ae0 100755
--- a/files/plugins/check_mysql_health
+++ b/files/plugins/check_mysql_health
@@ -61,6 +61,7 @@ sub new {
       bufferpool_hitrate => undef,
       wait_free => undef,
       log_waits => undef,
+      have_innodb => undef,
       warningrange => $params{warningrange},
       criticalrange => $params{criticalrange},
     };
@@ -76,7 +77,15 @@ sub init {
   my $dummy;
   $self->debug("enter init");
   $self->init_nagios();
-  if ($params{mode} =~ /server::instance::innodb::bufferpool::hitrate/) {
+  ($dummy, $self->{have_innodb}) 
+      = $self->{handle}->fetchrow_array(q{
+      SHOW VARIABLES LIKE 'have_innodb'
+  });
+  if ($self->{have_innodb} eq "NO") {
+    $self->add_nagios_critical("the innodb engine has a problem (have_innodb=no)");
+  } elsif ($self->{have_innodb} eq "DISABLED") {
+    # add_nagios_ok later
+  } elsif ($params{mode} =~ /server::instance::innodb::bufferpool::hitrate/) {
     ($dummy, $self->{bufferpool_reads}) 
         = $self->{handle}->fetchrow_array(q{
         SHOW /*!50000 global */ STATUS LIKE 'Innodb_buffer_pool_reads'
@@ -123,6 +132,15 @@ sub init {
       $self->{log_waits_rate} =
           $self->{delta_log_waits} / $self->{delta_timestamp};
     }
+  } elsif ($params{mode} =~ /server::instance::innodb::needoptimize/) {
+#fragmentation=$(($datafree * 100 / $datalength))
+
+#http://www.electrictoolbox.com/optimize-tables-mysql-php/
+    my  @result = $self->{handle}->fetchall_array(q{
+SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400
+});
+printf "%s\n", Data::Dumper::Dumper(\@result);
+
   }
 }
 
@@ -130,7 +148,9 @@ sub nagios {
   my $self = shift;
   my %params = @_;
   my $now = $params{lookback} ? '_now' : '';
-  if (! $self->{nagios_level}) {
+  if ($self->{have_innodb} eq "DISABLED") {
+    $self->add_nagios_ok("the innodb engine has been disabled");
+  } elsif (! $self->{nagios_level}) {
     if ($params{mode} =~ /server::instance::innodb::bufferpool::hitrate/) {
       my $refkey = 'bufferpool_hitrate'.($params{lookback} ? '_now' : '');
       $self->add_nagios(
@@ -468,6 +488,35 @@ sub init {
     ($dummy, $self->{threads_connected}) = $self->{handle}->fetchrow_array(q{
         SHOW /*!50000 global */ STATUS LIKE 'Threads_connected'
     });
+  } elsif ($params{mode} =~ /server::instance::createdthreads/) {
+    ($dummy, $self->{threads_created}) = $self->{handle}->fetchrow_array(q{
+        SHOW /*!50000 global */ STATUS LIKE 'Threads_created'
+    });
+    $self->valdiff(\%params, qw(threads_created));
+    $self->{threads_created_per_sec} = $self->{delta_threads_created} /
+        $self->{delta_timestamp};
+  } elsif ($params{mode} =~ /server::instance::runningthreads/) {
+    ($dummy, $self->{threads_running}) = $self->{handle}->fetchrow_array(q{
+        SHOW /*!50000 global */ STATUS LIKE 'Threads_running'
+    });
+  } elsif ($params{mode} =~ /server::instance::cachedthreads/) {
+    ($dummy, $self->{threads_cached}) = $self->{handle}->fetchrow_array(q{
+        SHOW /*!50000 global */ STATUS LIKE 'Threads_cached'
+    });
+  } elsif ($params{mode} =~ /server::instance::abortedconnects/) {
+    ($dummy, $self->{connects_aborted}) = $self->{handle}->fetchrow_array(q{
+        SHOW /*!50000 global */ STATUS LIKE 'Aborted_connects'
+    });
+    $self->valdiff(\%params, qw(connects_aborted));
+    $self->{connects_aborted_per_sec} = $self->{delta_connects_aborted} /
+        $self->{delta_timestamp};
+  } elsif ($params{mode} =~ /server::instance::abortedclients/) {
+    ($dummy, $self->{clients_aborted}) = $self->{handle}->fetchrow_array(q{
+        SHOW /*!50000 global */ STATUS LIKE 'Aborted_clients'
+    });
+    $self->valdiff(\%params, qw(clients_aborted));
+    $self->{clients_aborted_per_sec} = $self->{delta_clients_aborted} /
+        $self->{delta_timestamp};
   } elsif ($params{mode} =~ /server::instance::threadcachehitrate/) {
     ($dummy, $self->{threads_created}) = $self->{handle}->fetchrow_array(q{
         SHOW /*!50000 global */ STATUS LIKE 'Threads_created'
@@ -629,22 +678,26 @@ sub init {
     $self->valdiff(\%params, qw(handler_read_first handler_read_key
         handler_read_next handler_read_prev handler_read_rnd
         handler_read_rnd_next));
-    $self->{index_usage_now} = 100 - (100.0 * ($self->{delta_handler_read_rnd} +
-        $self->{delta_handler_read_rnd_next}) /
-        ($self->{delta_handler_read_first} +
-        $self->{delta_handler_read_key} + 
+    my $delta_reads = $self->{delta_handler_read_first} +
+        $self->{delta_handler_read_key} +
         $self->{delta_handler_read_next} +
-        $self->{delta_handler_read_prev} + 
-        $self->{delta_handler_read_rnd} + 
-        $self->{delta_handler_read_rnd_next}));
-    $self->{index_usage} = 100 - (100.0 * ($self->{handler_read_rnd} +
-        $self->{handler_read_rnd_next}) /
-        ($self->{handler_read_first} +
-        $self->{handler_read_key} + 
+        $self->{delta_handler_read_prev} +
+        $self->{delta_handler_read_rnd} +
+        $self->{delta_handler_read_rnd_next};
+    my $reads = $self->{handler_read_first} +
+        $self->{handler_read_key} +
         $self->{handler_read_next} +
-        $self->{handler_read_prev} + 
-        $self->{handler_read_rnd} + 
-        $self->{handler_read_rnd_next}));
+        $self->{handler_read_prev} +
+        $self->{handler_read_rnd} +
+        $self->{handler_read_rnd_next};
+    $self->{index_usage_now} = ($delta_reads == 0) ? 0 :
+        100 - (100.0 * ($self->{delta_handler_read_rnd} +
+        $self->{delta_handler_read_rnd_next}) /
+        $delta_reads);
+    $self->{index_usage} = ($reads == 0) ? 0 :
+        100 - (100.0 * ($self->{handler_read_rnd} +
+        $self->{handler_read_rnd_next}) /
+        $reads);
   } elsif ($params{mode} =~ /server::instance::tabletmpondisk/) {
     ($dummy, $self->{created_tmp_tables}) = $self->{handle}->fetchrow_array(q{
         SHOW /*!50000 global */ STATUS LIKE 'Created_tmp_tables'
@@ -667,6 +720,20 @@ sub init {
         SHOW /*!50000 global */ STATUS LIKE 'Open_files'
     });
     $self->{pct_open_files} = 100 * $self->{open_files} / $self->{open_files_limit};
+  } elsif ($params{mode} =~ /server::instance::needoptimize/) {
+    $self->{fragmented} = [];
+    #http://www.electrictoolbox.com/optimize-tables-mysql-php/
+    my  @result = $self->{handle}->fetchall_array(q{
+        SHOW TABLE STATUS
+    });
+    foreach (@result) {
+      my ($name, $engine, $data_length, $data_free) =
+          ($_->[0], $_->[1], $_->[6 ], $_->[9]);
+      next if ($params{name} && $params{name} ne $name);
+      my $fragmentation = $data_length ? $data_free * 100 / $data_length : 0;
+      push(@{$self->{fragmented}},
+          [$name, $fragmentation, $data_length, $data_free]);
+    }
   } elsif ($params{mode} =~ /server::instance::myisam/) {
     $self->{engine_myisam} = DBD::MySQL::Server::Instance::MyISAM->new(
         %params
@@ -693,6 +760,41 @@ sub nagios {
       $self->add_perfdata(sprintf "threads_connected=%d;%d;%d",
           $self->{threads_connected},
           $self->{warningrange}, $self->{criticalrange});
+    } elsif ($params{mode} =~ /server::instance::createdthreads/) {
+      $self->add_nagios(
+          $self->check_thresholds($self->{threads_created_per_sec}, 10, 20),
+          sprintf "%.2f threads created/sec", $self->{threads_created_per_sec});
+      $self->add_perfdata(sprintf "threads_created_per_sec=%.2f;%.2f;%.2f",
+          $self->{threads_created_per_sec},
+          $self->{warningrange}, $self->{criticalrange});
+    } elsif ($params{mode} =~ /server::instance::runningthreads/) {
+      $self->add_nagios(
+          $self->check_thresholds($self->{threads_running}, 10, 20),
+          sprintf "%d running threads", $self->{threads_running});
+      $self->add_perfdata(sprintf "threads_running=%d;%d;%d",
+          $self->{threads_running},
+          $self->{warningrange}, $self->{criticalrange});
+    } elsif ($params{mode} =~ /server::instance::cachedthreads/) {
+      $self->add_nagios(
+          $self->check_thresholds($self->{threads_cached}, 10, 20),
+          sprintf "%d cached threads", $self->{threads_cached});
+      $self->add_perfdata(sprintf "threads_cached=%d;%d;%d",
+          $self->{threads_cached},
+          $self->{warningrange}, $self->{criticalrange});
+    } elsif ($params{mode} =~ /server::instance::abortedconnects/) {
+      $self->add_nagios(
+          $self->check_thresholds($self->{connects_aborted_per_sec}, 1, 5),
+          sprintf "%.2f aborted connections/sec", $self->{connects_aborted_per_sec});
+      $self->add_perfdata(sprintf "connects_aborted_per_sec=%.2f;%.2f;%.2f",
+          $self->{connects_aborted_per_sec},
+          $self->{warningrange}, $self->{criticalrange});
+    } elsif ($params{mode} =~ /server::instance::abortedclients/) {
+      $self->add_nagios(
+          $self->check_thresholds($self->{clients_aborted_per_sec}, 1, 5),
+          sprintf "%.2f aborted (client died) connections/sec", $self->{clients_aborted_per_sec});
+      $self->add_perfdata(sprintf "clients_aborted_per_sec=%.2f;%.2f;%.2f",
+          $self->{clients_aborted_per_sec},
+          $self->{warningrange}, $self->{criticalrange});
     } elsif ($params{mode} =~ /server::instance::threadcachehitrate/) {
       my $refkey = 'threadcache_hitrate'.($params{lookback} ? '_now' : '');
       $self->add_nagios(
@@ -776,7 +878,7 @@ sub nagios {
         $self->check_thresholds($self->{$refkey}, "1", "2");
         $self->add_nagios_ok(
             sprintf "table lock contention %.2f%% (uptime < 10800)",
-            $self->{refkey});
+            $self->{$refkey});
       }
       $self->add_perfdata(sprintf "tablelock_contention=%.2f%%;%s;%s",
           $self->{table_lock_contention},
@@ -818,6 +920,16 @@ sub nagios {
           $self->{open_files},
           $self->{open_files_limit} * $self->{warningrange} / 100,
           $self->{open_files_limit} * $self->{criticalrange} / 100);
+    } elsif ($params{mode} =~ /server::instance::needoptimize/) {
+      foreach (@{$self->{fragmented}}) {
+        $self->add_nagios(
+            $self->check_thresholds($_->[1], 10, 25),
+            sprintf "table %s is %.2f%% fragmented", $_->[0], $_->[1]);
+        if ($params{name}) {
+          $self->add_perfdata(sprintf "'%s_frag'=%.2f%%;%d;%d",
+              $_->[0], $_->[1], $self->{warningrange}, $self->{criticalrange});
+        }
+      }
     } elsif ($params{mode} =~ /server::instance::myisam/) {
       $self->{engine_myisam}->nagios(%params);
       $self->merge_nagios($self->{engine_myisam});
@@ -879,6 +991,9 @@ sub new {
     timeout => $params{timeout},
     warningrange => $params{warningrange},
     criticalrange => $params{criticalrange},
+    verbose => $params{verbose},
+    report => $params{report},
+    labelformat => $params{labelformat},
     version => 'unknown',
     instance => undef,
     handle => undef,
@@ -905,17 +1020,36 @@ sub init {
   my %params = @_;
   $params{handle} = $self->{handle};
   $params{uptime} = $self->{uptime};
+  $self->set_global_db_thresholds(\%params);
   if ($params{mode} =~ /^server::instance/) {
     $self->{instance} = DBD::MySQL::Server::Instance->new(%params);
   } elsif ($params{mode} =~ /^server::sql/) {
-    $self->{genericsql} = $self->{handle}->fetchrow_array($params{selectname});
-    if ($self->{genericsql} =~ /^\s*\d+\s*$/) {
-      $self->{genericsql} = sprintf "%d", $self->{genericsql};
-    } elsif ($self->{genericsql} =~ /^\s*\d*\.\d+\s*$/) {
-      $self->{genericsql} = sprintf "%.2f", $self->{genericsql};
+    $self->set_local_db_thresholds(%params);
+    if ($params{regexp}) {
+      # sql output is treated as text
+      if ($params{name2} eq $params{name}) {
+        $self->add_nagios_unknown(sprintf "where's the regexp????");
+      } else {
+        $self->{genericsql} =
+            $self->{handle}->fetchrow_array($params{selectname});
+        if (! defined $self->{genericsql}) {
+          $self->add_nagios_unknown(sprintf "got no valid response for %s",
+              $params{selectname});
+        }
+      }
     } else {
-      $self->add_nagios_unknown(sprintf "got no valid response for %s",
-          $params{selectname});
+      # sql output must be a number (or array of numbers)
+      @{$self->{genericsql}} =
+          $self->{handle}->fetchrow_array($params{selectname});
+      if (! (defined $self->{genericsql} &&
+          (scalar(grep { /^[+-]?(?:\d+(?:\.\d*)?|\.\d+)$/ } @{$self->{genericsql}})) == 
+          scalar(@{$self->{genericsql}}))) {
+        $self->add_nagios_unknown(sprintf "got no valid response for %s",
+            $params{selectname});
+      } else {
+        # name2 in array
+        # units in array
+      }
     }
   } elsif ($params{mode} =~ /^server::uptime/) {
     # already set with the connection. but use minutes here
@@ -1001,17 +1135,55 @@ sub nagios {
           $self->{connection_time},
           $self->{warningrange}, $self->{criticalrange});
     } elsif ($params{mode} =~ /^server::sql/) {
-      $self->add_nagios(
-          $self->check_thresholds($self->{genericsql}, 1, 5),
-            sprintf "%s: %s%s", 
-            $params{name2} ? lc $params{name2} : lc $params{selectname},
-            $self->{genericsql},
-            $params{units} ? $params{units} : "");
-      $self->add_perfdata(sprintf "\'%s\'=%s%s;%s;%s",
-          $params{name2} ? lc $params{name2} : lc $params{selectname},
-          $self->{genericsql},
-          $params{units} ? $params{units} : "",
-          $self->{warningrange}, $self->{criticalrange});
+      if ($params{regexp}) {
+        if (substr($params{name2}, 0, 1) eq '!') {
+          $params{name2} =~ s/^!//;
+          if ($self->{genericsql} !~ /$params{name2}/) {
+            $self->add_nagios_ok(
+                sprintf "output %s does not match pattern %s",
+                    $self->{genericsql}, $params{name2});
+          } else {
+            $self->add_nagios_critical(
+                sprintf "output %s matches pattern %s",
+                    $self->{genericsql}, $params{name2});
+          }
+        } else {
+          if ($self->{genericsql} =~ /$params{name2}/) {
+            $self->add_nagios_ok(
+                sprintf "output %s matches pattern %s",
+                    $self->{genericsql}, $params{name2});
+          } else {
+            $self->add_nagios_critical(
+                sprintf "output %s does not match pattern %s",
+                    $self->{genericsql}, $params{name2});
+          }
+        }
+      } else {
+        $self->add_nagios(
+            # the first item in the list will trigger the threshold values
+            $self->check_thresholds($self->{genericsql}[0], 1, 5),
+                sprintf "%s: %s%s",
+                $params{name2} ? lc $params{name2} : lc $params{selectname},
+                # float as float, integers as integers
+                join(" ", map {
+                    (sprintf("%d", $_) eq $_) ? $_ : sprintf("%f", $_)
+                } @{$self->{genericsql}}),
+                $params{units} ? $params{units} : "");
+        my $i = 0;
+        # workaround... getting the column names from the database would be nicer
+        my @names2_arr = split(/\s+/, $params{name2});
+        foreach my $t (@{$self->{genericsql}}) {
+          $self->add_perfdata(sprintf "\'%s\'=%s%s;%s;%s",
+              $names2_arr[$i] ? lc $names2_arr[$i] : lc $params{selectname},
+              # float as float, integers as integers
+              (sprintf("%d", $t) eq $t) ? $t : sprintf("%f", $t),
+              $params{units} ? $params{units} : "",
+            ($i == 0) ? $self->{warningrange} : "",
+              ($i == 0) ? $self->{criticalrange} : ""
+          );
+          $i++;
+        }
+      }
     } elsif ($params{mode} =~ /^my::([^:.]+)/) {
       $self->{my}->nagios(%params);
       $self->merge_nagios($self->{my});
@@ -1134,34 +1306,153 @@ sub merge_nagios {
   push(@{$self->{nagios}->{perfdata}}, @{$child->{nagios}->{perfdata}});
 }
 
-
 sub calculate_result {
   my $self = shift;
-  if ($ENV{NRPE_MULTILINESUPPORT} && 
+  my $labels = shift || {};
+  my $multiline = 0;
+  map {
+    $self->{nagios_level} = $ERRORS{$_} if
+        (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}}));
+  } ("OK", "UNKNOWN", "WARNING", "CRITICAL");
+  if ($ENV{NRPE_MULTILINESUPPORT} &&
       length join(" ", @{$self->{nagios}->{perfdata}}) > 200) {
-    foreach my $level ("CRITICAL", "WARNING", "UNKNOWN", "OK") {
-      # first the bad news
-      if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$level}}})) {
-        $self->{nagios_message} .=
-            "\n".join("\n", @{$self->{nagios}->{messages}->{$ERRORS{$level}}});
-      }
-    }
-    $self->{nagios_message} =~ s/^\n//g;
-    $self->{perfdata} = join("\n", @{$self->{nagios}->{perfdata}});
+    $multiline = 1;
+  }
+  my $all_messages = join(($multiline ? "\n" : ", "), map {
+      join(($multiline ? "\n" : ", "), @{$self->{nagios}->{messages}->{$ERRORS{$_}}})
+  } grep {
+      scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})
+  } ("CRITICAL", "WARNING", "UNKNOWN", "OK"));
+  my $bad_messages = join(($multiline ? "\n" : ", "), map {
+      join(($multiline ? "\n" : ", "), @{$self->{nagios}->{messages}->{$ERRORS{$_}}})
+  } grep {
+      scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})
+  } ("CRITICAL", "WARNING", "UNKNOWN"));
+  my $all_messages_short = $bad_messages ? $bad_messages : 'no problems';
+  my $all_messages_html = "<table style=\"border-collapse: collapse;\">".
+      join("", map {
+          my $level = $_;
+          join("", map {
+              sprintf "<tr valign=\"top\"><td class=\"service%s\">%s</td></tr>",
+              $level, $_;
+          } @{$self->{nagios}->{messages}->{$ERRORS{$_}}});
+      } grep {
+          scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})
+      } ("CRITICAL", "WARNING", "UNKNOWN", "OK")).
+  "</table>";
+  if (exists $self->{identstring}) {
+    $self->{nagios_message} .= $self->{identstring};
+  }
+  if ($self->{report} eq "long") {
+    $self->{nagios_message} .= $all_messages;
+  } elsif ($self->{report} eq "short") {
+    $self->{nagios_message} .= $all_messages_short;
+  } elsif ($self->{report} eq "html") {
+    $self->{nagios_message} .= $all_messages_short."\n".$all_messages_html;
+  }
+  if ($self->{labelformat} eq "pnp4nagios") {
+    $self->{perfdata} = join(" ", @{$self->{nagios}->{perfdata}});
   } else {
-    foreach my $level ("CRITICAL", "WARNING", "UNKNOWN", "OK") {
-      # first the bad news
-      if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$level}}})) {
-        $self->{nagios_message} .= 
-            join(", ", @{$self->{nagios}->{messages}->{$ERRORS{$level}}}).", ";
+    $self->{perfdata} = join(" ", map {
+        my $perfdata = $_;
+        if ($perfdata =~ /^(.*?)=(.*)/) {
+          my $label = $1;
+          my $data = $2;
+          if (exists $labels->{$label} &&
+              exists $labels->{$label}->{$self->{labelformat}}) {
+            $labels->{$label}->{$self->{labelformat}}."=".$data;
+          } else {
+            $perfdata;
+          }
+        } else {
+          $perfdata;
+        }
+    } @{$self->{nagios}->{perfdata}});
+  }
+}
+
+sub set_global_db_thresholds {
+  my $self = shift;
+  my $params = shift;
+  my $warning = undef;
+  my $critical = undef;
+  return unless defined $params->{dbthresholds};
+  $params->{name0} = $params->{dbthresholds};
+  # :pluginmode   :name     :warning    :critical
+  # mode          empty     
+  # 
+  eval {
+    if ($self->{handle}->fetchrow_array(q{
+        SELECT table_name
+        FROM information_schema.tables
+        WHERE table_schema = ?
+        AND table_name = 'CHECK_MYSQL_HEALTH_THRESHOLDS';
+      }, $self->{database})) { # either --database... or information_schema
+      my @dbthresholds = $self->{handle}->fetchall_array(q{
+          SELECT * FROM check_mysql_health_thresholds
+      });
+      $params->{dbthresholds} = \@dbthresholds;
+      foreach (@dbthresholds) { 
+        if (($_->[0] eq $params->{cmdlinemode}) &&
+            (! defined $_->[1] || ! $_->[1])) {
+          ($warning, $critical) = ($_->[2], $_->[3]);
+        }
       }
     }
-    $self->{nagios_message} =~ s/, $//g;
-    $self->{perfdata} = join(" ", @{$self->{nagios}->{perfdata}});
+  };
+  if (! $@) {
+    if ($warning) {
+      $params->{warningrange} = $warning;
+      $self->trace("read warningthreshold %s from database", $warning);
+    }
+    if ($critical) {
+      $params->{criticalrange} = $critical;
+      $self->trace("read criticalthreshold %s from database", $critical);
+    }
   }
-  foreach my $level ("OK", "UNKNOWN", "WARNING", "CRITICAL") {
-    if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$level}}})) {
-      $self->{nagios_level} = $ERRORS{$level};
+}
+
+sub set_local_db_thresholds {
+  my $self = shift;
+  my %params = @_;
+  my $warning = undef;
+  my $critical = undef;
+  # :pluginmode   :name     :warning    :critical
+  # mode          name0
+  # mode          name2
+  # mode          name
+  #
+  # first: argument of --dbthresholds, it it exists
+  # second: --name2
+  # third: --name
+  if (ref($params{dbthresholds}) eq 'ARRAY') {
+    my $marker;
+    foreach (@{$params{dbthresholds}}) {
+      if ($_->[0] eq $params{cmdlinemode}) {
+        if (defined $_->[1] && $params{name0} && $_->[1] eq $params{name0}) {
+          ($warning, $critical) = ($_->[2], $_->[3]);
+          $marker = $params{name0};
+          last;
+        } elsif (defined $_->[1] && $params{name2} && $_->[1] eq $params{name2}) {
+          ($warning, $critical) = ($_->[2], $_->[3]);
+          $marker = $params{name2};
+          last;
+        } elsif (defined $_->[1] && $params{name} && $_->[1] eq $params{name}) {
+          ($warning, $critical) = ($_->[2], $_->[3]);
+          $marker = $params{name};
+          last;
+        }
+      }
+    }
+    if ($warning) {
+      $self->{warningrange} = $warning;
+      $self->trace("read warningthreshold %s for %s from database",
+         $marker, $warning);
+    }
+    if ($critical) {
+      $self->{criticalrange} = $critical;
+      $self->trace("read criticalthreshold %s for %s from database",
+          $marker, $critical);
     }
   }
 }
@@ -1246,9 +1537,30 @@ sub save_state {
   my $self = shift;
   my %params = @_;
   my $extension = "";
-  mkdir $params{statefilesdir} unless -d $params{statefilesdir};
-  my $statefile = sprintf "%s/%s_%s", 
-      $params{statefilesdir}, $params{hostname}, $params{mode};
+  my $mode = $params{mode};
+  if ($params{connect} && $params{connect} =~ /(\w+)\/(\w+)@(\w+)/) {
+    $params{connect} = $3;
+  } elsif ($params{connect}) {
+    # just to be sure
+    $params{connect} =~ s/\//_/g;
+  }
+  if ($^O =~ /MSWin/) {
+    $mode =~ s/::/_/g;
+    $params{statefilesdir} = $self->system_vartmpdir();
+  }
+  if (! -d $params{statefilesdir}) {
+    eval {
+      use File::Path;
+      mkpath $params{statefilesdir};
+    };
+  }
+  if ($@ || ! -w $params{statefilesdir}) {
+    $self->add_nagios($ERRORS{CRITICAL},
+        sprintf "statefilesdir %s does not exist or is not writable\n",
+        $params{statefilesdir});
+    return;
+  }
+  my $statefile = sprintf "%s_%s", $params{hostname}, $mode;
   $extension .= $params{differenciator} ? "_".$params{differenciator} : "";
   $extension .= $params{socket} ? "_".$params{socket} : "";
   $extension .= $params{port} ? "_".$params{port} : "";
@@ -1263,12 +1575,17 @@ sub save_state {
   $extension =~ s/\s/_/g;
   $statefile .= $extension;
   $statefile = lc $statefile;
-  open(STATE, ">$statefile");
-  if ((ref($params{save}) eq "HASH") && exists $params{save}->{timestamp}) {
-    $params{save}->{localtime} = scalar localtime $params{save}->{timestamp};
+  $statefile = sprintf "%s/%s", $params{statefilesdir}, $statefile;
+  if (open(STATE, ">$statefile")) {
+    if ((ref($params{save}) eq "HASH") && exists $params{save}->{timestamp}) {
+      $params{save}->{localtime} = scalar localtime $params{save}->{timestamp};
+    }
+    printf STATE Data::Dumper::Dumper($params{save});
+    close STATE;
+  } else { 
+    $self->add_nagios($ERRORS{CRITICAL},
+        sprintf "statefile %s is not writable", $statefile);
   }
-  printf STATE Data::Dumper::Dumper($params{save});
-  close STATE;
   $self->debug(sprintf "saved %s to %s",
       Data::Dumper::Dumper($params{save}), $statefile);
 }
@@ -1277,8 +1594,18 @@ sub load_state {
   my $self = shift;
   my %params = @_;
   my $extension = "";
-  my $statefile = sprintf "%s/%s_%s", 
-      $params{statefilesdir}, $params{hostname}, $params{mode};
+  my $mode = $params{mode};
+  if ($params{connect} && $params{connect} =~ /(\w+)\/(\w+)@(\w+)/) {
+    $params{connect} = $3;
+  } elsif ($params{connect}) {
+    # just to be sure
+    $params{connect} =~ s/\//_/g;
+  }
+  if ($^O =~ /MSWin/) {
+    $mode =~ s/::/_/g;
+    $params{statefilesdir} = $self->system_vartmpdir();
+  }
+  my $statefile = sprintf "%s_%s", $params{hostname}, $mode;
   $extension .= $params{differenciator} ? "_".$params{differenciator} : "";
   $extension .= $params{socket} ? "_".$params{socket} : "";
   $extension .= $params{port} ? "_".$params{port} : "";
@@ -1293,13 +1620,15 @@ sub load_state {
   $extension =~ s/\s/_/g;
   $statefile .= $extension;
   $statefile = lc $statefile;
+  $statefile = sprintf "%s/%s", $params{statefilesdir}, $statefile;
   if ( -f $statefile) {
     our $VAR1;
     eval {
       require $statefile;
     };
     if($@) {
-printf "rumms\n";
+      $self->add_nagios($ERRORS{CRITICAL},
+          sprintf "statefile %s is corrupt", $statefile);
     }
     $self->debug(sprintf "load %s", Data::Dumper::Dumper($VAR1));
     return $VAR1;
@@ -2825,7 +3154,7 @@ sub init {
     foreach my $line (split(/\n/, $data)) {
       if ($line =~ /\[(.*)\]/) {
         $in_section = $1;
-      } elsif ($line =~ /(.*)=(.*)/) {
+      } elsif ($line =~ /(.*?)\s*=\s*(.*)/) {
         $self->{config}->{$in_section}->{$1} = $2;
       }
     }
@@ -2871,10 +3200,10 @@ use lib dirname($0);
 use vars qw ($PROGNAME $REVISION $CONTACT $TIMEOUT $STATEFILESDIR $needs_restart %commandline);
 
 $PROGNAME = "check_mysql_health";
-$REVISION = '$Revision: 2.1.3 $';
+$REVISION = '$Revision: 2.1.7 $';
 $CONTACT = 'gerhard.lausser@consol.de';
 $TIMEOUT = 60;
-$STATEFILESDIR = '/var/run';
+$STATEFILESDIR = '/var/tmp/check_mysql_health';
 $needs_restart = 0;
 
 my @modes = (
@@ -2890,6 +3219,21 @@ my @modes = (
   ['server::instance::threadcachehitrate',
       'threadcache-hitrate', undef,
       'Hit rate of the thread-cache' ],
+  ['server::instance::createdthreads',
+      'threads-created', undef,
+      'Number of threads created per sec' ],
+  ['server::instance::runningthreads',
+      'threads-running', undef,
+      'Number of currently running threads' ],
+  ['server::instance::cachedthreads',
+      'threads-cached', undef,
+      'Number of currently cached threads' ],
+  ['server::instance::abortedconnects',
+      'connects-aborted', undef,
+      'Number of aborted connections per sec' ],
+  ['server::instance::abortedclients',
+      'clients-aborted', undef,
+      'Number of aborted connections (because the client died) per sec' ],
   ['server::instance::replication::slavelag',
       'slave-lag', ['replication-slave-lag'],
       'Seconds behind master' ],
@@ -2929,6 +3273,9 @@ my @modes = (
   ['server::instance::tabletmpondisk',
       'tmp-disk-tables', undef,
       'Percent of temp tables created on disk' ],
+  ['server::instance::needoptimize',
+      'table-fragmentation', undef,
+      'Show tables which should be optimized' ],
   ['server::instance::openfiles',
       'open-files', undef,
       'Percent of opened files' ],
@@ -2946,6 +3293,67 @@ my @modes = (
       'any sql command returning a single number' ],
 );
 
+# rrd data store names are limited to 19 characters
+my %labels = (
+  bufferpool_hitrate => {
+    groundwork => 'bp_hitrate',
+  },
+  bufferpool_hitrate_now => {
+    groundwork => 'bp_hitrate_now',
+  },
+  bufferpool_free_waits_rate => {
+    groundwork => 'bp_freewaits',
+  },
+  innodb_log_waits_rate => {
+    groundwork => 'inno_log_waits',
+  },
+  keycache_hitrate => {
+    groundwork => 'kc_hitrate',
+  },
+  keycache_hitrate_now => {
+    groundwork => 'kc_hitrate_now',
+  },
+  threads_created_per_sec => {
+    groundwork => 'thrds_creat_per_s',
+  },
+  connects_aborted_per_sec => {
+    groundwork => 'conn_abrt_per_s',
+  },
+  clients_aborted_per_sec => {
+    groundwork => 'clnt_abrt_per_s',
+  },
+  thread_cache_hitrate => {
+    groundwork => 'tc_hitrate',
+  },
+  thread_cache_hitrate_now => {
+    groundwork => 'tc_hitrate_now',
+  },
+  qcache_lowmem_prunes_rate => {
+    groundwork => 'qc_lowm_prnsrate',
+  },
+  slow_queries_rate => {
+    groundwork => 'slow_q_rate',
+  },
+  tablecache_hitrate => {
+    groundwork => 'tac_hitrate',
+  },
+  tablecache_fillrate => {
+    groundwork => 'tac_fillrate',
+  },
+  tablelock_contention => {
+    groundwork => 'tl_contention',
+  },
+  tablelock_contention_now => {
+    groundwork => 'tl_contention_now',
+  },
+  pct_tmp_table_on_disk => {
+    groundwork => 'tmptab_on_disk',
+  },
+  pct_tmp_table_on_disk_now => {
+    groundwork => 'tmptab_on_disk_now',
+  },
+);
+
 sub print_usage () {
   print <<EOUS;
   Usage:
@@ -2998,6 +3406,9 @@ EOUS
     --units
        one of %, KB, MB, GB. This is used for a better output of mode=sql
        and for specifying thresholds for mode=tablespace-free
+    --labelformat
+       one of pnp4nagios (which is the default) or groundwork.
+       It is used to shorten performance data labels to 19 characters.
 
   In mode sql you can url-encode the statement so you will not have to mess
   around with special characters in your Nagios service definitions.
@@ -3076,6 +3487,7 @@ my @params = (
     "perfdata",
     "warning=s",
     "critical=s",
+    "dbthresholds:s",
     "absolute|a",
     "environment|e=s%",
     "method=s",
@@ -3087,7 +3499,10 @@ my @params = (
     "units=s",
     "lookback=i",
     "3",
+    "statefilesdir=s",
     "with-mymodules-dyn-dir=s",
+    "report=s",
+    "labelformat=s",
     "extra-opts:s");
 
 if (! GetOptions(\%commandline, @params)) {
@@ -3151,11 +3566,22 @@ if (exists $commandline{method}) {
   $commandline{method} = "dbi";
 }
 
+if (exists $commandline{report}) {
+  # short, long, html
+} else {
+  $commandline{report} = "long";
+}
+
+if (exists $commandline{labelformat}) {
+  # groundwork
+} else {
+  $commandline{labelformat} = "pnp4nagios";
+}
+
 if (exists $commandline{'with-mymodules-dyn-dir'}) {
-  $DBD::MYSQL::Server::my_modules_dyn_dir = $commandline{'with-mymodules-dyn-dir
-'};
+  $DBD::MySQL::Server::my_modules_dyn_dir = $commandline{'with-mymodules-dyn-dir'};
 } else {
-  $DBD::MYSQL::Server::my_modules_dyn_dir = '/usr/local/nagios/libexec';
+  $DBD::MySQL::Server::my_modules_dyn_dir = '/usr/local/nagios/libexec';
 }
 
 if (exists $commandline{environment}) {
@@ -3233,6 +3659,14 @@ if (exists $commandline{shell}) {
   system("/bin/sh");
 }
 
+if (! exists $commandline{statefilesdir}) {
+  if (exists $ENV{OMD_ROOT}) {
+    $commandline{statefilesdir} = $ENV{OMD_ROOT}."/var/tmp/check_mysql_health";
+  } else {
+    $commandline{statefilesdir} = $STATEFILESDIR;
+  }
+}
+
 if (exists $commandline{name}) {
   # objects can be encoded like an url
   # with s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;
@@ -3267,16 +3701,16 @@ if ($commandline{mode} =~ /^my-([^\-.]+)/) {
   exit 3;
 }
 
-$commandline{mode} = (
-    map { $_->[0] }
-    grep {
-       ($commandline{mode} eq $_->[1]) ||
-       ( defined $_->[2] && grep { $commandline{mode} eq $_ } @{$_->[2]})
-    } @modes
-)[0];
 my %params = (
     timeout => $TIMEOUT,
-    mode => $commandline{mode},
+    mode => (
+        map { $_->[0] }
+        grep {
+           ($commandline{mode} eq $_->[1]) ||
+           ( defined $_->[2] && grep { $commandline{mode} eq $_ } @{$_->[2]})
+        } @modes
+    )[0],
+    cmdlinemode => $commandline{mode},
     method => $commandline{method} ||
         $ENV{NAGIOS__SERVICEMYSQL_METH} ||
         $ENV{NAGIOS__HOSTMYSQL_METH} || 'dbi',
@@ -3300,6 +3734,7 @@ my %params = (
         $ENV{NAGIOS__HOSTMYSQL_PASS},
     warningrange => $commandline{warning},
     criticalrange => $commandline{critical},
+    dbthresholds => $commandline{dbthresholds},
     absolute => $commandline{absolute},
     lookback => $commandline{lookback},
     selectname => $commandline{name} || $commandline{tablespace} || $commandline{datafile},
@@ -3309,7 +3744,10 @@ my %params = (
     units => $commandline{units},
     lookback => $commandline{lookback} || 0,
     eyecandy => $commandline{eyecandy},
-    statefilesdir => $STATEFILESDIR,
+    statefilesdir => $commandline{statefilesdir},
+    verbose => $commandline{verbose},
+    report => $commandline{report},
+    labelformat => $commandline{labelformat},
 );
 
 my $server = undef;
@@ -3318,14 +3756,14 @@ my $cluster = undef;
 if ($params{mode} =~ /^(server|my)/) {
   $server = DBD::MySQL::Server->new(%params);
   $server->nagios(%params);
-  $server->calculate_result();
+  $server->calculate_result(\%labels);
   $nagios_message = $server->{nagios_message};
   $nagios_level = $server->{nagios_level};
   $perfdata = $server->{perfdata};
 } elsif ($params{mode} =~ /^cluster/) {
   $cluster = DBD::MySQL::Cluster->new(%params);
   $cluster->nagios(%params);
-  $cluster->calculate_result();
+  $cluster->calculate_result(\%labels);
   $nagios_message = $cluster->{nagios_message};
   $nagios_level = $cluster->{nagios_level};
   $perfdata = $cluster->{perfdata};