{"name":"Finding Job Step Failures","description":"This metric returns a count of the job steps that failed in the latest execution of all jobs on the instance. This should be a calculated measure, where the change is what should alert the DBA. If any step in a job fails in the latest execution, this alert counts those failures and returns a value. This means if a job step fails in a job, and the job still reports success, this value changes.\r\nIf the job re-runs, and no steps error out, then this metric would report a 0.","tsql":"WITH cteActivity (job_id, start_execution_date)\r\nAS ( -- get the latest job execution for all jobs\r\n    SELECT job_id,\r\n           MAX(start_execution_date) AS start_execution_date\r\n    FROM msdb.dbo.sysjobactivity\r\n    GROUP BY job_id)\r\n, cteJobStep (Job_ID, Step_Name, run_date, run_time)\r\n    AS (SELECT jh.job_id,\r\n               jh.step_name AS JobStepName,\r\n               jh.run_date,\r\n               jh.run_time\r\n        FROM msdb.dbo.sysjobhistory jh\r\n            INNER JOIN cteActivity ja\r\n                ON jh.job_id = ja.job_id\r\n        WHERE jh.run_status = 0 --step failed\r\n              AND jh.step_id != 0\r\n              --         and jh.job_id = CONVERT(uniqueidentifier, '8C673935-F8C1-4E7D-94D3-1F3CAE50D7DC')\r\n              AND --this block ensures that we just pull information from the most recent job run\r\n            (\r\n            -- The start time of the step, converted to datetime\r\n            CONVERT(DATETIME, RTRIM(jh.run_date))\r\n            + (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100\r\n               * 10\r\n              ) \/ 216e4 >= ja.start_execution_date -- the time the job last started\r\n            )\r\n","instances":false,"databases":{"mode":1},"frequency":3600,"metricenabled":true,"alertenabled":true,"alertname":"Job Steps Have Failed","alertdescription":"A step in a job failed, whether or not the job itself reported success.","aboveorbelow":"above","_thresholds_low":{"selected":true,"value":1},"collections":1,"frequency_comment":"seconds (collected every hour)","rateofchange":false,"_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"targetentitytype":0}