Module: check_mk Branch: master Commit: b90f720df38df86921f2a4935383a89eecd5e9a5 URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=b90f720df38df8… Author: Simon Betz &lt;si(a)mathias-kettner.de&gt; Date: Tue Jan 24 12:36:22 2017 +0100 4296 mssql_databases: new check which monitors the auto close and shrink state of MSSQL databases Auto close and shrink states are configurable via WATO. Change-Id: I3ca9de33ad944c0dd8b0c8b95dfa4bcfebbb28a4 --- .werks/4296 | 9 +++++ ChangeLog | 1 + agents/windows/plugins/mssql.vbs | 21 +++++++++- checks/mssql_databases | 78 ++++++++++++++++++++++++++++++++++++ web/plugins/wato/check_parameters.py | 30 ++++++++++++++ 5 files changed, 137 insertions(+), 2 deletions(-) diff --git a/.werks/4296 b/.werks/4296 new file mode 100644 index 0000000..84a9fd5 --- /dev/null +++ b/.werks/4296 @@ -0,0 +1,9 @@ +Title: mssql_databases: new check which monitors the auto close and shrink state of MSSQL databases +Level: 1 +Component: checks +Compatible: compat +Version: 1.4.0i4 +Date: 1485257668 +Class: feature + +Auto close and shrink states are configurable via WATO. diff --git a/ChangeLog b/ChangeLog index b5bffcb..33a678a 100644 --- a/ChangeLog +++ b/ChangeLog @@ -17,6 +17,7 @@ * 4257 logwatch.groups: now allows regular expressions within include and exclude patterns each beginning with a tilde * 4013 Ruckus Spot / ruckus_spot_locations: new check to monitor the number of unqiue MACs addresses... * 4293 wut_webtherm: now detects devices which support WebGraphThermoBaro-MIB + * 4296 mssql_databases: new check which monitors the auto close and shrink state of MSSQL databases... * 4171 FIX: perle_psmu: fixed value error... * 4172 FIX: smart.temp: fixed discovery and check function... * 4173 FIX: if.include: ignore some more keys in 'Network interfaces and switch ports' rule which are only used for interface grouping diff --git a/agents/windows/plugins/mssql.vbs b/agents/windows/plugins/mssql.vbs index 6a4fdb5..cc7fc6a 100644 --- a/agents/windows/plugins/mssql.vbs +++ b/agents/windows/plugins/mssql.vbs @@ -102,6 +102,7 @@ End If Dim sections, section_id Set sections = CreateObject("Scripting.Dictionary") sections.add "instance", "<<<mssql_instance:sep(124)>>>" +sections.add "databases", "<<<mssql_databases>>>" sections.add "counters", "<<<mssql_counters>>>" sections.add "tablespaces", "<<<mssql_tablespaces>>>" sections.add "blocked_sessions", "<<<mssql_blocked_sessions>>>" @@ -185,7 +186,7 @@ For Each instance_id In instances.Keys: Do ' Continue trick ' Use either an instance specific config file named mssql_<instance-id>.ini ' or the default mysql.ini file. - cfg_file = cfg_dir & "\mssql_" & instance & ".ini" + cfg_file = cfg_dir & "\mssql_" & instance_id & ".ini" If Not FSO.FileExists(cfg_file) Then cfg_file = cfg_dir & "\mssql.ini" If Not FSO.FileExists(cfg_file) Then @@ -394,7 +395,23 @@ For Each instance_id In instances.Keys: Do ' Continue trick Loop RS.Close Next - + + ' Database properties, full list at https://msdn.microsoft.com/en-us/library/ms186823.aspx + addOutput(sections("databases")) + RS.Open "SELECT name, " & _ + "DATABASEPROPERTYEX(name, 'Status') AS Status, " & _ + "DATABASEPROPERTYEX(name, 'Recovery') AS Recovery, " & _ + "DATABASEPROPERTYEX(name, 'IsAutoClose') AS auto_close, " & _ + "DATABASEPROPERTYEX(name, 'IsAutoShrink') AS auto_shrink " & _ + "FROM master.dbo.sysdatabases", CONN + Do While Not RS.Eof + ' instance db_name status recovery auto_close auto_shrink + addOutput( instance_id & " " & Replace(Trim(RS("name")), " ", "_") & " " & Trim(RS("Status")) & _ + " " & Trim(RS("Recovery")) & " " & Trim(RS("auto_close")) & " " & Trim(RS("auto_shrink")) ) + RS.MoveNext + Loop + RS.Close + addOutput(sections("clusters")) Dim active_node, nodes For Each dbName in dbNames.Keys : Do diff --git a/checks/mssql_databases b/checks/mssql_databases new file mode 100644 index 0000000..1f65198 --- /dev/null +++ b/checks/mssql_databases @@ -0,0 +1,78 @@ +#!/usr/bin/python +# -*- encoding: utf-8; py-indent-offset: 4 -*- +# +------------------------------------------------------------------+ +# | ____ _ _ __ __ _ __ | +# | / ___| |__ ___ ___| | __ | \/ | |/ / | +# | | | | '_ \ / _ \/ __| |/ / | |\/| | ' / | +# | | |___| | | | __/ (__| < | | | | . \ | +# | \____|_| |_|\___|\___|_|\_\___|_| |_|_|\_\ | +# | | +# | Copyright Mathias Kettner 2017 mk(a)mathias-kettner.de | +# +------------------------------------------------------------------+ +# +# This file is part of Check_MK. +# The official homepage is at http://mathias-kettner.de/check_mk. +# +# check_mk is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation in version 2. check_mk is distributed +# in the hope that it will be useful, but WITHOUT ANY WARRANTY; with- +# out even the implied warranty of MERCHANTABILITY or FITNESS FOR A +# PARTICULAR PURPOSE. See the GNU General Public License for more de- +# tails. You should have received a copy of the GNU General Public +# License along with GNU Make; see the file COPYING. If not, write +# to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, +# Boston, MA 02110-1301 USA. + + +# example output +# <<<mssql_databases>>>^M +# MSSQL_MSSQL46 CorreLog_Report_T ONLINE FULL 0 0^M +# MSSQL_MSSQL46 DASH_CONFIG_T ONLINE FULL 0 0^M +# MSSQL_MSSQL46 master ONLINE SIMPLE 0 0^M +# MSSQL_MSSQL46 model ONLINE FULL 0 0^M +# MSSQL_MSSQL46 msdb ONLINE SIMPLE 0 0^M +# MSSQL_MSSQL46 NOC_ALARM_T ONLINE FULL 0 1^M +# MSSQL_MSSQL46 NOC_CONFIG_T ONLINE FULL 0 0^M +# MSSQL_MSSQL46 tempdb ONLINE SIMPLE 0 0^M +# MSSQL_MSSQL46 test_autoclose ONLINE FULL 1 0^M + + +def parse_mssql_databases(info): + parsed = {} + headers = ['Instance', 'DBname', 'Status', + 'Recovery', 'auto_close', 'auto_shrink'] + + for line in info: + if len(line) == 6: + data = dict(zip(headers, line)) + parsed.setdefault("%s %s" % (data["Instance"], data["DBname"]), data) + + return parsed + + +def inventory_mssql_databases(parsed): + return [ (db_name, {}) for db_name in parsed ] + + +def check_mssql_databases(item, params, parsed): + map_states = {"1" : (2, "on"), + "0" : (0, "off"), } + + if item in parsed: + data = parsed[item] + yield 0, 'Status: %s, Recovery: %s' % (data["Status"], data["Recovery"]) + + for what in ["close", "shrink"]: + state, state_readable = map_states[data["auto_%s" % what]] + state = params.get("map_auto_%s_state" % what, {}).get(state_readable, state) + yield state, 'Auto %s: %s' % (what, state_readable) + + +check_info['mssql_databases'] = { + 'parse_function' : parse_mssql_databases, + 'inventory_function' : inventory_mssql_databases, + 'check_function' : check_mssql_databases, + 'service_description' : '%s Database', + 'group' : 'mssql_databases', +} diff --git a/web/plugins/wato/check_parameters.py b/web/plugins/wato/check_parameters.py index d659622..6b8cafb 100644 --- a/web/plugins/wato/check_parameters.py +++ b/web/plugins/wato/check_parameters.py @@ -2260,6 +2260,36 @@ register_check_parameters( register_check_parameters( subgroup_applications, + "mssql_databases", + _("MSSQL Databases properties"), + Dictionary( + elements = [ + ("map_auto_close_state", Dictionary( + elements = [ + ( "on", MonitoringState(title=_("Auto close on")) ), + ( "off", MonitoringState(title=_("Auto close off")) ), + ], + title = _('Map auto close status'), + optional_keys = [], + )), + ("map_auto_shrink_state", Dictionary( + elements = [ + ( "on", MonitoringState(title=_("Auto shrink on")) ), + ( "off", MonitoringState(title=_("Auto shrink off")) ), + ], + title = _('Map auto shrink status'), + optional_keys = [], + )), + ] + ), + TextAscii( + title = _("Database identifier"), + ), + "dict", +) + +register_check_parameters( + subgroup_applications, "varnish_cache", _("Varnish Cache"), Dictionary(