November 12, 2019 at 12:42 pm
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
November 12, 2019 at 1:34 pm
Add a
UNION ALL SELECT NULL, NULL
maybe?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply