Nagios Plugin: Counting the Lines in a MySQL Database
Server - Nagios

 

Introduction


The "check_mysql_count.plplugin can be used to count lines in a MySQL database.  It can be used to count either the total number of lines, or the number of lines that meet a certain criterion.  You can download the plugin from here:

http://exchange.nagios.org/directory/Databases/Plugins/MySQL/check_mysql_count/details


Installation


The plugin is a simple, unarchived perl script that you can download to your home directory.  Once you've downloaded it, open it in your favorite text editor so that you can make one slight improvement.
Look for the that says:

$status = "Rows $state - $count rows\n";

(This should be in the vicinity of line number 77.)

After this line, add the following line:

if ($count =~ m/\D/) { $state = "CRITICAL"; $status = "$count \n"; }

Without this line, the plugin will return an "OK" status either whenever it can't connect to the MySQL server, or whenever the check is incorrect.  Adding this line will cause an actual error message to be returned.

Now, simply copy the plugin file to your Nagios plugins directory, add the executable permission bit, and change ownership and group association to "nagios".


Setting up the MySQL Server


For testing purposes, we set up a "widgets" database with a "master_widgets" table.  We populated the table thusly:

mysql> select * from master_widgets;
+----------------+---------------------------------+----------+
| name           | description                     | quantity |
+----------------+---------------------------------+----------+
| Rad Widget     | Very large                      |     3033 |
| Little Widget  | Very small                      |       26 |
| Tiny Widget    | Extremely small                 |      576 |
| Weird Widget   | Only weirdos use it             |       99 |
| Strange_Widget | Only strange people use it      |      101 |
| Strange_Widget | Only strange people will use it |        5 |
+----------------+---------------------------------+----------+
6 rows in set (0.00 sec)

We'll then set up a user who will be allowed to log into MySQL from the Nagios server:

mysql> grant all on nagios.* to 'nagios'@'localhost' identified by "password";

(Note the you'll have to assign a password to this user, since it is a required parameter for the plugin.)


Using It


There are no option switches with this plugin.  Rather, it works with eight command-line parameters that are entered without option switches.  These parameters will have to be entered in the same order every time, and all eight will have to be used every time.  Also, even though there's a "shebang" line in the header of the script, you'll still need to explicitly invoke the script with the perl interpreter. The parameters you need to supply are host, user, password, database, table, conditional, warning level, and critical level.  (As we said before, you'll have to use all eight of them, and they have to be in this particular order.)  To test this from the command-line, go to the Nagios server and log in as the "nagios" user.  "cd" into the plugins directory, and enter a command that looks something like this:

perl ./check_mysql_count.pl centos5-3 nagios password widgets
master_widgets quantity\<20 4 5

With this command, we're checking to see if inventory levels have fallen to less than 20 for any item.  If more than four items have fallen to this level, we'll get a warning.  And, if more than five items have fallen to this level, we'll get a critical condition.  Note that when we use either a greater-than or less-than symbol, we need to escape it.  We can do this by either preceding it with a backslash, or by surrounding it with a pair of single quotes.


Configuration


The host definition is done in the normal way:

# Host Definitions
define host{
use             linux-server
host_name       centos5-3
alias           MySql Server
address         192.168.0.200
}

Since you always have to use the same eight parameters in the same order, you'll only need one command definition file.

check_mysql_count.pl command definitions
define command{
command_name    check_mysql_count
command_line    /usr/bin/perl /$USER1$/check_mysql_count.pl
$HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$ $ARG5$ $ARG6$ $ARG7$
}


We can now build a variety of different service definitions to make this do various things.

First, let's build a service definition that will simply count all rows in the table:

define service{
use                     generic-service
host_name               centos5-3
service_description     Total Rows
check_command
check_mysql_count!nagios!password!widgets!master_widgets!1!5!6
}

In this case, the "1" that we've placed as the third parameter from the end can be any single digit number.  (For some reason, it doesn't matter what number you put there.)  Since we have six rows in this table, we should see a critical condition:



The next service definition will tell us how may items have fallen below a certain inventory level:

define service{
use                     generic-service
host_name               centos5-3
service_description     Too Few Widgets
check_command
check_mysql_count!nagios!password!widgets!master_widgets!quantity'<'100!4!5
}

Note how we've surrounded the less-than sign with a pair of single quotes.  Since we only have three items whose inventory levels have fallen to less than 100, we'll get an "OK" condition:


Nagios MySQL


Now, let's check for items that have excessive inventory:

define service{
use                     generic-service
host_name               centos5-3
service_description     Too Many Widgets
check_command
check_mysql_count!nagios!password!widgets!master_widgets!quantity'>'3000!4!5
}

Again, be sure to surround the greater-than sign with a pair of single quotes.


Nagios MySQL

The next part is a bit tricky.  This is where we'll count the number of lines that have an identical text string in a certain field.

define service{
use                     generic-service
host_name               centos5-3
service_description     Rad Widgets
check_command
check_mysql_count!nagios!password!widgets!master_widgets!name=\\"'Rad
Widget'\\"!4!5
}

Here, we're looking to see how many lines have the text string "Rad Widget" in the "name" field.  Note how we've had to use the double backslash and double quote construct both before and after the text string.  Additionally, if the text string contains a blank space, you'll need to also surround it with a pair of single quotes.  We only have one line that contains "Rad Widget" in the name field, so we'll get an "OK" condition.


Nagios MySQL

If the text string doesn't contain a blank space, then you can omit the single quotes:

define service{
use                     generic-service
host_name               centos5-3
service_description     Strange Widgets
check_command
check_mysql_count!nagios!password!widgets!master_widgets!name=\\"Strange_Widget\\"!4!5
}


Nagios MySQL

As a variation on the theme, we could have set the "warning" level to a high number that should never be reached, and then set the "critical" number to "2".  That way, we could get a critical message any time that a duplicate entry exists in the database table.


Conclusion


We've shown you several different ways to use the "check_mysql_count.plplugin.  With a bit of imagination, you just might find even more ways.