From f9a942d2a11e70ee68d5ea2c9aa762bff004bf43 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Thu, 7 Apr 2011 17:24:23 +0200
Subject: Initial version of the 'check_dbi' plugin.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This plugin connects to an SQL database using libdbi, thus supporting all
database backends supported by libdbi. It will then issue the specified SQL
query and check the result (the numeric value of the first column of the first
row to be precise) against the specified warning/critical ranges.

The performance data includes the connection time (µs-resolution as provided
by gettimeofday()) and the query result.
---
 plugins/Makefile.am |   4 +-
 plugins/check_dbi.c | 516 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 519 insertions(+), 1 deletion(-)
 create mode 100644 plugins/check_dbi.c

(limited to 'plugins')

diff --git a/plugins/Makefile.am b/plugins/Makefile.am
index 36a28b0b..4781e0fc 100644
--- a/plugins/Makefile.am
+++ b/plugins/Makefile.am
@@ -37,7 +37,7 @@ check_tcp_programs = check_ftp check_imap check_nntp check_pop \
 EXTRA_PROGRAMS = check_mysql check_radius check_pgsql check_snmp check_hpjd \
 	check_swap check_fping check_ldap check_game check_dig \
 	check_nagios check_by_ssh check_dns check_nt check_ide_smart	\
-	check_procs check_mysql_query check_apt
+	check_procs check_mysql_query check_apt check_dbi
 
 EXTRA_DIST = t tests utils.c netutils.c sslutils.c popen.c utils.h netutils.h \
 	popen.h common.h runcmd.c runcmd.h
@@ -64,6 +64,7 @@ test-debug:
 
 check_apt_LDADD = $(BASEOBJS) runcmd.o
 check_cluster_LDADD = $(BASEOBJS)
+check_dbi_LDADD = $(NETLIBS) $(DBILIBS)
 check_dig_LDADD = $(NETLIBS) runcmd.o 
 check_disk_LDADD = $(BASEOBJS) popen.o
 check_dns_LDADD = $(NETLIBS) runcmd.o
@@ -109,6 +110,7 @@ urlize_LDADD = $(BASEOBJS) popen.o
 
 check_apt_DEPENDENCIES = check_apt.c $(BASEOBJS) runcmd.o $(DEPLIBS)
 check_cluster_DEPENDENCIES = check_cluster.c $(BASEOBJS) $(DEPLIBS)
+check_dbi_DEPENDENCIES = check_dbi.c $(NETOBJS) $(DEPLIBS)
 check_dig_DEPENDENCIES = check_dig.c $(NETOBJS) runcmd.o $(DEPLIBS)
 check_disk_DEPENDENCIES = check_disk.c $(BASEOBJS) popen.o $(DEPLIBS) 
 check_dns_DEPENDENCIES = check_dns.c $(NETOBJS) runcmd.o $(DEPLIBS)
diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
new file mode 100644
index 00000000..b52602c4
--- /dev/null
+++ b/plugins/check_dbi.c
@@ -0,0 +1,516 @@
+/*****************************************************************************
+* 
+* Nagios check_dbi plugin
+* 
+* License: GPL
+* Copyright (c) 2011 Nagios Plugins Development Team
+* Author: Sebastian 'tokkee' Harl <sh@teamix.net>
+* 
+* Description:
+* 
+* This file contains the check_dbi plugin
+* 
+* Runs an arbitrary SQL command and checks the result.
+* 
+* 
+* 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 3 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, see <http://www.gnu.org/licenses/>.
+* 
+* 
+*****************************************************************************/
+
+const char *progname = "check_dbi";
+const char *copyright = "2011";
+const char *email = "nagiosplug-devel@lists.sourceforge.net";
+
+#include "common.h"
+#include "utils.h"
+
+#include "netutils.h"
+
+#include <dbi/dbi.h>
+
+#include <stdarg.h>
+
+typedef struct {
+	char *key;
+	char *value;
+} driver_option_t;
+
+char *host = NULL;
+int verbose = 0;
+
+char *warning_range = NULL;
+char *critical_range = NULL;
+thresholds *query_thresholds = NULL;
+
+char *np_dbi_driver = NULL;
+driver_option_t *np_dbi_options = NULL;
+int np_dbi_options_num = 0;
+char *np_dbi_database = NULL;
+char *np_dbi_query = NULL;
+
+int process_arguments (int, char **);
+int validate_arguments (void);
+void print_usage (void);
+void print_help (void);
+
+void np_dbi_print_error (dbi_conn, char *, ...);
+
+int do_query (dbi_conn, double *);
+
+int
+main (int argc, char **argv)
+{
+	int status = STATE_UNKNOWN;
+
+	dbi_driver driver;
+	dbi_conn conn;
+
+	struct timeval start_timeval, end_timeval;
+	double elapsed_time;
+
+	double query_val = 0.0;
+
+	int i;
+
+	setlocale (LC_ALL, "");
+	bindtextdomain (PACKAGE, LOCALEDIR);
+	textdomain (PACKAGE);
+
+	/* Parse extra opts if any */
+	argv = np_extra_opts (&argc, argv, progname);
+
+	if (process_arguments (argc, argv) == ERROR)
+		usage4 (_("Could not parse arguments"));
+
+	/* Set signal handling and alarm */
+	if (signal (SIGALRM, timeout_alarm_handler) == SIG_ERR) {
+		usage4 (_("Cannot catch SIGALRM"));
+	}
+	alarm (timeout_interval);
+
+	if (verbose > 2)
+		printf ("Initializing DBI\n");
+
+	if (dbi_initialize (NULL) < 0) {
+		printf ("UNKNOWN - failed to initialize DBI.\n");
+		return STATE_UNKNOWN;
+	}
+
+	if (verbose)
+		printf ("Opening DBI driver '%s'\n", np_dbi_driver);
+
+	driver = dbi_driver_open (np_dbi_driver);
+	if (! driver) {
+		printf ("UNKNOWN - failed to open DBI driver '%s'; possibly it's not installed.\n",
+				np_dbi_driver);
+
+		printf ("Known drivers:\n");
+		for (driver = dbi_driver_list (NULL); driver; driver = dbi_driver_list (driver)) {
+			printf (" - %s\n", dbi_driver_get_name (driver));
+		}
+		return STATE_UNKNOWN;
+	}
+
+	/* make a connection to the database */
+	gettimeofday (&start_timeval, NULL);
+
+	conn = dbi_conn_open (driver);
+	if (! conn) {
+		printf ("UNKNOWN - failed top open connection object.\n");
+		dbi_conn_close (conn);
+		return STATE_UNKNOWN;
+	}
+
+	for (i = 0; i < np_dbi_options_num; ++i) {
+		const char *opt;
+
+		if (verbose > 1)
+			printf ("Setting DBI driver option '%s' to '%s'\n",
+					np_dbi_options[i].key, np_dbi_options[i].value);
+
+		if (! dbi_conn_set_option (conn, np_dbi_options[i].key, np_dbi_options[i].value))
+			continue;
+		/* else: status != 0 */
+
+		np_dbi_print_error (conn, "UNKNOWN - failed to set option '%s' to '%s'",
+				np_dbi_options[i].key, np_dbi_options[i].value);
+		printf ("Known driver options:\n");
+
+		for (opt = dbi_conn_get_option_list (conn, NULL); opt;
+				opt = dbi_conn_get_option_list (conn, opt)) {
+			printf (" - %s\n", opt);
+		}
+		dbi_conn_close (conn);
+		return STATE_UNKNOWN;
+	}
+
+	if (host) {
+		if (verbose > 1)
+			printf ("Setting DBI driver option 'host' to '%s'\n", host);
+		dbi_conn_set_option (conn, "host", host);
+	}
+
+	if (verbose) {
+		const char *dbname, *host;
+
+		dbname = dbi_conn_get_option (conn, "dbname");
+		host = dbi_conn_get_option (conn, "host");
+
+		if (! dbname)
+			dbname = "<unspecified>";
+		if (! host)
+			host = "<unspecified>";
+
+		printf ("Connecting to database '%s' at host '%s'\n",
+				dbname, host);
+	}
+
+	if (dbi_conn_connect (conn) < 0) {
+		np_dbi_print_error (conn, "UNKOWN - failed to connect to database");
+		return STATE_UNKNOWN;
+	}
+
+	gettimeofday (&end_timeval, NULL);
+	while (start_timeval.tv_usec > end_timeval.tv_usec) {
+		--end_timeval.tv_sec;
+		end_timeval.tv_usec += 1000000;
+	}
+	elapsed_time = (double)(end_timeval.tv_sec - start_timeval.tv_sec)
+		+ (double)(end_timeval.tv_usec - start_timeval.tv_usec) / 1000000.0;
+
+	if (verbose)
+		printf("Time elapsed: %f\n", elapsed_time);
+
+	/* select a database */
+	if (np_dbi_database) {
+		if (verbose > 1)
+			printf ("Selecting database '%s'\n", np_dbi_database);
+
+		if (dbi_conn_select_db (conn, np_dbi_database)) {
+			np_dbi_print_error (conn, "UNKOWN - failed to select database '%s'",
+					np_dbi_database);
+			return STATE_UNKNOWN;
+		}
+	}
+
+	/* execute query */
+	status = do_query (conn, &query_val);
+	if (status != STATE_OK)
+		/* do_query prints an error message in this case */
+		return status;
+
+	status = get_status (query_val, query_thresholds);
+
+	if (verbose)
+		printf("Closing connection\n");
+	dbi_conn_close (conn);
+
+	printf ("%s - connection time: %fs, '%s' returned %f",
+			state_text (status), elapsed_time, np_dbi_query, query_val);
+	printf (" | conntime=%fs;;;0 query=%f;%s;%s;0\n", elapsed_time, query_val,
+			warning_range ? warning_range : "", critical_range ? critical_range : "");
+	return status;
+}
+
+/* process command-line arguments */
+int
+process_arguments (int argc, char **argv)
+{
+	int c;
+
+	int option = 0;
+	static struct option longopts[] = {
+		STD_LONG_OPTS,
+
+		{"driver", required_argument, 0, 'd'},
+		{"option", required_argument, 0, 'o'},
+		{"query", required_argument, 0, 'q'},
+		{"database", required_argument, 0, 'D'},
+		{0, 0, 0, 0}
+	};
+
+	while (1) {
+		c = getopt_long (argc, argv, "Vvht:c:w:H:d:o:q:D:",
+				longopts, &option);
+
+		if (c == EOF)
+			break;
+
+		switch (c) {
+		case '?':     /* usage */
+			usage5 ();
+		case 'h':     /* help */
+			print_help ();
+			exit (STATE_OK);
+		case 'V':     /* version */
+			print_revision (progname, NP_VERSION);
+			exit (STATE_OK);
+
+		case 'c':     /* critical range */
+			critical_range = optarg;
+			break;
+		case 'w':     /* warning range */
+			warning_range = optarg;
+			break;
+		case 't':     /* timeout */
+			if (!is_intnonneg (optarg))
+				usage2 (_("Timeout interval must be a positive integer"), optarg);
+			else
+				timeout_interval = atoi (optarg);
+
+		case 'H':     /* host */
+			if (!is_host (optarg))
+				usage2 (_("Invalid hostname/address"), optarg);
+			else
+				host = optarg;
+			break;
+		case 'v':
+			verbose++;
+			break;
+
+		case 'd':
+			np_dbi_driver = optarg;
+			break;
+		case 'o':
+			{
+				driver_option_t *new;
+
+				char *k, *v;
+
+				k = optarg;
+				v = strchr (k, (int)'=');
+
+				if (! v)
+					usage2 (_("Option must be '<key>=<value>'"), optarg);
+
+				*v = '\0';
+				++v;
+
+				new = realloc (np_dbi_options,
+						(np_dbi_options_num + 1) * sizeof (*new));
+				if (! new) {
+					printf ("UNKOWN - failed to reallocate memory\n");
+					exit (STATE_UNKNOWN);
+				}
+
+				np_dbi_options = new;
+				new = np_dbi_options + np_dbi_options_num;
+				++np_dbi_options_num;
+
+				new->key = k;
+				new->value = v;
+			}
+			break;
+		case 'q':
+			np_dbi_query = optarg;
+			break;
+		case 'D':
+			np_dbi_database = optarg;
+			break;
+		}
+	}
+
+	set_thresholds (&query_thresholds, warning_range, critical_range);
+
+	return validate_arguments ();
+}
+
+int
+validate_arguments ()
+{
+	if (! np_dbi_driver)
+		usage ("Must specify a DBI driver");
+
+	if (! np_dbi_query)
+		usage ("Must specify an SQL query to execute");
+
+	return OK;
+}
+
+void
+print_help (void)
+{
+	print_revision (progname, NP_VERSION);
+
+	printf (COPYRIGHT, copyright, email);
+
+	printf (_("This program checks a query result against threshold levels"));
+
+	printf ("\n\n");
+
+	print_usage ();
+
+	printf (UT_HELP_VRSN);
+	printf ("\n");
+
+	printf (" %s\n", "-d, --driver=STRING");
+	printf ("    %s\n", _("DBI driver to use"));
+	printf (" %s\n", "-o, --option=STRING");
+	printf ("    %s\n", _("DBI driver options"));
+	printf (" %s\n", "-q, --query=STRING");
+	printf ("    %s\n", _("SQL query to execute"));
+	printf ("\n");
+
+	printf (UT_WARN_CRIT_RANGE);
+
+	printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
+
+	printf (UT_VERBOSE);
+
+	printf ("\n");
+	printf (" %s\n", _("A DBI driver (-d option) and a query (-q option) are required."));
+	printf (" %s\n", _("This plugin connects to an SQL database using libdbi and executes the"));
+	printf (" %s\n", _("specified SQL query. The first column of the first row of the result"));
+	printf (" %s\n", _("will be used as the check result and, if specified, compared with the"));
+	printf (" %s\n", _("warning and critical ranges. The result from the query has to be numeric"));
+	printf (" %s\n\n", _("(strings representing numbers are fine)."));
+
+	printf (" %s\n", _("The number and type of required DBI driver options depends on the actual"));
+	printf (" %s\n", _("driver. See its documentation at http://libdbi-drivers.sourceforge.net/"));
+	printf (" %s\n", _("for details."));
+
+	printf (UT_SUPPORT);
+}
+
+void
+print_usage (void)
+{
+	printf ("%s\n", _("Usage:"));
+	printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] -q <SQL query>\n", progname);
+	printf (" [-H <host>] [-c <critical value>] [-w <warning value>]\n");
+}
+
+double
+get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
+{
+	double val = 0.0;
+
+	if (*field_type == DBI_TYPE_INTEGER) {
+		val = (double)dbi_result_get_longlong_idx (res, 1);
+	}
+	else if (*field_type == DBI_TYPE_DECIMAL) {
+		val = dbi_result_get_double_idx (res, 1);
+	}
+	else if (*field_type == DBI_TYPE_STRING) {
+		const char *val_str;
+		char *endptr = NULL;
+
+		val_str = dbi_result_get_string_idx (res, 1);
+		if ((! val_str) || (strcmp (val_str, "ERROR") == 0)) {
+			np_dbi_print_error (conn, "CRITICAL - failed to fetch string value");
+			*field_type = DBI_TYPE_ERROR;
+			return 0.0;
+		}
+
+		if (verbose > 2)
+			printf ("Query returned string '%s'\n", val_str);
+
+		val = strtod (val_str, &endptr);
+		if (endptr == val_str) {
+			printf ("CRITICAL - result value is not a numeric: %s\n", val_str);
+			*field_type = DBI_TYPE_ERROR;
+			return 0.0;
+		}
+		else if ((endptr != NULL) && (*endptr != '\0')) {
+			if (verbose)
+				printf ("Garbage after value: %s\n", endptr);
+		}
+	}
+	else {
+		printf ("CRITICAL - cannot parse value of type %s (%i)\n",
+				(*field_type == DBI_TYPE_BINARY)
+					? "BINARY"
+					: (*field_type == DBI_TYPE_DATETIME)
+						? "DATETIME"
+						: "<unknown>",
+				*field_type);
+		*field_type = DBI_TYPE_ERROR;
+		return 0.0;
+	}
+	return val;
+}
+
+int
+do_query (dbi_conn conn, double *res_val)
+{
+	dbi_result res;
+
+	unsigned short field_type;
+	double val = 0.0;
+
+	if (verbose)
+		printf ("Executing query '%s'\n", np_dbi_query);
+
+	res = dbi_conn_query (conn, np_dbi_query);
+	if (! res) {
+		np_dbi_print_error (conn, "CRITICAL - failed to execute query '%s'", np_dbi_query);
+		return STATE_CRITICAL;
+	}
+
+	if (dbi_result_get_numrows (res) == DBI_ROW_ERROR) {
+		np_dbi_print_error (conn, "CRITICAL - failed to fetch rows");
+		return STATE_CRITICAL;
+	}
+
+	if (dbi_result_get_numrows (res) < 1) {
+		printf ("WARNING - no rows returned\n");
+		return STATE_WARNING;
+	}
+
+	if (dbi_result_get_numfields (res) == DBI_FIELD_ERROR) {
+		np_dbi_print_error (conn, "CRITICAL - failed to fetch fields");
+		return STATE_CRITICAL;
+	}
+
+	if (dbi_result_get_numfields (res) < 1) {
+		printf ("WARNING - no fields returned\n");
+		return STATE_WARNING;
+	}
+
+	if (dbi_result_first_row (res) != 1) {
+		np_dbi_print_error (conn, "CRITICAL - failed to fetch first row");
+		return STATE_CRITICAL;
+	}
+
+	field_type = dbi_result_get_field_type_idx (res, 1);
+	if (field_type != DBI_TYPE_ERROR)
+		val = get_field (conn, res, &field_type);
+
+	if (field_type == DBI_TYPE_ERROR) {
+		np_dbi_print_error (conn, "CRITICAL - failed to fetch data");
+		return STATE_CRITICAL;
+	}
+
+	*res_val = val;
+
+	dbi_result_free (res);
+	return STATE_OK;
+}
+
+void
+np_dbi_print_error (dbi_conn conn, char *fmt, ...)
+{
+	const char *errmsg = NULL;
+	va_list ap;
+
+	va_start (ap, fmt);
+
+	dbi_conn_error (conn, &errmsg);
+	vprintf (fmt, ap);
+	printf (": %s\n", errmsg);
+
+	va_end (ap);
+}
+
-- 
cgit v1.2.3-74-g34f1


