1 /*****************************************************************************
2 *
3 * Nagios check_dbi plugin
4 *
5 * License: GPL
6 * Copyright (c) 2011 Nagios Plugins Development Team
7 * Author: Sebastian 'tokkee' Harl <sh@teamix.net>
8 *
9 * Description:
10 *
11 * This file contains the check_dbi plugin
12 *
13 * Runs an arbitrary (SQL) command and checks the result.
14 *
15 *
16 * This program is free software: you can redistribute it and/or modify
17 * it under the terms of the GNU General Public License as published by
18 * the Free Software Foundation, either version 3 of the License, or
19 * (at your option) any later version.
20 *
21 * This program is distributed in the hope that it will be useful,
22 * but WITHOUT ANY WARRANTY; without even the implied warranty of
23 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 * GNU General Public License for more details.
25 *
26 * You should have received a copy of the GNU General Public License
27 * along with this program. If not, see <http://www.gnu.org/licenses/>.
28 *
29 *
30 *****************************************************************************/
32 const char *progname = "check_dbi";
33 const char *copyright = "2011";
34 const char *email = "nagiosplug-devel@lists.sourceforge.net";
36 #include "common.h"
37 #include "utils.h"
39 #include "netutils.h"
41 /* required for NAN */
42 #ifndef _ISOC99_SOURCE
43 #define _ISOC99_SOURCE
44 #endif
46 #include <assert.h>
47 #include <math.h>
49 #include <dbi/dbi.h>
51 #include <stdarg.h>
53 typedef enum {
54 METRIC_CONN_TIME,
55 METRIC_QUERY_RESULT,
56 METRIC_QUERY_TIME,
57 } np_dbi_metric_t;
59 typedef struct {
60 char *key;
61 char *value;
62 } driver_option_t;
64 char *host = NULL;
65 int verbose = 0;
67 char *warning_range = NULL;
68 char *critical_range = NULL;
69 thresholds *dbi_thresholds = NULL;
71 np_dbi_metric_t metric = METRIC_QUERY_RESULT;
73 char *np_dbi_driver = NULL;
74 driver_option_t *np_dbi_options = NULL;
75 int np_dbi_options_num = 0;
76 char *np_dbi_database = NULL;
77 char *np_dbi_query = NULL;
79 int process_arguments (int, char **);
80 int validate_arguments (void);
81 void print_usage (void);
82 void print_help (void);
84 double timediff (struct timeval, struct timeval);
86 void np_dbi_print_error (dbi_conn, char *, ...);
88 int do_query (dbi_conn, double *, double *);
90 int
91 main (int argc, char **argv)
92 {
93 int status = STATE_UNKNOWN;
95 dbi_driver driver;
96 dbi_conn conn;
98 struct timeval start_timeval, end_timeval;
99 double conn_time = 0.0;
100 double query_time = 0.0;
102 double query_val = 0.0;
104 int i;
106 setlocale (LC_ALL, "");
107 bindtextdomain (PACKAGE, LOCALEDIR);
108 textdomain (PACKAGE);
110 /* Parse extra opts if any */
111 argv = np_extra_opts (&argc, argv, progname);
113 if (process_arguments (argc, argv) == ERROR)
114 usage4 (_("Could not parse arguments"));
116 /* Set signal handling and alarm */
117 if (signal (SIGALRM, timeout_alarm_handler) == SIG_ERR) {
118 usage4 (_("Cannot catch SIGALRM"));
119 }
120 alarm (timeout_interval);
122 if (verbose > 2)
123 printf ("Initializing DBI\n");
125 if (dbi_initialize (NULL) < 0) {
126 printf ("UNKNOWN - failed to initialize DBI.\n");
127 return STATE_UNKNOWN;
128 }
130 if (verbose)
131 printf ("Opening DBI driver '%s'\n", np_dbi_driver);
133 driver = dbi_driver_open (np_dbi_driver);
134 if (! driver) {
135 printf ("UNKNOWN - failed to open DBI driver '%s'; possibly it's not installed.\n",
136 np_dbi_driver);
138 printf ("Known drivers:\n");
139 for (driver = dbi_driver_list (NULL); driver; driver = dbi_driver_list (driver)) {
140 printf (" - %s\n", dbi_driver_get_name (driver));
141 }
142 return STATE_UNKNOWN;
143 }
145 /* make a connection to the database */
146 gettimeofday (&start_timeval, NULL);
148 conn = dbi_conn_open (driver);
149 if (! conn) {
150 printf ("UNKNOWN - failed top open connection object.\n");
151 dbi_conn_close (conn);
152 return STATE_UNKNOWN;
153 }
155 for (i = 0; i < np_dbi_options_num; ++i) {
156 const char *opt;
158 if (verbose > 1)
159 printf ("Setting DBI driver option '%s' to '%s'\n",
160 np_dbi_options[i].key, np_dbi_options[i].value);
162 if (! dbi_conn_set_option (conn, np_dbi_options[i].key, np_dbi_options[i].value))
163 continue;
164 /* else: status != 0 */
166 np_dbi_print_error (conn, "UNKNOWN - failed to set option '%s' to '%s'",
167 np_dbi_options[i].key, np_dbi_options[i].value);
168 printf ("Known driver options:\n");
170 for (opt = dbi_conn_get_option_list (conn, NULL); opt;
171 opt = dbi_conn_get_option_list (conn, opt)) {
172 printf (" - %s\n", opt);
173 }
174 dbi_conn_close (conn);
175 return STATE_UNKNOWN;
176 }
178 if (host) {
179 if (verbose > 1)
180 printf ("Setting DBI driver option 'host' to '%s'\n", host);
181 dbi_conn_set_option (conn, "host", host);
182 }
184 if (verbose) {
185 const char *dbname, *host;
187 dbname = dbi_conn_get_option (conn, "dbname");
188 host = dbi_conn_get_option (conn, "host");
190 if (! dbname)
191 dbname = "<unspecified>";
192 if (! host)
193 host = "<unspecified>";
195 printf ("Connecting to database '%s' at host '%s'\n",
196 dbname, host);
197 }
199 if (dbi_conn_connect (conn) < 0) {
200 np_dbi_print_error (conn, "UNKOWN - failed to connect to database");
201 return STATE_UNKNOWN;
202 }
204 gettimeofday (&end_timeval, NULL);
205 conn_time = timediff (start_timeval, end_timeval);
207 if (verbose)
208 printf("Time elapsed: %f\n", conn_time);
210 if (metric == METRIC_CONN_TIME)
211 status = get_status (conn_time, dbi_thresholds);
213 /* select a database */
214 if (np_dbi_database) {
215 if (verbose > 1)
216 printf ("Selecting database '%s'\n", np_dbi_database);
218 if (dbi_conn_select_db (conn, np_dbi_database)) {
219 np_dbi_print_error (conn, "UNKOWN - failed to select database '%s'",
220 np_dbi_database);
221 return STATE_UNKNOWN;
222 }
223 }
225 if (np_dbi_query) {
226 /* execute query */
227 status = do_query (conn, &query_val, &query_time);
228 if (status != STATE_OK)
229 /* do_query prints an error message in this case */
230 return status;
232 if (metric == METRIC_QUERY_RESULT)
233 status = get_status (query_val, dbi_thresholds);
234 else if (metric == METRIC_QUERY_TIME)
235 status = get_status (query_time, dbi_thresholds);
236 }
238 if (verbose)
239 printf("Closing connection\n");
240 dbi_conn_close (conn);
242 /* In case of METRIC_QUERY_RESULT, isnan(query_val) indicates an error
243 * which should have been reported and handled (abort) before */
244 assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val)));
246 printf ("%s - connection time: %fs", state_text (status), conn_time);
247 if (np_dbi_query) {
248 if (isnan (query_val))
249 printf (", '%s' query execution time: %fs", np_dbi_query, query_time);
250 else
251 printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
252 }
254 printf (" | conntime=%fs;%s;%s;0;", conn_time,
255 ((metric == METRIC_CONN_TIME) && warning_range) ? warning_range : "",
256 ((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "");
257 if (np_dbi_query) {
258 if (! isnan (query_val))
259 printf (" query=%f;%s;%s;;", query_val,
260 ((metric == METRIC_QUERY_RESULT) && warning_range) ? warning_range : "",
261 ((metric == METRIC_QUERY_RESULT) && critical_range) ? critical_range : "");
262 printf (" querytime=%fs;%s;%s;0;", query_time,
263 ((metric == METRIC_QUERY_TIME) && warning_range) ? warning_range : "",
264 ((metric == METRIC_QUERY_TIME) && critical_range) ? critical_range : "");
265 }
266 printf ("\n");
267 return status;
268 }
270 /* process command-line arguments */
271 int
272 process_arguments (int argc, char **argv)
273 {
274 int c;
276 int option = 0;
277 static struct option longopts[] = {
278 STD_LONG_OPTS,
280 {"metric", required_argument, 0, 'm'},
281 {"driver", required_argument, 0, 'd'},
282 {"option", required_argument, 0, 'o'},
283 {"query", required_argument, 0, 'q'},
284 {"database", required_argument, 0, 'D'},
285 {0, 0, 0, 0}
286 };
288 while (1) {
289 c = getopt_long (argc, argv, "Vvht:c:w:m:H:d:o:q:D:",
290 longopts, &option);
292 if (c == EOF)
293 break;
295 switch (c) {
296 case '?': /* usage */
297 usage5 ();
298 case 'h': /* help */
299 print_help ();
300 exit (STATE_OK);
301 case 'V': /* version */
302 print_revision (progname, NP_VERSION);
303 exit (STATE_OK);
305 case 'c': /* critical range */
306 critical_range = optarg;
307 break;
308 case 'w': /* warning range */
309 warning_range = optarg;
310 break;
311 case 'm':
312 if (! strcasecmp (optarg, "CONN_TIME"))
313 metric = METRIC_CONN_TIME;
314 else if (! strcasecmp (optarg, "QUERY_RESULT"))
315 metric = METRIC_QUERY_RESULT;
316 else if (! strcasecmp (optarg, "QUERY_TIME"))
317 metric = METRIC_QUERY_TIME;
318 else
319 usage2 (_("Invalid metric"), optarg);
320 break;
321 case 't': /* timeout */
322 if (!is_intnonneg (optarg))
323 usage2 (_("Timeout interval must be a positive integer"), optarg);
324 else
325 timeout_interval = atoi (optarg);
327 case 'H': /* host */
328 if (!is_host (optarg))
329 usage2 (_("Invalid hostname/address"), optarg);
330 else
331 host = optarg;
332 break;
333 case 'v':
334 verbose++;
335 break;
337 case 'd':
338 np_dbi_driver = optarg;
339 break;
340 case 'o':
341 {
342 driver_option_t *new;
344 char *k, *v;
346 k = optarg;
347 v = strchr (k, (int)'=');
349 if (! v)
350 usage2 (_("Option must be '<key>=<value>'"), optarg);
352 *v = '\0';
353 ++v;
355 new = realloc (np_dbi_options,
356 (np_dbi_options_num + 1) * sizeof (*new));
357 if (! new) {
358 printf ("UNKOWN - failed to reallocate memory\n");
359 exit (STATE_UNKNOWN);
360 }
362 np_dbi_options = new;
363 new = np_dbi_options + np_dbi_options_num;
364 ++np_dbi_options_num;
366 new->key = k;
367 new->value = v;
368 }
369 break;
370 case 'q':
371 np_dbi_query = optarg;
372 break;
373 case 'D':
374 np_dbi_database = optarg;
375 break;
376 }
377 }
379 set_thresholds (&dbi_thresholds, warning_range, critical_range);
381 return validate_arguments ();
382 }
384 int
385 validate_arguments ()
386 {
387 if (! np_dbi_driver)
388 usage ("Must specify a DBI driver");
390 if (((metric == METRIC_QUERY_RESULT) || (metric == METRIC_QUERY_TIME))
391 && (! np_dbi_query))
392 usage ("Must specify a query to execute (metric == QUERY_RESULT)");
394 if ((metric != METRIC_CONN_TIME)
395 && (metric != METRIC_QUERY_RESULT)
396 && (metric != METRIC_QUERY_TIME))
397 usage ("Invalid metric specified");
399 return OK;
400 }
402 void
403 print_help (void)
404 {
405 print_revision (progname, NP_VERSION);
407 printf (COPYRIGHT, copyright, email);
409 printf (_("This program connects to an (SQL) database using DBI and checks the\n"
410 "specified metric against threshold levels. The default metric is\n"
411 "the result of the specified query.\n"));
413 printf ("\n\n");
415 print_usage ();
417 printf (UT_HELP_VRSN);
418 /* include this conditionally to avoid 'zero-length printf format string'
419 * compiler warnings */
420 #ifdef NP_EXTRA_OPTS
421 printf (UT_EXTRA_OPTS);
422 #endif
423 printf ("\n");
425 printf (" %s\n", "-d, --driver=STRING");
426 printf (" %s\n", _("DBI driver to use"));
427 printf (" %s\n", "-o, --option=STRING");
428 printf (" %s\n", _("DBI driver options"));
429 printf (" %s\n", "-q, --query=STRING");
430 printf (" %s\n", _("query to execute"));
431 printf ("\n");
433 printf (UT_WARN_CRIT_RANGE);
434 printf (" %s\n", "-m, --metric=METRIC");
435 printf (" %s\n", _("Metric to check thresholds against. Available metrics:"));
436 printf (" CONN_TIME - %s\n", _("time used for setting up the database connection"));
437 printf (" QUERY_RESULT - %s\n", _("result (first column of first row) of the query"));
438 printf (" QUERY_TIME - %s\n", _("time used to execute the query"));
439 printf (" %s\n", _("(ignore the query result)"));
440 printf ("\n");
442 printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
444 printf (UT_VERBOSE);
446 printf ("\n");
447 printf (" %s\n", _("A DBI driver (-d option) is required. If the specified metric operates"));
448 printf (" %s\n\n", _("on a query, one has to be specified (-q option)."));
450 printf (" %s\n", _("This plugin connects to an (SQL) database using libdbi and, optionally,"));
451 printf (" %s\n", _("executes the specified query. The first column of the first row of the"));
452 printf (" %s\n", _("result will be parsed and, in QUERY_RESULT mode, compared with the"));
453 printf (" %s\n", _("warning and critical ranges. The result from the query has to be numeric"));
454 printf (" %s\n\n", _("(strings representing numbers are fine)."));
456 printf (" %s\n", _("The number and type of required DBI driver options depends on the actual"));
457 printf (" %s\n", _("driver. See its documentation at http://libdbi-drivers.sourceforge.net/"));
458 printf (" %s\n\n", _("for details."));
460 printf (" %s\n", _("Examples:"));
461 printf (" check_dbi -d pgsql -o username=postgres -m QUERY_RESULT \\\n");
462 printf (" -q 'SELECT COUNT(*) FROM pg_stat_activity' -w 5 -c 10\n");
463 printf (" Warning if more than five connections; critical if more than ten.\n\n");
465 printf (" check_dbi -d mysql -H localhost -o username=user -o password=secret \\\n");
466 printf (" -q 'SELECT COUNT(*) FROM logged_in_users -w 5:20 -c 0:50\n");
467 printf (" Warning if less than 5 or more than 20 users are logged in; critical\n");
468 printf (" if more than 50 users.\n\n");
470 printf (" check_dbi -d firebird -o username=user -o password=secret -o dbname=foo \\\n");
471 printf (" -m CONN_TIME -w 0.5 -c 2\n");
472 printf (" Warning if connecting to the database takes more than half of a second;\n");
473 printf (" critical if it takes more than 2 seconds.\n");
475 printf (UT_SUPPORT);
476 }
478 void
479 print_usage (void)
480 {
481 printf ("%s\n", _("Usage:"));
482 printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] [-q <query>]\n", progname);
483 printf (" [-H <host>] [-c <critical range>] [-w <warning range>] [-m <metric>]\n");
484 }
486 #define CHECK_IGNORE_ERROR(s) \
487 do { \
488 if (metric != METRIC_QUERY_RESULT) \
489 return (s); \
490 } while (0)
492 double
493 get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
494 {
495 double val = NAN;
497 if (*field_type == DBI_TYPE_INTEGER) {
498 val = (double)dbi_result_get_longlong_idx (res, 1);
499 }
500 else if (*field_type == DBI_TYPE_DECIMAL) {
501 val = dbi_result_get_double_idx (res, 1);
502 }
503 else if (*field_type == DBI_TYPE_STRING) {
504 const char *val_str;
505 char *endptr = NULL;
507 val_str = dbi_result_get_string_idx (res, 1);
508 if ((! val_str) || (strcmp (val_str, "ERROR") == 0)) {
509 CHECK_IGNORE_ERROR (NAN);
510 np_dbi_print_error (conn, "CRITICAL - failed to fetch string value");
511 *field_type = DBI_TYPE_ERROR;
512 return NAN;
513 }
515 if (verbose > 2)
516 printf ("Query returned string '%s'\n", val_str);
518 val = strtod (val_str, &endptr);
519 if (endptr == val_str) {
520 CHECK_IGNORE_ERROR (NAN);
521 printf ("CRITICAL - result value is not a numeric: %s\n", val_str);
522 *field_type = DBI_TYPE_ERROR;
523 return NAN;
524 }
525 else if ((endptr != NULL) && (*endptr != '\0')) {
526 if (verbose)
527 printf ("Garbage after value: %s\n", endptr);
528 }
529 }
530 else {
531 CHECK_IGNORE_ERROR (NAN);
532 printf ("CRITICAL - cannot parse value of type %s (%i)\n",
533 (*field_type == DBI_TYPE_BINARY)
534 ? "BINARY"
535 : (*field_type == DBI_TYPE_DATETIME)
536 ? "DATETIME"
537 : "<unknown>",
538 *field_type);
539 *field_type = DBI_TYPE_ERROR;
540 return NAN;
541 }
542 return val;
543 }
545 double
546 get_query_result (dbi_conn conn, dbi_result res, double *res_val)
547 {
548 unsigned short field_type;
549 double val = NAN;
551 if (dbi_result_get_numrows (res) == DBI_ROW_ERROR) {
552 CHECK_IGNORE_ERROR (STATE_OK);
553 np_dbi_print_error (conn, "CRITICAL - failed to fetch rows");
554 return STATE_CRITICAL;
555 }
557 if (dbi_result_get_numrows (res) < 1) {
558 CHECK_IGNORE_ERROR (STATE_OK);
559 printf ("WARNING - no rows returned\n");
560 return STATE_WARNING;
561 }
563 if (dbi_result_get_numfields (res) == DBI_FIELD_ERROR) {
564 CHECK_IGNORE_ERROR (STATE_OK);
565 np_dbi_print_error (conn, "CRITICAL - failed to fetch fields");
566 return STATE_CRITICAL;
567 }
569 if (dbi_result_get_numfields (res) < 1) {
570 CHECK_IGNORE_ERROR (STATE_OK);
571 printf ("WARNING - no fields returned\n");
572 return STATE_WARNING;
573 }
575 if (dbi_result_first_row (res) != 1) {
576 CHECK_IGNORE_ERROR (STATE_OK);
577 np_dbi_print_error (conn, "CRITICAL - failed to fetch first row");
578 return STATE_CRITICAL;
579 }
581 field_type = dbi_result_get_field_type_idx (res, 1);
582 if (field_type != DBI_TYPE_ERROR)
583 val = get_field (conn, res, &field_type);
585 *res_val = val;
587 if (field_type == DBI_TYPE_ERROR) {
588 CHECK_IGNORE_ERROR (STATE_OK);
589 np_dbi_print_error (conn, "CRITICAL - failed to fetch data");
590 return STATE_CRITICAL;
591 }
593 dbi_result_free (res);
594 return STATE_OK;
595 }
597 #undef CHECK_IGNORE_ERROR
599 int
600 do_query (dbi_conn conn, double *res_val, double *res_time)
601 {
602 dbi_result res;
604 struct timeval timeval_start, timeval_end;
605 int status = STATE_OK;
607 assert (np_dbi_query);
609 if (verbose)
610 printf ("Executing query '%s'\n", np_dbi_query);
612 gettimeofday (&timeval_start, NULL);
614 res = dbi_conn_query (conn, np_dbi_query);
615 if (! res) {
616 np_dbi_print_error (conn, "CRITICAL - failed to execute query '%s'", np_dbi_query);
617 return STATE_CRITICAL;
618 }
620 status = get_query_result (conn, res, res_val);
622 gettimeofday (&timeval_end, NULL);
623 *res_time = timediff (timeval_start, timeval_end);
625 return status;
626 }
628 double
629 timediff (struct timeval start, struct timeval end)
630 {
631 double diff;
633 while (start.tv_usec > end.tv_usec) {
634 --end.tv_sec;
635 end.tv_usec += 1000000;
636 }
637 diff = (double)(end.tv_sec - start.tv_sec)
638 + (double)(end.tv_usec - start.tv_usec) / 1000000.0;
639 return diff;
640 }
642 void
643 np_dbi_print_error (dbi_conn conn, char *fmt, ...)
644 {
645 const char *errmsg = NULL;
646 va_list ap;
648 va_start (ap, fmt);
650 dbi_conn_error (conn, &errmsg);
651 vprintf (fmt, ap);
652 printf (": %s\n", errmsg);
654 va_end (ap);
655 }