jrdev22 jrdev22 - 1 month ago 15
SQL Question

SQLServer Jobhistory - last succesful runtime & last unsuccessful runtime

I want to have a view about my sql jobs which contains

name
job_id
run_date
LastSuccessfulRunDate


This is what i found for the last successful run dates.
Works like a charm, but here i am, failing to add the last run date.

Use msdb
GO

SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM
dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = 1
GROUP BY SJ.name, JH.run_status
ORDER BY [Last Time Job Ran On] DESC
GO


Here's what i got so far but it's giving me the result in two lines, one with last run date and the other with last successful run (which is als incorrect because it selects the highest date there is - distinct value). I kindly ask for help to get the join right and the result in one line.

USE msdb
GO
SELECT DISTINCT SJ.Name AS JobName,
SJH.run_date AS LastRunDate,
SJH.job_id,
(SELECT MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME))
FROM sysjobs
RIGHT JOIN sysjobhistory
ON SJ.job_id = SJH.job_id
WHERE SJH.run_status = 1) AS LastSuccessfulRun,

CASE SJH.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Successful'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS LastRunStatus

FROM sysjobhistory SJH, sysjobs SJ

WHERE SJH.job_id = SJ.job_id and SJH.run_date =
(SELECT MAX(SJH.run_date) FROM sysjobhistory SJH WHERE SJ.job_id = SJH.job_id)

Answer

solution is:

SELECT sj.name,
        MAX(dbo.agent_datetime(sjh.run_date, sjh.run_time)) AS last_Succesful_run,
        x.last_Unsuccesful_run
FROM    sysjobhistory sjh
        INNER JOIN sysjobs sj ON sjh.job_id = sj.job_id
LEFT OUTER JOIN
     (SELECT sysjobs.name,
                MAX(dbo.agent_datetime(sysjobhistory.run_date, sysjobhistory.run_time)) AS last_Unsuccesful_run
        FROM    sysjobhistory
        INNER JOIN sysjobs ON sysjobhistory.job_id = sysjobs.job_id
        WHERE sysjobhistory.run_status = 0
        GROUP BY sysjobs.name) x
        ON x.name = sj.name
WHERE   run_status = 1 
GROUP BY sj.job_id, sj.name, last_Unsuccesful_run

dropped the status case bc it's clear enough this way. the case was not mandatory.