SQLServer Jobhistory - last successful runtime & last unsuccessful runtime

Question

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)

Hello jrdev22. If you have come up with a solution to your problem, you should not edit your question and add the answer to it. Instead, answer your own question (ie, add your solution as an answer). Then mark it as accepted (click the ✔) so people know that your question has been resolved (cf https://stackoverflow.com/help/someone-answers). Other people can then upvote your answer so you can score some points with it. — TT., Oct 25 '16 at 11:52

Gordon Linoff · Answer 1 · 2016-09-28T02:39:17.987

0

I think you want row_number():

SELECT x.*
FROM (SELECT SJ.NAME AS [Job Name], RUN_STATUS AS [Run Status],
             DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS LastRunDateTime,
             ROW_NUMBER() OVER (PARTITION BY SJ.NAME ORDER BY DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) DESC) as seqnum
      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 
     ) x
WHERE seqnum = 1
ORDER BY LastRunDateTime DESC;

Also, learn to use proper, explicit JOIN syntax. Commas in the FROM clause are archaic.

edited Sep 28 '16 at 02:39

answered Sep 27 '16 at 12:55

Gordon Linoff

1,242,037
58
646
786

I'm sorry for using archaic synthax / writing bad code. Your code is not working and i dont know what you mean by row_number(). I edited my issue and hope my goal here is a bit clearer now. – VJ87 Sep 27 '16 at 13:13
`Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'DBO'. ` – VJ87 Sep 27 '16 at 14:11
Learn to troubleshoot and debug queries. There is a comma missing and PARTITION is spelled wrong. – dfundako Sep 27 '16 at 16:30
And i thought if you provide an answer, your solution should be working. My bad. I managed to debug your answer and it's not helping me with my Problem as i described. I need LastSuccessfulRun and LastRun in one query. – VJ87 Sep 28 '16 at 06:56

VJ87 · Accepted Answer · 2016-11-02T10:38:04.363

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.

SQLServer Jobhistory - last successful runtime & last unsuccessful runtime

2 Answers2