From f9a942d2a11e70ee68d5ea2c9aa762bff004bf43 Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/.gitignore b/.gitignore index 0ffb91e..52ca6aa 100644 --- a/.gitignore +++ b/.gitignore @@ -134,6 +134,7 @@ NP-VERSION-FILE /plugins/check_by_ssh /plugins/check_clamd /plugins/check_cluster +/plugins/check_dbi /plugins/check_dig /plugins/check_disk /plugins/check_dns diff --git a/REQUIREMENTS b/REQUIREMENTS index fd41ded..9f2eec0 100644 --- a/REQUIREMENTS +++ b/REQUIREMENTS @@ -46,6 +46,10 @@ check_pqsql: - Requires the PostgreSQL libraries available from http://www.postgresql.org/ +check_dbi: + - Requires the DBI libraries available from + http://libdbi.sourceforge.net/ + check_radius: - Requires the radiusclient-ng library available from: http://developer.berlios.de/projects/radiusclient-ng/ diff --git a/configure.in b/configure.in index e8fc789..072d674 100644 --- a/configure.in +++ b/configure.in @@ -255,6 +255,19 @@ fi LIBS="$_SAVEDLIBS" CPPFLAGS="$_SAVEDCPPFLAGS" +dnl Check for DBI libraries +_SAVEDLIBS="$LIBS" +AC_CHECK_LIB(dbi,dbi_initialize) +if test "$ac_cv_lib_dbi_dbi_initialize" = "yes"; then + EXTRAS="$EXTRAS check_dbi" + DBILIBS="-ldbi" + AC_SUBST(DBILIBS) +else + AC_MSG_WARN([Skipping dbi plugin]) + AC_MSG_WARN([install DBI libs to compile this plugin (see REQUIREMENTS).]) +fi +LIBS="$_SAVEDLIBS" + dnl Check for radius libraries _SAVEDLIBS="$LIBS" AC_CHECK_LIB(radiusclient,rc_read_config) diff --git a/plugins/Makefile.am b/plugins/Makefile.am index 36a28b0..4781e0f 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 0000000..b52602c --- /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 +* +* 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 . +* +* +*****************************************************************************/ + +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 + +#include + +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 = ""; + if (! host) + host = ""; + + 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 '='"), 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 [-o [...]] -q \n", progname); + printf (" [-H ] [-c ] [-w ]\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" + : "", + *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 v0.10-9-g596f From 07768fa89561a4e55391634942e696a167bc6bbf Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 17:37:10 +0200 Subject: check_dbi: Include extra opts in help output. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index b52602c..47b239a 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 v0.10-9-g596f From e7dfcd4429b54bd98bac724665d7b5e6e30a7532 Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 47b239a..d4ac4e3 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 [-o [...]] -q \n", progname); - printf (" [-H ] [-c ] [-w ]\n"); + printf (" [-H ] [-c ] [-w ]\n"); + printf (" [-C ] [-W ]\n"); } double -- cgit v0.10-9-g596f From 0e02edec196a8f3cf866279c5609138db14ec931 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 18:09:33 +0200 Subject: check_dbi: Check and report the time used by the query. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index d4ac4e3..f59d7c4 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 v0.10-9-g596f From 61506bd8d7a095f5de571d21e37972a159372bdf Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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 diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index f59d7c4..611e0e7 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 v0.10-9-g596f From d7a1f2a7a08aeaaf5efd0d454b2355554a413f2b Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 611e0e7..f009944 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 + #include #include +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 [-o [...]] -q \n", progname); - printf (" [-H ] [-c ] [-w ]\n"); - printf (" [-C ] [-W ]\n"); + printf ("%s -d [-o [...]] [-q ]\n", progname); + printf (" [-H ] [-c ] [-w ] [-m ]\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 v0.10-9-g596f From 4ad6e441b68cca4b2f283af3f783794279fbb11c Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index f009944..4a0a4d6 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 +#include #include @@ -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 ] [-c ] [-w ] [-m ]\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) : "", *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 v0.10-9-g596f From eda2fe7f40810a03e1c9c32701916387fdf86165 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Fri, 15 Apr 2011 15:02:48 +0200 Subject: check_dbi: Added some examples to the help output. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 4a0a4d6..eefcf02 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 v0.10-9-g596f From aebde433fb2ad27f8ded9dd41224b6826ac787d2 Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index eefcf02..94c6b15 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 : "", 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 [-o [...]] [-q ]\n", progname); printf (" [-H ] [-c ] [-w ] [-m ]\n"); + printf (" [-e ]\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 v0.10-9-g596f From b7e661c4ae46cb6a919a5dbf1bfb28c38e409a64 Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 94c6b15..39898cf 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 : "", 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 [-o [...]] [-q ]\n", progname); printf (" [-H ] [-c ] [-w ] [-m ]\n"); - printf (" [-e ]\n"); + printf (" [-e ] [-r|-R ]\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 v0.10-9-g596f From 14fc0f741dcd8316d7052d38ceb0a6968b7d999f Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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). diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 39898cf..1edc2e4 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 v0.10-9-g596f From 022ba7ea3abbc56f70c3310ae85278c5b8da40ac Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Mon, 18 Apr 2011 13:47:26 +0200 Subject: check_dbi: Added simple regex example to help output. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 1edc2e4..03ec9ce 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 v0.10-9-g596f From ce75adf2872ef973d86cfaf36fcc0f717a7af66b Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Wed, 6 Jun 2012 15:06:44 +0200 Subject: check_dbi: Hint the user about cause when dbi init fails. diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c index 03ec9ce..8c4a511 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 v0.10-9-g596f From 84554196509fdbb595f93c05368b36dce1cde7c1 Mon Sep 17 00:00:00 2001 From: Sebastian Harl 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. diff --git a/plugins/t/check_dbi.t b/plugins/t/check_dbi.t new file mode 100644 index 0000000..e542ba5 --- /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 v0.10-9-g596f From e056cc9d8279fdb76ffd77dfeaed4fb13f95cef0 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Fri, 8 Jun 2012 13:18:07 +0200 Subject: =?UTF-8?q?t/check=5Fdbi.t:=20Don't=20use=20'INSERT=20=E2=80=A6=20?= =?UTF-8?q?VALUES=20(a,b),=20(c,d)'=20with=20SQLite3.?= 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. diff --git a/plugins/t/check_dbi.t b/plugins/t/check_dbi.t index e542ba5..c24b5a8 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 v0.10-9-g596f