From 07768fa89561a4e55391634942e696a167bc6bbf Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Thu, 7 Apr 2011 17:37:10 +0200
Subject: check_dbi: Include extra opts in help output.

---
 plugins/check_dbi.c | 5 +++++
 1 file changed, 5 insertions(+)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index b52602c4..47b239a9 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -353,6 +353,11 @@ print_help (void)
 	print_usage ();
 
 	printf (UT_HELP_VRSN);
+/* include this conditionally to avoid 'zero-length printf format string'
+ * compiler warnings */
+#ifdef NP_EXTRA_OPTS
+	printf (UT_EXTRA_OPTS);
+#endif
 	printf ("\n");
 
 	printf (" %s\n", "-d, --driver=STRING");
-- 
cgit v1.2.3-74-g34f1


From e7dfcd4429b54bd98bac724665d7b5e6e30a7532 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Thu, 7 Apr 2011 17:59:24 +0200
Subject: check_dbi: Added threshold ranges for the connection time.

The -W and -C options are used for that. The plugin return value is determined
by the worst check result.
---
 plugins/check_dbi.c | 52 +++++++++++++++++++++++++++++++++++++++++++++-------
 1 file changed, 45 insertions(+), 7 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 47b239a9..d4ac4e32 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -54,6 +54,10 @@ char *warning_range = NULL;
 char *critical_range = NULL;
 thresholds *query_thresholds = NULL;
 
+char *conntime_warning_range = NULL;
+char *conntime_critical_range = NULL;
+thresholds *conntime_thresholds = NULL;
+
 char *np_dbi_driver = NULL;
 driver_option_t *np_dbi_options = NULL;
 int np_dbi_options_num = 0;
@@ -72,8 +76,11 @@ int do_query (dbi_conn, double *);
 int
 main (int argc, char **argv)
 {
+	int conntime_status = STATE_UNKNOWN;
 	int status = STATE_UNKNOWN;
 
+	int exit_status = STATE_UNKNOWN;
+
 	dbi_driver driver;
 	dbi_conn conn;
 
@@ -193,6 +200,8 @@ main (int argc, char **argv)
 	if (verbose)
 		printf("Time elapsed: %f\n", elapsed_time);
 
+	conntime_status = get_status (elapsed_time, conntime_thresholds);
+
 	/* select a database */
 	if (np_dbi_database) {
 		if (verbose > 1)
@@ -217,11 +226,23 @@ main (int argc, char **argv)
 		printf("Closing connection\n");
 	dbi_conn_close (conn);
 
-	printf ("%s - connection time: %fs, '%s' returned %f",
-			state_text (status), elapsed_time, np_dbi_query, query_val);
-	printf (" | conntime=%fs;;;0 query=%f;%s;%s;0\n", elapsed_time, query_val,
-			warning_range ? warning_range : "", critical_range ? critical_range : "");
-	return status;
+	/* 'conntime_status' is worse than 'status' (but not UNKOWN) */
+	if (((conntime_status < STATE_UNKNOWN) && (conntime_status > status))
+			/* 'status' is UNKNOWN and 'conntime_status' is not OK */
+			|| ((status >= STATE_UNKNOWN) && (conntime_status != STATE_OK)))
+		exit_status = conntime_status;
+	else
+		exit_status = status;
+
+	printf ("%s - %s: connection time: %fs, %s: '%s' returned %f",
+			state_text (exit_status),
+			state_text (conntime_status), elapsed_time,
+			state_text (status), np_dbi_query, query_val);
+	printf (" | conntime=%fs;%s;%s;0 query=%f;%s;%s;0\n", elapsed_time,
+			conntime_warning_range ? conntime_warning_range : "",
+			conntime_critical_range ? conntime_critical_range : "",
+			query_val, warning_range ? warning_range : "", critical_range ? critical_range : "");
+	return exit_status;
 }
 
 /* process command-line arguments */
@@ -234,6 +255,9 @@ process_arguments (int argc, char **argv)
 	static struct option longopts[] = {
 		STD_LONG_OPTS,
 
+		{"conntime-warning", required_argument, 0, 'W'},
+		{"conntime-critical", required_argument, 0, 'C'},
+
 		{"driver", required_argument, 0, 'd'},
 		{"option", required_argument, 0, 'o'},
 		{"query", required_argument, 0, 'q'},
@@ -242,7 +266,7 @@ process_arguments (int argc, char **argv)
 	};
 
 	while (1) {
-		c = getopt_long (argc, argv, "Vvht:c:w:H:d:o:q:D:",
+		c = getopt_long (argc, argv, "Vvht:c:w:H:W:C:d:o:q:D:",
 				longopts, &option);
 
 		if (c == EOF)
@@ -270,6 +294,13 @@ process_arguments (int argc, char **argv)
 			else
 				timeout_interval = atoi (optarg);
 
+		case 'C':     /* critical conntime range */
+			conntime_critical_range = optarg;
+			break;
+		case 'W':     /* warning conntime range */
+			conntime_warning_range = optarg;
+			break;
+
 		case 'H':     /* host */
 			if (!is_host (optarg))
 				usage2 (_("Invalid hostname/address"), optarg);
@@ -323,6 +354,7 @@ process_arguments (int argc, char **argv)
 	}
 
 	set_thresholds (&query_thresholds, warning_range, critical_range);
+	set_thresholds (&conntime_thresholds, conntime_warning_range, conntime_critical_range);
 
 	return validate_arguments ();
 }
@@ -369,6 +401,11 @@ print_help (void)
 	printf ("\n");
 
 	printf (UT_WARN_CRIT_RANGE);
+	printf (" %s\n", "-W, --conntime-warning=RANGE");
+	printf ("    %s\n", _("Connection time warning range"));
+	printf (" %s\n", "-C, --conntime-critical=RANGE");
+	printf ("    %s\n", _("Connection time critical range"));
+	printf ("\n");
 
 	printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
 
@@ -394,7 +431,8 @@ print_usage (void)
 {
 	printf ("%s\n", _("Usage:"));
 	printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] -q <SQL query>\n", progname);
-	printf (" [-H <host>] [-c <critical value>] [-w <warning value>]\n");
+	printf (" [-H <host>] [-c <critical range>] [-w <warning range>]\n");
+	printf (" [-C <critical conntime range>] [-W <warning conntime range>]\n");
 }
 
 double
-- 
cgit v1.2.3-74-g34f1


From 0e02edec196a8f3cf866279c5609138db14ec931 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Thu, 7 Apr 2011 18:09:33 +0200
Subject: check_dbi: Check and report the time used by the query.

---
 plugins/check_dbi.c | 54 ++++++++++++++++++++++++++++++++++++-----------------
 1 file changed, 37 insertions(+), 17 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index d4ac4e32..f59d7c41 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -69,9 +69,11 @@ int validate_arguments (void);
 void print_usage (void);
 void print_help (void);
 
+double timediff (struct timeval, struct timeval);
+
 void np_dbi_print_error (dbi_conn, char *, ...);
 
-int do_query (dbi_conn, double *);
+int do_query (dbi_conn, double *, double *);
 
 int
 main (int argc, char **argv)
@@ -85,7 +87,8 @@ main (int argc, char **argv)
 	dbi_conn conn;
 
 	struct timeval start_timeval, end_timeval;
-	double elapsed_time;
+	double conn_time = 0.0;
+	double query_time = 0.0;
 
 	double query_val = 0.0;
 
@@ -190,17 +193,12 @@ main (int argc, char **argv)
 	}
 
 	gettimeofday (&end_timeval, NULL);
-	while (start_timeval.tv_usec > end_timeval.tv_usec) {
-		--end_timeval.tv_sec;
-		end_timeval.tv_usec += 1000000;
-	}
-	elapsed_time = (double)(end_timeval.tv_sec - start_timeval.tv_sec)
-		+ (double)(end_timeval.tv_usec - start_timeval.tv_usec) / 1000000.0;
+	conn_time = timediff (start_timeval, end_timeval);
 
 	if (verbose)
-		printf("Time elapsed: %f\n", elapsed_time);
+		printf("Time elapsed: %f\n", conn_time);
 
-	conntime_status = get_status (elapsed_time, conntime_thresholds);
+	conntime_status = get_status (conn_time, conntime_thresholds);
 
 	/* select a database */
 	if (np_dbi_database) {
@@ -215,7 +213,7 @@ main (int argc, char **argv)
 	}
 
 	/* execute query */
-	status = do_query (conn, &query_val);
+	status = do_query (conn, &query_val, &query_time);
 	if (status != STATE_OK)
 		/* do_query prints an error message in this case */
 		return status;
@@ -234,14 +232,15 @@ main (int argc, char **argv)
 	else
 		exit_status = status;
 
-	printf ("%s - %s: connection time: %fs, %s: '%s' returned %f",
+	printf ("%s - %s: connection time: %fs, %s: '%s' returned %f in %fs",
 			state_text (exit_status),
-			state_text (conntime_status), elapsed_time,
-			state_text (status), np_dbi_query, query_val);
-	printf (" | conntime=%fs;%s;%s;0 query=%f;%s;%s;0\n", elapsed_time,
+			state_text (conntime_status), conn_time,
+			state_text (status), np_dbi_query, query_val, query_time);
+	printf (" | conntime=%fs;%s;%s;0 query=%f;%s;%s;0 querytime=%fs;;;0\n", conn_time,
 			conntime_warning_range ? conntime_warning_range : "",
 			conntime_critical_range ? conntime_critical_range : "",
-			query_val, warning_range ? warning_range : "", critical_range ? critical_range : "");
+			query_val, warning_range ? warning_range : "", critical_range ? critical_range : "",
+			query_time);
 	return exit_status;
 }
 
@@ -486,16 +485,20 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 }
 
 int
-do_query (dbi_conn conn, double *res_val)
+do_query (dbi_conn conn, double *res_val, double *res_time)
 {
 	dbi_result res;
 
 	unsigned short field_type;
 	double val = 0.0;
 
+	struct timeval timeval_start, timeval_end;
+
 	if (verbose)
 		printf ("Executing query '%s'\n", np_dbi_query);
 
+	gettimeofday (&timeval_start, NULL);
+
 	res = dbi_conn_query (conn, np_dbi_query);
 	if (! res) {
 		np_dbi_print_error (conn, "CRITICAL - failed to execute query '%s'", np_dbi_query);
@@ -531,6 +534,9 @@ do_query (dbi_conn conn, double *res_val)
 	if (field_type != DBI_TYPE_ERROR)
 		val = get_field (conn, res, &field_type);
 
+	gettimeofday (&timeval_end, NULL);
+	*res_time = timediff (timeval_start, timeval_end);
+
 	if (field_type == DBI_TYPE_ERROR) {
 		np_dbi_print_error (conn, "CRITICAL - failed to fetch data");
 		return STATE_CRITICAL;
@@ -542,6 +548,20 @@ do_query (dbi_conn conn, double *res_val)
 	return STATE_OK;
 }
 
+double
+timediff (struct timeval start, struct timeval end)
+{
+	double diff;
+
+	while (start.tv_usec > end.tv_usec) {
+		--end.tv_sec;
+		end.tv_usec += 1000000;
+	}
+	diff = (double)(end.tv_sec - start.tv_sec)
+		+ (double)(end.tv_usec - start.tv_usec) / 1000000.0;
+	return diff;
+}
+
 void
 np_dbi_print_error (dbi_conn conn, char *fmt, ...)
 {
-- 
cgit v1.2.3-74-g34f1


From 61506bd8d7a095f5de571d21e37972a159372bdf Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Fri, 8 Apr 2011 10:58:00 +0200
Subject: check_dbi: Fixed/improved perfdata output.

 - do not use zero as 'min' value for the query result perfdata

 - added all semicolons
---
 plugins/check_dbi.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index f59d7c41..611e0e78 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -236,7 +236,7 @@ main (int argc, char **argv)
 			state_text (exit_status),
 			state_text (conntime_status), conn_time,
 			state_text (status), np_dbi_query, query_val, query_time);
-	printf (" | conntime=%fs;%s;%s;0 query=%f;%s;%s;0 querytime=%fs;;;0\n", conn_time,
+	printf (" | conntime=%fs;%s;%s;0; query=%f;%s;%s;; querytime=%fs;;;0;\n", conn_time,
 			conntime_warning_range ? conntime_warning_range : "",
 			conntime_critical_range ? conntime_critical_range : "",
 			query_val, warning_range ? warning_range : "", critical_range ? critical_range : "",
-- 
cgit v1.2.3-74-g34f1


From d7a1f2a7a08aeaaf5efd0d454b2355554a413f2b Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Fri, 15 Apr 2011 13:35:09 +0200
Subject: check_dbi: Replaced -W/-C with -m.

The -m option may be used to specify a metric to check the thresholds against.
This is more flexible than --conntime-warning/--conntime-critical (-W/-C) as
it may be extended to support further metrics without introducing tons of
arguments. Also, it does not make much sense to check the conntime and query
result at the same time.

Currently, the metrics CONN_TIME and QUERY_RESULT are available.

A query is no longer required unless QUERY_RESULT (the default) is used.
---
 plugins/check_dbi.c | 131 ++++++++++++++++++++++++++++------------------------
 1 file changed, 70 insertions(+), 61 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 611e0e78..f0099440 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -10,7 +10,7 @@
 * 
 * This file contains the check_dbi plugin
 * 
-* Runs an arbitrary SQL command and checks the result.
+* Runs an arbitrary (SQL) command and checks the result.
 * 
 * 
 * This program is free software: you can redistribute it and/or modify
@@ -38,10 +38,17 @@ const char *email = "nagiosplug-devel@lists.sourceforge.net";
 
 #include "netutils.h"
 
+#include <assert.h>
+
 #include <dbi/dbi.h>
 
 #include <stdarg.h>
 
+typedef enum {
+	METRIC_CONN_TIME,
+	METRIC_QUERY_RESULT,
+} np_dbi_metric_t;
+
 typedef struct {
 	char *key;
 	char *value;
@@ -52,11 +59,9 @@ int verbose = 0;
 
 char *warning_range = NULL;
 char *critical_range = NULL;
-thresholds *query_thresholds = NULL;
+thresholds *dbi_thresholds = NULL;
 
-char *conntime_warning_range = NULL;
-char *conntime_critical_range = NULL;
-thresholds *conntime_thresholds = NULL;
+np_dbi_metric_t metric = METRIC_QUERY_RESULT;
 
 char *np_dbi_driver = NULL;
 driver_option_t *np_dbi_options = NULL;
@@ -78,11 +83,8 @@ int do_query (dbi_conn, double *, double *);
 int
 main (int argc, char **argv)
 {
-	int conntime_status = STATE_UNKNOWN;
 	int status = STATE_UNKNOWN;
 
-	int exit_status = STATE_UNKNOWN;
-
 	dbi_driver driver;
 	dbi_conn conn;
 
@@ -198,7 +200,8 @@ main (int argc, char **argv)
 	if (verbose)
 		printf("Time elapsed: %f\n", conn_time);
 
-	conntime_status = get_status (conn_time, conntime_thresholds);
+	if (metric == METRIC_CONN_TIME)
+		status = get_status (conn_time, dbi_thresholds);
 
 	/* select a database */
 	if (np_dbi_database) {
@@ -212,36 +215,35 @@ main (int argc, char **argv)
 		}
 	}
 
-	/* execute query */
-	status = do_query (conn, &query_val, &query_time);
-	if (status != STATE_OK)
-		/* do_query prints an error message in this case */
-		return status;
+	if (np_dbi_query) {
+		/* execute query */
+		status = do_query (conn, &query_val, &query_time);
+		if (status != STATE_OK)
+			/* do_query prints an error message in this case */
+			return status;
 
-	status = get_status (query_val, query_thresholds);
+		if (metric == METRIC_QUERY_RESULT)
+			status = get_status (query_val, dbi_thresholds);
+	}
 
 	if (verbose)
 		printf("Closing connection\n");
 	dbi_conn_close (conn);
 
-	/* 'conntime_status' is worse than 'status' (but not UNKOWN) */
-	if (((conntime_status < STATE_UNKNOWN) && (conntime_status > status))
-			/* 'status' is UNKNOWN and 'conntime_status' is not OK */
-			|| ((status >= STATE_UNKNOWN) && (conntime_status != STATE_OK)))
-		exit_status = conntime_status;
-	else
-		exit_status = status;
-
-	printf ("%s - %s: connection time: %fs, %s: '%s' returned %f in %fs",
-			state_text (exit_status),
-			state_text (conntime_status), conn_time,
-			state_text (status), np_dbi_query, query_val, query_time);
-	printf (" | conntime=%fs;%s;%s;0; query=%f;%s;%s;; querytime=%fs;;;0;\n", conn_time,
-			conntime_warning_range ? conntime_warning_range : "",
-			conntime_critical_range ? conntime_critical_range : "",
-			query_val, warning_range ? warning_range : "", critical_range ? critical_range : "",
-			query_time);
-	return exit_status;
+	printf ("%s - connection time: %fs", state_text (status), conn_time);
+	if (np_dbi_query)
+		printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
+
+	printf (" | conntime=%fs;%s;%s;0;", conn_time,
+			((metric == METRIC_CONN_TIME) && warning_range) ? warning_range : "",
+			((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "");
+	if (np_dbi_query)
+		printf (" query=%f;%s;%s;; querytime=%fs;;;0;", query_val,
+				((metric == METRIC_QUERY_RESULT) && warning_range) ? warning_range : "",
+				((metric == METRIC_QUERY_RESULT) && critical_range) ? critical_range : "",
+				query_time);
+	printf ("\n");
+	return status;
 }
 
 /* process command-line arguments */
@@ -254,9 +256,7 @@ process_arguments (int argc, char **argv)
 	static struct option longopts[] = {
 		STD_LONG_OPTS,
 
-		{"conntime-warning", required_argument, 0, 'W'},
-		{"conntime-critical", required_argument, 0, 'C'},
-
+		{"metric", required_argument, 0, 'm'},
 		{"driver", required_argument, 0, 'd'},
 		{"option", required_argument, 0, 'o'},
 		{"query", required_argument, 0, 'q'},
@@ -265,7 +265,7 @@ process_arguments (int argc, char **argv)
 	};
 
 	while (1) {
-		c = getopt_long (argc, argv, "Vvht:c:w:H:W:C:d:o:q:D:",
+		c = getopt_long (argc, argv, "Vvht:c:w:m:H:d:o:q:D:",
 				longopts, &option);
 
 		if (c == EOF)
@@ -287,19 +287,20 @@ process_arguments (int argc, char **argv)
 		case 'w':     /* warning range */
 			warning_range = optarg;
 			break;
+		case 'm':
+			if (! strcasecmp (optarg, "CONN_TIME"))
+				metric = METRIC_CONN_TIME;
+			else if (! strcasecmp (optarg, "QUERY_RESULT"))
+				metric = METRIC_QUERY_RESULT;
+			else
+				usage2 (_("Invalid metric"), optarg);
+			break;
 		case 't':     /* timeout */
 			if (!is_intnonneg (optarg))
 				usage2 (_("Timeout interval must be a positive integer"), optarg);
 			else
 				timeout_interval = atoi (optarg);
 
-		case 'C':     /* critical conntime range */
-			conntime_critical_range = optarg;
-			break;
-		case 'W':     /* warning conntime range */
-			conntime_warning_range = optarg;
-			break;
-
 		case 'H':     /* host */
 			if (!is_host (optarg))
 				usage2 (_("Invalid hostname/address"), optarg);
@@ -352,8 +353,7 @@ process_arguments (int argc, char **argv)
 		}
 	}
 
-	set_thresholds (&query_thresholds, warning_range, critical_range);
-	set_thresholds (&conntime_thresholds, conntime_warning_range, conntime_critical_range);
+	set_thresholds (&dbi_thresholds, warning_range, critical_range);
 
 	return validate_arguments ();
 }
@@ -364,8 +364,12 @@ validate_arguments ()
 	if (! np_dbi_driver)
 		usage ("Must specify a DBI driver");
 
-	if (! np_dbi_query)
-		usage ("Must specify an SQL query to execute");
+	if ((metric == METRIC_QUERY_RESULT) && (! np_dbi_query))
+		usage ("Must specify a query to execute (metric == QUERY_RESULT)");
+
+	if ((metric != METRIC_CONN_TIME)
+			&& (metric != METRIC_QUERY_RESULT))
+		usage ("Invalid metric specified");
 
 	return OK;
 }
@@ -377,7 +381,9 @@ print_help (void)
 
 	printf (COPYRIGHT, copyright, email);
 
-	printf (_("This program checks a query result against threshold levels"));
+	printf (_("This program connects to an (SQL) database using DBI and checks the\n"
+			"specified metric against threshold levels. The default metric is\n"
+			"the result of the specified query.\n"));
 
 	printf ("\n\n");
 
@@ -396,14 +402,14 @@ print_help (void)
 	printf (" %s\n", "-o, --option=STRING");
 	printf ("    %s\n", _("DBI driver options"));
 	printf (" %s\n", "-q, --query=STRING");
-	printf ("    %s\n", _("SQL query to execute"));
+	printf ("    %s\n", _("query to execute"));
 	printf ("\n");
 
 	printf (UT_WARN_CRIT_RANGE);
-	printf (" %s\n", "-W, --conntime-warning=RANGE");
-	printf ("    %s\n", _("Connection time warning range"));
-	printf (" %s\n", "-C, --conntime-critical=RANGE");
-	printf ("    %s\n", _("Connection time critical range"));
+	printf (" %s\n", "-m, --metric=METRIC");
+	printf ("    %s\n", _("Metric to check thresholds against. Available metrics:"));
+	printf ("    CONN_TIME    - %s\n", _("time used for setting up the database connection"));
+	printf ("    QUERY_RESULT - %s\n", _("result (first column of first row) of the query"));
 	printf ("\n");
 
 	printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
@@ -411,10 +417,12 @@ print_help (void)
 	printf (UT_VERBOSE);
 
 	printf ("\n");
-	printf (" %s\n", _("A DBI driver (-d option) and a query (-q option) are required."));
-	printf (" %s\n", _("This plugin connects to an SQL database using libdbi and executes the"));
-	printf (" %s\n", _("specified SQL query. The first column of the first row of the result"));
-	printf (" %s\n", _("will be used as the check result and, if specified, compared with the"));
+	printf (" %s\n", _("A DBI driver (-d option) is required. If the specified metric operates"));
+	printf (" %s\n\n", _("on a query, one has to be specified (-q option)."));
+
+	printf (" %s\n", _("This plugin connects to an (SQL) database using libdbi and, optionally,"));
+	printf (" %s\n", _("executes the specified query. The first column of the first row of the"));
+	printf (" %s\n", _("result will be parsed and, in QUERY_RESULT mode, compared with the"));
 	printf (" %s\n", _("warning and critical ranges. The result from the query has to be numeric"));
 	printf (" %s\n\n", _("(strings representing numbers are fine)."));
 
@@ -429,9 +437,8 @@ void
 print_usage (void)
 {
 	printf ("%s\n", _("Usage:"));
-	printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] -q <SQL query>\n", progname);
-	printf (" [-H <host>] [-c <critical range>] [-w <warning range>]\n");
-	printf (" [-C <critical conntime range>] [-W <warning conntime range>]\n");
+	printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] [-q <query>]\n", progname);
+	printf (" [-H <host>] [-c <critical range>] [-w <warning range>] [-m <metric>]\n");
 }
 
 double
@@ -494,6 +501,8 @@ do_query (dbi_conn conn, double *res_val, double *res_time)
 
 	struct timeval timeval_start, timeval_end;
 
+	assert (np_dbi_query);
+
 	if (verbose)
 		printf ("Executing query '%s'\n", np_dbi_query);
 
-- 
cgit v1.2.3-74-g34f1


From 4ad6e441b68cca4b2f283af3f783794279fbb11c Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Fri, 15 Apr 2011 14:37:21 +0200
Subject: check_dbi: Added QUERY_TIME metric.

This metric checks the execution time of the specified query. In case the
query does not return any (parsable) data, this is not treated as an error
when using this metric.
---
 plugins/check_dbi.c | 127 +++++++++++++++++++++++++++++++++++++---------------
 1 file changed, 90 insertions(+), 37 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index f0099440..4a0a4d69 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -38,7 +38,13 @@ const char *email = "nagiosplug-devel@lists.sourceforge.net";
 
 #include "netutils.h"
 
+/* required for NAN */
+#ifndef _ISOC99_SOURCE
+#define _ISOC99_SOURCE
+#endif
+
 #include <assert.h>
+#include <math.h>
 
 #include <dbi/dbi.h>
 
@@ -47,6 +53,7 @@ const char *email = "nagiosplug-devel@lists.sourceforge.net";
 typedef enum {
 	METRIC_CONN_TIME,
 	METRIC_QUERY_RESULT,
+	METRIC_QUERY_TIME,
 } np_dbi_metric_t;
 
 typedef struct {
@@ -224,24 +231,38 @@ main (int argc, char **argv)
 
 		if (metric == METRIC_QUERY_RESULT)
 			status = get_status (query_val, dbi_thresholds);
+		else if (metric == METRIC_QUERY_TIME)
+			status = get_status (query_time, dbi_thresholds);
 	}
 
 	if (verbose)
 		printf("Closing connection\n");
 	dbi_conn_close (conn);
 
+	/* In case of METRIC_QUERY_RESULT, isnan(query_val) indicates an error
+	 * which should have been reported and handled (abort) before */
+	assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val)));
+
 	printf ("%s - connection time: %fs", state_text (status), conn_time);
