active_checks/check_sql

   1 #!/usr/bin/env python
   2 # -*- encoding: utf-8; py-indent-offset: 4 -*-
   3 # +------------------------------------------------------------------+
   4 # |             ____ _               _        __  __ _  __           |
   5 # |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
   6 # |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
   7 # |           | |___| | | |  __/ (__|   <    | |  | | . \            |
   8 # |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
   9 # |                                                                  |
  10 # | Copyright Mathias Kettner 2014             mk@mathias-kettner.de |
  11 # +------------------------------------------------------------------+
  12 #
  13 # This file is part of Check_MK.
  14 # The official homepage is at http://mathias-kettner.de/check_mk.
  15 #
  16 # check_mk is free software;  you can redistribute it and/or modify it
  17 # under the  terms of the  GNU General Public License  as published by
  18 # the Free Software Foundation in version 2.  check_mk is  distributed
  19 # in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
  20 # out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
  21 # PARTICULAR PURPOSE. See the  GNU General Public License for more de-
  22 # tails. You should have  received  a copy of the  GNU  General Public
  23 # License along with GNU Make; see the file  COPYING.  If  not,  write
  24 # to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
  25 # Boston, MA 02110-1301 USA.
  26
  27 # DB2 support requires installation of the IBM Data Server Client:
  28 #  http://www-01.ibm.com/support/docview.wss?uid=swg27016878
  29 # as well as the ibm_db2 Python DBI driver for DB2:
  30 #  https://pypi.python.org/pypi/ibm_db
  31 """Check_MK SQL Test"""
  32 import argparse
  33 import logging
  34 import os
  35 import sys
  36
  37 import cmk.utils.password_store
  38 cmk.utils.password_store.replace_passwords()
  39
  40 LOG = logging.getLogger(__name__)
  41
  42 DEFAULT_PORTS = {
  43     "postgres": 5432,
  44     "mysql": 3306,
  45     "mssql": 1433,
  46     "oracle": 1521,
  47     "db2": 50000,
  48 }
  49
  50 MP_INF = (float('-inf'), float('+inf'))
  51
  52 #   . parse commandline argumens
  53
  54
  55 def levels(values):
  56     lower, upper = values.split(':')
  57     lower = float(lower) if lower else MP_INF[0]
  58     upper = float(upper) if upper else MP_INF[1]
  59     return (lower, upper)
  60
  61
  62 def sql_cmd_piece(values):
  63     """Parse every piece of the SQL command (replace \\n and \\;)"""
  64     return values.replace(r"\n", "\n").replace(r"\;", ";")
  65
  66
  67 def parse_args(argv):
  68     """Parse commandline arguments (incl password store and logging set up)"""
  69     this = str(os.path.basename(argv[0]))
  70     fmt = argparse.RawDescriptionHelpFormatter
  71     parser = argparse.ArgumentParser(prog=this, description=__doc__, formatter_class=fmt)
  72     # flags
  73     parser.add_argument(
  74         "-v",
  75         "--verbose",
  76         action="count",
  77         default=0,
  78         help='''Verbose mode: print SQL statement and levels
  79                              (for even more output use -vv''',
  80     )
  81     parser.add_argument(
  82         "--debug",
  83         action="store_true",
  84         help='''Debug mode: let Python exceptions come through''',
  85     )
  86     parser.add_argument(
  87         "-m",
  88         "--metrics",
  89         action="store_true",
  90         help='''Add performance data to the output''',
  91     )
  92     parser.add_argument(
  93         "-o",
  94         "--procedure",
  95         action="store_true",
  96         help='''treat the main argument as a procedure instead
  97                               of an SQL-Statement''',
  98     )
  99     parser.add_argument(
 100         "-i",
 101         "--input",
 102         metavar="CSV",
 103         default=[],
 104         type=lambda s: s.split(','),
 105         help='''comma separated list if values of input variables
 106                              if required by the procedure''',
 107     )
 108     # optional arguments
 109     parser.add_argument(
 110         "-d",
 111         "--dbms",
 112         default='postgres',
 113         choices=['postgres', 'mysql', 'mssql', 'oracle', 'db2'],
 114         help='''Name of the database management system.
 115                              Default is "postgres"''',
 116     )
 117     parser.add_argument(
 118         "-H",
 119         "--hostname",
 120         metavar='HOST',
 121         default='127.0.0.1',
 122         help='''Hostname or IP-Address where the database lives.
 123                              Default is "127.0.0.1"''',
 124     )
 125     parser.add_argument(
 126         "-P",
 127         "--port",
 128         default=None,
 129         type=int,
 130         help='''Port used to connect to the database.
 131                              Default depends on DBMS''',
 132     )
 133     parser.add_argument(
 134         "-w",
 135         "--warning",
 136         metavar='RANGE',
 137         default=MP_INF,
 138         type=levels,
 139         help='''lower and upper level for the warning state,
 140                              separated by a colon''',
 141     )
 142     parser.add_argument(
 143         "-c",
 144         "--critical",
 145         metavar='RANGE',
 146         default=MP_INF,
 147         type=levels,
 148         help='''lower and upper level for the critical state,
 149                              separated by a colon''',
 150     )
 151     parser.add_argument(
 152         "-t",
 153         "--text",
 154         default='',
 155         help='''Additional text prefixed to the output''',
 156     )
 157
 158     # required arguments
 159     parser.add_argument(
 160         "-n",
 161         "--name",
 162         required=True,
 163         help='''Name of the database on the DBMS''',
 164     )
 165     parser.add_argument(
 166         "-u",
 167         "--user",
 168         required=True,
 169         help='''Username for database access''',
 170     )
 171     parser.add_argument(
 172         "-p",
 173         "--password",
 174         required=True,
 175         help='''Password for database access''',
 176     )
 177     parser.add_argument(
 178         "cmd",
 179         metavar="SQL-Statement|Procedure",
 180         type=sql_cmd_piece,
 181         nargs='+',
 182         help='''Valid SQL-Statement for the selected database.
 183                              The statement must return at least a number and a
 184                              string, plus optional performance data.
 185
 186                              Alternatively: If the the "-o" option is given,
 187                              treat the argument as a procedure name.
 188
 189                              The procedure must return one output variable,
 190                              which content is evaluated the same way as the
 191                              output of the SQL-Statement''',
 192     )
 193     args = parser.parse_args(argv[1:])
 194     args.cmd = ' '.join(args.cmd)
 195
 196     # LOGGING
 197     fmt = "%(message)s"
 198     if args.verbose > 1:
 199         fmt = "%(levelname)s: %(lineno)s: " + fmt
 200         if args.dbms == "mssql":
 201             os.environ["TDSDUMP"] = "stdout"
 202     logging.basicConfig(level=max(30 - 10 * args.verbose, 0), format=fmt)
 203
 204     # V-VERBOSE INFO
 205     for key, val in args.__dict__.items():
 206         if key in ('user', 'password'):
 207             val = '****'
 208         LOG.debug('argparse: %s = %r', key, val)
 209     return args
 210
 211
 212 #.
 213
 214
 215 def bail_out(exit_code, output):
 216     state_txt = ["OK", "WARN", "CRIT", "UNKNOWN"][exit_code]
 217     sys.stdout.write("%s - %s\n" % (state_txt, output))
 218     sys.exit(exit_code)
 219
 220
 221 #   . DBMS specific code here!
 222 #
 223 # For every DBMS specify a connect and execute function.
 224 # Add them in the dict in the 'main' connect and execute functions
 225 #
 226 def _default_execute(cursor, cmd, inpt, procedure):
 227     if procedure:
 228         LOG.info("SQL Procedure Name: %s", cmd)
 229         LOG.info("Input Values: %s", inpt)
 230         cursor.callproc(cmd, inpt)
 231         LOG.debug("inpt after 'callproc' = %r", inpt)
 232     else:
 233         LOG.info("SQL Statement: %s", cmd)
 234         cursor.execute(cmd)
 235
 236     return cursor.fetchall()
 237
 238
 239 def postgres_connect(host, port, db_name, user, pwd):
 240     import psycopg2
 241     return psycopg2.connect(host=host, port=port, \
 242                             database=db_name, user=user, password=pwd)
 243
 244
 245 def postgres_execute(cursor, cmd, inpt, procedure):
 246     return _default_execute(cursor, cmd, inpt, procedure)
 247
 248
 249 def mysql_connect(host, port, db_name, user, pwd):
 250     import MySQLdb
 251     return MySQLdb.connect(host=host, port=port, db=db_name, \
 252                            user=user, passwd=pwd)
 253
 254
 255 def mysql_execute(cursor, cmd, inpt, procedure):
 256     return _default_execute(cursor, cmd, inpt, procedure)
 257
 258
 259 def mssql_connect(host, port, db_name, user, pwd):
 260     import pymssql
 261     return pymssql.connect(host=host, port=port, \
 262                            database=db_name, user=user, password=pwd)
 263
 264
 265 def mssql_execute(cursor, cmd, _inpt, procedure):
 266     if procedure:
 267         LOG.info("SQL Procedure Name: %s", cmd)
 268         cmd = 'EXEC ' + cmd
 269     else:
 270         LOG.info("SQL Statement: %s", cmd)
 271
 272     cursor.execute(cmd)
 273
 274     return cursor.fetchall()
 275
 276
 277 def oracle_connect(host, port, db_name, user, pwd):
 278     sys.path.append(
 279         '/usr/lib/python%s.%s/site-packages' % (sys.version_info.major, sys.version_info.minor))
 280     try:
 281         import cx_Oracle  # pylint: disable=import-error
 282     except ImportError as exc:
 283         bail_out(3, "%s. Please install it via 'pip install cx_Oracle'." % exc)
 284
 285     cstring = "%s/%s@%s:%s/%s" % (user, pwd, host, port, db_name)
 286     return cx_Oracle.connect(cstring)
 287
 288
 289 def oracle_execute(cursor, cmd, inpt, procedure):
 290     try:
 291         import cx_Oracle  # pylint: disable=import-error
 292     except ImportError as exc:
 293         bail_out(3, "%s. Please install it via 'pip install cx_Oracle'." % exc)
 294
 295     if procedure:
 296         LOG.info("SQL Procedure Name: %s", cmd)
 297         LOG.info("Input Values: %s", inpt)
 298
 299         # In an earlier version, this code-branch
 300         # had been executed regardles of the dbms.
 301         # clearly this is oracle specific.
 302         outvar = cursor.var(cx_Oracle.STRING)  # pylint:disable=undefined-variable
 303         # However, I have not been able to test it.
 304         inpt.append(outvar)
 305
 306         cursor.callproc(cmd, inpt)
 307
 308         LOG.debug("inpt after 'callproc' = %r", inpt)
 309         LOG.debug("outvar = %r", outvar)
 310
 311         # for empty input this is just
 312         #  _res = outvar.getvalue()
 313         _res = ','.join(i.getvalue() for i in inpt)
 314         LOG.debug("outvar.getvalue() = %r", _res)
 315         params_result = _res.split(",")
 316         LOG.debug("params_result = %r", params_result)
 317
 318     else:
 319         LOG.info("SQL Statement: %s", cmd)
 320         cursor.execute(cmd)
 321
 322     return cursor.fetchall()
 323
 324
 325 def db2_connect(host, port, db_name, user, pwd):
 326     # IBM data server driver
 327     try:
 328         import ibm_db  # pylint: disable=import-error
 329         import ibm_db_dbi  # pylint: disable=import-error
 330     except ImportError as exc:
 331         bail_out(3, "%s. Please install it via pip." % exc)
 332
 333     cstring = 'DRIVER={IBM DB2 ODBC DRIVER};DATABASE=%s;' \
 334               'HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s;' \
 335               % (db_name, host, port, user, pwd)
 336     ibm_db_conn = ibm_db.connect(cstring, '', '')
 337     return ibm_db_dbi.Connection(ibm_db_conn)
 338
 339
 340 def db2_execute(cursor, cmd, inpt, procedure):
 341     return _default_execute(cursor, cmd, inpt, procedure)
 342
 343
 344 #.
 345
 346
 347 def connect(dbms, host, port, db_name, user, pwd):
 348     """Connect to the correct database
 349
 350     A python library is imported depending on the value of dbms.
 351     Return the created connection object.
 352     """
 353     if port is None:
 354         port = DEFAULT_PORTS[dbms]
 355
 356     return {
 357         'postgres': postgres_connect,
 358         'mysql': mysql_connect,
 359         'mssql': mssql_connect,
 360         'oracle': oracle_connect,
 361         'db2': db2_connect,
 362     }[dbms](host, port, db_name, user, pwd)
 363
 364
 365 def execute(dbms, connection, cmd, inpt, procedure=False):
 366     """Execute the sql statement, or call the procedure.
 367
 368     Some corrections are made for libraries that do not adhere to the
 369     python SQL API: https://www.python.org/dev/peps/pep-0249/
 370     """
 371     cursor = connection.cursor()
 372
 373     try:
 374         result = {
 375             'postgres': postgres_execute,
 376             'mysql': mysql_execute,
 377             'mssql': mssql_execute,
 378             'oracle': oracle_execute,
 379             'db2': db2_execute,
 380         }[dbms](cursor, cmd, inpt, procedure)
 381     finally:
 382         cursor.close()
 383         connection.close()
 384
 385     LOG.info("SQL Result:\n%r", result)
 386     return result
 387
 388
 389 def process_result(result, warn, crit, metrics=False, debug=False):
 390     """Process the first row (!) of the result of the SQL command.
 391
 392     Only the first row of the result (result[0]) is considered.
 393     It is assumed to be an sequence of length 3, consisting of of
 394     [numerical_value, text, performance_data].
 395     The full result is returned as muliline output.
 396     """
 397     if not result:
 398         bail_out(3, "SQL statement/procedure returned no data")
 399     row0 = result[0]
 400
 401     number = float(row0[0])
 402
 403     # handle case where sql query only results in one column
 404     if len(row0) == 1:
 405         text = "%s" % row0[0]
 406     else:
 407         text = "%s" % row0[1]
 408
 409     perf = ""
 410     if metrics:
 411         try:
 412             perf = " | performance_data=%s" % str(row0[2])
 413         except IndexError:
 414             if debug:
 415                 raise
 416
 417     state = 0
 418     if warn != MP_INF or crit != MP_INF:
 419         if not warn[0] < number < warn[1]:
 420             state = 1
 421         if not crit[0] < number < crit[1]:
 422             state = 2
 423         text += ": %s" % number
 424     else:  # no levels were given
 425         if number in (0, 1, 2, 3):
 426             state = int(number)
 427         else:
 428             bail_out(3, "<%d> is not a state, and no levels given" % number)
 429
 430     return state, text + perf
 431
 432
 433 def main(argv=None):
 434
 435     args = parse_args(argv or sys.argv)
 436
 437     try:
 438         msg = "connecting to database"
 439         conn = connect(args.dbms, args.hostname, args.port, args.name, args.user, args.password)
 440
 441         msg = "executing SQL command"
 442         result = execute(args.dbms, conn, args.cmd, args.input, procedure=args.procedure)
 443
 444         msg = "processing result of SQL statement/procedure"
 445         state, text = process_result(
 446             result,
 447             args.warning,
 448             args.critical,
 449             metrics=args.metrics,
 450             debug=args.debug,
 451         )
 452     except () if args.debug else Exception as exc:
 453         errmsg = str(exc).strip('()').replace(r'\n', ' ')
 454         bail_out(3, "Error while %s: %s" % (msg, errmsg))
 455
 456     bail_out(state, args.text + text)
 457
 458
 459 if __name__ == '__main__':
 460     main()