MSSQL/MySQL/MariaDB/SQLite3 Monitoring Framework

Now, this isn't to monitor if the services are up. That's easy.

No. This is to monitor for a set value. Maybe you want to make sure your shopping cart is working as it should. Maybe you have your visitor stats in a table, and you want to ensure it's updating properly.

First, let's get to know basic SQL, and pulling ONLY data that you want.

For wordpress, it would be something like this to get new comments in the last hour:

SELECT COUNT(*) as new_comments FROM wp_comments where comment_date >= NOW() - INTERVAL 1 HOUR;  

This would return something like this:

+--------------+
| new_comments |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

Now that we have just the data we want (the count of new comments for the last hour), how do we turn this into an actionable alert?

Simple. We use this handy-dandy alert script I just built!

Installing the scripts

Copy and paste this into /root/scripts/alerter.sh:

#!/usr/bin/php
<?php  
if (isset($_SERVER['argv'][2])){  
        $debug = true;
}
else{  
        $debug = false;
}
$conf = $_SERVER['argv'][1];
if (!isset($conf) || empty($conf)){  
        echo "Please provide a config file to use";
        exit();
}
if (!file_exists($conf)){  
        echo "The config file provided ($conf) doesn't appear to exist";
        exit();
}
include $conf;  
if (DB_DRIVER == "mysql" || DB_DRIVER == "mariadb"){  
    if (extension_loaded("mysql")){
        if (null !== DB_SERVER_PORT || empty(DB_SERVER_PORT) || !is_numeric(DB_SERVER_PORT)){
            $port = 3306;
        }
        else{
            $port = DB_SERVER_PORT;
        }
        $myServer = DB_SERVER.":$port";
        $dbhandle = mysql_connect($myServer,DB_USER,DB_PASS) or die ("Couldn't connect to ".DB_DRIVER." on $myServer\n Error:".mysql_error()."\n");
        $selected = mysql_select_db(DB_NAME,$dbhandle) or die("Couldn't open database ".DB_NAME.".\nError:\n".mysql_error()."\n");
        $query = QUERY;
        $result = mysql_query($query);
        $row = mysql_fetch_array($result);
        $count = $row[QUERY_RETURN_COLUMN];
        $alert = false;
        if (GT_LT_EQ == "GT"){
                if ($count > THRESHOLD){
                        $alert = true;
                }
        }
        elseif(GT_LT_EQ == "LT"){
                if ($count < THRESHOLD){
                        $alert = true;
                }
        }
        elseif(GT_LT_EQ == "EQ"){
                if ($count == THRESHOLD){
                        $alert = true;
                }
        }
        else{
                exit();
        }
        mysql_close($dbhandle);
    }
    else{
        echo "Missing extension mysql. Unable to create connections to MySQL/MariaDB/Percona servers.\n";
        echo "Please install php-mysql or php5-mysql (depending on your Linux distro).\n";
        exit();
    }
}
elseif (DB_DRIVER == "mssql"){  
    if (extension_loaded("mssql")){
        if (null !== DB_SERVER_PORT || empty(DB_SERVER_PORT) || !is_numeric(DB_SERVER_PORT)){
            $port = 1433;
        }
        else{
                $port = DB_SERVER_PORT;
        }
        $myServer = DB_SERVER.":$port";
        $dbhandle = mssql_connect(DB_SERVER, DB_USER, DB_PASS) or die("Couldn't connect to SQL Server on $myServer\n Error:".mssql_get_last_message()."\n");

        /* Select a database to work with */
        $selected = mssql_select_db(DB_NAME, $dbhandle) or die("Couldn't open database ".DB_NAME."\n Error:".mssql_get_last_message()."\n");

        /* Declare the salespulse2 stored procedure is the query we\'re going to run */
        $query = QUERY;

        /* execute the SQL query and return records */
        $result = mssql_query($query);
        $row = mssql_fetch_array($result);
        $count = $row[QUERY_RETURN_COLUMN];
        $alert = false;
        if (GT_LT_EQ == "GT"){
                if ($count > THRESHOLD){
                        $alert = true;
                }
        }
        elseif(GT_LT_EQ == "LT"){
                if ($count < THRESHOLD){
                        $alert = true;
                }
        }
        elseif(GT_LT_EQ == "EQ"){
                if ($count == THRESHOLD){
                        $alert = true;
                }
        }
        else{
                exit();
        }
        mssql_close($dbhandle);
    }
    else{
        echo "Missing extension mssql. Unable to create connections to Microsoft SQL Server instances.\n";
        echo "Please install php-mssql or php5-mssql (depending on your Linux distro).\n";
        exit();
    }
}
elseif (DB_DRIVER == "sqlite3"){  
    if (extension_loaded("sqlite3")){
        $myServer = DB_SERVER;
        $db = new SQLite3($myServer) or die("Unable to open SQLite3 file: ".$db->lastErrorMsg());
        $result = $db->querySingle(QUERY) or die("Error running query: ".$db->lastErrorMsg());
        $count = $result[QUERY_RETURN_COLUMN];
        $alert = false;
        if (GT_LT_EQ == "GT"){
                if ($count > THRESHOLD){
                        $alert = true;
                }
        }
        elseif(GT_LT_EQ == "LT"){
                if ($count < THRESHOLD){
                        $alert = true;
                }
        }
        elseif(GT_LT_EQ == "EQ"){
                if ($count == THRESHOLD){
                        $alert = true;
                }
        }
        else{
                exit();
        }
    }
    else{
        echo "Missing extension sqlite3. Unable to load SQLite3 files.\n";
        echo "Please install php-sqlite or php5-sqlite (depending on your Linux distro).\n";
        exit();
    }
}
else{  
    echo "Invalid Database Driver, or one not selected in the config file\n";
    exit();
}

