Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

get last job status

Posted on 2014-03-10
9
Medium Priority
?
291 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 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
Technology Partners: 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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