-	if (np_dbi_query)
-		printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
+	if (np_dbi_query) {
+		if (isnan (query_val))
+			printf (", '%s' query execution time: %fs", np_dbi_query, query_time);
+		else
+			printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
+	}
 
 	printf (" | conntime=%fs;%s;%s;0;", conn_time,
 			((metric == METRIC_CONN_TIME) && warning_range) ? warning_range : "",
 			((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "");
-	if (np_dbi_query)
-		printf (" query=%f;%s;%s;; querytime=%fs;;;0;", query_val,
-				((metric == METRIC_QUERY_RESULT) && warning_range) ? warning_range : "",
-				((metric == METRIC_QUERY_RESULT) && critical_range) ? critical_range : "",
-				query_time);
+	if (np_dbi_query) {
+		if (! isnan (query_val))
+			printf (" query=%f;%s;%s;;", query_val,
+					((metric == METRIC_QUERY_RESULT) && warning_range) ? warning_range : "",
+					((metric == METRIC_QUERY_RESULT) && critical_range) ? critical_range : "");
+		printf (" querytime=%fs;%s;%s;0;", query_time,
+				((metric == METRIC_QUERY_TIME) && warning_range) ? warning_range : "",
+				((metric == METRIC_QUERY_TIME) && critical_range) ? critical_range : "");
+	}
 	printf ("\n");
 	return status;
 }
@@ -292,6 +313,8 @@ process_arguments (int argc, char **argv)
 				metric = METRIC_CONN_TIME;
 			else if (! strcasecmp (optarg, "QUERY_RESULT"))
 				metric = METRIC_QUERY_RESULT;
+			else if (! strcasecmp (optarg, "QUERY_TIME"))
+				metric = METRIC_QUERY_TIME;
 			else
 				usage2 (_("Invalid metric"), optarg);
 			break;
@@ -364,11 +387,13 @@ validate_arguments ()
 	if (! np_dbi_driver)
 		usage ("Must specify a DBI driver");
 
-	if ((metric == METRIC_QUERY_RESULT) && (! np_dbi_query))
+	if (((metric == METRIC_QUERY_RESULT) || (metric == METRIC_QUERY_TIME))
+			&& (! np_dbi_query))
 		usage ("Must specify a query to execute (metric == QUERY_RESULT)");
 
 	if ((metric != METRIC_CONN_TIME)
-			&& (metric != METRIC_QUERY_RESULT))
+			&& (metric != METRIC_QUERY_RESULT)
+			&& (metric != METRIC_QUERY_TIME))
 		usage ("Invalid metric specified");
 
 	return OK;
@@ -410,6 +435,8 @@ print_help (void)
 	printf ("    %s\n", _("Metric to check thresholds against. Available metrics:"));
 	printf ("    CONN_TIME    - %s\n", _("time used for setting up the database connection"));
 	printf ("    QUERY_RESULT - %s\n", _("result (first column of first row) of the query"));
+	printf ("    QUERY_TIME   - %s\n", _("time used to execute the query"));
+	printf ("                   %s\n", _("(ignore the query result)"));
 	printf ("\n");
 
 	printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
@@ -441,10 +468,16 @@ print_usage (void)
 	printf (" [-H <host>] [-c <critical range>] [-w <warning range>] [-m <metric>]\n");
 }
 
+#define CHECK_IGNORE_ERROR(s) \
+	do { \
+		if (metric != METRIC_QUERY_RESULT) \
+			return (s); \
+	} while (0)
+
 double
 get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 {
-	double val = 0.0;
+	double val = NAN;
 
 	if (*field_type == DBI_TYPE_INTEGER) {
 		val = (double)dbi_result_get_longlong_idx (res, 1);
@@ -458,9 +491,10 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 
 		val_str = dbi_result_get_string_idx (res, 1);
 		if ((! val_str) || (strcmp (val_str, "ERROR") == 0)) {
+			CHECK_IGNORE_ERROR (NAN);
 			np_dbi_print_error (conn, "CRITICAL - failed to fetch string value");
 			*field_type = DBI_TYPE_ERROR;
-			return 0.0;
+			return NAN;
 		}
 
 		if (verbose > 2)
@@ -468,9 +502,10 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 
 		val = strtod (val_str, &endptr);
 		if (endptr == val_str) {
+			CHECK_IGNORE_ERROR (NAN);
 			printf ("CRITICAL - result value is not a numeric: %s\n", val_str);
 			*field_type = DBI_TYPE_ERROR;
-			return 0.0;
+			return NAN;
 		}
 		else if ((endptr != NULL) && (*endptr != '\0')) {
 			if (verbose)
@@ -478,6 +513,7 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 		}
 	}
 	else {
+		CHECK_IGNORE_ERROR (NAN);
 		printf ("CRITICAL - cannot parse value of type %s (%i)\n",
 				(*field_type == DBI_TYPE_BINARY)
 					? "BINARY"
@@ -486,55 +522,43 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 						: "<unknown>",
 				*field_type);
 		*field_type = DBI_TYPE_ERROR;
-		return 0.0;
+		return NAN;
 	}
 	return val;
 }
 
-int
-do_query (dbi_conn conn, double *res_val, double *res_time)
+double
+get_query_result (dbi_conn conn, dbi_result res, double *res_val)
 {
-	dbi_result res;
-
 	unsigned short field_type;
-	double val = 0.0;
-
-	struct timeval timeval_start, timeval_end;
-
-	assert (np_dbi_query);
-
-	if (verbose)
-		printf ("Executing query '%s'\n", np_dbi_query);
-
-	gettimeofday (&timeval_start, NULL);
-
-	res = dbi_conn_query (conn, np_dbi_query);
-	if (! res) {
-		np_dbi_print_error (conn, "CRITICAL - failed to execute query '%s'", np_dbi_query);
-		return STATE_CRITICAL;
-	}
+	double val = NAN;
 
 	if (dbi_result_get_numrows (res) == DBI_ROW_ERROR) {
+		CHECK_IGNORE_ERROR (STATE_OK);
 		np_dbi_print_error (conn, "CRITICAL - failed to fetch rows");
 		return STATE_CRITICAL;
 	}
 
 	if (dbi_result_get_numrows (res) < 1) {
+		CHECK_IGNORE_ERROR (STATE_OK);
 		printf ("WARNING - no rows returned\n");
 		return STATE_WARNING;
 	}
 
 	if (dbi_result_get_numfields (res) == DBI_FIELD_ERROR) {
+		CHECK_IGNORE_ERROR (STATE_OK);
 		np_dbi_print_error (conn, "CRITICAL - failed to fetch fields");
 		return STATE_CRITICAL;
 	}
 
 	if (dbi_result_get_numfields (res) < 1) {
+		CHECK_IGNORE_ERROR (STATE_OK);
 		printf ("WARNING - no fields returned\n");
 		return STATE_WARNING;
 	}
 
 	if (dbi_result_first_row (res) != 1) {
+		CHECK_IGNORE_ERROR (STATE_OK);
 		np_dbi_print_error (conn, "CRITICAL - failed to fetch first row");
 		return STATE_CRITICAL;
 	}
@@ -543,20 +567,49 @@ do_query (dbi_conn conn, double *res_val, double *res_time)
 	if (field_type != DBI_TYPE_ERROR)
 		val = get_field (conn, res, &field_type);
 
-	gettimeofday (&timeval_end, NULL);
-	*res_time = timediff (timeval_start, timeval_end);
+	*res_val = val;
 
 	if (field_type == DBI_TYPE_ERROR) {
+		CHECK_IGNORE_ERROR (STATE_OK);
 		np_dbi_print_error (conn, "CRITICAL - failed to fetch data");
 		return STATE_CRITICAL;
 	}
 
-	*res_val = val;
-
 	dbi_result_free (res);
 	return STATE_OK;
 }
 
