summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--.gitignore1
-rw-r--r--REQUIREMENTS4
-rw-r--r--configure.in13
-rw-r--r--plugins/Makefile.am4
-rw-r--r--plugins/check_dbi.c817
-rw-r--r--plugins/t/check_dbi.t103
6 files changed, 941 insertions, 1 deletions
diff --git a/.gitignore b/.gitignore
index 0ffb91e8..52ca6aa4 100644
--- a/.gitignore
+++ b/.gitignore
@@ -134,6 +134,7 @@ NP-VERSION-FILE
134/plugins/check_by_ssh 134/plugins/check_by_ssh
135/plugins/check_clamd 135/plugins/check_clamd
136/plugins/check_cluster 136/plugins/check_cluster
137/plugins/check_dbi
137/plugins/check_dig 138/plugins/check_dig
138/plugins/check_disk 139/plugins/check_disk
139/plugins/check_dns 140/plugins/check_dns
diff --git a/REQUIREMENTS b/REQUIREMENTS
index fd41ded8..9f2eec0f 100644
--- a/REQUIREMENTS
+++ b/REQUIREMENTS
@@ -46,6 +46,10 @@ check_pqsql:
46 - Requires the PostgreSQL libraries available from 46 - Requires the PostgreSQL libraries available from
47 http://www.postgresql.org/ 47 http://www.postgresql.org/
48 48
49check_dbi:
50 - Requires the DBI libraries available from
51 http://libdbi.sourceforge.net/
52
49check_radius: 53check_radius:
50 - Requires the radiusclient-ng library available from: 54 - Requires the radiusclient-ng library available from:
51 http://developer.berlios.de/projects/radiusclient-ng/ 55 http://developer.berlios.de/projects/radiusclient-ng/
diff --git a/configure.in b/configure.in
index bf661095..1d4ed006 100644
--- a/configure.in
+++ b/configure.in
@@ -255,6 +255,19 @@ fi
255LIBS="$_SAVEDLIBS" 255LIBS="$_SAVEDLIBS"
256CPPFLAGS="$_SAVEDCPPFLAGS" 256CPPFLAGS="$_SAVEDCPPFLAGS"
257 257
258dnl Check for DBI libraries
259_SAVEDLIBS="$LIBS"
260AC_CHECK_LIB(dbi,dbi_initialize)
261if test "$ac_cv_lib_dbi_dbi_initialize" = "yes"; then
262 EXTRAS="$EXTRAS check_dbi"
263 DBILIBS="-ldbi"
264 AC_SUBST(DBILIBS)
265else
266 AC_MSG_WARN([Skipping dbi plugin])
267 AC_MSG_WARN([install DBI libs to compile this plugin (see REQUIREMENTS).])
268fi
269LIBS="$_SAVEDLIBS"
270
258dnl Check for radius libraries 271dnl Check for radius libraries
259_SAVEDLIBS="$LIBS" 272_SAVEDLIBS="$LIBS"
260AC_CHECK_LIB(radiusclient,rc_read_config) 273AC_CHECK_LIB(radiusclient,rc_read_config)
diff --git a/plugins/Makefile.am b/plugins/Makefile.am
index 3a2afc15..0eb0255b 100644
--- a/plugins/Makefile.am
+++ b/plugins/Makefile.am
@@ -37,7 +37,7 @@ check_tcp_programs = check_ftp check_imap check_nntp check_pop \
37EXTRA_PROGRAMS = check_mysql check_radius check_pgsql check_snmp check_hpjd \ 37EXTRA_PROGRAMS = check_mysql check_radius check_pgsql check_snmp check_hpjd \
38 check_swap check_fping check_ldap check_game check_dig \ 38 check_swap check_fping check_ldap check_game check_dig \
39 check_nagios check_by_ssh check_dns check_nt check_ide_smart \ 39 check_nagios check_by_ssh check_dns check_nt check_ide_smart \
40 check_procs check_mysql_query check_apt 40 check_procs check_mysql_query check_apt check_dbi
41 41
42EXTRA_DIST = t tests utils.c netutils.c sslutils.c popen.c utils.h netutils.h \ 42EXTRA_DIST = t tests utils.c netutils.c sslutils.c popen.c utils.h netutils.h \
43 popen.h common.h runcmd.c runcmd.h 43 popen.h common.h runcmd.c runcmd.h
@@ -64,6 +64,7 @@ test-debug:
64 64
65check_apt_LDADD = $(BASEOBJS) runcmd.o 65check_apt_LDADD = $(BASEOBJS) runcmd.o
66check_cluster_LDADD = $(BASEOBJS) 66check_cluster_LDADD = $(BASEOBJS)
67check_dbi_LDADD = $(NETLIBS) $(DBILIBS)
67check_dig_LDADD = $(NETLIBS) runcmd.o 68check_dig_LDADD = $(NETLIBS) runcmd.o
68check_disk_LDADD = $(BASEOBJS) popen.o 69check_disk_LDADD = $(BASEOBJS) popen.o
69check_dns_LDADD = $(NETLIBS) runcmd.o 70check_dns_LDADD = $(NETLIBS) runcmd.o
@@ -109,6 +110,7 @@ urlize_LDADD = $(BASEOBJS) popen.o
109 110
110check_apt_DEPENDENCIES = check_apt.c $(BASEOBJS) runcmd.o $(DEPLIBS) 111check_apt_DEPENDENCIES = check_apt.c $(BASEOBJS) runcmd.o $(DEPLIBS)
111check_cluster_DEPENDENCIES = check_cluster.c $(BASEOBJS) $(DEPLIBS) 112check_cluster_DEPENDENCIES = check_cluster.c $(BASEOBJS) $(DEPLIBS)
113check_dbi_DEPENDENCIES = check_dbi.c $(NETOBJS) $(DEPLIBS)
112check_dig_DEPENDENCIES = check_dig.c $(NETOBJS) runcmd.o $(DEPLIBS) 114check_dig_DEPENDENCIES = check_dig.c $(NETOBJS) runcmd.o $(DEPLIBS)
113check_disk_DEPENDENCIES = check_disk.c $(BASEOBJS) popen.o $(DEPLIBS) 115check_disk_DEPENDENCIES = check_disk.c $(BASEOBJS) popen.o $(DEPLIBS)
114check_dns_DEPENDENCIES = check_dns.c $(NETOBJS) runcmd.o $(DEPLIBS) 116check_dns_DEPENDENCIES = check_dns.c $(NETOBJS) runcmd.o $(DEPLIBS)
diff --git a/plugins/check_dbi.c b/plugins/check_dbi.c
new file mode 100644
index 00000000..8c4a511d
--- /dev/null
+++ b/plugins/check_dbi.c
@@ -0,0 +1,817 @@
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*****************************************************************************/
31
32const char *progname = "check_dbi";
33const char *copyright = "2011";
34const char *email = "nagiosplug-devel@lists.sourceforge.net";
35
36#include "common.h"
37#include "utils.h"
38
39#include "netutils.h"
40
41#include "regex.h"
42
43/* required for NAN */
44#ifndef _ISOC99_SOURCE
45#define _ISOC99_SOURCE
46#endif
47
48#include <assert.h>
49#include <math.h>
50
51#include <dbi/dbi.h>
52
53#include <stdarg.h>
54
55typedef enum {
56 METRIC_CONN_TIME,
57 METRIC_SERVER_VERSION,
58 METRIC_QUERY_RESULT,
59 METRIC_QUERY_TIME,
60} np_dbi_metric_t;
61
62typedef enum {
63 TYPE_NUMERIC,
64 TYPE_STRING,
65} np_dbi_type_t;
66
67typedef struct {
68 char *key;
69 char *value;
70} driver_option_t;
71
72char *host = NULL;
73int verbose = 0;
74
75char *warning_range = NULL;
76char *critical_range = NULL;
77thresholds *dbi_thresholds = NULL;
78
79char *expect = NULL;
80
81regex_t expect_re;
82char *expect_re_str = NULL;
83int expect_re_cflags = 0;
84
85np_dbi_metric_t metric = METRIC_QUERY_RESULT;
86np_dbi_type_t type = TYPE_NUMERIC;
87
88char *np_dbi_driver = NULL;
89driver_option_t *np_dbi_options = NULL;
90int np_dbi_options_num = 0;
91char *np_dbi_database = NULL;
92char *np_dbi_query = NULL;
93
94int process_arguments (int, char **);
95int validate_arguments (void);
96void print_usage (void);
97void print_help (void);
98
99double timediff (struct timeval, struct timeval);
100
101void np_dbi_print_error (dbi_conn, char *, ...);
102
103int do_query (dbi_conn, const char **, double *, double *);
104
105int
106main (int argc, char **argv)
107{
108 int status = STATE_UNKNOWN;
109
110 dbi_driver driver;
111 dbi_conn conn;
112
113 unsigned int server_version;
114
115 struct timeval start_timeval, end_timeval;
116 double conn_time = 0.0;
117 double query_time = 0.0;
118
119 const char *query_val_str = NULL;
120 double query_val = 0.0;
121
122 int i;
123
124 setlocale (LC_ALL, "");
125 bindtextdomain (PACKAGE, LOCALEDIR);
126 textdomain (PACKAGE);
127
128 /* Parse extra opts if any */
129 argv = np_extra_opts (&argc, argv, progname);
130
131 if (process_arguments (argc, argv) == ERROR)
132 usage4 (_("Could not parse arguments"));
133
134 /* Set signal handling and alarm */
135 if (signal (SIGALRM, timeout_alarm_handler) == SIG_ERR) {
136 usage4 (_("Cannot catch SIGALRM"));
137 }
138 alarm (timeout_interval);
139
140 if (verbose > 2)
141 printf ("Initializing DBI\n");
142
143 if (dbi_initialize (NULL) < 0) {
144 printf ("UNKNOWN - failed to initialize DBI; possibly you don't have any drivers installed.\n");
145 return STATE_UNKNOWN;
146 }
147
148 if (verbose)
149 printf ("Opening DBI driver '%s'\n", np_dbi_driver);
150
151 driver = dbi_driver_open (np_dbi_driver);
152 if (! driver) {
153 printf ("UNKNOWN - failed to open DBI driver '%s'; possibly it's not installed.\n",
154 np_dbi_driver);
155
156 printf ("Known drivers:\n");
157 for (driver = dbi_driver_list (NULL); driver; driver = dbi_driver_list (driver)) {
158 printf (" - %s\n", dbi_driver_get_name (driver));
159 }
160 return STATE_UNKNOWN;
161 }
162
163 /* make a connection to the database */
164 gettimeofday (&start_timeval, NULL);
165
166 conn = dbi_conn_open (driver);
167 if (! conn) {
168 printf ("UNKNOWN - failed top open connection object.\n");
169 dbi_conn_close (conn);
170 return STATE_UNKNOWN;
171 }
172
173 for (i = 0; i < np_dbi_options_num; ++i) {
174 const char *opt;
175
176 if (verbose > 1)
177 printf ("Setting DBI driver option '%s' to '%s'\n",
178 np_dbi_options[i].key, np_dbi_options[i].value);
179
180 if (! dbi_conn_set_option (conn, np_dbi_options[i].key, np_dbi_options[i].value))
181 continue;
182 /* else: status != 0 */
183
184 np_dbi_print_error (conn, "UNKNOWN - failed to set option '%s' to '%s'",
185 np_dbi_options[i].key, np_dbi_options[i].value);
186 printf ("Known driver options:\n");
187
188 for (opt = dbi_conn_get_option_list (conn, NULL); opt;
189 opt = dbi_conn_get_option_list (conn, opt)) {
190 printf (" - %s\n", opt);
191 }
192 dbi_conn_close (conn);
193 return STATE_UNKNOWN;
194 }
195
196 if (host) {
197 if (verbose > 1)
198 printf ("Setting DBI driver option 'host' to '%s'\n", host);
199 dbi_conn_set_option (conn, "host", host);
200 }
201
202 if (verbose) {
203 const char *dbname, *host;
204
205 dbname = dbi_conn_get_option (conn, "dbname");
206 host = dbi_conn_get_option (conn, "host");
207
208 if (! dbname)
209 dbname = "<unspecified>";
210 if (! host)
211 host = "<unspecified>";
212
213 printf ("Connecting to database '%s' at host '%s'\n",
214 dbname, host);
215 }
216
217 if (dbi_conn_connect (conn) < 0) {
218 np_dbi_print_error (conn, "UNKOWN - failed to connect to database");
219 return STATE_UNKNOWN;
220 }
221
222 gettimeofday (&end_timeval, NULL);
223 conn_time = timediff (start_timeval, end_timeval);
224
225 server_version = dbi_conn_get_engine_version (conn);
226 if (verbose)
227 printf ("Connected to server version %u\n", server_version);
228
229 if (metric == METRIC_SERVER_VERSION)
230 status = get_status (server_version, dbi_thresholds);
231
232 if (verbose)
233 printf ("Time elapsed: %f\n", conn_time);
234
235 if (metric == METRIC_CONN_TIME)
236 status = get_status (conn_time, dbi_thresholds);
237
238 /* select a database */
239 if (np_dbi_database) {
240 if (verbose > 1)
241 printf ("Selecting database '%s'\n", np_dbi_database);
242
243 if (dbi_conn_select_db (conn, np_dbi_database)) {
244 np_dbi_print_error (conn, "UNKOWN - failed to select database '%s'",
245 np_dbi_database);
246 return STATE_UNKNOWN;
247 }
248 }
249
250 if (np_dbi_query) {
251 /* execute query */
252 status = do_query (conn, &query_val_str, &query_val, &query_time);
253 if (status != STATE_OK)
254 /* do_query prints an error message in this case */
255 return status;
256
257 if (metric == METRIC_QUERY_RESULT) {
258 if (expect) {
259 if ((! query_val_str) || strcmp (query_val_str, expect))
260 status = STATE_CRITICAL;
261 else
262 status = STATE_OK;
263 }
264 else if (expect_re_str) {
265 int err;
266
267 err = regexec (&expect_re, query_val_str, 0, NULL, /* flags = */ 0);
268 if (! err)
269 status = STATE_OK;
270 else if (err == REG_NOMATCH)
271 status = STATE_CRITICAL;
272 else {
273 char errmsg[1024];
274 regerror (err, &expect_re, errmsg, sizeof (errmsg));
275 printf ("ERROR - failed to execute regular expression: %s\n",
276 errmsg);
277 status = STATE_CRITICAL;
278 }
279 }
280 else
281 status = get_status (query_val, dbi_thresholds);
282 }
283 else if (metric == METRIC_QUERY_TIME)
284 status = get_status (query_time, dbi_thresholds);
285 }
286
287 if (verbose)
288 printf("Closing connection\n");
289 dbi_conn_close (conn);
290
291 /* In case of METRIC_QUERY_RESULT, isnan(query_val) indicates an error
292 * which should have been reported and handled (abort) before
293 * ... unless we expected a string to be returned */
294 assert ((metric != METRIC_QUERY_RESULT) || (! isnan (query_val))
295 || (type == TYPE_STRING));
296
297 assert ((type != TYPE_STRING) || (expect || expect_re_str));
298
299 printf ("%s - connection time: %fs", state_text (status), conn_time);
300 if (np_dbi_query) {
301 if (type == TYPE_STRING) {
302 assert (expect || expect_re_str);
303 printf (", '%s' returned '%s' in %fs", np_dbi_query,
304 query_val_str ? query_val_str : "<nothing>", query_time);
305 if (status != STATE_OK) {
306 if (expect)
307 printf (" (expected '%s')", expect);
308 else if (expect_re_str)
309 printf (" (expected regex /%s/%s)", expect_re_str,
310 ((expect_re_cflags & REG_ICASE) ? "i" : ""));
311 }
312 }
313 else if (isnan (query_val))
314 printf (", '%s' query execution time: %fs", np_dbi_query, query_time);
315 else
316 printf (", '%s' returned %f in %fs", np_dbi_query, query_val, query_time);
317 }
318
319 printf (" | conntime=%fs;%s;%s;0; server_version=%u;%s;%s;0;", conn_time,
320 ((metric == METRIC_CONN_TIME) && warning_range) ? warning_range : "",
321 ((metric == METRIC_CONN_TIME) && critical_range) ? critical_range : "",
322 server_version,
323 ((metric == METRIC_SERVER_VERSION) && warning_range) ? warning_range : "",
324 ((metric == METRIC_SERVER_VERSION) && critical_range) ? critical_range : "");
325 if (np_dbi_query) {
326 if (! isnan (query_val)) /* this is also true when -e is used */
327 printf (" query=%f;%s;%s;;", query_val,
328 ((metric == METRIC_QUERY_RESULT) && warning_range) ? warning_range : "",
329 ((metric == METRIC_QUERY_RESULT) && critical_range) ? critical_range : "");
330 printf (" querytime=%fs;%s;%s;0;", query_time,
331 ((metric == METRIC_QUERY_TIME) && warning_range) ? warning_range : "",
332 ((metric == METRIC_QUERY_TIME) && critical_range) ? critical_range : "");
333 }
334 printf ("\n");
335 return status;
336}
337
338/* process command-line arguments */
339int
340process_arguments (int argc, char **argv)
341{
342 int c;
343
344 int option = 0;
345 static struct option longopts[] = {
346 STD_LONG_OPTS,
347
348 {"expect", required_argument, 0, 'e'},
349 {"regex", required_argument, 0, 'r'},
350 {"regexi", required_argument, 0, 'R'},
351 {"metric", required_argument, 0, 'm'},
352 {"driver", required_argument, 0, 'd'},
353 {"option", required_argument, 0, 'o'},
354 {"query", required_argument, 0, 'q'},
355 {"database", required_argument, 0, 'D'},
356 {0, 0, 0, 0}
357 };
358
359 while (1) {
360 c = getopt_long (argc, argv, "Vvht:c:w:e:r:R:m:H:d:o:q:D:",
361 longopts, &option);
362
363 if (c == EOF)
364 break;
365
366 switch (c) {
367 case '?': /* usage */
368 usage5 ();
369 case 'h': /* help */
370 print_help ();
371 exit (STATE_OK);
372 case 'V': /* version */
373 print_revision (progname, NP_VERSION);
374 exit (STATE_OK);
375
376 case 'c': /* critical range */
377 critical_range = optarg;
378 type = TYPE_NUMERIC;
379 break;
380 case 'w': /* warning range */
381 warning_range = optarg;
382 type = TYPE_NUMERIC;
383 break;
384 case 'e':
385 expect = optarg;
386 type = TYPE_STRING;
387 break;
388 case 'R':
389 expect_re_cflags = REG_ICASE;
390 /* fall through */
391 case 'r':
392 {
393 int err;
394
395 expect_re_cflags |= REG_EXTENDED | REG_NOSUB | REG_NEWLINE;
396 expect_re_str = optarg;
397 type = TYPE_STRING;
398
399 err = regcomp (&expect_re, expect_re_str, expect_re_cflags);
400 if (err) {
401 char errmsg[1024];
402 regerror (err, &expect_re, errmsg, sizeof (errmsg));
403 printf ("ERROR - failed to compile regular expression: %s\n",
404 errmsg);
405 return ERROR;
406 }
407 break;
408 }
409
410 case 'm':
411 if (! strcasecmp (optarg, "CONN_TIME"))
412 metric = METRIC_CONN_TIME;
413 else if (! strcasecmp (optarg, "SERVER_VERSION"))
414 metric = METRIC_SERVER_VERSION;
415 else if (! strcasecmp (optarg, "QUERY_RESULT"))
416 metric = METRIC_QUERY_RESULT;
417 else if (! strcasecmp (optarg, "QUERY_TIME"))
418 metric = METRIC_QUERY_TIME;
419 else
420 usage2 (_("Invalid metric"), optarg);
421 break;
422 case 't': /* timeout */
423 if (!is_intnonneg (optarg))
424 usage2 (_("Timeout interval must be a positive integer"), optarg);
425 else
426 timeout_interval = atoi (optarg);
427
428 case 'H': /* host */
429 if (!is_host (optarg))
430 usage2 (_("Invalid hostname/address"), optarg);
431 else
432 host = optarg;
433 break;
434 case 'v':
435 verbose++;
436 break;
437
438 case 'd':
439 np_dbi_driver = optarg;
440 break;
441 case 'o':
442 {
443 driver_option_t *new;
444
445 char *k, *v;
446
447 k = optarg;
448 v = strchr (k, (int)'=');
449
450 if (! v)
451 usage2 (_("Option must be '<key>=<value>'"), optarg);
452
453 *v = '\0';
454 ++v;
455
456 new = realloc (np_dbi_options,
457 (np_dbi_options_num + 1) * sizeof (*new));
458 if (! new) {
459 printf ("UNKOWN - failed to reallocate memory\n");
460 exit (STATE_UNKNOWN);
461 }
462
463 np_dbi_options = new;
464 new = np_dbi_options + np_dbi_options_num;
465 ++np_dbi_options_num;
466
467 new->key = k;
468 new->value = v;
469 }
470 break;
471 case 'q':
472 np_dbi_query = optarg;
473 break;
474 case 'D':
475 np_dbi_database = optarg;
476 break;
477 }
478 }
479
480 set_thresholds (&dbi_thresholds, warning_range, critical_range);
481
482 return validate_arguments ();
483}
484
485int
486validate_arguments ()
487{
488 if (! np_dbi_driver)
489 usage ("Must specify a DBI driver");
490
491 if (((metric == METRIC_QUERY_RESULT) || (metric == METRIC_QUERY_TIME))
492 && (! np_dbi_query))
493 usage ("Must specify a query to execute (metric == QUERY_RESULT)");
494
495 if ((metric != METRIC_CONN_TIME)
496 && (metric != METRIC_SERVER_VERSION)
497 && (metric != METRIC_QUERY_RESULT)
498 && (metric != METRIC_QUERY_TIME))
499 usage ("Invalid metric specified");
500
501 if (expect && (warning_range || critical_range || expect_re_str))
502 usage ("Do not mix -e and -w/-c/-r/-R");
503
504 if (expect_re_str && (warning_range || critical_range || expect))
505 usage ("Do not mix -r/-R and -w/-c/-e");
506
507 if (expect && (metric != METRIC_QUERY_RESULT))
508 usage ("Option -e requires metric QUERY_RESULT");
509
510 if (expect_re_str && (metric != METRIC_QUERY_RESULT))
511 usage ("Options -r/-R require metric QUERY_RESULT");
512
513 return OK;
514}
515
516void
517print_help (void)
518{
519 print_revision (progname, NP_VERSION);
520
521 printf (COPYRIGHT, copyright, email);
522
523 printf (_("This program connects to an (SQL) database using DBI and checks the\n"
524 "specified metric against threshold levels. The default metric is\n"
525 "the result of the specified query.\n"));
526
527 printf ("\n\n");
528
529 print_usage ();
530
531 printf (UT_HELP_VRSN);
532/* include this conditionally to avoid 'zero-length printf format string'
533 * compiler warnings */
534#ifdef NP_EXTRA_OPTS
535 printf (UT_EXTRA_OPTS);
536#endif
537 printf ("\n");
538
539 printf (" %s\n", "-d, --driver=STRING");
540 printf (" %s\n", _("DBI driver to use"));
541 printf (" %s\n", "-o, --option=STRING");
542 printf (" %s\n", _("DBI driver options"));
543 printf (" %s\n", "-q, --query=STRING");
544 printf (" %s\n", _("query to execute"));
545 printf ("\n");
546
547 printf (UT_WARN_CRIT_RANGE);
548 printf (" %s\n", "-e, --expect=STRING");
549 printf (" %s\n", _("String to expect as query result"));
550 printf (" %s\n", _("Do not mix with -w, -c, -r, or -R!"));
551 printf (" %s\n", "-r, --regex=REGEX");
552 printf (" %s\n", _("Extended POSIX regular expression to check query result against"));
553 printf (" %s\n", _("Do not mix with -w, -c, -e, or -R!"));
554 printf (" %s\n", "-R, --regexi=REGEX");
555 printf (" %s\n", _("Case-insensitive extended POSIX regex to check query result against"));
556 printf (" %s\n", _("Do not mix with -w, -c, -e, or -r!"));
557 printf (" %s\n", "-m, --metric=METRIC");
558 printf (" %s\n", _("Metric to check thresholds against. Available metrics:"));
559 printf (" CONN_TIME - %s\n", _("time used for setting up the database connection"));
560 printf (" QUERY_RESULT - %s\n", _("result (first column of first row) of the query"));
561 printf (" QUERY_TIME - %s\n", _("time used to execute the query"));
562 printf (" %s\n", _("(ignore the query result)"));
563 printf ("\n");
564
565 printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
566
567 printf (UT_VERBOSE);
568
569 printf ("\n");
570 printf (" %s\n", _("A DBI driver (-d option) is required. If the specified metric operates"));
571 printf (" %s\n\n", _("on a query, one has to be specified (-q option)."));
572
573 printf (" %s\n", _("This plugin connects to an (SQL) database using libdbi and, optionally,"));
574 printf (" %s\n", _("executes the specified query. The first column of the first row of the"));
575 printf (" %s\n", _("result will be parsed and, in QUERY_RESULT mode, compared with the"));
576 printf (" %s\n", _("warning and critical ranges. The result from the query has to be numeric"));
577 printf (" %s\n\n", _("(strings representing numbers are fine)."));
578
579 printf (" %s\n", _("The number and type of required DBI driver options depends on the actual"));
580 printf (" %s\n", _("driver. See its documentation at http://libdbi-drivers.sourceforge.net/"));
581 printf (" %s\n\n", _("for details."));
582
583 printf (" %s\n", _("Examples:"));
584 printf (" check_dbi -d pgsql -o username=postgres -m QUERY_RESULT \\\n");
585 printf (" -q 'SELECT COUNT(*) FROM pg_stat_activity' -w 5 -c 10\n");
586 printf (" Warning if more than five connections; critical if more than ten.\n\n");
587
588 printf (" check_dbi -d mysql -H localhost -o username=user -o password=secret \\\n");
589 printf (" -q 'SELECT COUNT(*) FROM logged_in_users -w 5:20 -c 0:50\n");
590 printf (" Warning if less than 5 or more than 20 users are logged in; critical\n");
591 printf (" if more than 50 users.\n\n");
592
593 printf (" check_dbi -d firebird -o username=user -o password=secret -o dbname=foo \\\n");
594 printf (" -m CONN_TIME -w 0.5 -c 2\n");
595 printf (" Warning if connecting to the database takes more than half of a second;\n");
596 printf (" critical if it takes more than 2 seconds.\n\n");
597
598 printf (" check_dbi -d mysql -H localhost -o username=user \\\n");
599 printf (" -q 'SELECT concat(@@version, \" \", @@version_comment)' \\\n");
600 printf (" -r '^5\\.[01].*MySQL Enterprise Server'\n");
601 printf (" Critical if the database server is not a MySQL enterprise server in either\n");
602 printf (" version 5.0.x or 5.1.x.\n\n");
603
604 printf (" check_dbi -d pgsql -u username=user -m SERVER_VERSION \\\n");
605 printf (" -w 090000:090099 -c 090000:090199\n");
606 printf (" Warn if the PostgreSQL server version is not 9.0.x; critical if the version\n");
607 printf (" is less than 9.x or higher than 9.1.x.\n");
608
609 printf (UT_SUPPORT);
610}
611
612void
613print_usage (void)
614{
615 printf ("%s\n", _("Usage:"));
616 printf ("%s -d <DBI driver> [-o <DBI driver option> [...]] [-q <query>]\n", progname);
617 printf (" [-H <host>] [-c <critical range>] [-w <warning range>] [-m <metric>]\n");
618 printf (" [-e <string>] [-r|-R <regex>]\n");
619}
620
621#define CHECK_IGNORE_ERROR(s) \
622 do { \
623 if (metric != METRIC_QUERY_RESULT) \
624 return (s); \
625 } while (0)
626
627const char *
628get_field_str (dbi_conn conn, dbi_result res, unsigned short field_type)
629{
630 const char *str;
631
632 if (field_type != DBI_TYPE_STRING) {
633 printf ("CRITICAL - result value is not a string\n");
634 return NULL;
635 }
636
637 str = dbi_result_get_string_idx (res, 1);
638 if ((! str) || (strcmp (str, "ERROR") == 0)) {
639 CHECK_IGNORE_ERROR (NULL);
640 np_dbi_print_error (conn, "CRITICAL - failed to fetch string value");
641 return NULL;
642 }
643
644 if ((verbose && (type == TYPE_STRING)) || (verbose > 2))
645 printf ("Query returned string '%s'\n", str);
646 return str;
647}
648
649double
650get_field (dbi_conn conn, dbi_result res, unsigned short *field_type)
651{
652 double val = NAN;
653
654 if (*field_type == DBI_TYPE_INTEGER) {
655 val = (double)dbi_result_get_longlong_idx (res, 1);
656 }
657 else if (*field_type == DBI_TYPE_DECIMAL) {
658 val = dbi_result_get_double_idx (res, 1);
659 }
660 else if (*field_type == DBI_TYPE_STRING) {
661 const char *val_str;
662 char *endptr = NULL;
663
664 val_str = get_field_str (conn, res, *field_type);
665 if (! val_str) {
666 CHECK_IGNORE_ERROR (NAN);
667 *field_type = DBI_TYPE_ERROR;
668 return NAN;
669 }
670
671 val = strtod (val_str, &endptr);
672 if (endptr == val_str) {
673 CHECK_IGNORE_ERROR (NAN);
674 printf ("CRITICAL - result value is not a numeric: %s\n", val_str);
675 *field_type = DBI_TYPE_ERROR;
676 return NAN;
677 }
678 else if ((endptr != NULL) && (*endptr != '\0')) {
679 if (verbose)
680 printf ("Garbage after value: %s\n", endptr);
681 }
682 }
683 else {
684 CHECK_IGNORE_ERROR (NAN);
685 printf ("CRITICAL - cannot parse value of type %s (%i)\n",
686 (*field_type == DBI_TYPE_BINARY)
687 ? "BINARY"
688 : (*field_type == DBI_TYPE_DATETIME)
689 ? "DATETIME"
690 : "<unknown>",
691 *field_type);
692 *field_type = DBI_TYPE_ERROR;
693 return NAN;
694 }
695 return val;
696}
697
698double
699get_query_result (dbi_conn conn, dbi_result res, const char **res_val_str, double *res_val)
700{
701 unsigned short field_type;
702 double val = NAN;
703
704 if (dbi_result_get_numrows (res) == DBI_ROW_ERROR) {
705 CHECK_IGNORE_ERROR (STATE_OK);
706 np_dbi_print_error (conn, "CRITICAL - failed to fetch rows");
707 return STATE_CRITICAL;
708 }
709
710 if (dbi_result_get_numrows (res) < 1) {
711 CHECK_IGNORE_ERROR (STATE_OK);
712 printf ("WARNING - no rows returned\n");
713 return STATE_WARNING;
714 }
715
716 if (dbi_result_get_numfields (res) == DBI_FIELD_ERROR) {
717 CHECK_IGNORE_ERROR (STATE_OK);
718 np_dbi_print_error (conn, "CRITICAL - failed to fetch fields");
719 return STATE_CRITICAL;
720 }
721
722 if (dbi_result_get_numfields (res) < 1) {
723 CHECK_IGNORE_ERROR (STATE_OK);
724 printf ("WARNING - no fields returned\n");
725 return STATE_WARNING;
726 }
727
728 if (dbi_result_first_row (res) != 1) {
729 CHECK_IGNORE_ERROR (STATE_OK);
730 np_dbi_print_error (conn, "CRITICAL - failed to fetch first row");
731 return STATE_CRITICAL;
732 }
733
734 field_type = dbi_result_get_field_type_idx (res, 1);
735 if (field_type != DBI_TYPE_ERROR) {
736 if (type == TYPE_STRING)
737 /* the value will be freed in dbi_result_free */
738 *res_val_str = strdup (get_field_str (conn, res, field_type));
739 else
740 val = get_field (conn, res, &field_type);
741 }
742
743 *res_val = val;
744
745 if (field_type == DBI_TYPE_ERROR) {
746 CHECK_IGNORE_ERROR (STATE_OK);
747 np_dbi_print_error (conn, "CRITICAL - failed to fetch data");
748 return STATE_CRITICAL;
749 }
750
751 dbi_result_free (res);
752 return STATE_OK;
753}
754
755#undef CHECK_IGNORE_ERROR
756
757int
758do_query (dbi_conn conn, const char **res_val_str, double *res_val, double *res_time)
759{
760 dbi_result res;
761
762 struct timeval timeval_start, timeval_end;
763 int status = STATE_OK;
764
765 assert (np_dbi_query);
766
767 if (verbose)
768 printf ("Executing query '%s'\n", np_dbi_query);
769
770 gettimeofday (&timeval_start, NULL);
771
772 res = dbi_conn_query (conn, np_dbi_query);
773 if (! res) {
774 np_dbi_print_error (conn, "CRITICAL - failed to execute query '%s'", np_dbi_query);
775 return STATE_CRITICAL;
776 }
777
778 status = get_query_result (conn, res, res_val_str, res_val);
779
780 gettimeofday (&timeval_end, NULL);
781 *res_time = timediff (timeval_start, timeval_end);
782
783 if (verbose)
784 printf ("Time elapsed: %f\n", *res_time);
785
786 return status;
787}
788
789double
790timediff (struct timeval start, struct timeval end)
791{
792 double diff;
793
794 while (start.tv_usec > end.tv_usec) {
795 --end.tv_sec;
796 end.tv_usec += 1000000;
797 }
798 diff = (double)(end.tv_sec - start.tv_sec)
799 + (double)(end.tv_usec - start.tv_usec) / 1000000.0;
800 return diff;
801}
802
803void
804np_dbi_print_error (dbi_conn conn, char *fmt, ...)
805{
806 const char *errmsg = NULL;
807 va_list ap;
808
809 va_start (ap, fmt);
810
811 dbi_conn_error (conn, &errmsg);
812 vprintf (fmt, ap);
813 printf (": %s\n", errmsg);
814
815 va_end (ap);
816}
817
diff --git a/plugins/t/check_dbi.t b/plugins/t/check_dbi.t
new file mode 100644
index 00000000..c24b5a8c
--- /dev/null
+++ b/plugins/t/check_dbi.t
@@ -0,0 +1,103 @@
1#! /usr/bin/perl -w -I ..
2#
3# Database Server Tests via check_dbi
4#
5#
6# Uses the 'sqlite3' DBD driver and command line utility.
7
8use strict;
9use Test::More;
10use NPTest;
11
12use File::Temp;
13
14use vars qw($tests);
15
16plan skip_all => "check_dbi not compiled" unless (-x "check_dbi");
17
18$tests = 20;
19plan tests => $tests;
20
21my $missing_driver_output = "failed to open DBI driver 'sqlite3'";
22
23my $bad_driver_output = "/failed to open DBI driver 'nodriver'/";
24my $conn_time_output = "/OK - connection time: [0-9\.]+s \|/";
25my $missing_query_output = "/Must specify a query to execute/";
26my $no_rows_output = "/WARNING - no rows returned/";
27my $not_numeric_output = "/CRITICAL - result value is not a numeric:/";
28my $query_time_output = "/OK - connection time: [0-9\.]+s, 'SELECT 1' returned 1.000000 in [0-9\.]+s \|/";
29my $syntax_error_output = "/CRITICAL - failed to execute query 'GET ALL FROM test': 1: near \"GET\": syntax error/";
30
31my $result;
32
33SKIP: {
34 my $sqlite3 = qx(which sqlite3 2> /dev/null);
35 chomp($sqlite3);
36
37 skip "No Sqlite3 found", $tests unless $sqlite3;
38
39 my $sqlite3_check = qx(./check_dbi -d sqlite3 -q '');
40 if ($sqlite3_check =~ m/$missing_driver_output/) {
41 skip "No 'sqlite3' DBD driver found", $tests;
42 }
43
44 my $fh = File::Temp->new(
45 TEMPLATE => "/tmp/check_dbi_sqlite3.XXXXXXX",
46 UNLINK => 1,
47 );
48 my $filename = $fh->filename;
49 $filename =~ s/^\/tmp\///;
50
51 system("$sqlite3 /tmp/$filename 'CREATE TABLE test(a INT, b TEXT)'");
52 system("$sqlite3 /tmp/$filename 'INSERT INTO test VALUES (1, \"text1\")'");
53 system("$sqlite3 /tmp/$filename 'INSERT INTO test VALUES (2, \"text2\")'");
54
55 my $check_cmd = "./check_dbi -d sqlite3 -o sqlite3_dbdir=/tmp -o dbname=$filename";
56
57 $result = NPTest->testCmd("$check_cmd -q 'SELECT 1'");
58 cmp_ok($result->return_code, '==', 0, "Sqlite3 login okay and can run query");
59
60 $result = NPTest->testCmd("$check_cmd");
61 cmp_ok($result->return_code, '==', 3, "Missing query parameter");
62 like($result->output, $missing_query_output, "Missing query parameter error message");
63
64 $result = NPTest->testCmd("$check_cmd -q 'GET ALL FROM test'");
65 cmp_ok($result->return_code, '==', 2, "Invalid query");
66 like($result->output, $syntax_error_output, "Syntax error message");
67
68 $result = NPTest->testCmd("$check_cmd -q 'SELECT 2.71828' -w 2 -c 3");
69 cmp_ok($result->return_code, '==', 1, "Got warning");
70
71 $result = NPTest->testCmd("$check_cmd -q 'SELECT 3.1415' -w 2 -c 3");
72 cmp_ok($result->return_code, '==', 2, "Got critical");
73
74 $result = NPTest->testCmd("$check_cmd -q ''");
75 cmp_ok($result->return_code, '==', 1, "No rows returned");
76 like($result->output, $no_rows_output, "Now rows returned warning message");
77
78 $result = NPTest->testCmd("$check_cmd -q 'SELECT b FROM test'");
79 cmp_ok($result->return_code, '==', 2, "Value is not a numeric");
80 like($result->output, $not_numeric_output, "Value is not a numeric error message");
81
82 $result = NPTest->testCmd("$check_cmd -m QUERY_RESULT -q 'SELECT b FROM test' -e text1");
83 cmp_ok($result->return_code, '==', 0, "Query result string comparison okay");
84
85 $result = NPTest->testCmd("$check_cmd -q 'SELECT b FROM test' -r 'eXt[0-9]'");
86 cmp_ok($result->return_code, '==', 2, "Query result case-insensitive regex failure");
87
88 $result = NPTest->testCmd("$check_cmd -q 'SELECT b FROM test' -R 'eXt[0-9]'");
89 cmp_ok($result->return_code, '==', 0, "Query result case-sensitive regex okay");
90
91 $result = NPTest->testCmd("$check_cmd -m CONN_TIME -w 0.5 -c 0.7");
92 cmp_ok($result->return_code, '==', 0, "CONN_TIME metric okay");
93 like($result->output, $conn_time_output, "CONN_TIME metric output okay");
94
95 $result = NPTest->testCmd("$check_cmd -m QUERY_TIME -q 'SELECT 1'");
96 cmp_ok($result->return_code, '==', 0, "QUERY_TIME metric okay");
97 like($result->output, $query_time_output, "QUERY_TIME metric output okay");
98
99 $result = NPTest->testCmd("./check_dbi -d nodriver -q ''");
100 cmp_ok($result->return_code, '==', 3, "Unknown DBI driver");
101 like($result->output, $bad_driver_output, "Correct error message");
102}
103