[checkmk-commits] 6125 mssql_connections: new check to monitor active connections of a MSSQL database

Module: check_mk Branch: master Commit: 7424414393ac2b496ae75213accc109c80269052 URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=7424414393ac2b… Author: Tom Baerwinkel &lt;tb(a)mathias-kettner.de&gt; Date: Thu Jul 19 17:25:27 2018 +0200 6125 mssql_connections: new check to monitor active connections of a MSSQL database Change-Id: Iee362225857b4771e7345b4e813b22a7ec652659 --- .werks/6125 | 10 +++++ agents/windows/plugins/mssql.vbs | 16 +++++++ checkman/mssql_connections | 21 +++++++++ checks/mssql_connections | 77 ++++++++++++++++++++++++++++++++ cmk/gui/plugins/wato/check_parameters.py | 25 +++++++++++ 5 files changed, 149 insertions(+) diff --git a/.werks/6125 b/.werks/6125 new file mode 100644 index 0000000..4d46a3b --- /dev/null +++ b/.werks/6125 @@ -0,0 +1,10 @@ +Title: mssql_connections: new check to monitor active connections of a MSSQL database +Level: 1 +Component: checks +Compatible: compat +Edition: cre +Version: 1.6.0i1 +Date: 1532013883 +Class: feature + + diff --git a/agents/windows/plugins/mssql.vbs b/agents/windows/plugins/mssql.vbs index 3781217..fe7888e 100644 --- a/agents/windows/plugins/mssql.vbs +++ b/agents/windows/plugins/mssql.vbs @@ -101,6 +101,7 @@ sections.add "datafiles", "<<<mssql_datafiles:sep(124)>>>" sections.add "clusters", "<<<mssql_cluster:sep(124)>>>" ' Has been deprecated with 1.4.0i1. Keep this for nicer transition for some versions. sections.add "versions", "<<<mssql_versions:sep(124)>>>" +sections.add "connections", "<<<mssql_connections>>>" For Each section_id In sections.Keys addOutput(sections(section_id)) @@ -506,6 +507,21 @@ For Each instance_id In instances.Keys: Do ' Continue trick addOutput(instance_id & "|" & Replace(dbName, " ", "_") & "|" & active_node & "|" & nodes) Loop While False: Next + addOutput(sections("connections")) + Dim connection_count, database_name + + RS.Open "SELECT name AS DBName, ISNULL((SELECT COUNT(dbid) AS NumberOfConnections FROM " &_ + "sys.sysprocesses WHERE dbid > 0 AND name = DB_NAME(dbid) GROUP BY dbid ),0) AS NumberOfConnections " &_ + "FROM sys.databases", CONN + + Do While Not RS.Eof + database_name = RS("DBName") + connection_count = RS("NumberOfConnections") + + addOutput(instance_id & " " & Replace(database_name, " ", "_") & " " & connection_count) + RS.MoveNext + Loop + CONN.Close Loop While False: Next diff --git a/checkman/mssql_connections b/checkman/mssql_connections new file mode 100644 index 0000000..08523ea --- /dev/null +++ b/checkman/mssql_connections @@ -0,0 +1,21 @@ +title: MSSQL Connections +agents: windows +catalog: app/mssql +license: GPL +distribution: check_mk +description: + This check reports number of active connections for a database. + + If the number of active connections exceeds the defined upper + levels the check result will be {WARN} or {CRIT}. + + This check needs the Check_MK mssql.vbs plugin installed in + the agent. Please refer to the online documentation + for how to install that plugin. + +item: + The MSSQL database identifier. + +inventory: + One check is created for each database. + diff --git a/checks/mssql_connections b/checks/mssql_connections new file mode 100644 index 0000000..57c7176 --- /dev/null +++ b/checks/mssql_connections @@ -0,0 +1,77 @@ +#!/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_connections>>> +# MSSQLSERVER master 16 +# MSSQLSERVER tempdb 1 + + +factory_settings["mssql_connections_default_levels"] = { + "levels" : (20, 50), +} + + +def parse_mssql_connections(info): + parsed = {} + for line in info: + try: + instance, db_name, connection_count = line + connection_count = int(connection_count) + parsed.setdefault("%s %s" % (instance, db_name), connection_count) + except ValueError: + pass + return parsed + + +def inventory_mssql_connections(parsed): + for item in parsed: + yield item, {} + + +def check_mssql_connections(item, params, parsed): + # If a database is missing in the agent output there are no + # active connections for that database. + connection_count = parsed.get(item, 0) + + warn, crit = params['levels'] + if connection_count >= crit: + return 2, 'Connections: %d (warn/crit at %d/%d)' % (connection_count, warn, crit) + elif connection_count >= warn: + return 1, 'Connections: %d (warn/crit at %d/%d)' % (connection_count, warn, crit) + else: + return 0, 'Connections: %d' % connection_count + + +check_info['mssql_connections'] = { + 'parse_function' : parse_mssql_connections, + 'inventory_function' : inventory_mssql_connections, + 'check_function' : check_mssql_connections, + 'service_description' : 'MSSQL Connections %s', + 'group' : 'mssql_connections', + 'default_levels_variable' : "mssql_connections_default_levels", +} diff --git a/cmk/gui/plugins/wato/check_parameters.py b/cmk/gui/plugins/wato/check_parameters.py index b8d8a9f..cfcb1d6 100644 --- a/cmk/gui/plugins/wato/check_parameters.py +++ b/cmk/gui/plugins/wato/check_parameters.py @@ -3042,6 +3042,31 @@ register_check_parameters( "dict", ) + +register_check_parameters( + subgroup_applications, + "mssql_connections", + _("MSSQL Connections"), + Dictionary( + elements = [ + ( "levels", + Tuple( + title = _("Upper levels for the number of active database connections"), + elements = [ + Integer(title = _("Warning if over"), default_value=20), + Integer(title = _("Critical if over"), default_value=50), + ] + ),) + ] + ), + TextAscii( + title = _("Database identifier"), + allow_empty = True + ), + 'dict', +) + + register_check_parameters( subgroup_applications, "varnish_cache",