Solved

get last job status

Posted on 2014-03-10
9
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 69

Accepted Solution

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

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

627 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