[Nagiosplug-devel] check_mysql_slow

Eli Shemer elish at consist.co.il
Thu Oct 23 12:02:33 CEST 2008

Hey there,


This is a small plugin, based on the original check_mysql_query.c

I wanted to know what you think about it and maybe you have other ideas how to implement this.


The idea is to monitor slow queries on remote systems that may be bringing a mysql process to its knees,

Which as of current does not raise an exception on the nagios system




const char *progname = "check_mysql_query";

const char *revision = "$Revision: 2034 $";

const char *copyright = "1999-2007";

const char *email = "nagiosplug-devel at lists.sourceforge.net";


#include "common.h"

#include "utils.h"

#include "utils_base.h"

#include "netutils.h"


#include <mysql.h>

#include <errmsg.h>


char *db_user = NULL;

char *db_host = NULL;

char *db_socket = NULL;

char *db_pass = NULL;

char *db = NULL;

char *warning = NULL;

char *critical = NULL;


unsigned int db_port = MYSQL_PORT;


int process_arguments (int, char **);

int validate_arguments (void);

void print_help (void);

void print_usage (void);


char *sql_query = NULL;

int verbose = 0;

thresholds *my_thresholds = NULL;


int main (int argc, char **argv)


       MYSQL mysql;

       MYSQL_RES *res;

       MYSQL_ROW row;


       char *error = NULL;

       int time;

       int warning_int,critical_int;


       setlocale (LC_ALL, "");

       bindtextdomain (PACKAGE, LOCALEDIR);

       textdomain (PACKAGE);


       /* Parse extra opts if any */

       argv=np_extra_opts (&argc, argv, progname);


       if (process_arguments (argc, argv) == ERROR)

              usage4 (_("Could not parse arguments"));


        warning_int = atoi(warning);

        critical_int = atoi(critical);


       /* initialize mysql  */

       mysql_init (&mysql);




       /* establish a connection to the server and error checking */

       if (!mysql_real_connect(&mysql,db_host,db_user,db_pass,"",db_port,db_socket,0)) {

              if (mysql_errno (&mysql) == CR_UNKNOWN_HOST)

                     die (STATE_WARNING, "QUERY %s: %s\n", _("WARNING"), mysql_error (&mysql));

              else if (mysql_errno (&mysql) == CR_VERSION_ERROR)

                     die (STATE_WARNING, "QUERY %s: %s\n", _("WARNING"), mysql_error (&mysql));

              else if (mysql_errno (&mysql) == CR_OUT_OF_MEMORY)

                     die (STATE_WARNING, "QUERY %s: %s\n", _("WARNING"), mysql_error (&mysql));

              else if (mysql_errno (&mysql) == CR_IPSOCK_ERROR)

                     die (STATE_WARNING, "QUERY %s: %s\n", _("WARNING"), mysql_error (&mysql));

              else if (mysql_errno (&mysql) == CR_SOCKET_CREATE_ERROR)

                     die (STATE_WARNING, "QUERY %s: %s\n", _("WARNING"), mysql_error (&mysql));


                     die (STATE_CRITICAL, "QUERY %s: %s\n", _("CRITICAL"), mysql_error (&mysql));



       if (mysql_query (&mysql, "show processlist;") != 0) {

              error = strdup(mysql_error(&mysql));

              mysql_close (&mysql);

              die (STATE_CRITICAL, "QUERY %s: %s - %s\n", _("CRITICAL"), _("Error with query"), error);



       /* store the result */

       if ( (res = mysql_store_result (&mysql)) == NULL) {

              error = strdup(mysql_error(&mysql));

              mysql_close (&mysql);

              die (STATE_CRITICAL, "QUERY %s: Error with store_result - %s\n", _("CRITICAL"), error);



       /* Check there is some data */

       if (mysql_num_rows(res) == 0) {


              die (STATE_WARNING, "QUERY %s: %s\n", _("WARNING"), _("No rows returned"));



       /* fetch the first row */

       while ( (row = mysql_fetch_row (res)) != NULL) {

         if(strcmp(row[4],"Query") == 0)


            time = (int) atoi(row[5]);

           if (time>=warning_int && time<critical_int)


              die (STATE_WARNING, "%s QUERY time %d\n", _("WARNING"),time);


           else if (time>=critical_int)


              die (STATE_CRITICAL, "%s QUERY time %d\n", _("CRITICAL"),time);



/**      else if (strcmp(row[4],"Sleep") == 0)


            time = (int)atoi(row[5]);

            printf("MYSQL PERFORMANCE SEEMS OK\n");

            exit (STATE_OK);





        printf("MYSQL PERFORMANCE SEEMS OK \n");

       mysql_free_result (res);

       mysql_close (&mysql);

        exit (STATE_OK);

       return 1;




/* process command-line arguments */


process_arguments (int argc, char **argv)


       int c;

       int option = 0;


       static struct option longopts[] = {

              {"hostname", required_argument, 0, 'H'},

              {"socket", required_argument, 0, 's'},

              {"database", required_argument, 0, 'd'},

              {"username", required_argument, 0, 'u'},

              {"password", required_argument, 0, 'p'},

              {"port", required_argument, 0, 'P'},

              {"verbose", no_argument, 0, 'v'},

              {"version", no_argument, 0, 'V'},

              {"help", no_argument, 0, 'h'},

              {"warning", required_argument, 0, 'w'},

              {"critical", required_argument, 0, 'c'},

              {0, 0, 0, 0}



       if (argc < 1)

              return ERROR;


       while (1) {

              c = getopt_long (argc, argv, "hvVSP:p:u:d:H:s:q:w:c:", longopts, &option);


              if (c == -1 || c == EOF)



              switch (c) {

              case 'H':                                                             /* hostname */

                     if (is_host (optarg)) {

                            db_host = optarg;


                     else {

                            usage2 (_("Invalid hostname/address"), optarg);



              case 's':                                                             /* socket */

                     db_socket = optarg;


              case 'u':                                                             /* username */

                     db_user = optarg;


              case 'p':                                                             /* authentication information: password */

                     db_pass = strdup(optarg);


                     /* Delete the password from process list */

                     while (*optarg != '\0') {

                            *optarg = 'X';




              case 'P':                                                             /* critical time threshold */

                     db_port = atoi (optarg);


              case 'v':



              case 'V':                                                             /* version */

                     print_revision (progname, revision);

                     exit (STATE_OK);

              case 'h':                                                             /* help */

                     print_help ();

                     exit (STATE_OK);

              case 'w':

                     warning = optarg;


              case 'c':

                     critical = optarg;


              case '?':                                                             /* help */

                     usage5 ();




       c = optind;


       set_thresholds(&my_thresholds, warning, critical);


       return validate_arguments ();





validate_arguments (void)


       if (db_user == NULL)

              db_user = strdup("");


       if (db_host == NULL)

              db_host = strdup("");


       if (db_pass == NULL)

              db_pass = strdup("");


       if (db == NULL)

              db = strdup("");


       if (warning == NULL)

              warning = strdup("3");


       if (critical == NULL)

              critical = strdup("10");


       return OK;





print_help (void)


       char *myport;

       asprintf (&myport, "%d", MYSQL_PORT);


       print_revision (progname, revision);


       printf (_(COPYRIGHT), copyright, email);


       printf ("%s\n", _("This program checks a query result against threshold levels"));


       printf ("\n\n");

       print_usage ();


       printf (_(UT_HELP_VRSN));

       printf (_(UT_EXTRA_OPTS));

       printf ("    %s\n", _("SQL query to run. Only first column in first row will be read"));

       printf (_(UT_WARN_CRIT_RANGE));

       printf (_(UT_HOST_PORT), 'P', myport);

       printf (" %s\n", "-s, --socket=STRING");

       printf ("    %s\n", _("Use the specified socket (has no effect if -H is used)"));

       printf (" -u, --username=STRING\n");

       printf ("    %s\n", _("Username to login with"));

       printf (" -p, --password=STRING\n");

       printf ("    %s\n", _("Password to login with"));

       printf ("    ==> %s <==\n", _("IMPORTANT: THIS FORM OF AUTHENTICATION IS NOT SECURE!!!"));

       printf ("    %s\n", _("Your clear-text password could be visible as a process table entry"));


       printf ("\n");

       printf (" %s\n", _("A query is required. The result from the query should be numeric."));

       printf (" %s\n", _("For extra security, create a user with minimal access."));



       printf ("\n");

       printf ("%s\n", _("Notes:"));

       printf (_(UT_EXTRA_OPTS_NOTES));



       printf (_(UT_SUPPORT));





print_usage (void)


  printf (_("Usage:"));

  printf (" %s -q SQL_slow [-w warn] [-c crit] [-H host] [-P port] [-s socket]\n",progname);

  printf ("       [-d database] [-u user] [-p password]\n");





אלי שמר

מנהל תשתיות WEB

קונסיסט מערכות




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-plugins.org/archive/devel/attachments/20081023/db3c57df/attachment.html>

More information about the Devel mailing list