+#undef CHECK_IGNORE_ERROR
+
+int
+do_query (dbi_conn conn, double *res_val, double *res_time)
+{
+	dbi_result res;
+
+	struct timeval timeval_start, timeval_end;
+	int status = STATE_OK;
+
+	assert (np_dbi_query);
+
+	if (verbose)
+		printf ("Executing query '%s'\n", np_dbi_query);
+
+	gettimeofday (&timeval_start, NULL);
+
+	res = dbi_conn_query (conn, np_dbi_query);
+	if (! res) {
+		np_dbi_print_error (conn, "CRITICAL - failed to execute query '%s'", np_dbi_query);
+		return STATE_CRITICAL;
+	}
+
+	status = get_query_result (conn, res, res_val);
+
+	gettimeofday (&timeval_end, NULL);
+	*res_time = timediff (timeval_start, timeval_end);
+
+	return status;
+}
+
 double
 timediff (struct timeval start, struct timeval end)
 {
-- 
cgit v1.2.3-74-g34f1


From eda2fe7f40810a03e1c9c32701916387fdf86165 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Fri, 15 Apr 2011 15:02:48 +0200
Subject: check_dbi: Added some examples to the help output.

---
 plugins/check_dbi.c | 17 ++++++++++++++++-
 1 file changed, 16 insertions(+), 1 deletion(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 4a0a4d69..eefcf02a 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -455,7 +455,22 @@ print_help (void)
 
 	printf (" %s\n", _("The number and type of required DBI driver options depends on the actual"));
 	printf (" %s\n", _("driver. See its documentation at http://libdbi-drivers.sourceforge.net/"));
-	printf (" %s\n", _("for details."));
+	printf (" %s\n\n", _("for details."));
+
+	printf (" %s\n", _("Examples:"));
+	printf ("  check_dbi -d pgsql -o username=postgres -m QUERY_RESULT \\\n");
+	printf ("    -q 'SELECT COUNT(*) FROM pg_stat_activity' -w 5 -c 10\n");
+	printf ("  Warning if more than five connections; critical if more than ten.\n\n");
+
+	printf ("  check_dbi -d mysql -H localhost -o username=user -o password=secret \\\n");
+	printf ("    -q 'SELECT COUNT(*) FROM logged_in_users -w 5:20 -c 0:50\n");
+	printf ("  Warning if less than 5 or more than 20 users are logged in; critical\n");
+	printf ("  if more than 50 users.\n\n");
+
+	printf ("  check_dbi -d firebird -o username=user -o password=secret -o dbname=foo \\\n");
+	printf ("    -m CONN_TIME -w 0.5 -c 2\n");
+	printf ("  Warning if connecting to the database takes more than half of a second;\n");
+	printf ("  critical if it takes more than 2 seconds.\n");
 
 	printf (UT_SUPPORT);
 }
-- 
cgit v1.2.3-74-g34f1


From aebde433fb2ad27f8ded9dd41224b6826ac787d2 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Fri, 15 Apr 2011 16:19:16 +0200
Subject: check_dbi: Added -e option.

This option may be used to specify a string that is expected as the query
return value. The string is compared to the query result using strcmp().

The option may not be mixed with -w/-c.
---
 plugins/check_dbi.c | 95 ++++++++++++++++++++++++++++++++++++++++++-----------
 1 file changed, 76 insertions(+), 19 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index eefcf02a..94c6b15f 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -68,6 +68,8 @@ char *warning_range = NULL;
 char *critical_range = NULL;
 thresholds *dbi_thresholds = NULL;
 
+char *expect = NULL;
+
 np_dbi_metric_t metric = METRIC_QUERY_RESULT;
 
 char *np_dbi_driver = NULL;
@@ -85,7 +87,7 @@ double timediff (struct timeval, struct timeval);
 
 void np_dbi_print_error (dbi_conn, char *, ...);
 
-int do_query (dbi_conn, double *, double *);
+int do_query (dbi_conn, const char **, double *, double *);
 
 int
 main (int argc, char **argv)
@@ -99,6 +101,7 @@ main (int argc, char **argv)
 	double conn_time = 0.0;
 	double query_time = 0.0;
 
+	const char *query_val_str = NULL;
 	double query_val = 0.0;
 
 	int i;
@@ -224,13 +227,21 @@ main (int argc, char **argv)
 
 	if (np_dbi_query) {
 		/* execute query */
-		status = do_query (conn, &query_val, &query_time);
+		status = do_query (conn, &query_val_str, &query_val, &query_time);
 		if (status != STATE_OK)
 			/* do_query prints an error message in this case */
 			return status;
 
-		if (metric == METRIC_QUERY_RESULT)
-			status = get_status (query_val, dbi_thresholds);
+		if (metric == METRIC_QUERY_RESULT) {
+			if (expect) {
+				if ((! query_val_str) || strcmp (query_val_str, expect))
+					status = STATE_CRITICAL;
+				else
+					status = STATE_OK;
+			}
+			else
+				status = get_status (query_val, dbi_thresholds);
+		}
 		else if (metric == METRIC_QUERY_TIME)
 			status = get_status (query_time, dbi_thresholds);
 	}
@@ -241,11 +252,17 @@ main (int argc, char **argv)
 
 	/* In case of METRIC_QUERY_RESULT, isnan(query_val) indicates an error
 	 * which should have been reported and handled (abort) before */
-	assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val)));
+	assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val)) || expect);
 
 	printf ("%s - connection time: %fs", state_text (status), conn_time);
 	if (np_dbi_query) {
-		if (isnan (query_val))
+		if (expect) {
+			printf (", '%s' returned '%s' in %fs", np_dbi_query,
+					query_val_str ? query_val_str : "<nothing>", query_time);
+			if (status != STATE_OK)
+				printf (" (expected '%s')", expect);
+		}
+		else if (isnan (query_val))
 			printf (", '%s' query execution time: %fs", np_dbi_query, query_time);
 		else
 			printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
@@ -255,7 +272,7 @@ main (int argc, char **argv)
 			((metric == METRIC_CONN_TIME) && warning_range) ? warning_range : "",
 			((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "");
 	if (np_dbi_query) {
-		if (! isnan (query_val))
+		if (! isnan (query_val)) /* this is also true when -e is used */
 			printf (" query=%f;%s;%s;;", query_val,
 					((metric == METRIC_QUERY_RESULT) && warning_range) ? warning_range : "",
 					((metric == METRIC_QUERY_RESULT) && critical_range) ? critical_range : "");
@@ -277,6 +294,7 @@ process_arguments (int argc, char **argv)
 	static struct option longopts[] = {
 		STD_LONG_OPTS,
 
+		{"expect", required_argument, 0, 'e'},
 		{"metric", required_argument, 0, 'm'},
 		{"driver", required_argument, 0, 'd'},
 		{"option", required_argument, 0, 'o'},
@@ -286,7 +304,7 @@ process_arguments (int argc, char **argv)
 	};
 
 	while (1) {
-		c = getopt_long (argc, argv, "Vvht:c:w:m:H:d:o:q:D:",
+		c = getopt_long (argc, argv, "Vvht:c:w:e:m:H:d:o:q:D:",
 				longopts, &option);
 
 		if (c == EOF)
@@ -308,6 +326,9 @@ process_arguments (int argc, char **argv)
 		case 'w':     /* warning range */
 			warning_range = optarg;
 			break;
+		case 'e':
+			expect = optarg;
+			break;
 		case 'm':
 			if (! strcasecmp (optarg, "CONN_TIME"))
 				metric = METRIC_CONN_TIME;
@@ -396,6 +417,12 @@ validate_arguments ()
 			&& (metric != METRIC_QUERY_TIME))
 		usage ("Invalid metric specified");
 
+	if (expect && (warning_range || critical_range))
+		usage ("Do not mix -e and -w/-c");
+
+	if (expect && (metric != METRIC_QUERY_RESULT))
+		usage ("Option -e requires metric QUERY_RESULT");
+
 	return OK;
 }
 
@@ -431,6 +458,9 @@ print_help (void)
 	printf ("\n");
 
 	printf (UT_WARN_CRIT_RANGE);
+	printf (" %s\n", "-e, --expect=STRING");
+	printf ("    %s\n", _("String to expect as query result"));
+	printf ("    %s\n", _("Do not mix with -w or -c!"));
 	printf (" %s\n", "-m, --metric=METRIC");
 	printf ("    %s\n", _("Metric to check thresholds against. Available metrics:"));
 	printf ("    CONN_TIME    - %s\n", _("time used for setting up the database connection"));
@@ -481,6 +511,7 @@ print_usage (void)
 	printf ("%s\n", _("Usage:"));
 	printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] [-q <query>]\n", progname);
 	printf (" [-H <host>] [-c <critical range>] [-w <warning range>] [-m <metric>]\n");
+	printf (" [-e <string>]\n");
 }
 
 #define CHECK_IGNORE_ERROR(s) \
@@ -489,6 +520,28 @@ print_usage (void)
 			return (s); \
 	} while (0)
 
+const char *
+get_field_str (dbi_conn conn, dbi_result res, unsigned short field_type)
+{
+	const char *str;
+
+	if (field_type != DBI_TYPE_STRING) {
+		printf ("CRITICAL - result value is not a string\n");
+		return NULL;
+	}
+
+	str = dbi_result_get_string_idx (res, 1);
+	if ((! str) || (strcmp (str, "ERROR") == 0)) {
+		CHECK_IGNORE_ERROR (NULL);
+		np_dbi_print_error (conn, "CRITICAL - failed to fetch string value");
+		return NULL;
+	}
+
+	if ((verbose && expect) || (verbose > 2))
+		printf ("Query returned string '%s'\n", str);
+	return str;
+}
+
 double
 get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 {
@@ -504,17 +557,13 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 		const char *val_str;
 		char *endptr = NULL;
 
-		val_str = dbi_result_get_string_idx (res, 1);
-		if ((! val_str) || (strcmp (val_str, "ERROR") == 0)) {
+		val_str = get_field_str (conn, res, *field_type);
+		if (! val_str) {
 			CHECK_IGNORE_ERROR (NAN);
-			np_dbi_print_error (conn, "CRITICAL - failed to fetch string value");
 			*field_type = DBI_TYPE_ERROR;
 			return NAN;
 		}
 
-		if (verbose > 2)
-			printf ("Query returned string '%s'\n", val_str);
-
 		val = strtod (val_str, &endptr);
 		if (endptr == val_str) {
 			CHECK_IGNORE_ERROR (NAN);
@@ -543,7 +592,7 @@ get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
 }
 
 double
-get_query_result (dbi_conn conn, dbi_result res, double *res_val)
+get_query_result (dbi_conn conn, dbi_result res, const char **res_val_str, double *res_val)
 {
 	unsigned short field_type;
 	double val = NAN;
@@ -579,8 +628,13 @@ get_query_result (dbi_conn conn, dbi_result res, double *res_val)
 	}
 
 	field_type = dbi_result_get_field_type_idx (res, 1);
-	if (field_type != DBI_TYPE_ERROR)
-		val = get_field (conn, res, &field_type);
+	if (field_type != DBI_TYPE_ERROR) {
+		if (expect)
+			/* the value will be freed in dbi_result_free */
+			*res_val_str = strdup (get_field_str (conn, res, field_type));
+		else
+			val = get_field (conn, res, &field_type);
+	}
 
 	*res_val = val;
 
@@ -597,7 +651,7 @@ get_query_result (dbi_conn conn, dbi_result res, double *res_val)
 #undef CHECK_IGNORE_ERROR
 
 int
-do_query (dbi_conn conn, double *res_val, double *res_time)
+do_query (dbi_conn conn, const char **res_val_str, double *res_val, double *res_time)
 {
 	dbi_result res;
 
@@ -617,11 +671,14 @@ do_query (dbi_conn conn, double *res_val, double *res_time)
 		return STATE_CRITICAL;
 	}
 
-	status = get_query_result (conn, res, res_val);
+	status = get_query_result (conn, res, res_val_str, res_val);
 
 	gettimeofday (&timeval_end, NULL);
 	*res_time = timediff (timeval_start, timeval_end);
 
+	if (verbose)
+		printf ("Time elapsed: %f\n", *res_time);
+
 	return status;
 }
 
-- 
cgit v1.2.3-74-g34f1


From b7e661c4ae46cb6a919a5dbf1bfb28c38e409a64 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Mon, 18 Apr 2011 13:04:28 +0200
Subject: check_dbi: Added -r and -R options.

These options may be used to specify an extended POSIX regular expression that
is applied to the query result. When using -R, a case-insensitive match is
done.

The options may not be mixed with -w/-c/-e.
---
 plugins/check_dbi.c | 101 +++++++++++++++++++++++++++++++++++++++++++++-------
 1 file changed, 89 insertions(+), 12 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 94c6b15f..39898cfa 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -38,6 +38,8 @@ const char *email = "nagiosplug-devel@lists.sourceforge.net";
 
 #include "netutils.h"
 
+#include "regex.h"
+
 /* required for NAN */
 #ifndef _ISOC99_SOURCE
 #define _ISOC99_SOURCE
@@ -56,6 +58,11 @@ typedef enum {
 	METRIC_QUERY_TIME,
 } np_dbi_metric_t;
 
+typedef enum {
+	TYPE_NUMERIC,
+	TYPE_STRING,
+} np_dbi_type_t;
+
 typedef struct {
 	char *key;
 	char *value;
@@ -70,7 +77,12 @@ thresholds *dbi_thresholds = NULL;
 
 char *expect = NULL;
 
+regex_t expect_re;
+char *expect_re_str = NULL;
+int expect_re_cflags = 0;
+
 np_dbi_metric_t metric = METRIC_QUERY_RESULT;
+np_dbi_type_t type = TYPE_NUMERIC;
 
 char *np_dbi_driver = NULL;
 driver_option_t *np_dbi_options = NULL;
@@ -239,6 +251,22 @@ main (int argc, char **argv)
 				else
 					status = STATE_OK;
 			}
+			else if (expect_re_str) {
+				int err;
+
+				err = regexec (&expect_re, query_val_str, 0, NULL, /* flags = */ 0);
+				if (! err)
+					status = STATE_OK;
+				else if (err == REG_NOMATCH)
+					status = STATE_CRITICAL;
+				else {
+					char errmsg[1024];
+					regerror (err, &expect_re, errmsg, sizeof (errmsg));
+					printf ("ERROR - failed to execute regular expression: %s\n",
+							errmsg);
+					status = STATE_CRITICAL;
+				}
+			}
 			else
 				status = get_status (query_val, dbi_thresholds);
 		}
