Module: check_mk Branch: master Commit: 0766ab0c9fd86dc701e5847dbff6442b0bd865a7 URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=0766ab0c9fd86d… Author: Simon Betz &lt;si(a)mathias-kettner.de&gt; Date: Mon Jun 25 15:57:49 2018 +0200 6140 mssql_blocked_sessions: Now the check is instance aware Change-Id: Id5b316d26cea6d71895700dd2f126ff63f032905 --- .werks/6140 | 18 +++++++ agents/windows/plugins/mssql.vbs | 9 +++- checks/mssql_blocked_sessions | 90 ++++++++++++++++++++------------ cmk/gui/plugins/wato/check_parameters.py | 34 ++++++++++++ cmk_base/config.py | 2 +- 5 files changed, 118 insertions(+), 35 deletions(-) diff --git a/.werks/6140 b/.werks/6140 new file mode 100644 index 0000000..b336ef8 --- /dev/null +++ b/.werks/6140 @@ -0,0 +1,18 @@ +Title: mssql_blocked_sessions: This check is now instance aware +Level: 1 +Component: checks +Compatible: incomp +Edition: cre +Version: 1.6.0i1 +Date: 1529935714 +Class: feature + +In order to make this check work take the following steps: +<ul> +<li>Install the newest agent plugin {{mssql.vbs}}.</li> + +<li>Transform all configured parameters from the old deprecared ruleset +{{MSSQL Blocked Sessions}} into the new ruleset of the same name.</li> + +<li>Perform a rediscovery on the affected hosts.</li> +</ul> diff --git a/agents/windows/plugins/mssql.vbs b/agents/windows/plugins/mssql.vbs index f7e7921..c50f109 100644 --- a/agents/windows/plugins/mssql.vbs +++ b/agents/windows/plugins/mssql.vbs @@ -307,13 +307,18 @@ For Each instance_id In instances.Keys: Do ' Continue trick "FROM sys.dm_os_waiting_tasks " & _ "WHERE blocking_session_id <> 0 ", CONN addOutput(sections("blocked_sessions")) - Dim session_id, wait_duration_ms, wait_type, blocking_session_id + Dim instanceName, session_id, wait_duration_ms, wait_type, blocking_session_id Do While NOT RS.Eof + instanceName = Replace(Trim(RS("instance_name")), " ", "_") + If instanceName = "" Then + instanceName = "None" + End If session_id = Trim(RS("session_id")) wait_duration_ms = Trim(RS("wait_duration_ms")) wait_type = Trim(RS("wait_type")) blocking_session_id = Trim(RS("blocking_session_id")) - addOutput(session_id & "|" & wait_duration_ms & "|" & wait_type & "|" & blocking_session_id) + addOutput(instanceName & "|" session_id & "|" & wait_duration_ms & "|" & _ + wait_type & "|" & blocking_session_id) RS.MoveNext Loop RS.Close diff --git a/checks/mssql_blocked_sessions b/checks/mssql_blocked_sessions index a9e163a..2d6519e 100644 --- a/checks/mssql_blocked_sessions +++ b/checks/mssql_blocked_sessions @@ -35,49 +35,74 @@ # schema as "mssql_instance". -factory_settings['mssql_blocked_sessions_default_levels'] = {'state': 2} +factory_settings['mssql_blocked_sessions_default_levels'] = { + 'state': 2, +} + + +def parse_mssql_blocked_sessions(info): + db_instance = collections. namedtuple("DBInstance", ["session_id", "wait_type", + "blocking_session_id", "wait_duration"]) + parsed = {} + for line in info: + if len(line) == 5: + inst, session_id, wait_duration_ms, wait_type, blocking_session_id = line + elif len(line) == 4: + session_id, wait_duration_ms, wait_type, blocking_session_id = line + inst = "" + else: + continue + parsed.setdefault(inst, []).append( + db_instance(session_id, wait_type, + blocking_session_id, float(wait_duration_ms)/1000,)) + return parsed -def inventory_mssql_blocked_sessions(info): - return [(None, {})] +def inventory_mssql_blocked_sessions(parsed): + for instance in parsed: + yield instance, {} + + +def check_mssql_blocked_sessions(item, params, parsed): + if item not in parsed: + # Assume general connection problem to the database, which is reported + # by the "X Instance" service and skip this check. + raise MKCounterWrapped("Failed to retrieve data from database") -def check_mssql_blocked_sessions(_no_item, params, info): summary = {} details = [] + warn, crit = params.get('waittime', (None, None)) ignored_waittypes = set() - for line in info: - if "blocked" in line[0].lower(): - continue # Skip debug output which was send by old agent by accident + waittypes_to_be_ignored = params.get("ignore_waittypes", []) - session_id, wait_duration_ms, wait_type, blocking_session_id = line - - if wait_type in params.get("ignore_waittypes", []): - ignored_waittypes.add(wait_type) + for e in parsed[item]: + if e.wait_type in waittypes_to_be_ignored: + ignored_waittypes.add(e.wait_type) continue + level_info = "" state = 0 - if params.has_key('waittime'): - warn, crit = params['waittime'] - wait_duration_sec = float(wait_duration_ms) / 1000 - if wait_duration_sec >= crit: - state = 2 - elif wait_duration_sec >= warn: - state = 1 - - summary.setdefault(session_id, set()) - summary[session_id].add(blocking_session_id) - details.append((state, "Session %s blocked by %s (Wait %s ms, Type: %s)" % \ - (session_id, blocking_session_id, wait_duration_ms, wait_type))) + if crit is not None and e.wait_duration >= crit: + state = 2 + elif warn is not None and e.wait_duration >= warn: + state = 1 + if state: + level_info = " (warn/crit at %s/%s)" % ( + get_age_human_readable(warn), + get_age_human_readable(crit)) + + summary.setdefault(e.session_id, 0) + summary[e.session_id] += 1 + details.append((state, "Session %s blocked by %s (Type: %s, Wait: %s)%s" % \ + (e.session_id, e.blocking_session_id, e.wait_type, + get_age_human_readable(e.wait_duration), level_info))) if summary: - yield params['state'], "Summary: %s" % \ - ", ".join(["%s blocked by %s ID(s)" % (k, len(v)) - for k,v in summary.items()]) - - fst_state, fst_infotext = details[0] - yield fst_state, "\nDetails:\n%s" % fst_infotext - for state, infotext in details[1:]: + yield params['state'], "Summary: %s" % ( + ", ".join(["%s blocked by %s ID(s)" % (k, v) + for k,v in summary.iteritems()])) + for state, infotext in details: yield state, "\n%s" % infotext else: @@ -88,9 +113,10 @@ def check_mssql_blocked_sessions(_no_item, params, info): check_info['mssql_blocked_sessions'] = { + 'parse_function' : parse_mssql_blocked_sessions, 'inventory_function' : inventory_mssql_blocked_sessions, 'check_function' : check_mssql_blocked_sessions, - 'service_description' : 'MSSQL Blocked Sessions', - 'group' : 'mssql_blocked_sessions', + 'service_description' : 'MSSQL %s Blocked Sessions', + 'group' : 'mssql_instance_blocked_sessions', 'default_levels_variable' : 'mssql_blocked_sessions_default_levels', } diff --git a/cmk/gui/plugins/wato/check_parameters.py b/cmk/gui/plugins/wato/check_parameters.py index 55778f0..57d9f3c 100644 --- a/cmk/gui/plugins/wato/check_parameters.py +++ b/cmk/gui/plugins/wato/check_parameters.py @@ -11026,6 +11026,40 @@ register_check_parameters( ), None, "dict", + deprecated=True, +) + +register_check_parameters( + subgroup_applications, + "mssql_instance_blocked_sessions", + _("MSSQL Blocked Sessions"), + Dictionary( + elements = [ + ("state", MonitoringState( + title=_("State of MSSQL Blocked Sessions is treated as"), + help=_("The default state if there is at least one " + "blocked session."), + default_value=2, + )), + ("waittime", Tuple( + title=_("Levels for wait"), + help=_("The threshholds for wait_duration_ms. Will " + "overwrite the default state set above."), + default_value=(0, 0), + elements=[ + Float(title = _("Warning at"), unit = _("seconds"), display_format = "%.3f"), + Float(title = _("Critical at"), unit = _("seconds"), display_format = "%.3f"), + ] + )), + ("ignore_waittypes", DualListChoice( + title=_("Ignore wait types"), + rows=40, + choices=[ (entry, entry) for entry in mssql_waittypes ], + )), + ], + ), + TextAscii(title=_("Instance name")), + "dict", ) register_check_parameters( diff --git a/cmk_base/config.py b/cmk_base/config.py index 9bc15b6..be96928 100644 --- a/cmk_base/config.py +++ b/cmk_base/config.py @@ -1323,7 +1323,7 @@ _old_service_descriptions = { "mssql_tablespaces" : "%s Sizes", "mssql_transactionlogs" : "Transactionlog %s", "mssql_versions" : "%s Version", - + "mssql_blocked_sessions" : lambda item: (False, "MSSQL Blocked Sessions"), } def service_description(hostname, check_plugin_name, item):