Hi, I'm currently using Zabbix Monitoring and when connecting via ODBC, I'm executing the below command, but the script errors within the Zabbix Tool as if no backup jobs have failed it doesn't return a value (which we would expect).
The issue being that Zabbix needs to have a value response, it can be anything such as ISNULL etc, but unless it does return something, then the monitoring failures.
Any suggestions on how this script can be edited to provide a result, even if no backup jobs have failed?
use msdb
;WITH CTE_MostRecentJobRun AS
(
-- For each job get the most recent run (this will be the one where Rnk=1)
SELECT job_id,run_status,run_date,run_time
,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk
FROM sysjobhistory
WHERE step_id=0
)
SELECT
name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status]
FROM CTE_MostRecentJobRun MRJR
JOIN sysjobs SJ
ON MRJR.job_id=sj.job_id
WHERE Rnk=1 AND name LIKE '%Backup%'
AND run_status=0 -- i.e. failed
ORDER BY name