[nagiosplug] check_pgsql: Added support for executing queries.

 Module: nagiosplug
 Branch: master
 Commit: 912df3ef9b188c82893dace1e9b56c42a558fdba
 Author: Sebastian Harl <[email protected]>
   Date: Wed Apr  6 16:51:37 2011 +0200
    URL: http://nagiosplug.git.sf.net/git/gitweb.cgi?p=nagiosplug/nagiosplug;a=commit;h=912df3e

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 files changed, 115 insertions(+), 11 deletions(-)

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 \@\@\- <XML> \-\@\@
@@ -117,7 +118,6 @@ Please note that all tags must be lowercase to use the DocBook XML DTD.
 <para>ToDo List</para>
 <itemizedlist>
 <listitem>Add option to get password from a secured file rather than the command line</listitem>
-<listitem>Add option to specify the query to execute</listitem>
 </itemizedlist>
 </sect2>
 
@@ -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 <host>] [-P <port>] [-c <critical time>] [-w <warning time>]\n", progname);
-	printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n");
+	printf (" [-t <timeout>] [-d <database>] [-l <logname>] [-p <password>]\n"
+			"[-q <query>] [-C <critical query range>] [-W <warning query range>]\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;
+}
+


------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead. 
Download for free and get started troubleshooting in minutes. 
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk