From 912df3ef9b188c82893dace1e9b56c42a558fdba Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Wed, 6 Apr 2011 16:51:37 +0200 Subject: 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. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 69edae7..edad116 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -56,6 +56,7 @@ void print_usage (void); void print_help (void); int is_pg_dbname (char *); int is_pg_logname (char *); +int do_query (PGconn *, char *); char *pghost = NULL; /* host name of the backend server */ char *pgport = NULL; /* port of the backend server */ @@ -67,12 +68,12 @@ char *pguser = NULL; char *pgpasswd = NULL; double twarn = (double)DEFAULT_WARN; double tcrit = (double)DEFAULT_CRIT; +char *pgquery = NULL; +char *query_warning = NULL; +char *query_critical = NULL; +thresholds *qthresholds = NULL; int verbose = 0; -PGconn *conn; -/*PGresult *res;*/ - - /****************************************************************************** The (psuedo?)literate programming XML is contained within \@\@\- \-\@\@ @@ -117,7 +118,6 @@ Please note that all tags must be lowercase to use the DocBook XML DTD. ToDo List Add option to get password from a secured file rather than the command line -Add option to specify the query to execute @@ -132,8 +132,11 @@ Please note that all tags must be lowercase to use the DocBook XML DTD. int main (int argc, char **argv) { + PGconn *conn; + int elapsed_time; int status = STATE_UNKNOWN; + int query_status = STATE_UNKNOWN; /* begin, by setting the parameters for a backend connection if the * parameters are null, then the system will try to use reasonable @@ -194,14 +197,18 @@ main (int argc, char **argv) else { status = STATE_OK; } - if (verbose) - printf("Closing connection\n"); - PQfinish (conn); printf (_(" %s - database %s (%d sec.)|%s\n"), state_text(status), dbName, elapsed_time, fperfdata("time", elapsed_time, "s", (int)twarn, twarn, (int)tcrit, tcrit, TRUE, 0, FALSE,0)); - return status; + + if (pgquery) + query_status = do_query (conn, pgquery); + + if (verbose) + printf("Closing connection\n"); + PQfinish (conn); + return (query_status > status) ? query_status : status; } @@ -225,12 +232,15 @@ process_arguments (int argc, char **argv) {"authorization", required_argument, 0, 'a'}, {"port", required_argument, 0, 'P'}, {"database", required_argument, 0, 'd'}, + {"query", required_argument, 0, 'q'}, + {"query_critical", required_argument, 0, 'C'}, + {"query_warning", required_argument, 0, 'W'}, {"verbose", no_argument, 0, 'v'}, {0, 0, 0, 0} }; while (1) { - c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:v", + c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:q:C:W:v", longopts, &option); if (c == EOF) @@ -263,6 +273,12 @@ process_arguments (int argc, char **argv) else twarn = strtod (optarg, NULL); break; + case 'C': /* critical query threshold */ + query_critical = optarg; + break; + case 'W': /* warning query threshold */ + query_warning = optarg; + break; case 'H': /* host */ if (!is_host (optarg)) usage2 (_("Invalid hostname/address"), optarg); @@ -291,12 +307,17 @@ process_arguments (int argc, char **argv) case 'a': pgpasswd = optarg; break; + case 'q': + pgquery = optarg; + break; case 'v': verbose++; break; } } + set_thresholds (&qthresholds, query_warning, query_critical); + return validate_arguments (); } @@ -448,6 +469,13 @@ print_help (void) printf (UT_TIMEOUT, DEFAULT_SOCKET_TIMEOUT); + printf (" %s\n", "-q, --query=STRING"); + printf (" %s\n", _("SQL query to run. Only first column in first row will be read")); + printf (" %s\n", "-W, --query-warning=RANGE"); + printf (" %s\n", _("SQL query value to result in warning status (double)")); + printf (" %s\n", "-C, --query-critical=RANGE"); + printf (" %s\n", _("SQL query value to result in critical status (double)")); + printf (UT_VERBOSE); printf ("\n"); @@ -458,6 +486,15 @@ print_help (void) printf (" %s\n", _("connects to the template1 database, which is present in every functioning")); printf (" %s\n\n", _("PostgreSQL DBMS.")); + printf (" %s\n", _("If a query is specified using the -q option, it will be executed after")); + printf (" %s\n", _("connecting to the server. The result from the query has to be numeric.")); + printf (" %s\n", _("Multiple SQL commands, separated by semicolon, are allowed but the result ")); + printf (" %s\n", _("of the last command is taken into account only. The value of the first")); + printf (" %s\n\n", _("column in the first row is used as the check result.")); + + printf (" %s\n", _("See the chapter \"Monitoring Database Activity\" of the PostgreSQL manual")); + printf (" %s\n\n", _("for details about how to access internal statistics of the database server.")); + printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To")); printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP")); printf (" %s\n\n", _("connections (start the postmaster with the -i option).")); @@ -476,5 +513,72 @@ print_usage (void) { printf ("%s\n", _("Usage:")); printf ("%s [-H ] [-P ] [-c ] [-w ]\n", progname); - printf (" [-t ] [-d ] [-l ] [-p ]\n"); + printf (" [-t ] [-d ] [-l ] [-p ]\n" + "[-q ] [-C ] [-W ]\n"); } + +int +do_query (PGconn *conn, char *query) +{ + PGresult *res; + + char *val_str; + double value; + + char *endptr = NULL; + + int my_status = STATE_UNKNOWN; + + if (verbose) + printf ("Executing SQL query \"%s\".\n"); + res = PQexec (conn, query); + + if (PGRES_TUPLES_OK != PQresultStatus (res)) { + printf (_("QUERY %s - %s: %s.\n"), _("CRITICAL"), _("Error with query"), + PQerrorMessage (conn)); + return STATE_CRITICAL; + } + + if (PQntuples (res) < 1) { + printf ("QUERY %s - %s.\n", _("WARNING"), _("No rows returned")); + return STATE_WARNING; + } + + if (PQnfields (res) < 1) { + printf ("QUERY %s - %s.\n", _("WARNING"), _("No columns returned")); + return STATE_WARNING; + } + + val_str = PQgetvalue (res, 0, 0); + if (! val_str) { + printf ("QUERY %s - %s.\n", _("CRITICAL"), _("No data returned")); + return STATE_CRITICAL; + } + + value = strtod (val_str, &endptr); + if (verbose) + printf ("Query result: %f\n", value); + + if (endptr == val_str) { + printf ("QUERY %s - %s: %s\n", _("CRITICAL"), _("Is not a numeric"), val_str); + return STATE_CRITICAL; + } + else if ((endptr != NULL) && (*endptr != '\0')) { + if (verbose) + printf ("Garbage after value: %s.\n", endptr); + } + + my_status = get_status (value, qthresholds); + printf ("QUERY %s - ", + (my_status == STATE_OK) + ? _("OK") + : (my_status == STATE_WARNING) + ? _("WARNING") + : (my_status == STATE_CRITICAL) + ? _("CRITICAL") + : _("UNKNOWN")); + printf (_("'%s' returned %f"), query, value); + printf ("|query=%f;%s;%s;0\n", value, query_warning, query_critical); + return my_status; +} + -- cgit v0.10-9-g596f From 58ef38e2bb9503f4fbcca5fb8b17ccaf47f9ed67 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Wed, 6 Apr 2011 16:59:19 +0200 Subject: check_pgsql: Fixed query perfdata output for empty warn/crit ranges. Previously, "(null)" was printed (when using GNU's libc). This has been changed to print the empty string instead. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index edad116..2acddc4 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -578,7 +578,9 @@ do_query (PGconn *conn, char *query) ? _("CRITICAL") : _("UNKNOWN")); printf (_("'%s' returned %f"), query, value); - printf ("|query=%f;%s;%s;0\n", value, query_warning, query_critical); + printf ("|query=%f;%s;%s;0\n", value, + query_warning ? query_warning : "", + query_critical ? query_critical : ""); return my_status; } -- cgit v0.10-9-g596f From f3e2ebd974c2b0c07321663fc7255c7ace916ba5 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 10:40:11 +0200 Subject: check_pgsql: Use PQconnectdb() rather than PQsetdbLogin(). This is more flexible and the recommended way to connect to a PostgreSQL database. Also, the verbose output now includes detailed information about the connection. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 2acddc4..cf526a6 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -42,6 +42,20 @@ const char *email = "nagiosplug-devel@lists.sourceforge.net"; #define DEFAULT_DB "template1" #define DEFAULT_HOST "127.0.0.1" +/* return the PSQL server version as a 3-tuple */ +#define PSQL_SERVER_VERSION3(server_version) \ + (server_version) / 10000, \ + (server_version) / 100 - (int)((server_version) / 10000) * 100, \ + (server_version) - (int)((server_version) / 100) * 100 +/* return true if the given host is a UNIX domain socket */ +#define PSQL_IS_UNIX_DOMAIN_SOCKET(host) \ + ((NULL == (host)) || ('\0' == *(host)) || ('/' == *(host))) +/* return a 3-tuple identifying a host/port independent of the socket type */ +#define PSQL_SOCKET3(host, port) \ + ((NULL == (host)) || ('\0' == *(host))) ? DEFAULT_PGSOCKET_DIR : host, \ + PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \ + port + enum { DEFAULT_PORT = 5432, DEFAULT_WARN = 2, @@ -133,6 +147,7 @@ int main (int argc, char **argv) { PGconn *conn; + char *conninfo = NULL; int elapsed_time; int status = STATE_UNKNOWN; @@ -164,18 +179,30 @@ main (int argc, char **argv) } alarm (timeout_interval); - if (verbose) - printf("Connecting to database:\n DB: %s\n User: %s\n Host: %s\n Port: %d\n", dbName, - (pguser != NULL) ? pguser : "unspecified", - (pghost != NULL) ? pghost : "unspecified", - (pgport != NULL) ? atoi(pgport) : DEFAULT_PORT); + asprintf (&conninfo, "dbname = '%s'", dbName); + if (pghost) + asprintf (&conninfo, "%s host = '%s'", conninfo, pghost); + if (pgport) + asprintf (&conninfo, "%s port = '%s'", conninfo, pgport); + if (pgoptions) + asprintf (&conninfo, "%s options = '%s'", conninfo, pgoptions); + /* if (pgtty) -- ignored by PQconnectdb */ + if (pguser) + asprintf (&conninfo, "%s user = '%s'", conninfo, pguser); + + if (verbose) /* do not include password (see right below) in output */ + printf ("Connecting to PostgreSQL using conninfo: %s%s\n", conninfo, + pgpasswd ? " password = " : ""); + + if (pgpasswd) + asprintf (&conninfo, "%s password = '%s'", conninfo, pgpasswd); /* make a connection to the database */ time (&start_time); - conn = - PQsetdbLogin (pghost, pgport, pgoptions, pgtty, dbName, pguser, pgpasswd); + conn = PQconnectdb (conninfo); time (&end_time); elapsed_time = (int) (end_time - start_time); + if (verbose) printf("Time elapsed: %d\n", elapsed_time); @@ -197,6 +224,20 @@ main (int argc, char **argv) else { status = STATE_OK; } + + if (verbose) { + char *server_host = PQhost (conn); + int server_version = PQserverVersion (conn); + + printf ("Successfully connected to database %s (user %s) " + "at server %s%s%s (server version: %d.%d.%d, " + "protocol version: %d, pid: %d)\n", + PQdb (conn), PQuser (conn), + PSQL_SOCKET3 (server_host, PQport (conn)), + PSQL_SERVER_VERSION3 (server_version), + PQprotocolVersion (conn), PQbackendPID (conn)); + } + printf (_(" %s - database %s (%d sec.)|%s\n"), state_text(status), dbName, elapsed_time, fperfdata("time", elapsed_time, "s", -- cgit v0.10-9-g596f From a241ab0b9de9aa1dca32745d8461d649c48b4cc2 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 11:13:49 +0200 Subject: check_pgsql: Allow UNIX socket directories as hostname as well. PostgreSQL accepts the directory name of its UNIX socket as hostname as well, e.g. /var/run/postgresql/. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index cf526a6..c20c991 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -321,7 +321,7 @@ process_arguments (int argc, char **argv) query_warning = optarg; break; case 'H': /* host */ - if (!is_host (optarg)) + if ((*optarg != '/') && (!is_host (optarg))) usage2 (_("Invalid hostname/address"), optarg); else pghost = optarg; -- cgit v0.10-9-g596f From b3773ae7e5dbe1781345bc9d2539b145b67343d8 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 11:32:30 +0200 Subject: check_pgsql: Removed -4/-6 flags from help output. These options are not currently supported. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index c20c991..8c45731 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -496,8 +496,6 @@ print_help (void) printf (UT_HOST_PORT, 'P', myport); - printf (UT_IPv46); - printf (" %s\n", "-d, --database=STRING"); printf (" %s", _("Database to check ")); printf (_("(default: %s)"), DEFAULT_DB); -- cgit v0.10-9-g596f From c3f97e6180f9dc711ac0424894f3a30849d3ff67 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 11:50:10 +0200 Subject: check_pgsql: Added support for the -o command line option. This option may be used to specify further connection parameters to be passed to PQconnectdb(). For example, this may be used to specify a service name in pg_service.conf to be used for additional connection parameters: -o 'service=' or to specify the SSL mode: -o 'sslmode=require'. See the chapter "libpq - C Library" in the PostgreSQL manual for details. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 8c45731..d94914f 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -80,6 +80,7 @@ char *pgtty = NULL; char dbName[NAMEDATALEN] = DEFAULT_DB; char *pguser = NULL; char *pgpasswd = NULL; +char *pgparams = NULL; double twarn = (double)DEFAULT_WARN; double tcrit = (double)DEFAULT_CRIT; char *pgquery = NULL; @@ -130,9 +131,6 @@ Please note that all tags must be lowercase to use the DocBook XML DTD. Future Enhancements ToDo List - -Add option to get password from a secured file rather than the command line - @@ -179,7 +177,10 @@ main (int argc, char **argv) } alarm (timeout_interval); - asprintf (&conninfo, "dbname = '%s'", dbName); + if (pgparams) + asprintf (&conninfo, "%s ", pgparams); + + asprintf (&conninfo, "%sdbname = '%s'", conninfo ? conninfo : "", dbName); if (pghost) asprintf (&conninfo, "%s host = '%s'", conninfo, pghost); if (pgport) @@ -273,6 +274,7 @@ process_arguments (int argc, char **argv) {"authorization", required_argument, 0, 'a'}, {"port", required_argument, 0, 'P'}, {"database", required_argument, 0, 'd'}, + {"option", required_argument, 0, 'o'}, {"query", required_argument, 0, 'q'}, {"query_critical", required_argument, 0, 'C'}, {"query_warning", required_argument, 0, 'W'}, @@ -281,7 +283,7 @@ process_arguments (int argc, char **argv) }; while (1) { - c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:q:C:W:v", + c = getopt_long (argc, argv, "hVt:c:w:H:P:d:l:p:a:o:q:C:W:v", longopts, &option); if (c == EOF) @@ -348,6 +350,12 @@ process_arguments (int argc, char **argv) case 'a': pgpasswd = optarg; break; + case 'o': + if (pgparams) + asprintf (&pgparams, "%s %s", pgparams, optarg); + else + asprintf (&pgparams, "%s", optarg); + break; case 'q': pgquery = optarg; break; @@ -503,6 +511,8 @@ print_help (void) printf (" %s\n", _("Login name of user")); printf (" %s\n", "-p, --password = STRING"); printf (" %s\n", _("Password (BIG SECURITY ISSUE)")); + printf (" %s\n", "-o, --option = STRING"); + printf (" %s\n", _("Connection parameters (keyword = value), see below")); printf (UT_WARN_CRIT); @@ -534,6 +544,13 @@ print_help (void) printf (" %s\n", _("See the chapter \"Monitoring Database Activity\" of the PostgreSQL manual")); printf (" %s\n\n", _("for details about how to access internal statistics of the database server.")); + printf (" %s\n", _("For a list of available connection parameters which may be used with the -o")); + printf (" %s\n", _("command line option, see the documentation for PQconnectdb() in the chapter")); + printf (" %s\n", _("\"libpq - C Library\" of the PostgreSQL manual. For example, this may be")); + printf (" %s\n", _("used to specify a service name in pg_service.conf to be used for additional")); + printf (" %s\n", _("connection parameters: -o 'service=' or to specify the SSL mode:")); + printf (" %s\n\n", _("-o 'sslmode=require'.")); + printf (" %s\n", _("The plugin will connect to a local postmaster if no host is specified. To")); printf (" %s\n", _("connect to a remote host, be sure that the remote postmaster accepts TCP/IP")); printf (" %s\n\n", _("connections (start the postmaster with the -i option).")); -- cgit v0.10-9-g596f From 034f6b3699d589ae1e744142a95da17b2fba41b3 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 7 Apr 2011 18:28:41 +0200 Subject: check_pgsql: Updated copyright. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index d94914f..ea3f402 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -3,7 +3,7 @@ * Nagios check_pgsql plugin * * License: GPL -* Copyright (c) 1999-2007 Nagios Plugins Development Team +* Copyright (c) 1999-2011 Nagios Plugins Development Team * * Description: * @@ -29,7 +29,7 @@ *****************************************************************************/ const char *progname = "check_pgsql"; -const char *copyright = "1999-2007"; +const char *copyright = "1999-2011"; const char *email = "nagiosplug-devel@lists.sourceforge.net"; #include "common.h" -- cgit v0.10-9-g596f From c56a22cbbf17a9195feb7413086f7b96336e1aac Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Fri, 8 Apr 2011 10:53:49 +0200 Subject: check_pgsql: Leave 'min' value in query perfdata empty. There is no reasonable default value for that. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index ea3f402..54d2d58 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -634,7 +634,7 @@ do_query (PGconn *conn, char *query) ? _("CRITICAL") : _("UNKNOWN")); printf (_("'%s' returned %f"), query, value); - printf ("|query=%f;%s;%s;0\n", value, + printf ("|query=%f;%s;%s;;\n", value, query_warning ? query_warning : "", query_critical ? query_critical : ""); return my_status; -- cgit v0.10-9-g596f From c0bef3da51bd8b6be658f619a4659c95ad83d4bd Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Fri, 8 Apr 2011 11:17:33 +0200 Subject: =?UTF-8?q?check=5Fpgsql:=20Determine=20connection=20time=20in=20?= =?UTF-8?q?=C2=B5s-resolution.?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit … thus, treat "elapsed time" and the thresholds as floating point values. diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 54d2d58..d3116b0 100644 --- a/plugins/check_pgsql.c +++ b/plugins/check_pgsql.c @@ -147,7 +147,9 @@ main (int argc, char **argv) PGconn *conn; char *conninfo = NULL; - int elapsed_time; + struct timeval start_timeval; + struct timeval end_timeval; + double elapsed_time; int status = STATE_UNKNOWN; int query_status = STATE_UNKNOWN; @@ -199,13 +201,19 @@ main (int argc, char **argv) asprintf (&conninfo, "%s password = '%s'", conninfo, pgpasswd); /* make a connection to the database */ - time (&start_time); + gettimeofday (&start_timeval, NULL); conn = PQconnectdb (conninfo); - time (&end_time); - elapsed_time = (int) (end_time - start_time); + 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: %d\n", elapsed_time); + printf("Time elapsed: %f\n", elapsed_time); /* check to see that the backend connection was successfully made */ if (verbose) @@ -239,10 +247,10 @@ main (int argc, char **argv) PQprotocolVersion (conn), PQbackendPID (conn)); } - printf (_(" %s - database %s (%d sec.)|%s\n"), + printf (_(" %s - database %s (%f sec.)|%s\n"), state_text(status), dbName, elapsed_time, fperfdata("time", elapsed_time, "s", - (int)twarn, twarn, (int)tcrit, tcrit, TRUE, 0, FALSE,0)); + !!(twarn > 0.0), twarn, !!(tcrit > 0.0), tcrit, TRUE, 0, FALSE,0)); if (pgquery) query_status = do_query (conn, pgquery); -- cgit v0.10-9-g596f