Module: check_mk Branch: master Commit: fbad9166363bde1a10dea0167f148786333e502c URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=fbad9166363bde… Author: Bastian Kuhn &lt;bk(a)mathias-kettner.de&gt; Date: Mon Aug 10 14:43:48 2015 +0200 #1278 mssql_blocked_sessions: New Check to show blocked MSSQL Sessions --- .werks/1278 | 9 +++++++ ChangeLog | 1 + agents/windows/plugins/mssql.vbs | 16 ++++++++++++ checkman/mssql_blocked_sessions | 15 ++++++++++++ checks/mssql_blocked_sessions | 50 ++++++++++++++++++++++++++++++++++++++ 5 files changed, 91 insertions(+) diff --git a/.werks/1278 b/.werks/1278 new file mode 100644 index 0000000..b3b75f2 --- /dev/null +++ b/.werks/1278 @@ -0,0 +1,9 @@ +Title: mssql_blocked_sessions: New Check to show blocked MSSQL Sessions +Level: 1 +Component: checks +Compatible: compat +Version: 1.2.7i3 +Date: 1439210600 +Class: feature + + diff --git a/ChangeLog b/ChangeLog index 3d650d9..747bcd9 100644 --- a/ChangeLog +++ b/ChangeLog @@ -40,6 +40,7 @@ * 2417 winperf_phydisk: can now work in a cluster environment * 2523 ipmi: new wato rule to set ignored sensors and activate/deactivate summarizing * 1277 check_ldap: It is now possible to specify a alternative Hostname... + * 1278 mssql_blocked_sessions: New Check to show blocked MSSQL Sessions * 2315 FIX: windows agent: BOM replacement, fixed incorrect byte offset... * 2316 FIX: windows agent: fix garbled output of cached agent plugins... * 2358 FIX: check_mk_agent.solaris: more correct computation of zfs used space... diff --git a/agents/windows/plugins/mssql.vbs b/agents/windows/plugins/mssql.vbs index 69ff3d3..95d68bb 100644 --- a/agents/windows/plugins/mssql.vbs +++ b/agents/windows/plugins/mssql.vbs @@ -202,6 +202,21 @@ For Each instId In instIds.Keys Loop RS.Close + RS.Open "SELECT session_id, wait_duration_ms, wait_type, blocking_session_id " & _ + "FROM sys.dm_os_waiting_tasks " & _ + "WHERE blocking_session_id <> 0 ", CONN + addOutput( "<<<mssql_blocked_sessions>>>" ) + Dim session_id, wait_duration_ms, wait_type, blocking_session_id + Do While NOT RS.Eof + 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 ) + RS.MoveNext + Loop + RS.Close + ' First only read all databases in this instance and save it to the db names dict RS.Open "EXEC sp_databases", CONN Dim x, dbName, dbNames @@ -257,6 +272,7 @@ For Each instId In instIds.Keys ' which have at least one backup Dim lastBackupDate addOutput( "<<<mssql_backup>>>" ) + addOutput( "Blocked _Sessions" ) For Each dbName in dbNames.Keys RS.open "SELECT CONVERT(VARCHAR, DATEADD(s, DATEDIFF(s, '19700101', MAX(backup_finish_date)), '19700101'), 120) AS last_backup_date " & _ "FROM msdb.dbo.backupset " & _ diff --git a/checkman/mssql_blocked_sessions b/checkman/mssql_blocked_sessions new file mode 100644 index 0000000..5617a78 --- /dev/null +++ b/checkman/mssql_blocked_sessions @@ -0,0 +1,15 @@ +title: MSSQL Blocked Sessions +agents: windows +catalog: app/mssql +license: GPL +distribution: check_mk +description: + This Checks lists all bolcked sessions on a MSSQL Server. As long at least one session is blocked, + it will be {CRITICAL}. + + 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. + +inventory: + One Check per Host will be created diff --git a/checks/mssql_blocked_sessions b/checks/mssql_blocked_sessions new file mode 100644 index 0000000..033e3e9 --- /dev/null +++ b/checks/mssql_blocked_sessions @@ -0,0 +1,50 @@ +#!/usr/bin/python +# -*- encoding: utf-8; py-indent-offset: 4 -*- +# +------------------------------------------------------------------+ +# | ____ _ _ __ __ _ __ | +# | / ___| |__ ___ ___| | __ | \/ | |/ / | +# | | | | '_ \ / _ \/ __| |/ / | |\/| | ' / | +# | | |___| | | | __/ (__| < | | | | . \ | +# | \____|_| |_|\___|\___|_|\_\___|_| |_|_|\_\ | +# | | +# | Copyright Mathias Kettner 2014 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- +# ails. 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. +# +# session_id, wait_duration_ms, wait_type, blocking_session_id +# <<<mssql_blocked_sessions>>> +# Blocked _Sessions +# 119 232292187 LCK_M_U 75 +# 76 221526672 LCK_M_U 115 + + +def inventory_mssql_blocked_sessions(info): + return [(None, None)] + +def check_mssql_blocked_sessions(item, params, info): + if len(info) > 1: + for session_id, wait_duration_ms, wait_type, blocking_session_id in info[1:]: + yield 2, "Session %s blocked by %s (Wait %s ms, Type: %s)" % \ + ( session_id, blocking_session_id, wait_duration_ms, wait_type ) + else: + yield 0, "No blocked sessions" + + +check_info['mssql_blocked_sessions'] = { + 'check_function' : check_mssql_blocked_sessions, + 'inventory_function' : inventory_mssql_blocked_sessions, + 'service_description' : "MSSQL Blocked Sessions", +}