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. --- plugins/check_pgsql.c | 126 +++++++++++++++++++++++++++++++++++++++++++++----- 1 file 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); 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 v1.2.3-74-g34f1 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. --- plugins/check_pgsql.c | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index edad116a..2acddc4f 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 v1.2.3-74-g34f1 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. --- plugins/check_pgsql.c | 55 ++++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 48 insertions(+), 7 deletions(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 2acddc4f..cf526a6e 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 v1.2.3-74-g34f1 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/. --- plugins/check_pgsql.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index cf526a6e..c20c9914 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 v1.2.3-74-g34f1 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. --- plugins/check_pgsql.c | 2 -- 1 file changed, 2 deletions(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index c20c9914..8c457317 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 v1.2.3-74-g34f1 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. --- plugins/check_pgsql.c | 27 ++++++++++++++++++++++----- 1 file changed, 22 insertions(+), 5 deletions(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 8c457317..d94914fe 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 v1.2.3-74-g34f1 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. --- plugins/check_pgsql.c | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index d94914fe..ea3f4029 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 v1.2.3-74-g34f1 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. --- plugins/check_pgsql.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index ea3f4029..54d2d581 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 v1.2.3-74-g34f1 From c0bef3da51bd8b6be658f619a4659c95ad83d4bd Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Fri, 8 Apr 2011 11:17:33 +0200 Subject: check_pgsql: Determine connection time in µs-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. --- plugins/check_pgsql.c | 22 +++++++++++++++------- 1 file changed, 15 insertions(+), 7 deletions(-) diff --git a/plugins/check_pgsql.c b/plugins/check_pgsql.c index 54d2d581..d3116b00 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 v1.2.3-74-g34f1