Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

get last job status

Posted on 2014-03-10
9
Medium Priority
?
293 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 70

Expert Comment

by:Scott Pletcher
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

Expert Comment

by:Scott Pletcher
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 70

Expert Comment

by:Scott Pletcher
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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

972 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