Simone Bizzotto
2018-11-15 17:54:53 UTC
Hello guys,
I maybe found an improvement for mssql.vbs.
The mssql_backup check now allows different dates for different backup types, however there's a corner-case where you get an alert you shouldn't get.
The corner case is having check_mk alert you for log backups, let's say, older than 30 minutes and having that particular database in SIMPLE mode.
Now, if a database "borns" as SIMPLE, there's no history for log backups whatsoever, so the current plugin works fine, because it doesn't report any data.
But, if you had at some point in time that database in FULL recovery model, and then switched to SIMPLE .... Well, you have the history, the latest log backup is days old, and you get an alert.
I propose a new query within mssql.vbs that skips returning latest log backup if the database is indeed in SIMPLE recovery model.
Modification is quite easy
RS.Open "DECLARE @HADRStatus sql_variant; DECLARE @SQLCommand nvarchar(max); " & _
"SET @HADRStatus = (SELECT SERVERPROPERTY ('IsHadrEnabled')); " & _
"IF (@HADRStatus IS NULL or @HADRStatus <> 1) " & _
"BEGIN " & _
"SET @SQLCommand = 'SELECT CONVERT(VARCHAR, DATEADD(s, DATEDIFF(s, ''19700101'', MAX(backup_finish_date)), ''19700101''), 120) AS last_backup_date, " & _
"type, machine_name, ''True'' as is_primary_replica, ''1'' as is_local, '''' as replica_id FROM msdb.dbo.backupset " & _
--> "INNER JOIN sys.databases ON msdb.dbo.backupset.database_name = sys.databases.name " & _
"WHERE database_name = ''" & dbName & "'' AND machine_name = SERVERPROPERTY(''Machinename'') " & _
--> "AND NOT (sys.databases.recovery_model_desc = ''SIMPLE'' AND msdb.dbo.backupset.type = ''L'') " & _
"GROUP BY type, machine_name ' " & _
"END " & _
"ELSE " & _
"BEGIN " & _
"SET @SQLCommand = 'SELECT CONVERT(VARCHAR, DATEADD(s, DATEDIFF(s, ''19700101'', MAX(b.backup_finish_date)), ''19700101''), 120) AS last_backup_date, " & _
"b.type, b.machine_name, isnull(rep.is_primary_replica,0) as is_primary_replica, rep.is_local, isnull(convert(varchar(40), rep.replica_id), '''') AS replica_id " & _
"FROM msdb.dbo.backupset b " & _
"LEFT OUTER JOIN sys.databases db ON b.database_name = db.name " & _
"LEFT OUTER JOIN sys.dm_hadr_database_replica_states rep ON db.database_id = rep.database_id " & _
"WHERE database_name = ''" & dbName & "'' AND (rep.is_local is null or rep.is_local = 1) " & _
"AND (rep.is_primary_replica is null or rep.is_primary_replica = ''True'') and machine_name = SERVERPROPERTY(''Machinename'') " & _
--> "AND NOT (db.recovery_model_desc = ''SIMPLE'' AND b.backupset.type = ''L'') " & _
"GROUP BY type, rep.replica_id, rep.is_primary_replica, rep.is_local, b.database_name, b.machine_name, rep.synchronization_state, rep.synchronization_health' " & _
"END " & _
"EXEC (@SQLCommand)" ,CONN
Let me know what you think
Simone Bizzotto
Il Gruppo ABB in Italia ha adottato il Modello Organizzativo ai sensi del D.Lgs. 231/2001, in forza del quale l'assunzione di obbligazioni da parte di societa' ABB avviene a firma congiunta di due procuratori muniti di idonei poteri, con la sola eccezione delle persone che rivestono la carica di Amministratore Delegato o di Direttore Generale.Le informazioni contenute in questo messaggio di posta elettronica sono riservate e confidenziali e ne e' vietata la diffusione in qualunque modo eseguita.Qualora Lei non fosse la persona cui il presente messaggio e' destinato, e' invitata a non diffonderlo, e ad eliminarlo, dandone gentilmente comunicazione al mittente.
ABB Group in Italy adopts a Compliance Programme under the Italian Law (D.Lgs.231/2001). According to this ABB Compliance Programme, any commitment of ABB Italian Companies is taken by the double signature of ABB Representatives granted by a proper Power of Attorney with the only exception of Managing Director or General Manager.The information included in this e-mail and any attachments are confidential and may also be privileged. If you are not the correct recipient, you are kindly requested to notify the sender immediately, to cancel it and not to disclose the contents to any other person.
I maybe found an improvement for mssql.vbs.
The mssql_backup check now allows different dates for different backup types, however there's a corner-case where you get an alert you shouldn't get.
The corner case is having check_mk alert you for log backups, let's say, older than 30 minutes and having that particular database in SIMPLE mode.
Now, if a database "borns" as SIMPLE, there's no history for log backups whatsoever, so the current plugin works fine, because it doesn't report any data.
But, if you had at some point in time that database in FULL recovery model, and then switched to SIMPLE .... Well, you have the history, the latest log backup is days old, and you get an alert.
I propose a new query within mssql.vbs that skips returning latest log backup if the database is indeed in SIMPLE recovery model.
Modification is quite easy
RS.Open "DECLARE @HADRStatus sql_variant; DECLARE @SQLCommand nvarchar(max); " & _
"SET @HADRStatus = (SELECT SERVERPROPERTY ('IsHadrEnabled')); " & _
"IF (@HADRStatus IS NULL or @HADRStatus <> 1) " & _
"BEGIN " & _
"SET @SQLCommand = 'SELECT CONVERT(VARCHAR, DATEADD(s, DATEDIFF(s, ''19700101'', MAX(backup_finish_date)), ''19700101''), 120) AS last_backup_date, " & _
"type, machine_name, ''True'' as is_primary_replica, ''1'' as is_local, '''' as replica_id FROM msdb.dbo.backupset " & _
--> "INNER JOIN sys.databases ON msdb.dbo.backupset.database_name = sys.databases.name " & _
"WHERE database_name = ''" & dbName & "'' AND machine_name = SERVERPROPERTY(''Machinename'') " & _
--> "AND NOT (sys.databases.recovery_model_desc = ''SIMPLE'' AND msdb.dbo.backupset.type = ''L'') " & _
"GROUP BY type, machine_name ' " & _
"END " & _
"ELSE " & _
"BEGIN " & _
"SET @SQLCommand = 'SELECT CONVERT(VARCHAR, DATEADD(s, DATEDIFF(s, ''19700101'', MAX(b.backup_finish_date)), ''19700101''), 120) AS last_backup_date, " & _
"b.type, b.machine_name, isnull(rep.is_primary_replica,0) as is_primary_replica, rep.is_local, isnull(convert(varchar(40), rep.replica_id), '''') AS replica_id " & _
"FROM msdb.dbo.backupset b " & _
"LEFT OUTER JOIN sys.databases db ON b.database_name = db.name " & _
"LEFT OUTER JOIN sys.dm_hadr_database_replica_states rep ON db.database_id = rep.database_id " & _
"WHERE database_name = ''" & dbName & "'' AND (rep.is_local is null or rep.is_local = 1) " & _
"AND (rep.is_primary_replica is null or rep.is_primary_replica = ''True'') and machine_name = SERVERPROPERTY(''Machinename'') " & _
--> "AND NOT (db.recovery_model_desc = ''SIMPLE'' AND b.backupset.type = ''L'') " & _
"GROUP BY type, rep.replica_id, rep.is_primary_replica, rep.is_local, b.database_name, b.machine_name, rep.synchronization_state, rep.synchronization_health' " & _
"END " & _
"EXEC (@SQLCommand)" ,CONN
Let me know what you think
Simone Bizzotto
Il Gruppo ABB in Italia ha adottato il Modello Organizzativo ai sensi del D.Lgs. 231/2001, in forza del quale l'assunzione di obbligazioni da parte di societa' ABB avviene a firma congiunta di due procuratori muniti di idonei poteri, con la sola eccezione delle persone che rivestono la carica di Amministratore Delegato o di Direttore Generale.Le informazioni contenute in questo messaggio di posta elettronica sono riservate e confidenziali e ne e' vietata la diffusione in qualunque modo eseguita.Qualora Lei non fosse la persona cui il presente messaggio e' destinato, e' invitata a non diffonderlo, e ad eliminarlo, dandone gentilmente comunicazione al mittente.
ABB Group in Italy adopts a Compliance Programme under the Italian Law (D.Lgs.231/2001). According to this ABB Compliance Programme, any commitment of ABB Italian Companies is taken by the double signature of ABB Representatives granted by a proper Power of Attorney with the only exception of Managing Director or General Manager.The information included in this e-mail and any attachments are confidential and may also be privileged. If you are not the correct recipient, you are kindly requested to notify the sender immediately, to cancel it and not to disclose the contents to any other person.