@@ -251,16 +279,26 @@ main (int argc, char **argv)
 	dbi_conn_close (conn);
 
 	/* In case of METRIC_QUERY_RESULT, isnan(query_val) indicates an error
-	 * which should have been reported and handled (abort) before */
-	assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val)) || expect);
+	 * which should have been reported and handled (abort) before
+	 * ... unless we expected a string to be returned */
+	assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val))
+			|| (type == TYPE_STRING));
+
+	assert ((type != TYPE_STRING) || (expect || expect_re_str));
 
 	printf ("%s - connection time: %fs", state_text (status), conn_time);
 	if (np_dbi_query) {
-		if (expect) {
+		if (type == TYPE_STRING) {
+			assert (expect || expect_re_str);
 			printf (", '%s' returned '%s' in %fs", np_dbi_query,
 					query_val_str ? query_val_str : "<nothing>", query_time);
-			if (status != STATE_OK)
-				printf (" (expected '%s')", expect);
+			if (status != STATE_OK) {
+				if (expect)
+					printf (" (expected '%s')", expect);
+				else if (expect_re_str)
+					printf (" (expected regex /%s/%s)", expect_re_str,
+							((expect_re_cflags & REG_ICASE) ? "i" : ""));
+			}
 		}
 		else if (isnan (query_val))
 			printf (", '%s' query execution time: %fs", np_dbi_query, query_time);
@@ -295,6 +333,8 @@ process_arguments (int argc, char **argv)
 		STD_LONG_OPTS,
 
 		{"expect", required_argument, 0, 'e'},
+		{"regex", required_argument, 0, 'r'},
+		{"regexi", required_argument, 0, 'R'},
 		{"metric", required_argument, 0, 'm'},
 		{"driver", required_argument, 0, 'd'},
 		{"option", required_argument, 0, 'o'},
@@ -304,7 +344,7 @@ process_arguments (int argc, char **argv)
 	};
 
 	while (1) {
-		c = getopt_long (argc, argv, "Vvht:c:w:e:m:H:d:o:q:D:",
+		c = getopt_long (argc, argv, "Vvht:c:w:e:r:R:m:H:d:o:q:D:",
 				longopts, &option);
 
 		if (c == EOF)
@@ -322,13 +362,38 @@ process_arguments (int argc, char **argv)
 
 		case 'c':     /* critical range */
 			critical_range = optarg;
+			type = TYPE_NUMERIC;
 			break;
 		case 'w':     /* warning range */
 			warning_range = optarg;
+			type = TYPE_NUMERIC;
 			break;
 		case 'e':
 			expect = optarg;
+			type = TYPE_STRING;
 			break;
+		case 'R':
+			expect_re_cflags = REG_ICASE;
+			/* fall through */
+		case 'r':
+			{
+				int err;
+
+				expect_re_cflags |= REG_EXTENDED | REG_NOSUB | REG_NEWLINE;
+				expect_re_str = optarg;
+				type = TYPE_STRING;
+
+				err = regcomp (&expect_re, expect_re_str, expect_re_cflags);
+				if (err) {
+					char errmsg[1024];
+					regerror (err, &expect_re, errmsg, sizeof (errmsg));
+					printf ("ERROR - failed to compile regular expression: %s\n",
+							errmsg);
+					return ERROR;
+				}
+				break;
+			}
+
 		case 'm':
 			if (! strcasecmp (optarg, "CONN_TIME"))
 				metric = METRIC_CONN_TIME;
@@ -417,12 +482,18 @@ validate_arguments ()
 			&& (metric != METRIC_QUERY_TIME))
 		usage ("Invalid metric specified");
 
-	if (expect && (warning_range || critical_range))
-		usage ("Do not mix -e and -w/-c");
+	if (expect && (warning_range || critical_range || expect_re_str))
+		usage ("Do not mix -e and -w/-c/-r/-R");
+
+	if (expect_re_str && (warning_range || critical_range || expect))
+		usage ("Do not mix -r/-R and -w/-c/-e");
 
 	if (expect && (metric != METRIC_QUERY_RESULT))
 		usage ("Option -e requires metric QUERY_RESULT");
 
+	if (expect_re_str && (metric != METRIC_QUERY_RESULT))
+		usage ("Options -r/-R require metric QUERY_RESULT");
+
 	return OK;
 }
 
@@ -460,7 +531,13 @@ print_help (void)
 	printf (UT_WARN_CRIT_RANGE);
 	printf (" %s\n", "-e, --expect=STRING");
 	printf ("    %s\n", _("String to expect as query result"));
-	printf ("    %s\n", _("Do not mix with -w or -c!"));
+	printf ("    %s\n", _("Do not mix with -w, -c, -r, or -R!"));
+	printf (" %s\n", "-r, --regex=REGEX");
+	printf ("    %s\n", _("Extended POSIX regular expression to check query result against"));
+	printf ("    %s\n", _("Do not mix with -w, -c, -e, or -R!"));
+	printf (" %s\n", "-R, --regexi=REGEX");
+	printf ("    %s\n", _("Case-insensitive extended POSIX regex to check query result against"));
+	printf ("    %s\n", _("Do not mix with -w, -c, -e, or -r!"));
 	printf (" %s\n", "-m, --metric=METRIC");
 	printf ("    %s\n", _("Metric to check thresholds against. Available metrics:"));
 	printf ("    CONN_TIME    - %s\n", _("time used for setting up the database connection"));
@@ -511,7 +588,7 @@ print_usage (void)
 	printf ("%s\n", _("Usage:"));
 	printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] [-q <query>]\n", progname);
 	printf (" [-H <host>] [-c <critical range>] [-w <warning range>] [-m <metric>]\n");
-	printf (" [-e <string>]\n");
+	printf (" [-e <string>] [-r|-R <regex>]\n");
 }
 
 #define CHECK_IGNORE_ERROR(s) \
@@ -537,7 +614,7 @@ get_field_str (dbi_conn conn, dbi_result res, unsigned short field_type)
 		return NULL;
 	}
 
-	if ((verbose && expect) || (verbose > 2))
+	if ((verbose && (type == TYPE_STRING)) || (verbose > 2))
 		printf ("Query returned string '%s'\n", str);
 	return str;
 }
