Script to find last successful run of SQL agent jobs

Recently I was asked to prepare a script to find the number of SQL agent jobs in a SQL instance where last successful run is older than 7 days.

This can be achieved using below script where first CTE is fetching the latest successful run of the jobs and in second CTE we are fetching details like name and schedule id of enabled jobs where last run date is older than 7 days or never run. Finally we are fetching count of all enabled jobs where schedule is attached but last successful run is older than 7 days.

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(msdb.dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status),
jobhistory_1 as (
SELECT jn.job_id, jn.name, js.schedule_id from jobhistory jh right outer join msdb..sysjobs  jn on jn.job_id = jh.job_id LEFT OUTER JOIN msdb..sysjobschedules js ON jn.job_id = js.job_id
where (jh.last_run_time < DATEADD(day, -7, CAST( GETDATE() AS Date)) OR jh.last_run_time IS NULL) AND jn.enabled<>0 )

SELECT COUNT(*)  from jobhistory_1 where schedule_id IS NOT NULL

You can use below script to fetch the name of all enabled jobs with schedule attached with their last run time

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(msdb.dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status),
jobhistory_1 as (
SELECT jn.job_id, jn.name, js.schedule_id, jh.last_run_time from jobhistory jh right outer join msdb..sysjobs  jn on jn.job_id = jh.job_id LEFT OUTER JOIN msdb..sysjobschedules js ON jn.job_id = js.job_id
where  jn.enabled<>0 )

SELECT name, last_run_time from jobhistory_1 where schedule_id IS NOT NULL

Hope these scripts will be useful!!

Happy learning!!