if ($alert == true){  
    $mailFrom = MAIL_FROM;
    $mailTo = MAIL_TO;
    $mailSubj = MAIL_SUBJ;
    $mailBody = MAIL_BODY;
    $message = str_replace("::count::",$count,$mailBody);
    if (USE_PHP_MAIL == true){
        $headers = 'From: $mailFrom' . "\r\n" .
        'Reply-To: $mailFrom' . "\r\n" .
        'X-Mailer: PHP/' . phpversion();
        mail($mailTo, $mailSubj, $message, $headers);
    }
    else{
        shell_exec("/root/scripts/sendmail.sh '".MAIL_SERVER."' '$mailFrom' '$mailTo' '$mailSubj' '$message'");
    }
}
if ($debug == true){  
        if ($alert == true){
                echo "Alert was set to true. You should have gotten an e-mail.\n";

        }
        else{
                echo "Alert was set to false.  No e-mail should have been generated.\n";
        }
        echo "Values parsed:
    DB_SERVER: ".DB_SERVER."
    DB_SERVER_PORT: ".DB_SERVER_PORT."
    DB_USER: ".DB_USER."
    DB_PASS: ".DB_PASS."
    DB_NAME: ".DB_NAME."
    DB_DRIVER: ".DB_DRIVER."
    QUERY_RETURN_COLUMN: ".QUERY_RETURN_COLUMN."
    QUERY: ".QUERY."
    GT_LT_EQ(Greater/less/equal): ".GT_LT_EQ."
    THRESHOLD: ".THRESHOLD."
    USE_PHP_MAIL: ".USE_PHP_MAIL."
    MAIL_SERVER: ".MAIL_SERVER."
    MAIL_FROM: ".MAIL_FROM."
    MAIL_TO: ".MAIL_TO."
    MAIL_SUBJ: ".MAIL_SUBJ."
    MAIL_BODY: ".MAIL_BODY."
";
}
?>

Next, let's make this executable:

chmod +x /root/scripts/alerter.sh  

Configure your Mailer

If you don't have sendmail/postfix installed, install "expect" from your package manager.

For Ubuntu/Debian-like:

apt-get update && apt-get -y install expect  

for RPM-based:

yum -y install expect  

Next, copy and paste this into /root/scripts/sendmail.sh:

