[checkmk-commits] 3671 mssql_backup: Is now aware of backup types and outputs ages per backup type

Module: check_mk Branch: master Commit: b33a2c6357dd65e07713f0eec2f86187478b7f35 URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=b33a2c6357dd65… Author: Lars Michelsen &lt;lm(a)mathias-kettner.de&gt; Date: Wed Jul 13 14:51:15 2016 +0200 3671 mssql_backup: Is now aware of backup types and outputs ages per backup type --- .werks/3671 | 9 ++++++ ChangeLog | 3 +- agents/windows/plugins/mssql.vbs | 16 ++++++--- checks/mssql_backup | 66 +++++++++++++++++++++++++++++++------- web/plugins/metrics/check_mk.py | 49 ++++++++++++++++++++++++++++ 5 files changed, 127 insertions(+), 16 deletions(-) diff --git a/.werks/3671 b/.werks/3671 new file mode 100644 index 0000000..d55129d --- /dev/null +++ b/.werks/3671 @@ -0,0 +1,9 @@ +Title: mssql_backup: Is now aware of backup types and outputs ages per backup type +Level: 1 +Component: checks +Compatible: compat +Version: 1.4.0i1 +Date: 1468414240 +Class: feature + + diff --git a/ChangeLog b/ChangeLog index 84c803c..9dae57a 100644 --- a/ChangeLog +++ b/ChangeLog @@ -112,6 +112,7 @@ * 3691 oracle_jobs: status of missing jobs is now configurable * 3554 Inotify: Added configurable levels for movedto, movefrom and move self * 3706 hp_proliant_systeminfo, hp_proliant_mem, hp_proliant_da_phydrv: new inventory plugins + * 3671 mssql_backup: Is now aware of backup types and outputs ages per backup type * 3073 FIX: windows agent: relative paths to mrpe scripts are now treated as relative to the agent installation directory... * 3061 FIX: mk_jolokia: Fixed debugging of the agent plugin * 3074 FIX: windows agent: fixed incorrect values for 32-bit performance counters @@ -411,8 +412,8 @@ * 3657 Custom host attributes can now be configured... * 3658 LDAP: Connections can now be enabled/disabled per site... * 3553 SNMPv3 noAuthNoPriv: Fixed diagnostic page - * 3667 Search expressions in host search can now be given as regexes... * 3154 added ability to retrieve passwords for active checks from a password store + * 3667 Search expressions in host search can now be given as regexes... * 3060 FIX: Folder properties: Fixed exception when a user has no alias set... * 3062 FIX: Git integration: Fixed not adding files in WATO folders to git control * 3203 FIX: Distributed WATO: Fixed exception in remote host service discovery... diff --git a/agents/windows/plugins/mssql.vbs b/agents/windows/plugins/mssql.vbs index 4a529af..538c97d 100644 --- a/agents/windows/plugins/mssql.vbs +++ b/agents/windows/plugins/mssql.vbs @@ -277,17 +277,25 @@ For Each instance_id In instances.Keys ' Loop all databases to get the date of the last backup. Only show databases ' which have at least one backup - Dim lastBackupDate + Dim lastBackupDate, backup_type addOutput( "<<<mssql_backup>>>" ) 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 " & _ + RS.open "SELECT CONVERT(VARCHAR, DATEADD(s, DATEDIFF(s, '19700101', MAX(backup_finish_date)), '19700101'), 120) AS last_backup_date," & _ + "type " & _ "FROM msdb.dbo.backupset " & _ - "WHERE database_name = '" & dbName & "'", CONN + "WHERE database_name = '" & dbName & "'" & _ + "GROUP BY type", CONN Do While Not RS.Eof lastBackupDate = Trim(RS("last_backup_date")) + + backup_type = Trim(RS("type")) + If backup_type = "" Then + backup_type = "-" + End If + If lastBackupDate <> "" Then addOutput("MSSQL_" & instance_id & " " & Replace(dbName, " ", "_") & _ - " " & lastBackupDate) + " " & lastBackupDate & " " & backup_type) End If RS.MoveNext Loop diff --git a/checks/mssql_backup b/checks/mssql_backup index 47f865b..0a8f215 100644 --- a/checks/mssql_backup +++ b/checks/mssql_backup @@ -24,9 +24,28 @@ # to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, # Boston, MA 02110-1301 USA. +# Old agent # <<<mssql_backup>>> # MSSQL_SQLEXPRESS test123 1331207325 +# Newer agent +# <<<mssql_backup>>> +# MSSQL_SQL0x master 2016-07-08 20:20:27 +# MSSQL_SQL0x model 2016-07-08 20:20:28 +# MSSQL_SQL0x model 2016-07-12 09:09:42 +# MSSQL_SQL0x model 2016-07-11 20:20:07 +# MSSQL_SQL0x msdb 2016-07-08 20:20:43 +# MSSQL_SQL0x msdb 2016-07-11 20:20:07 + +# New agent (added backup type) +# <<<mssql_backup>>> +# MSSQL_SQL0x master 2016-07-08 20:20:27 D +# MSSQL_SQL0x model 2016-07-08 20:20:28 D +# MSSQL_SQL0x model 2016-07-12 09:09:42 L +# MSSQL_SQL0x model 2016-07-11 20:20:07 I +# MSSQL_SQL0x msdb 2016-07-08 20:20:43 D +# MSSQL_SQL0x msdb 2016-07-11 20:20:07 I + # Might be None to have no thresholds or a tuple of two ints # (<warn age in seconds>, <crit age in seconds>) mssql_backup_default_levels = None @@ -34,21 +53,40 @@ mssql_backup_default_levels = None def inventory_mssql_backup(info): return [ (line[0] + ' ' + line[1], 'mssql_backup_default_levels') for line in info - if len(line) == 4 ] + if len(line) in [3, 4, 5] ] def check_mssql_backup(item, params, info): + def backup_type_name(backup_type): + return { + "D": "database ", + "I": "database diff ", + "L": "log ", + "F": "file or filegroup ", + "G": "file diff ", + "P": "partial ", + "Q": "partial diff ", + "-": "unspecific ", + }.get(backup_type, "") + for line in info: - if len(line) not in [3, 4]: + if len(line) not in [3, 4, 5]: continue last_backup_date = False - try: - inst, tablespace, last_backup_date, last_backup_time = line - except ValueError: + if len(line) == 3: inst, tablespace, last_backup_timestamp = line + backup_type = None + + elif len(line) == 4: + inst, tablespace, last_backup_date, last_backup_time = line + backup_type = None + + elif len(line) == 5: + inst, tablespace, last_backup_date, last_backup_time, backup_type = line if item == inst + ' ' + tablespace: if last_backup_date: + # TODO: Timezone issue? Agent should always send UTC timestamp. timestamp = time.mktime(time.strptime(last_backup_date + ' ' + last_backup_time, '%Y-%m-%d %H:%M:%S')) else: @@ -57,20 +95,26 @@ def check_mssql_backup(item, params, info): state = 0 sec_ago = time.time() - timestamp + age_warn, age_crit = None, None if params is not None: if sec_ago >= params[1]: state = 2 elif sec_ago >= params[0]: state = 1 - perfdata = [('seconds', sec_ago, params[0], params[1])] + age_warn, age_crit = params[0], params[1] + + if backup_type == None: + perfkey = "seconds" else: - perfdata = [('seconds', sec_ago)] + perfkey = "backup_age_%s" % backup_type_name(backup_type).strip().replace(" ", "_") + + perfdata = [(perfkey, sec_ago, age_warn, age_crit)] - return (state, 'Last backup was at %s (%s ago)' % - (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(timestamp)), - get_age_human_readable(sec_ago)), perfdata) + yield state, 'Last %sbackup was at %s (%s ago)' % \ + (backup_type_name(backup_type), + time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(timestamp)), + get_age_human_readable(sec_ago)), perfdata - return (3, 'Tablespace %s could not be found' % item) check_info['mssql_backup'] = { 'check_function': check_mssql_backup, diff --git a/web/plugins/metrics/check_mk.py b/web/plugins/metrics/check_mk.py index 8a5dc97..4eec359 100644 --- a/web/plugins/metrics/check_mk.py +++ b/web/plugins/metrics/check_mk.py @@ -2472,6 +2472,55 @@ metric_info["job_duration"] = { "color" : "33/a", } +metric_info["backup_age_database"] = { + "title" : _("Age of last database backup"), + "unit" : "s", + "color" : "11/a", +} + +metric_info["backup_age_database_diff"] = { + "title" : _("Age of last differential database backup"), + "unit" : "s", + "color" : "14/a", +} + +metric_info["backup_age_log"] = { + "title" : _("Age of last log backup"), + "unit" : "s", + "color" : "21/a", +} + +metric_info["backup_age_file_or_filegroup"] = { + "title" : _("Age of last file or filegroup backup"), + "unit" : "s", + "color" : "24/a", +} + +metric_info["backup_age_file_diff"] = { + "title" : _("Age of last differential file backup"), + "unit" : "s", + "color" : "31/a", +} + +metric_info["backup_age_partial"] = { + "title" : _("Age of last partial backup"), + "unit" : "s", + "color" : "34/a", +} + +metric_info["backup_age_differential_partial"] = { + "title" : _("Age of last differential partial backup"), + "unit" : "s", + "color" : "41/a", +} + +metric_info["backup_age"] = { + "title" : _("Time since last backup"), + "unit" : "s", + "color" : "34/a", +} + + metric_info["checkpoint_age"] = { "title" : _("Time since last checkpoint"), "unit" : "s",