summaryrefslogtreecommitdiffstats
path: root/plugins/check_pgsql.c
diff options
context:
space:
mode:
authorSebastian Harl <sh@teamix.net>2011-04-06 16:51:37 +0200
committerSebastian Harl <sh@teamix.net>2012-07-05 11:36:57 +0200
commit912df3ef9b188c82893dace1e9b56c42a558fdba (patch)
treec61361eb6584bbda1db05c76db904ce4a5b285ba /plugins/check_pgsql.c
parentc98223f44175fcfc2ee550349d238db1146b7d2d (diff)
downloadmonitoring-plugins-912df3ef9b188c82893dace1e9b56c42a558fdba.tar.gz
check_pgsql: Added support for executing queries.
The query result (the double value of the first column in the first row, to be precise) will be checked against threshold ranges specified using the -C and -W options. Note that this also allows to query PostgreSQL internal values using the information available from the database daemon's "statistics collector" -- see the chapter "Monitoring Database Activity" in the PostgreSQL manual for details.
Diffstat (limited to 'plugins/check_pgsql.c')
-rw-r--r--plugins/check_pgsql.c126
1 files changed, 115 insertions, 11 deletions
diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c
index 69edae75..edad116a 100644
--- a/plugins/check_pgsql.c
+++ b/plugins/check_pgsql.c
@@ -56,6 +56,7 @@ void print_usage (void);
56void print_help (void); 56void print_help (void);
57int is_pg_dbname (char *); 57int is_pg_dbname (char *);
58int is_pg_logname (char *); 58int is_pg_logname (char *);
59int do_query (PGconn *, char *);
59 60
60char *pghost = NULL; /* host name of the backend server */ 61char *pghost = NULL; /* host name of the backend server */
61char *pgport = NULL; /* port of the backend server */ 62char *pgport = NULL; /* port of the backend server */
@@ -67,12 +68,12 @@ char *pguser = NULL;
67char *pgpasswd = NULL; 68char *pgpasswd = NULL;
68double twarn = (double)DEFAULT_WARN; 69double twarn = (double)DEFAULT_WARN;
69double tcrit = (double)DEFAULT_CRIT; 70double tcrit = (double)DEFAULT_CRIT;
71char *pgquery = NULL;
72char *query_warning = NULL;
73char *query_critical = NULL;
74thresholds *qthresholds = NULL;
70int verbose = 0; 75int verbose = 0;
71 76
72PGconn *conn;
73/*PGresult *res;*/
74
75
76/****************************************************************************** 77/******************************************************************************
77 78
78The (psuedo?)literate programming XML is contained within \@\@\- <XML> \-\@\@ 79The (psuedo?)literate programming XML is contained within \@\@\- <XML> \-\@\@
@@ -117,7 +118,6 @@ Please note that all tags must be lowercase to use the DocBook XML DTD.
117<para>ToDo List</para> 118<para>ToDo List</para>
118<itemizedlist> 119<itemizedlist>
119<listitem>Add option to get password from a secured file rather than the command line</listitem> 120<listitem>Add option to get password from a secured file rather than the command line</listitem>
120<listitem>Add option to specify the query to execute</listitem>
121</itemizedlist> 121</itemizedlist>
122</sect2> 122</sect2>
123 123
@@ -132,8 +132,11 @@ Please note that all tags must be lowercase to use the DocBook XML DTD.
132int 132int
133main (int argc, char **argv) 133main (int argc, char **argv)
134{ 134{
135 PGconn *conn;
136
135 int elapsed_time; 137 int elapsed_time;
136 int status = STATE_UNKNOWN; 138 int status = STATE_UNKNOWN;
139 int query_status = STATE_UNKNOWN;
137 140
138 /* begin, by setting the parameters for a backend connection if the 141 /* begin, by setting the parameters for a backend connection if the
139 * parameters are null, then the system will try to use reasonable 142 * parameters are null, then the system will try to use reasonable
@@ -194,14 +197,18 @@ main (int argc, char **argv)
194 else { 197 else {
195 status = STATE_OK; 198 status = STATE_OK;
196 } 199 }
197 if (verbose)
198 printf("Closing connection\n");
199 PQfinish (conn);
200 printf (_(" %s - database %s (%d sec.)|%s\n"), 200 printf (_(" %s - database %s (%d sec.)|%s\n"),
201 state_text(status), dbName, elapsed_time, 201 state_text(status), dbName, elapsed_time,
202 fperfdata("time", elapsed_time, "s", 202 fperfdata("time", elapsed_time, "s",
203 (int)twarn, twarn, (int)tcrit, tcrit, TRUE, 0, FALSE,0)); 203 (int)twarn, twarn, (int)tcrit, tcrit, TRUE, 0, FALSE,0));
204 return status; 204
205 if (pgquery)
206 query_status = do_query (conn, pgquery);
207
208 if (verbose)
209 printf("Closing connection\n");
210 PQfinish (conn);
211 return (query_status > status) ? query_status : status;
205} 212}
206 213
207 214
@@ -225,12 +232,15 @@ process_arguments (int argc, char **argv)
225 {"authorization", required_argument, 0, 'a'}, 232 {"authorization", required_argument, 0, 'a'},
226 {"port", required_argument, 0, 'P'}, 233 {"port", required_argument, 0, 'P'},
227 {"database", required_argument, 0, 'd'}, 234 {"database", required_argument, 0, 'd'},
235 {"query", required_argument, 0, 'q'},
236 {"query_critical", required_argument, 0, 'C'},
237 {"query_warning", required_argument, 0, 'W'},
228 {"verbose", no_argument, 0, 'v'}, 238 {"verbose", no_argument, 0, 'v'},
229 {0, 0, 0, 0} 239 {0, 0, 0, 0}
230 }; 240 };
231 241
232 while (1) { 242 while (1) {
233 c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:v", 243 c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:q:C:W:v",
234 longopts, &option); 244 longopts, &option);
235 245
236 if (c == EOF) 246 if (c == EOF)
@@ -263,6 +273,12 @@ process_arguments (int argc, char **argv)
263 else 273 else
264 twarn = strtod (optarg, NULL); 274 twarn = strtod (optarg, NULL);
265 break; 275 break;
276 case 'C': /* critical query threshold */
277 query_critical = optarg;
278 break;
279 case 'W': /* warning query threshold */
280 query_warning = optarg;
281 break;
266 case 'H': /* host */ 282 case 'H': /* host */
267 if (!is_host (optarg)) 283 if (!is_host (optarg))
268 usage2 (_("Invalid hostname/address"), optarg); 284 usage2 (_("Invalid hostname/address"), optarg);
@@ -291,12 +307,17 @@ process_arguments (int argc, char **argv)
291 case 'a': 307 case 'a':
292 pgpasswd = optarg; 308 pgpasswd = optarg;
293 break; 309 break;
310 case 'q':
311 pgquery = optarg;
312 break;
294 case 'v': 313 case 'v':
295 verbose++; 314 verbose++;
296 break; 315 break;
297 } 316 }
298 } 317 }
299 318
319 set_thresholds (&qthresholds, query_warning, query_critical);
320
300 return validate_arguments (); 321 return validate_arguments ();
301} 322}
302 323
@@ -448,6 +469,13 @@ print_help (void)
448 469
449 printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT); 470 printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);
450 471
472 printf (" %s\n", "-q, --query=STRING");
473 printf (" %s\n", _("SQL query to run. Only first column in first row will be read"));
474 printf (" %s\n", "-W, --query-warning=RANGE");
475 printf (" %s\n", _("SQL query value to result in warning status (double)"));
476 printf (" %s\n", "-C, --query-critical=RANGE");
477 printf (" %s\n", _("SQL query value to result in critical status (double)"));
478
451 printf (UT_VERBOSE); 479 printf (UT_VERBOSE);
452 480
453 printf ("\n"); 481 printf ("\n");
@@ -458,6 +486,15 @@ print_help (void)
458 printf (" %s\n", _("connects to the template1 database, which is present in every functioning")); 486 printf (" %s\n", _("connects to the template1 database, which is present in every functioning"));
459 printf (" %s\n\n", _("PostgreSQL DBMS.")); 487 printf (" %s\n\n", _("PostgreSQL DBMS."));
460 488
489 printf (" %s\n", _("If a query is specified using the -q option, it will be executed after"));
490 printf (" %s\n", _("connecting to the server. The result from the query has to be numeric."));
491 printf (" %s\n", _("Multiple SQL commands, separated by semicolon, are allowed but the result "));
492 printf (" %s\n", _("of the last command is taken into account only. The value of the first"));
493 printf (" %s\n\n", _("column in the first row is used as the check result."));
494
495 printf (" %s\n", _("See the chapter \"Monitoring Database Activity\" of the PostgreSQL manual"));
496 printf (" %s\n\n", _("for details about how to access internal statistics of the database server."));
497
461 printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To")); 498 printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To"));
462 printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP")); 499 printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP"));
463 printf (" %s\n\n", _("connections (start the postmaster with the -i option).")); 500 printf (" %s\n\n", _("connections (start the postmaster with the -i option)."));
@@ -476,5 +513,72 @@ print_usage (void)
476{ 513{
477 printf ("%s\n", _("Usage:")); 514 printf ("%s\n", _("Usage:"));
478 printf ("%s [-H <host>] [-P <port>] [-c <critical time>] [-w <warning time>]\n", progname); 515 printf ("%s [-H <host>] [-P <port>] [-c <critical time>] [-w <warning time>]\n", progname);
479 printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n"); 516 printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n"
517 "[-q <query>] [-C <critical query range>] [-W <warning query range>]\n");
480} 518}
519
520int
521do_query (PGconn *conn, char *query)
522{
523 PGresult *res;
524
525 char *val_str;
526 double value;
527
528 char *endptr = NULL;
529
530 int my_status = STATE_UNKNOWN;
531
532 if (verbose)
533 printf ("Executing SQL query \"%s\".\n");
534 res = PQexec (conn, query);
535
536 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
537 printf (_("QUERY %s - %s: %s.\n"), _("CRITICAL"), _("Error with query"),
538 PQerrorMessage (conn));
539 return STATE_CRITICAL;
540 }
541
542 if (PQntuples (res) < 1) {
543 printf ("QUERY %s - %s.\n", _("WARNING"), _("No rows returned"));
544 return STATE_WARNING;
545 }
546
547 if (PQnfields (res) < 1) {
548 printf ("QUERY %s - %s.\n", _("WARNING"), _("No columns returned"));
549 return STATE_WARNING;
550 }
551
552 val_str = PQgetvalue (res, 0, 0);
553 if (! val_str) {
554 printf ("QUERY %s - %s.\n", _("CRITICAL"), _("No data returned"));
555 return STATE_CRITICAL;
556 }
557
558 value = strtod (val_str, &endptr);
559 if (verbose)
560 printf ("Query result: %f\n", value);
561
562 if (endptr == val_str) {
563 printf ("QUERY %s - %s: %s\n", _("CRITICAL"), _("Is not a numeric"), val_str);
564 return STATE_CRITICAL;
565 }
566 else if ((endptr != NULL) && (*endptr != '\0')) {
567 if (verbose)
568 printf ("Garbage after value: %s.\n", endptr);
569 }
570
571 my_status = get_status (value, qthresholds);
572 printf ("QUERY %s - ",
573 (my_status == STATE_OK)
574 ? _("OK")
575 : (my_status == STATE_WARNING)
576 ? _("WARNING")
577 : (my_status == STATE_CRITICAL)
578 ? _("CRITICAL")
579 : _("UNKNOWN"));
580 printf (_("'%s' returned %f"), query, value);
581 printf ("|query=%f;%s;%s;0\n", value, query_warning, query_critical);
582 return my_status;
583}
584