Zabbix Agent2 MSSQL: AG Get backup query rewrite

[andro42]

I use latest zabbix-agent2-plugin-mssql.exe mssql plugin.

Problem: MS SQL 2022 Availability Group, when in AG I set that Secodary AG Replica is the Prefered Backup Replica (see pic bellow)


In this case MSSQL plugin wrongly states, that backup has not completed on Primary Replica on AG Databases.

After some research I checked the query, that Zabbix MSSQL plugin sends:

https://git.zabbix.com/projects/AP/repos/mssql/browse/plugin/queries/last.backup.get.sql

this query only checks, if the AG Replica is primary --> then it returns all databases,
if the AG Replica is secondary --> then it returns only local databases.
Iz ignores the AG Backup preferences config.

So it would make sense to rewrite the query "last.backup.get.sql":
(I checked; it works on SQL 2016 and later)

------------------------------------------------------------
-- 1. Original hardcoded Query
------------------------------------------------------------

SELECT bs.database_name as dbname,
  [type],
  DATEDIFF(SECOND, bs.backup_finish_date, getdate()) as time_since_last_backup,
  (DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)) as duration,
  db.recovery_model as db_recovery_model
FROM msdb.dbo.backupset as bs
LEFT JOIN sys.databases as db ON bs.database_name = db.name
WHERE bs.database_name not in (
  SELECT AGDatabases.database_name AS Databasename
  FROM sys.dm_hadr_availability_group_states States
  INNER JOIN master.sys.availability_groups Groups
    ON States.group_id = Groups.group_id
  INNER JOIN sys.availability_databases_cluster AGDatabases
    ON Groups.group_id = AGDatabases.group_id
  WHERE primary_replica != @@Servername OR primary_replica is NULL
) and db.name is not NULL
GROUP BY bs.database_name,
  backup_finish_date,
  [type],
  backup_start_date,
  db.recovery_model
HAVING backup_finish_date = (
  SELECT MAX(backup_finish_date)
  FROM msdb.dbo.backupset
  WHERE database_name = bs.database_name
    AND bs.type = [type]
)
ORDER BY bs.database_name

------------------------------------------------------------
-- 2. Corrected Query
------------------------------------------------------------

WITH AGPreferredDatabases AS (
  SELECT name
  FROM sys.databases
  WHERE sys.fn_hadr_backup_is_preferred_replica(name) = 1
)
SELECT bs.database_name as dbname,
  [type],
  DATEDIFF(SECOND, bs.backup_finish_date, getdate()) as time_since_last_backup,
  (DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)) as duration,
  db.recovery_model as db_recovery_model
FROM msdb.dbo.backupset as bs
  LEFT JOIN sys.databases as db ON bs.database_name = db.name
  INNER JOIN AGPreferredDatabases agdb ON db.name = agdb.name
WHERE db.name is not NULL
GROUP BY bs.database_name,
  backup_finish_date,
  [type],
  backup_start_date,
  db.recovery_model
HAVING backup_finish_date = (
  SELECT MAX(backup_finish_date)
  FROM msdb.dbo.backupset
  WHERE database_name = bs.database_name
    AND bs.type = [type]
)
ORDER BY bs.database_name