Solved

get last job status

Posted on 2014-03-10
9
280 Views
Last Modified: 2014-03-25
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
Comment
Question by:bibi92
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 39918745
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39919240
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
 

Author Comment

by:bibi92
ID: 39919932
if any step "failed" I'd want to show "failed".  Thanks
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 39920472
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39921691
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39953469
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
 

Author Comment

by:bibi92
ID: 39954494
Ok, thanks but I have tested the first query.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39954501
Yeah, I did too.  Sorry, the MAX() just didn't look right, but it may actually be :-) .
0
 

Author Comment

by:bibi92
ID: 39954525
Ok, thanks I have not see this case when I have tested the script.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now