Get the list of all succeeded and in progress jobs from past 2 days

Job execution history is stored on the system table sysjobhistory in msdb database.  With the help of run_status flag, we will get the list of failure or canceled jobs.

Status of the job execution:

·         0 = Failed

·         1 = Succeeded

·         2 = Retry

·         3 = Canceled

·         4 = In Progress

With the help of below sql script we will get the succeeded or in progress jobs

SELECT   j.[name] as [Job Name],           js.step_name,           jh.step_name,           jh.run_date,           jh.run_time,                                  case when jh.run_status=1 then 'Succeeded'                                  when jh.run_status=4 then 'In Progress' end [Run Status],          jh.message,            jh.server  FROM     msdb.dbo.sysjobhistory jh           INNER JOIN msdb.dbo.sysjobs j             ON jh.job_id = j.job_id           INNER JOIN msdb.dbo.sysjobsteps js             ON j.job_id = js.job_id            AND jh.step_id = js.step_id  WHERE    jh.run_status in( 1,4)          AND jh.run_date > replace(CONVERT(varchar, getdate()-2, 23),'-','')  ORDER BY jh.instance_id DESC

We will get result as below