Nagios: check_mysql_query_cnt.pl to check the number of rows and provide perfdata

A customer of mine wanted metrics from a MySQL database for a display. The current plugins were not what I needed for the projects. I created a simple plugin that performs a row count on the selected database.

The script is written in the Perl language. It provides options for the user, error message when the query does not result in a count, and Nagios perfdata. The perfdata is displayed in PnP4Nagios and the rows displayed are based upon the result.

Any questions or comments let me know. Below is the Perl script. The script does require the Nagios utils.pm and DBD::Mysql.

#!/usr/bin/perl -w 

# Copyright (c) 2011 Mikhail Kniaziewicz (http://www.systemmonitoringhowto.com)
#Date: March 23, 2011
#Script: check_lxmemory.pl
#Purpose: Provide a Nagios Pluggin to check the counts
#	  from a MySQL Database and provide perfdata.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

use strict;
use Getopt::Long;
use File::Basename;
use DBI;
use DBD::mysql;
#Change the following to the directory containing utils.pm
#(utils.pm ships with nagios).
use lib "/nagios/libexec/";
use utils qw($TIMEOUT %ERRORS &usage &support &print_revision);

Getopt::Long::Configure('bundling');
my ( $query,$dsn, $opt_h, $opt_w, $opt_c, $opt_q, $opt_H, $opt_D,
 $opt_u ,$opt_p, $opt_P, $results, $execute, @rows, $connect, $message, $perfdata);
my $opt_t    = $TIMEOUT;
my $opt_V  = "0.1.1";
my $PROGNAME = basename($0);
my $MYSQL = "/usr/bin/mysql";
my $result;

#Help and Usage sub prototypes
sub print_help () ;
sub print_usage () ; 

GetOptions(
    "version|V"  => \$opt_V,
    "help|?|h"   => \$opt_h,
    "t=i"        => \$opt_t,
    "timeout=i"  => \$opt_t,
    "w=i"        => \$opt_w,
    "warning=i"  => \$opt_w,
    "c=i"        => \$opt_c,
    "critical=i" => \$opt_c,
    "query=s"    => \$opt_q,
    "q=s"	 => \$opt_q,
    "hostname=s" => \$opt_H,
    "H=s"	 => \$opt_H,
    "username"   => \$opt_u,
    "u=s"	 => \$opt_u,
    "database"   => \$opt_D,
    "D=s"	 => \$opt_D,
    "p=s"	 => \$opt_p,
    "password=s" => \$opt_p,
    "port|P=i"   => \$opt_P,
);

print_usage() if ($opt_h);
print_help() if !($opt_c) && !($opt_h);

sub print_help (){
    print "\n";
    printversion();
    print "\nCopyright (c) 2011 Mikhail Kniaziewicz \(mikhailk1\@verizon.net\)\n\n";
    print "\n$PROGNAME is used to find a count from a MySQL SELECT query.
    You should create a user account with a password and grant only
    SELECT privilages to the database you want $PROGNAME to query.\n";
    print "\n";
    print_usage();
    print "\n";
    print_support();
    exit 1;
}
sub print_usage () {
    print "\nUSAGE: $PROGNAME [-w warning] [-c critical]
     [-q \"Query String\"]\n [-H hostname] [-u username] [-p password]
     [-D database] [-P port]\n";
    print "\nEXAMPLE: $PROGNAME -w 10000 -c 20000
     -q \"SELECT COUNT\(\*\) from nagios_hosts\;\" -u nagios -p nagios -D nagios -P 3306\n";
}

sub printversion(){
	print "PROGRAM:$PROGNAME Version:$opt_V \n";
}

sub print_support(){
	print "For support, please email mikhailk1\@verizon.net
               with a description of the problem. Plugin is only
               designed for MySQL in server version 5\.0\.2\n\n";
}

#Let's get down to business
#Check to make sure we have all the plugin elements
if (!($opt_w && $opt_c && $opt_q && $opt_H && $opt_u
&& $opt_D && $opt_p)){
 print print_usage();
 exit $ERRORS{'UNKNOWN'};
} else {
#Create MySQL connection string
$dsn = "dbi:mysql:$opt_D:$opt_H:3306";
$connect = DBI->connect($dsn,$opt_u,$opt_p) \
|| die "Cannot connect to the DB: $DBI::errstr\n";
$query = $connect->prepare($opt_q);
$query->execute();
while(@rows = $query->fetchrow_array()){
    $result = "@rows";
}
}

#Check the results for numeric value
if (!($result =~ m/^(\d+)$/)){
	print "STATUS UNKNOWN: $result rows $perfdata\n";
	print_help();
	exit $ERRORS{'UNKNOWN'};
}

#Let's process the results
$perfdata = "|num_rows=$result;$opt_w;$opt_c;0;$result";
if ($opt_w > $result && $opt_c > $result){
	print "STATUS OK: $result rows $perfdata\n";
	exit $ERRORS{'OK'};
}elsif ($opt_w <= $result && $opt_c >= $result){
	print "STATUS WARNING: $result rows $perfdata\n";
	exit $ERRORS{'WARNING'};
}elsif ($opt_w <= $result && $opt_c <= $result){
	print "STATUS CRITICAL: $result rows $perfdata\n";
	exit $ERRORS{'CRITICAL'};
}else {
	print "STATUS UNKNOWN: $result rows $perfdata\n";
	exit $ERRORS{'UNKNOWN'};
}

Enjoy and please try to check out the sponsors to the right.

Mike Kniaziewicz

Scripts: Ping

Here are several scripts that can be used for a host check using ping. These commands should be adjusted to your enterprise. Since you will have numerous hosts to check, you will need to create a hosts.config file. The hosts.config file used in these examples consists of the following hosts:
#72.14.204.99    google.com
#98.137.149.56    yahoo.com
#207.171.7.63    perl.org

We will be importing this file into our scripts. Remember you might have hundreds of IP addresses and hosts to check. Also remember the program path may be different than what is in these scripts, so check the path prior to running any of these scripts.

BASH

##!/bin/bash
#export PATH=$PATH:/bin:/usr/sbin:/sbin/:/opt:/tmp
#source in our hosts.config file under /tmp

#for hosts in $(awk '/^[1-9]/ {print $1}' /tmp/hosts.config)
#do
#ping -c 3 -t 30 $hosts >/dev/null
#    if [ $? -ne 0 ]
#    then
#        echo $hosts $?
#    fi
#done
#exit

PERL

#!/usr/bin/perl -w
use strict;
use Net::Ping;
$ENV{'PATH'}='/bin:/usr/sbin/:/usr/bin:/sbin:/opt:/tmp';
open( FH,'new("syn",30);
while (<FH>)
{
	my ($host,$hostname) = split /\t/;
	if ($host =~ /^\d/)
	{
         print "$host is alive.\n" if $p->ping($host);
	 print "$host is unreachable.\n" if !$p->ping($host);
	}
}
$p->close();
close(FH);
exit(0);