@@ -629,7 +706,7 @@ get_query_result (dbi_conn conn, dbi_result res, const char **res_val_str, doubl
 
 	field_type = dbi_result_get_field_type_idx (res, 1);
 	if (field_type != DBI_TYPE_ERROR) {
-		if (expect)
+		if (type == TYPE_STRING)
 			/* the value will be freed in dbi_result_free */
 			*res_val_str = strdup (get_field_str (conn, res, field_type));
 		else
-- 
cgit v1.2.3-74-g34f1


From 14fc0f741dcd8316d7052d38ceb0a6968b7d999f Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Mon, 18 Apr 2011 13:33:41 +0200
Subject: check_dbi: Added SERVER_VERSION metric.

This metric compares the database engine server version with the specified
ranges. The version string [[[[A.]B.]C.]D.]E[.] is returned as E + D*100 +
C*10000 + B*1000000 + A*100000000 (see libdbi's dbi_conn_get_engine_version()
documentation).
---
 plugins/check_dbi.c | 29 +++++++++++++++++++++++++----
 1 file changed, 25 insertions(+), 4 deletions(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 39898cfa..1edc2e44 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -54,6 +54,7 @@ const char *email = "nagiosplug-devel@lists.sourceforge.net";
 
 typedef enum {
 	METRIC_CONN_TIME,
+	METRIC_SERVER_VERSION,
 	METRIC_QUERY_RESULT,
 	METRIC_QUERY_TIME,
 } np_dbi_metric_t;
@@ -109,6 +110,8 @@ main (int argc, char **argv)
 	dbi_driver driver;
 	dbi_conn conn;
 
+	unsigned int server_version;
+
 	struct timeval start_timeval, end_timeval;
 	double conn_time = 0.0;
 	double query_time = 0.0;
@@ -219,8 +222,15 @@ main (int argc, char **argv)
 	gettimeofday (&end_timeval, NULL);
 	conn_time = timediff (start_timeval, end_timeval);
 
+	server_version = dbi_conn_get_engine_version (conn);
 	if (verbose)
-		printf("Time elapsed: %f\n", conn_time);
+		printf ("Connected to server version %u\n", server_version);
+
+	if (metric == METRIC_SERVER_VERSION)
+		status = get_status (server_version, dbi_thresholds);
+
+	if (verbose)
+		printf ("Time elapsed: %f\n", conn_time);
 
 	if (metric == METRIC_CONN_TIME)
 		status = get_status (conn_time, dbi_thresholds);
@@ -306,9 +316,12 @@ main (int argc, char **argv)
 			printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
 	}
 
-	printf (" | conntime=%fs;%s;%s;0;", conn_time,
+	printf (" | conntime=%fs;%s;%s;0; server_version=%u;%s;%s;0;", conn_time,
 			((metric == METRIC_CONN_TIME) && warning_range) ? warning_range : "",
-			((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "");
+			((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "",
+			server_version,
+			((metric == METRIC_SERVER_VERSION) && warning_range) ? warning_range : "",
+			((metric == METRIC_SERVER_VERSION) && critical_range) ? critical_range : "");
 	if (np_dbi_query) {
 		if (! isnan (query_val)) /* this is also true when -e is used */
 			printf (" query=%f;%s;%s;;", query_val,
@@ -397,6 +410,8 @@ process_arguments (int argc, char **argv)
 		case 'm':
 			if (! strcasecmp (optarg, "CONN_TIME"))
 				metric = METRIC_CONN_TIME;
+			else if (! strcasecmp (optarg, "SERVER_VERSION"))
+				metric = METRIC_SERVER_VERSION;
 			else if (! strcasecmp (optarg, "QUERY_RESULT"))
 				metric = METRIC_QUERY_RESULT;
 			else if (! strcasecmp (optarg, "QUERY_TIME"))
@@ -478,6 +493,7 @@ validate_arguments ()
 		usage ("Must specify a query to execute (metric == QUERY_RESULT)");
 
 	if ((metric != METRIC_CONN_TIME)
+			&& (metric != METRIC_SERVER_VERSION)
 			&& (metric != METRIC_QUERY_RESULT)
 			&& (metric != METRIC_QUERY_TIME))
 		usage ("Invalid metric specified");
@@ -577,7 +593,12 @@ print_help (void)
 	printf ("  check_dbi -d firebird -o username=user -o password=secret -o dbname=foo \\\n");
 	printf ("    -m CONN_TIME -w 0.5 -c 2\n");
 	printf ("  Warning if connecting to the database takes more than half of a second;\n");
-	printf ("  critical if it takes more than 2 seconds.\n");
+	printf ("  critical if it takes more than 2 seconds.\n\n");
+
+	printf ("  check_dbi -d pgsql -u username=user -m SERVER_VERSION \\\n");
+	printf ("    -w 090000:090099 -c 090000:090199\n");
+	printf ("  Warn if the PostgreSQL server version is not 9.0.x; critical if the version\n");
+	printf ("  is less than 9.x or higher than 9.1.x.\n");
 
 	printf (UT_SUPPORT);
 }
-- 
cgit v1.2.3-74-g34f1


From 022ba7ea3abbc56f70c3310ae85278c5b8da40ac Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Mon, 18 Apr 2011 13:47:26 +0200
Subject: check_dbi: Added simple regex example to help output.

---
 plugins/check_dbi.c | 6 ++++++
 1 file changed, 6 insertions(+)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 1edc2e44..03ec9ce5 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -595,6 +595,12 @@ print_help (void)
 	printf ("  Warning if connecting to the database takes more than half of a second;\n");
 	printf ("  critical if it takes more than 2 seconds.\n\n");
 
+	printf ("  check_dbi -d mysql -H localhost -o username=user \\\n");
+	printf ("    -q 'SELECT concat(@@version, \" \", @@version_comment)' \\\n");
+	printf ("    -r '^5\\.[01].*MySQL Enterprise Server'\n");
+	printf ("  Critical if the database server is not a MySQL enterprise server in either\n");
+	printf ("  version 5.0.x or 5.1.x.\n\n");
+
 	printf ("  check_dbi -d pgsql -u username=user -m SERVER_VERSION \\\n");
 	printf ("    -w 090000:090099 -c 090000:090199\n");
 	printf ("  Warn if the PostgreSQL server version is not 9.0.x; critical if the version\n");
-- 
cgit v1.2.3-74-g34f1


From ce75adf2872ef973d86cfaf36fcc0f717a7af66b Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Wed, 6 Jun 2012 15:06:44 +0200
Subject: check_dbi: Hint the user about cause when dbi init fails.

---
 plugins/check_dbi.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'plugins')

diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index 03ec9ce5..8c4a511d 100644
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
@@ -141,7 +141,7 @@ main (int argc, char **argv)
 		printf ("Initializing DBI\n");
 
 	if (dbi_initialize (NULL) < 0) {
-		printf ("UNKNOWN - failed to initialize DBI.\n");
+		printf ("UNKNOWN - failed to initialize DBI; possibly you don't have any drivers installed.\n");
 		return STATE_UNKNOWN;
 	}
 
-- 
cgit v1.2.3-74-g34f1


From 84554196509fdbb595f93c05368b36dce1cde7c1 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@teamix.net>
Date: Fri, 8 Jun 2012 11:14:21 +0200
Subject: Added a few test cases for the check_dbi plugin.

All tests use sqlite3. A temporary database is created for the purpose of the
tests.
---
 plugins/t/check_dbi.t | 102 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 102 insertions(+)
 create mode 100644 plugins/t/check_dbi.t

(limited to 'plugins')

diff --git a/plugins/t/check_dbi.t b/plugins/t/check_dbi.t
new file mode 100644
index 00000000..e542ba53
--- /dev/null
+++ b/plugins/t/check_dbi.t
@@ -0,0 +1,102 @@
+#! /usr/bin/perl -w -I ..
+#
+# Database Server Tests via check_dbi
+#
+#
+# Uses the 'sqlite3' DBD driver and command line utility.
+
+use strict;
+use Test::More;
+use NPTest;
+
+use File::Temp;
+
+use vars qw($tests);
+
+plan skip_all => "check_dbi not compiled" unless (-x "check_dbi");
+
+$tests = 20;
+plan tests => $tests;
+
+my $missing_driver_output = "failed to open DBI driver 'sqlite3'";
+
+my $bad_driver_output    = "/failed to open DBI driver 'nodriver'/";
+my $conn_time_output     = "/OK - connection time: [0-9\.]+s \|/";
+my $missing_query_output = "/Must specify a query to execute/";
+my $no_rows_output       = "/WARNING - no rows returned/";
+my $not_numeric_output   = "/CRITICAL - result value is not a numeric:/";
+my $query_time_output    = "/OK - connection time: [0-9\.]+s, 'SELECT 1' returned 1.000000 in [0-9\.]+s \|/";
+my $syntax_error_output  = "/CRITICAL - failed to execute query 'GET ALL FROM test': 1: near \"GET\": syntax error/";
+
+my $result;
+
+SKIP: {
+	my $sqlite3 = qx(which sqlite3 2> /dev/null);
+	chomp($sqlite3);
+
+	skip "No Sqlite3 found", $tests unless $sqlite3;
+
+	my $sqlite3_check = qx(./check_dbi -d sqlite3 -q '');
+	if ($sqlite3_check =~ m/$missing_driver_output/) {
+		skip "No 'sqlite3' DBD driver found", $tests;
+	}
+
+	my $fh = File::Temp->new(
+		TEMPLATE => "/tmp/check_dbi_sqlite3.XXXXXXX",
+		UNLINK   => 1,
+	);
+	my $filename = $fh->filename;
+	$filename =~ s/^\/tmp\///;
+
+	system("$sqlite3 /tmp/$filename 'CREATE TABLE test(a INT, b TEXT)'");
+	system("$sqlite3 /tmp/$filename 'INSERT INTO test VALUES (1, \"text1\"), (2, \"text2\")'");
+
+	my $check_cmd = "./check_dbi -d sqlite3 -o sqlite3_dbdir=/tmp -o dbname=$filename";
+
+	$result = NPTest->testCmd("$check_cmd -q 'SELECT 1'");
+	cmp_ok($result->return_code, '==', 0, "Sqlite3 login okay and can run query");
+
+	$result = NPTest->testCmd("$check_cmd");
+	cmp_ok($result->return_code, '==', 3, "Missing query parameter");
+	like($result->output, $missing_query_output, "Missing query parameter error message");
+
+	$result = NPTest->testCmd("$check_cmd -q 'GET ALL FROM test'");
+	cmp_ok($result->return_code, '==', 2, "Invalid query");
+	like($result->output, $syntax_error_output, "Syntax error message");
+
+	$result = NPTest->testCmd("$check_cmd -q 'SELECT 2.71828' -w 2 -c 3");
+	cmp_ok($result->return_code, '==', 1, "Got warning");
+
+	$result = NPTest->testCmd("$check_cmd -q 'SELECT 3.1415' -w 2 -c 3");
+	cmp_ok($result->return_code, '==', 2, "Got critical");
+
+	$result = NPTest->testCmd("$check_cmd -q ''");
+	cmp_ok($result->return_code, '==', 1, "No rows returned");
+	like($result->output, $no_rows_output, "Now rows returned warning message");
+
+	$result = NPTest->testCmd("$check_cmd -q 'SELECT b FROM test'");
+	cmp_ok($result->return_code, '==', 2, "Value is not a numeric");
+	like($result->output, $not_numeric_output, "Value is not a numeric error message");
+
+	$result = NPTest->testCmd("$check_cmd -m QUERY_RESULT -q 'SELECT b FROM test' -e text1");
+	cmp_ok($result->return_code, '==', 0, "Query result string comparison okay");
+
+	$result = NPTest->testCmd("$check_cmd -q 'SELECT b FROM test' -r 'eXt[0-9]'");
+	cmp_ok($result->return_code, '==', 2, "Query result case-insensitive regex failure");
+
+	$result = NPTest->testCmd("$check_cmd -q 'SELECT b FROM test' -R 'eXt[0-9]'");
+	cmp_ok($result->return_code, '==', 0, "Query result case-sensitive regex okay");
+
+	$result = NPTest->testCmd("$check_cmd -m CONN_TIME -w 0.5 -c 0.7");
+	cmp_ok($result->return_code, '==', 0, "CONN_TIME metric okay");
+	like($result->output, $conn_time_output, "CONN_TIME metric output okay");
+
+	$result = NPTest->testCmd("$check_cmd -m QUERY_TIME -q 'SELECT 1'");
+	cmp_ok($result->return_code, '==', 0, "QUERY_TIME metric okay");
+	like($result->output, $query_time_output, "QUERY_TIME metric output okay");
+
+	$result = NPTest->testCmd("./check_dbi -d nodriver -q ''");
+	cmp_ok($result->return_code, '==', 3, "Unknown DBI driver");
+	like($result->output, $bad_driver_output, "Correct error message");
+}
+
-- 
cgit v1.2.3-74-g34f1


From e056cc9d8279fdb76ffd77dfeaed4fb13f95cef0 Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@tokkee.org>
Date: Fri, 8 Jun 2012 13:18:07 +0200
Subject: t/check_dbi.t: Don't use 'INSERT … VALUES (a,b), (c,d)' with SQLite3.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Older versions of SQLite3 don't seem to support that.
---
 plugins/t/check_dbi.t | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'plugins')

diff --git a/plugins/t/check_dbi.t b/plugins/t/check_dbi.t
index e542ba53..c24b5a8c 100644
--- a/plugins/t/check_dbi.t
+++ b/plugins/t/check_dbi.t
@@ -49,7 +49,8 @@ SKIP: {
 	$filename =~ s/^\/tmp\///;
 
 	system("$sqlite3 /tmp/$filename 'CREATE TABLE test(a INT, b TEXT)'");
-	system("$sqlite3 /tmp/$filename 'INSERT INTO test VALUES (1, \"text1\"), (2, \"text2\")'");
+	system("$sqlite3 /tmp/$filename 'INSERT INTO test VALUES (1, \"text1\")'");
+	system("$sqlite3 /tmp/$filename 'INSERT INTO test VALUES (2, \"text2\")'");
 
 	my $check_cmd = "./check_dbi -d sqlite3 -o sqlite3_dbdir=/tmp -o dbname=$filename";
 
-- 
cgit v1.2.3-74-g34f1