#! /usr/bin/expect

set timeout 20  
set server [lindex $argv 0]  
set sndr_mail [lindex $argv 1]  
set rcpt_mail [lindex $argv 2]  
set mail_subj [lindex $argv 3]  
set mail_body [lindex $argv 4]

spawn telnet $server 25

expect "Connected to "  
expect "220 "  
send "HELO tappin.com\n"  
expect "250 "  
send "MAIL FROM:<$sndr_mail>\n"  
expect "250 "  
send "RCPT TO:<$rcpt_mail>\n"  
expect "250 "  
send "DATA\n"  
expect "354 "  
send "From:$sndr_mail\n"  
send "To:$rcpt_mail\n"  
send "Subject:$mail_subj\n\n"  
send "$mail_body\n"  
send ".\n"  
expect "250 "  
send "quit\n"  
expect "221 "  

Making config files

Now, just create a folder, named /root/sql-alert-confs or whatever you like.

In that folder, create your first alert configuration. Let's call this one /root/sql-alert-confs/test.conf

To build this, we will use the example output from our WordPress comments from earlier in our config, and tell it to alert when we get more than 5 comments in an hour.

<?php  
//Config file example for MSSQL Querying and alerting

/* DB config variables */
/* DB server can be either the IP of the server, the FQDN of the server, or for SQLite3, the local path to the file */
define('DB_SERVER','127.0.0.1');

/* if unsure of the proper port, make this empty, and it will default to the standard port for the DB engine you're working with */
define('DB_SERVER_PORT','3306');

define('DB_USER','some_user');  
/* DB driver is the type of DB you're using.  Valid types are: mssql, mysql, mariadb, & sqlite3 */
define('DB_DRIVER','mysql');

/* LEAVE THIS PART AS-IS!!! */
define('DB_PASS','some_pass');

/* OK, back to editing */
define('DB_NAME','some_db');

/* In order for this script to work, each system needs to only return one column, or at least the column we want.
    Multiple results/rows of results won't work.  This next one tells my system which column to do comparisons against.
    The QUERY_RETURN_COLUMN is the column your query will return. In this example, I'm having it return COUNT(*) AS "new_comments", so new_comments is my QUERY_RETURN_COLUMN
    so you'd put that.*/
define('QUERY_RETURN_COLUMN','new_comments');  
/* Query Config Variables */
define('QUERY',"SELECT COUNT(*) as new_comments FROM wp_comments where comment_date >= NOW() - INTERVAL 1 HOUR;");  
/* Should this result alert on greater(GT), less(LT), or equal(EQ)? */
define('GT_LT_EQ','GT');  
/* This is the second part of GT_LT_EQ. in the following example, "1" would be the threshold:
    $x > 1 */
define('THRESHOLD','5');  
/* Set this to true if you want to use PHPs built-in mail() function rather than the sendmail.sh expect script */
define('USE_PHP_MAIL',false);

define('MAIL_SERVER','mail.example.com');  
define('MAIL_FROM','alerts_your_product@example.com');  
define('MAIL_TO','your_email@example.com');  
define('MAIL_SUBJ','A flood of new comments have just came in.');

/* This is a fairly straight-forward type of event.  You set the message however you like.  No HTML.  new lines are done with "\r\n", and if you want
to have it return the count it got from QUERY_RETURN_COLUMN, use "::count::". It will replace it when it sends the e-mail with the actual count. */  
define('MAIL_BODY','There are quite a few new comments in the last hour!\r\n  
Total number of new comments: ::count::');  
?>

The sample script is fairly well-documented, but keep this in mind: if you don't have the driver installed for PHP to connect to a given DB type, this will output an error.

Once you've tweaked this script to your liking, save it. To test run it, do this:

/root/scripts/alerter.sh /root/sql-alert-confs/test.conf true

This will run the alerter with your config file, and output debug data.

To set this up as a CRON, simply add this to your crontab:

## Run every hour ##
00 * * * * /root/scripts/alerter.sh /root/sql-alert-confs/test.conf  

This will run the cron without any debug output.