Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

get last job status

Hello,

How can I modify this query for having the last run job status :
set nocount on; select distinct replace (j.name, ' ', '')  +'|'+ case h.run_status when 0 then 'Failed' when 1 then 'Successful' when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus from msdb..sysjobhistory h, msdb..sysjobs j where j.job_id = h.job_id and h.run_date = (select max(hi.run_date) from msdb..sysjobhistory hi where h.job_id = hi.job_id ) and h.run_status <> '4' order by 1

Open in new window


Thanks
0
bibi92
Asked:
bibi92
  • 4
  • 3
  • 2
1 Solution
 
tim_csCommented:
Try this

;WITH CTE AS (SELECT 
	j.name
	,h.run_status
	,ROW_NUMBER() OVER (PARTITION BY h.job_id ORDER BY h.run_date DESC) rn
FROM
	msdb..sysjobhistory AS h
	INNER JOIN msdb..sysjobs AS j
		ON j.job_id = h.job_id
WHERE
	h.run_status <> 4
	AND step_id = 0 )



SELECT 
    REPLACE(c.name, ' ', '') + '|' + CASE c.run_status
                                       WHEN 0 THEN 'Failed'
                                       WHEN 1 THEN 'Successful'
                                       WHEN 3 THEN 'Cancelled'
                                       WHEN 4 THEN 'In Progress'
                                     END AS JobStatus
FROM 
	CTE c
WHERE
	c.rn = 2
ORDER BY
	1

Open in new window

0
 
Scott PletcherSenior DBACommented:
Depending on what you need to do, this could be far trickier than it seems at first.

For jobs that have multiple steps, what status do you want to show if they're different?  Say, step 1 succeeds but step 2 fails.  I'd expect if any step "failed" you'd want to show "failed", but I need to be sure.
0
 
bibi92Author Commented:
if any step "failed" I'd want to show "failed".  Thanks
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
tim_csCommented:
Did you try mine?  

Also, I noticed that in your case statement you have a value of 4 for "In Progress" but you are removing that from your results.  If you want it to show up you'll have to remove the "run_status <> 4" line.
0
 
Scott PletcherSenior DBACommented:
I decided to use a temp table for lookup efficiency.

It's vastly trickier than it seems to examine every step of only the last run of each job, because the sysjobhistory table:
1) may be missing a step(s) from the history -- that's why the last two columns are in the output, to easily let you see if the last run history is complete or not.
2) doesn't directly indicate or link job steps from the same job run

I excluded:
    currently executing steps;
    any history older than 6 months;
    disabled jobs.
Naturally change any/all of that if/as needed.



IF OBJECT_ID('tempdb.dbo.#jobhistory') IS NOT NULL
    DROP TABLE #jobhistory
CREATE TABLE #jobhistory (
    job_id uniqueidentifier,    
    row_num int,
    run_date int,
    run_time int,
    step_id int,
    run_status int,
    UNIQUE CLUSTERED ( job_id, row_num )
    )

INSERT INTO #jobhistory (job_id, row_num, run_date, run_time, step_id, run_status )
SELECT
    jh.job_id,
    ROW_NUMBER() OVER (PARTITION BY jh.job_id ORDER BY jh.run_date, jh.run_time, jh.step_id) AS row_num,
    jh.run_date, jh.run_time, jh.step_id, jh.run_status
    --MAX(CAST(run_date AS char(8)) + ' ' + RIGHT('00' + CAST(run_time AS varchar(6)), 6))
FROM msdb.dbo.sysjobhistory jh
WHERE
    --don't consider currently executing steps
    jh.run_status <> '4' AND
    --don't consider any history older than 6 months    
    jh.run_date >= CONVERT(char(8), (DATEADD(MONTH, -6, GETDATE())), 112) AND
    --don't consider disabled jobs
    EXISTS(
        SELECT 1
        FROM msdb.dbo.sysjobs j
        WHERE
            j.job_id = jh.job_id AND
            j.enabled = 1
        )

SELECT
    jh.job_id,
    MAX(CASE WHEN jh.run_status = 1 THEN 1 ELSE 0 END) AS did_job_succeed,
    (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps js WHERE js.job_id = jh.job_id) AS #steps_in_job,
    SUM(CASE WHEN jh.step_id > 0 THEN 1 ELSE 0 END) AS #steps_in_history_for_last_run
FROM (
    SELECT jh_last_row.job_id, ISNULL(MAX(jh_first_step.row_num), 0) + 1 AS first_step_of_last_job_execution
    FROM (
        SELECT jh_last_step.job_id, MAX(jh_last_step.row_num) AS last_row_num
        FROM #jobhistory jh_last_step
        GROUP BY jh_last_step.job_id
    ) AS jh_last_row_num
    INNER JOIN #jobhistory jh_last_row ON
        jh_last_row.job_id = jh_last_row_num.job_id AND
        jh_last_row.row_num = jh_last_row_num.last_row_num
    LEFT OUTER JOIN #jobhistory jh_first_step ON
        jh_first_step.job_id = jh_last_row.job_id AND
        jh_first_step.step_id >= jh_last_row.step_id AND
        jh_first_step.row_num < jh_last_row_num.last_row_num
    GROUP BY
        jh_last_row.job_id
) AS jh_last_job_execution
INNER JOIN #jobhistory jh ON
    jh.job_id = jh_last_job_execution.job_id AND
    jh.row_num >= jh_last_job_execution.first_step_of_last_job_execution
GROUP BY
    jh.job_id
0
 
Scott PletcherSenior DBACommented:
CORRECTION:

I think:
    MAX(CASE WHEN jh.run_status = 1 THEN 1 ELSE 0 END) AS did_job_succeed,
should be:
    MIN(CASE WHEN jh.run_status = 1 THEN 1 ELSE 0 END) AS did_job_succeed,
which would mean "the job succeeded (only) if ALL steps succeeded, otherwise it did not succeed".
0
 
bibi92Author Commented:
Ok, thanks but I have tested the first query.
0
 
Scott PletcherSenior DBACommented:
Yeah, I did too.  Sorry, the MAX() just didn't look right, but it may actually be :-) .
0
 
bibi92Author Commented:
Ok, thanks I have not see this case when I have tested the script.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now