![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
index b52602c4a2753958fd71d8384c0f12341e532702..03ec9ce50036c5393377e142a3f0315914cac1e7 100644 (file)
--- a/plugins/check_dbi.c
+++ b/plugins/check_dbi.c
*
* 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
#include "netutils.h"
+#include "regex.h"
+
+/* required for NAN */
+#ifndef _ISOC99_SOURCE
+#define _ISOC99_SOURCE
+#endif
+
+#include <assert.h>
+#include <math.h>
+
#include <dbi/dbi.h>
#include <stdarg.h>
+typedef enum {
+ METRIC_CONN_TIME,
+ METRIC_SERVER_VERSION,
+ METRIC_QUERY_RESULT,
+ METRIC_QUERY_TIME,
+} np_dbi_metric_t;
+
+typedef enum {
+ TYPE_NUMERIC,
+ TYPE_STRING,
+} np_dbi_type_t;
+
typedef struct {
char *key;
char *value;
char *warning_range = NULL;
char *critical_range = NULL;
-thresholds *query_thresholds = NULL;
+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;
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, const char **, double *, double *);
int
main (int argc, char **argv)
dbi_driver driver;
dbi_conn conn;
+ unsigned int server_version;
+
struct timeval start_timeval, end_timeval;
- double elapsed_time;
+ double conn_time = 0.0;
+ double query_time = 0.0;
+ const char *query_val_str = NULL;
double query_val = 0.0;
int i;
}
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);
+ server_version = dbi_conn_get_engine_version (conn);
if (verbose)
- printf("Time elapsed: %f\n", elapsed_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);
/* select a database */
if (np_dbi_database) {
}
}
- /* 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 (np_dbi_query) {
+ /* execute query */
+ 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) {
+ if (expect) {
+ if ((! query_val_str) || strcmp (query_val_str, expect))
+ status = STATE_CRITICAL;
+ 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);
+ }
+ else if (metric == METRIC_QUERY_TIME)
+ status = get_status (query_time, dbi_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 : "");
+ /* In case of METRIC_QUERY_RESULT, isnan(query_val) indicates an error
+ * 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 (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) {
+ 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);
+ else
+ printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_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 : "",
+ 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,
+ ((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;
}
static struct option longopts[] = {
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'},
{"query", required_argument, 0, 'q'},
};
while (1) {
- c = getopt_long (argc, argv, "Vvht:c:w: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)
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;
+ 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"))
+ metric = METRIC_QUERY_TIME;
+ else
+ usage2 (_("Invalid metric"), optarg);
break;
case 't': /* timeout */
if (!is_intnonneg (optarg))
}
}
- set_thresholds (&query_thresholds, warning_range, critical_range);
+ set_thresholds (&dbi_thresholds, warning_range, critical_range);
return 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) || (metric == METRIC_QUERY_TIME))
+ && (! np_dbi_query))
+ 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");
+
+ 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;
}
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");
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");
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", "-e, --expect=STRING");
+ printf (" %s\n", _("String to expect as query result"));
+ 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"));
+ 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);
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)."));
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\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");
+ printf (" is less than 9.x or higher than 9.1.x.\n");
printf (UT_SUPPORT);
}
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 ("%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>] [-r|-R <regex>]\n");
+}
+
+#define CHECK_IGNORE_ERROR(s) \
+ do { \
+ if (metric != METRIC_QUERY_RESULT) \
+ 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 && (type == TYPE_STRING)) || (verbose > 2))
+ printf ("Query returned string '%s'\n", str);
+ return str;
}
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);
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");
+ val_str = get_field_str (conn, res, *field_type);
+ if (! val_str) {
+ CHECK_IGNORE_ERROR (NAN);
*field_type = DBI_TYPE_ERROR;
- return 0.0;
+ 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);
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)
}
}
else {
+ CHECK_IGNORE_ERROR (NAN);
printf ("CRITICAL - cannot parse value of type %s (%i)\n",
(*field_type == DBI_TYPE_BINARY)
? "BINARY"
: "<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
+get_query_result (dbi_conn conn, dbi_result res, const char **res_val_str, 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;
- }
+ 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;
}
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 (type == TYPE_STRING)
+ /* 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;
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, const char **res_val_str, 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_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;
+}
+
+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, ...)
{