checks/mssql_counters

   1 #!/usr/bin/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 # There are different types of information. Can we handle them in a
  28 # general way? There are:
  29 #  - Percentage values
  30 #  - Size values in KB
  31 #  - Counters
  32 #  - Rate counters (per second)
  33
  34 # <<<mssql_counters>>>
  35 # MSSQL_SQLEXPRESS:Buffer_Manager Buffer_cache_hit_ratio 12
  36 # MSSQL_SQLEXPRESS:Databases master Data_File(s)_Size_(KB) 2304
  37 # MSSQL_SQLEXPRESS:Databases master Transactions/sec 13733
  38 # MSSQL_SQLEXPRESS:Databases master Percent_Log_Used 57
  39 # MSSQL_SQLEXPRESS:Databases master Log_File(s)_Size_(KB)
  40 # FOOBAR 170
  41
  42 inventory_mssql_counters_rules = []
  43
  44 #TODO if not counters: raise
  45
  46
  47 def inventory_mssql_counters_generic(parsed, want_counters, dflt=None):
  48     want_counters = set(want_counters)
  49     for (obj, instance), node_data in parsed.iteritems():
  50         for counters in node_data.values():
  51             if not want_counters.intersection(counters.keys()):
  52                 continue
  53             yield "%s %s" % (obj, instance), dflt
  54
  55
  56 #   .--main----------------------------------------------------------------.
  57 #   |                                       _                              |
  58 #   |                       _ __ ___   __ _(_)_ __                         |
  59 #   |                      | '_ ` _ \ / _` | | '_ \                        |
  60 #   |                      | | | | | | (_| | | | | |                       |
  61 #   |                      |_| |_| |_|\__,_|_|_| |_|                       |
  62 #   |                                                                      |
  63 #   +----------------------------------------------------------------------+
  64 #   |                                                                      |
  65 #   '----------------------------------------------------------------------'
  66
  67 # Previously there was no main check, but we need it because
  68 # the sub checks
  69 # - mssql_counters.transactions
  70 # - mssql_counters.pageactivity
  71 # - mssql_counters.locks
  72 # will become cluster aware and thus all subchecks, too, because they share
  73 # the same section. This main check is just a dummy with the benefit of the
  74 # parse function.
  75
  76
  77 def parse_mssql_counters_base(info):
  78     parsed = {}
  79     for line in info:
  80         node_name = line[0]
  81         line = line[1:]
  82         if len(line) < 3:
  83             continue
  84
  85         obj, counter, instance = line[:3]
  86         if obj.endswith(':Databases'):
  87             obj = obj[:-10]
  88
  89         values = line[3:]
  90         if len(values) == 1:
  91             values = values[0]
  92             try:
  93                 values = float(values)
  94             except ValueError:
  95                 try:
  96                     values = int(values)
  97                 except ValueError:
  98                     pass
  99
 100         if counter == "utc_time":
 101             # mssql returns localized format. great! let's try ...
 102             try:
 103                 # ... iso 8601
 104                 values = utc_mktime(
 105                     time.strptime(" ".join(values).split(".")[0], "%Y-%m-%d %H:%M:%S"))
 106             except ValueError:
 107                 try:
 108                     # ... german
 109                     values = utc_mktime(time.strptime(" ".join(values), "%d.%m.%Y %H:%M:%S"))
 110                 except ValueError:
 111                     pass
 112
 113         data = parsed.setdefault((obj, instance), {})\
 114                      .setdefault(node_name, {})
 115         data.setdefault(counter, values)
 116     return parsed
 117
 118
 119 def inventory_mssql_counters_base(parsed):
 120     return []
 121
 122
 123 def check_mssql_counters_base(item, params, parsed):
 124     return 3, 'Not implemented check plugin'
 125
 126
 127 check_info['mssql_counters'] = {
 128     'parse_function': parse_mssql_counters_base,
 129     'inventory_function': inventory_mssql_counters_base,
 130     'check_function': check_mssql_counters_base,
 131     'service_description': "MSSQL",
 132     'node_info': True,
 133 }
 134
 135 #.
 136 #   .--Percentage based values---------------------------------------------.
 137 #   |          ____                         _                              |
 138 #   |         |  _ \ ___ _ __ ___ ___ _ __ | |_ __ _  __ _  ___            |
 139 #   |         | |_) / _ \ '__/ __/ _ \ '_ \| __/ _` |/ _` |/ _ \           |
 140 #   |         |  __/  __/ | | (_|  __/ | | | || (_| | (_| |  __/           |
 141 #   |         |_|   \___|_|  \___\___|_| |_|\__\__,_|\__, |\___|           |
 142 #   |                                                |___/                 |
 143 #   |      _                        _              _                       |
 144 #   |     | |__   __ _ ___  ___  __| | __   ____ _| |_   _  ___  ___       |
 145 #   |     | '_ \ / _` / __|/ _ \/ _` | \ \ / / _` | | | | |/ _ \/ __|      |
 146 #   |     | |_) | (_| \__ \  __/ (_| |  \ V / (_| | | |_| |  __/\__ \      |
 147 #   |     |_.__/ \__,_|___/\___|\__,_|   \_/ \__,_|_|\__,_|\___||___/      |
 148 #   |                                                                      |
 149 #   '----------------------------------------------------------------------'
 150
 151
 152 def inventory_mssql_counters_cache_hits(parsed, want_counters):
 153     add_zero_based_services = host_extra_conf_merged(host_name(), inventory_mssql_counters_rules)\
 154                               .get('add_zero_based_services', False)
 155
 156     for (obj, instance), node_data in parsed.iteritems():
 157         for counters in node_data.values():
 158             for counter in counters.keys():
 159                 if counter not in want_counters:
 160                     continue
 161
 162                 if counters.get('%s_base' % counter, 0.0) == 0.0 \
 163                    and not add_zero_based_services:
 164                     continue
 165
 166                 yield "%s %s %s" % (obj, instance, counter), None
 167
 168
 169 def check_mssql_counters_cache_hits(item, params, parsed):
 170     obj, instance, counter = item.split()
 171     node_data = parsed.get((obj, instance), {})
 172
 173     for node_name, counters in node_data.iteritems():
 174         value = counters.get(counter)
 175         base = counters.get("%s_base" % counter, 0)
 176
 177         if value is None or base is None:
 178             # Assume general connection problem to the database, which is reported
 179             # by the "X Instance" service and skip this check.
 180             raise MKCounterWrapped("Failed to connect to database")
 181
 182         if base == 0:
 183             base = 1
 184         perc = 100.0 * value / base
 185
 186         node_info = ""
 187         if node_name:
 188             node_info = "[%s] " % node_name
 189         infotext = "%s%s" % (node_info, get_percent_human_readable(perc))
 190         state = 0
 191         if params:
 192             #TODO: Previously params=None(=dflt) in inventory_mssql_counters
 193             warn, crit = params
 194             if perc <= crit:
 195                 state = 2
 196             elif perc <= warn:
 197                 state = 1
 198             if state:
 199                 infotext += " (warn/crit below %s/%s)" % (warn, crit)
 200         yield state, infotext, [(counter, perc)]
 201
 202
 203 check_info['mssql_counters.cache_hits'] = {
 204     'inventory_function'    : lambda parsed: inventory_mssql_counters_cache_hits(parsed,
 205                               ['cache_hit_ratio', 'log_cache_hit_ratio', 'buffer_cache_hit_ratio']),
 206     'check_function'        : check_mssql_counters_cache_hits,
 207     'service_description'   : "MSSQL %s",
 208     'has_perfdata'          : True,
 209     'node_info'             : True,
 210 }
 211
 212 #.
 213 #   .--Rates---------------------------------------------------------------.
 214 #   |                       ____       _                                   |
 215 #   |                      |  _ \ __ _| |_ ___  ___                        |
 216 #   |                      | |_) / _` | __/ _ \/ __|                       |
 217 #   |                      |  _ < (_| | ||  __/\__ \                       |
 218 #   |                      |_| \_\__,_|\__\___||___/                       |
 219 #   |                                                                      |
 220 #   '----------------------------------------------------------------------'
 221
 222 #   ---transactions---------------------------------------------------------
 223
 224
 225 def check_mssql_counters_transactions(item, params, parsed):
 226     obj, instance = item.split()
 227     node_data = parsed.get((obj, instance), {})
 228
 229     if not any(node_data.values()):
 230         # Assume general connection problem to the database, which is reported
 231         # by the "X Instance" service and skip this check.
 232         raise MKCounterWrapped("Failed to connect to database")
 233
 234     for node_name, counters in node_data.iteritems():
 235         now = counters.get('utc_time')
 236         if now is None:
 237             now = time.time()
 238
 239         node_info = ""
 240         if node_name:
 241             node_info = "[%s] " % node_name
 242
 243         for counter_key, title in [
 244             ('transactions/sec', 'Transactions'),
 245             ('write_transactions/sec', 'Write Transactions'),
 246             ('tracked_transactions/sec', 'Tracked Transactions'),
 247         ]:
 248             value = counters.get(counter_key)
 249             if value is None:
 250                 continue
 251
 252             rate = get_rate("mssql_counters.transactions.%s.%s.%s" % (node_name, item, counter_key),
 253                             now, value)
 254             infotext = "%s%s: %.1f/s" % (node_info, title, rate)
 255             node_info = ""
 256             yield 0, infotext, [(counter_key, rate)]
 257
 258
 259 check_info['mssql_counters.transactions'] = {
 260     'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
 261                               ['transactions/sec', 'write_transactions/sec', 'tracked_transactions/sec']),
 262     'check_function'        : check_mssql_counters_transactions,
 263     'service_description'   : "MSSQL %s Transactions",
 264     'has_perfdata'          : True,
 265     'node_info'             : True,
 266 }
 267
 268 #   ---locks----------------------------------------------------------------
 269
 270
 271 def check_mssql_counters_locks(item, params, parsed):
 272     obj, instance = item.split()
 273     node_data = parsed.get((obj, instance), {})
 274
 275     if not any(node_data.values()):
 276         # Assume general connection problem to the database, which is reported
 277         # by the "X Instance" service and skip this check.
 278         raise MKCounterWrapped("Failed to connect to database")
 279
 280     for node_name, counters in node_data.iteritems():
 281         now = counters.get('utc_time')
 282         if now is None:
 283             now = time.time()
 284
 285         node_info = ""
 286         if node_name:
 287             node_info = "[%s] " % node_name
 288
 289         for counter_key, title in [
 290             ('lock_requests/sec', 'Requests'),
 291             ('lock_timeouts/sec', 'Timeouts'),
 292             ('number_of_deadlocks/sec', 'Deadlocks'),
 293             ('lock_waits/sec', 'Waits'),
 294         ]:
 295             value = counters.get(counter_key)
 296             if value is None:
 297                 continue
 298
 299             rate = get_rate("mssql_counters.locks.%s.%s.%s" % (node_name, item, counter_key), now,
 300                             value)
 301             infotext = "%s%s: %.1f/s" % (node_info, title, rate)
 302             node_info = ""
 303
 304             state = 0
 305             warn, crit = params.get(counter_key, (None, None))
 306             if crit is not None and rate >= crit:
 307                 state = 2
 308             elif warn is not None and rate >= warn:
 309                 state = 1
 310             if state:
 311                 infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)
 312
 313             yield state, infotext, [(counter_key, rate, warn, crit)]
 314
 315
 316 check_info['mssql_counters.locks'] = {
 317     'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
 318                               ['number_of_deadlocks/sec', 'lock_requests/sec', 'lock_timeouts/sec', 'lock_waits/sec'],
 319                               dflt={}),
 320     'check_function'        : check_mssql_counters_locks,
 321     'service_description'   : "MSSQL %s Locks",
 322     'has_perfdata'          : True,
 323     'group'                 : 'mssql_counters_locks',
 324     'node_info'             : True,
 325 }
 326
 327 #   ---sql stats------------------------------------------------------------
 328
 329
 330 def inventory_mssql_counters_sqlstats(parsed, want_counters, dflt=None):
 331     for (obj, instance), node_data in parsed.iteritems():
 332         for counters in node_data.values():
 333             for counter in counters:
 334                 if counter not in want_counters:
 335                     continue
 336                 yield "%s %s %s" % (obj, instance, counter), dflt
 337
 338
 339 def check_mssql_counters_sqlstats(item, params, parsed):
 340     obj, instance, counter = item.split()
 341     node_data = parsed.get((obj, instance), {})
 342
 343     if not any(node_data.values()):
 344         # Assume general connection problem to the database, which is reported
 345         # by the "X Instance" service and skip this check.
 346         raise MKCounterWrapped("Failed to connect to database")
 347
 348     for node_name, counters in node_data.iteritems():
 349         value = counters.get(counter)
 350         if value is None:
 351             return
 352
 353         now = counters.get('utc_time')
 354         if now is None:
 355             now = time.time()
 356
 357         rate = get_rate("mssql_counters.sqlstats.%s.%s.%s" % (node_name, item, counter), now, value)
 358         node_info = ""
 359         if node_name:
 360             node_info = "[%s] " % node_name
 361         infotext = "%s%.1f/sec" % (node_info, rate)
 362
 363         state = 0
 364         warn, crit = params.get(counter, (None, None))
 365         if crit is not None and rate >= crit:
 366             state = 2
 367         elif warn is not None and rate >= warn:
 368             state = 1
 369         if state:
 370             infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)
 371
 372         yield state, infotext, [(counter, rate, warn, crit)]
 373
 374
 375 check_info["mssql_counters.sqlstats"] = {
 376     "inventory_function"    : lambda parsed: inventory_mssql_counters_sqlstats(parsed,
 377                               ["batch_requests/sec", "sql_compilations/sec", "sql_re-compilations/sec"],
 378                               dflt={}),
 379     "check_function"        : check_mssql_counters_sqlstats,
 380     "service_description"   : "MSSQL %s",
 381     "has_perfdata"          : True,
 382     "group"                 : "mssql_stats",
 383     'node_info'             : True,
 384 }
 385
 386 #   ---page activity--------------------------------------------------------
 387
 388
 389 def check_mssql_counters_pageactivity(item, params, parsed):
 390     obj, instance = item.split()
 391     node_data = parsed.get((obj, instance), {})
 392
 393     if not any(node_data.values()):
 394         # Assume general connection problem to the database, which is reported
 395         # by the "X Instance" service and skip this check.
 396         raise MKCounterWrapped("Failed to connect to database")
 397
 398     for node_name, counters in node_data.iteritems():
 399         now = counters.get('utc_time')
 400         if now is None:
 401             now = time.time()
 402
 403         node_info = ""
 404         if node_name:
 405             node_info = "[%s] " % node_name
 406
 407         for counter_key, title in [
 408             ("page_reads/sec", "Reads"),
 409             ("page_writes/sec", "Writes"),
 410             ("page_lookups/sec", "Lookups"),
 411         ]:
 412             value = counters.get(counter_key)
 413             if value is None:
 414                 continue
 415
 416             rate = get_rate("mssql_counters.pageactivity.%s.%s.%s" % (node_name, item, counter_key),
 417                             now, value)
 418             infotext = "%s%s: %.1f/s" % (node_info, title, rate)
 419             node_info = ""
 420
 421             state = 0
 422             warn, crit = params.get(counter_key, (None, None))
 423             if crit is not None and rate >= crit:
 424                 state = 2
 425             elif warn is not None and rate >= warn:
 426                 state = 1
 427             if state:
 428                 infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)
 429
 430             yield state, infotext, [(counter_key, rate, warn, crit)]
 431
 432
 433 check_info['mssql_counters.pageactivity'] = {
 434     'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
 435                               ["page_reads/sec", "page_writes/sec", "page_lookups/sec"],
 436                               dflt={}),
 437     'check_function'        : check_mssql_counters_pageactivity,
 438     'service_description'   : "MSSQL %s Page Activity",
 439     'has_perfdata'          : True,
 440     'group'                 : "mssql_page_activity",
 441     'node_info'             : True,
 442 }
 443
 444 #   ---locks per batch------------------------------------------------------
 445
 446
 447 def inventory_mssql_counters_locks_per_batch(parsed):
 448     db_names = [(obj.split(":")[0], node_name)
 449                 for (obj, _instance), node_data in parsed.iteritems() if ":" in obj
 450                 for node_name in node_data]
 451
 452     for db_name, node_name in db_names:
 453         if "lock_requests/sec" in parsed.get(("%s:Locks" % db_name, "_Total"), {})\
 454                                         .get(node_name, {}) \
 455            and "batch_requests/sec" in parsed.get(("%s:SQL_Statistics" % db_name, "None"))\
 456                                              .get(node_name, {}):
 457             yield db_name, {}
 458
 459
 460 def check_mssql_counters_locks_per_batch(item, params, parsed):
 461     data_locks_data = parsed.get(("%s:Locks" % item, "_Total"), {})
 462     data_stats_data = parsed.get(("%s:SQL_Statistics" % item, "None"), {})
 463
 464     if not any(data_locks_data.values() + data_stats_data.values()):
 465         # Assume general connection problem to the database, which is reported
 466         # by the "X Instance" service and skip this check.
 467         raise MKCounterWrapped("Failed to connect to database")
 468
 469     for node_name in set(data_locks_data.keys() + data_stats_data.keys()):
 470         data_locks = data_locks_data[node_name]
 471         data_stats = data_stats_data[node_name]
 472         now = data_locks.get('utc_time', data_stats.get('utc_time'))
 473         if now is None:
 474             now = time.time()
 475
 476         locks = data_locks["lock_requests/sec"]
 477         batches = data_stats["batch_requests/sec"]
 478
 479         lock_rate = get_rate("mssql_counters_locks_per_batch.%s.%s.locks" % (node_name, item), now,
 480                              locks)
 481         batch_rate = get_rate("mssql_counters_locks_per_batch.%s.%s.batches" % (node_name, item),
 482                               now, batches)
 483
 484         if batch_rate == 0:
 485             lock_per_batch = 0
 486         else:
 487             lock_per_batch = lock_rate / batch_rate
 488
 489         node_info = ""
 490         if node_name:
 491             node_info = "[%s] " % node_name
 492         infotext = "%s%.1f" % (node_info, lock_per_batch)
 493         state = 0
 494
 495         warn, crit = params.get('locks_per_batch', (None, None))
 496         if crit is not None and lock_per_batch >= crit:
 497             state = 2
 498         elif warn is not None and lock_per_batch >= warn:
 499             state = 1
 500
 501         if state:
 502             infotext += " (warn/crit at %.1f/%.1f per second)" % (warn, crit)
 503
 504         yield state, infotext, [("locks_per_batch", lock_per_batch, warn, crit)]
 505
 506
 507 check_info["mssql_counters.locks_per_batch"] = {
 508     "inventory_function": inventory_mssql_counters_locks_per_batch,
 509     "check_function": check_mssql_counters_locks_per_batch,
 510     "service_description": "MSSQL %s Locks per Batch",
 511     "has_perfdata": True,
 512     "group": "mssql_stats",
 513     'node_info': True,
 514 }
 515
 516 #.
 517 #   .--File Sizes----------------------------------------------------------.
 518 #   |                _____ _ _        ____  _                              |
 519 #   |               |  ___(_) | ___  / ___|(_)_______  ___                 |
 520 #   |               | |_  | | |/ _ \ \___ \| |_  / _ \/ __|                |
 521 #   |               |  _| | | |  __/  ___) | |/ /  __/\__ \                |
 522 #   |               |_|   |_|_|\___| |____/|_/___\___||___/                |
 523 #   |                                                                      |
 524 #   '----------------------------------------------------------------------'
 525
 526
 527 def check_mssql_file_sizes(item, params, parsed):
 528     obj, instance = item.split()
 529     node_data = parsed.get((obj, instance), {})
 530
 531     if not any(node_data.values()):
 532         # Assume general connection problem to the database, which is reported
 533         # by the "X Instance" service and skip this check.
 534         raise MKCounterWrapped("Failed to connect to database")
 535
 536     if not params:
 537         params = {}
 538
 539     for node_name, counters in node_data.iteritems():
 540         node_info = ""
 541         if node_name:
 542             node_info = "[%s] " % node_name
 543
 544         log_files_size = counters.get("log_file(s)_size_(kb)")
 545         for val_bytes, key, title in [
 546             (counters.get("data_file(s)_size_(kb)"), "data_files", "Data files"),
 547             (log_files_size, "log_files", "Log files total"),
 548         ]:
 549             if val_bytes is None:
 550                 continue
 551
 552             val_bytes = val_bytes * 1024
 553             infotext = "%s%s: %s" % (node_info, title, get_bytes_human_readable(val_bytes))
 554             node_info = ""
 555
 556             state = 0
 557             warn, crit = params.get(key, (None, None))
 558             if crit is not None and val_bytes >= crit:
 559                 state = 2
 560             elif warn is not None and val_bytes >= warn:
 561                 state = 1
 562             if state:
 563                 infotext += " (warn/crit at %s/%s)" % (get_bytes_human_readable(warn),
 564                                                        get_bytes_human_readable(crit))
 565
 566             yield state, infotext, [(key, val_bytes, warn, crit)]
 567
 568         log_files_used = counters.get("log_file(s)_used_size_(kb)")
 569         infotext = "Log files used: %s" % get_bytes_human_readable(log_files_used)
 570         try:
 571             log_files_used_perc = 100.0 * log_files_used / log_files_size
 572             infotext += ", %s" % get_percent_human_readable(log_files_used_perc)
 573         except (TypeError, ZeroDivisionError):
 574             log_files_used_perc = None
 575
 576         warn, crit = params.get("log_files_used", (None, None))
 577         if isinstance(crit, float) and log_files_used_perc is not None:
 578             log_files_used_value = log_files_used_perc
 579             readable_f = get_percent_human_readable
 580         elif isinstance(warn, int):
 581             log_files_used_value = log_files_used
 582             readable_f = get_bytes_human_readable
 583         else:
 584             yield 0, infotext
 585             continue
 586
 587         state = 0
 588         if crit is not None and log_files_used_value >= crit:
 589             state = 2
 590         elif warn is not None and log_files_used_value >= warn:
 591             state = 1
 592         if state:
 593             infotext += " (warn/crit at %s/%s)" % (readable_f(warn), readable_f(crit))
 594         yield state, infotext
 595
 596
 597 check_info['mssql_counters.file_sizes'] = {
 598     'inventory_function'    : lambda parsed: inventory_mssql_counters_generic(parsed,
 599                               ['data_file(s)_size_(kb)', 'log_file(s)_size_(kb)', 'log_file(s)_used_size_(kb)'],
 600                               dflt={}),
 601     'check_function'        : check_mssql_file_sizes,
 602     'service_description'   : "MSSQL %s File Sizes",
 603     'has_perfdata'          : True,
 604     'group'                 : "mssql_file_sizes",
 605     'node_info'             : True,
 606 }