Query SQL Jobs - Only Failed and Latest Status

  • I want to query the system tables to get a list of jobs that failed.  I only want to return the most recent jobs that failed.  If a job failed yesterday, but then the issue was fixed and job executed again and succeeded, this job should not appear in the resultset as the latest status was "success".  If a job failed, I also want to get the last time the job was successful.  I am having a little bit of an issue with this and hoping someone could point out the issue in my query:

    SELECT DISTINCT 
    J.NAME AS "Job_Name",
    J.DESCRIPTION AS "Job_Description",
    H.Step_Name AS "Step_Name",
    H.MESSAGE AS "Error_Message",
    MSDB.dbo.Agent_Datetime(h.run_date, h.run_time) AS Last_Status_Date,
    (SELECT MAX(MSDB.dbo.Agent_Datetime(run_date, run_time)) FROM MSDB.dbo.SysJobHistory WHERE run_status = 1 and step_id = 0 AND job_id = j.job_id) AS Last_Successful_Date
    --,
    --h.run_date,
    --h.run_time
    --MAX(h.run_time)
    FROM
    MSDB.dbo.SysJobHistory H,
    MSDB.dbo.SysJobs J
    WHERE
    J.job_id = H.job_id
    AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id)
    AND h.run_status = 0 -- Only Failed Jobs
    AND h.step_name <> '(Job outcome)'
    AND J.NAME NOT LIKE 'DBAMaint_%'
    AND J.NAME <> 'Configure_Verify_Policies'
    AND J.NAME <> 'SQL_Query_Trace'
    ORDER BY
    J.NAME
  • This is the issue

    AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id)AND h.run_status = 0 -- Only Failed Jobs

    The run_status needs to go into the subquery

    AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id AND hi.run_status = 0) -- Only Failed Jobs

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Does this get you anywhere? Haven't got any failed jobs to test it on at the moment.

    WITH OrderedJobs
    AS
    (SELECT
    j.job_id
    , j.name
    , jh.run_status
    , jh.run_date
    , jh.run_time
    , rn = ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY jh.run_date DESC, jh.run_time DESC)
    FROM dbo.sysjobs j
    JOIN dbo.sysjobhistory jh
    ON jh.job_id = j.job_id
    WHERE jh.step_id = 0)
    , LastSuccessful
    AS
    (SELECT
    j.job_id
    , jh.run_status
    , jh.run_date
    , jh.run_time
    , rn = ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY jh.run_date DESC, jh.run_time DESC)
    FROM dbo.sysjobs j
    JOIN dbo.sysjobhistory jh
    ON jh.job_id = j.job_id
    WHERE jh.step_id = 0
    AND jh.run_status = 1)
    SELECT
    oj.name
    , oj.run_status
    , oj.run_date
    , oj.run_time
    , oj.rn
    , LastSuccessfulRunDate = ls.run_date
    , LastSuccessfulRunTime = ls.run_time
    , LastSuccessfulRunStatus = ls.run_status
    FROM OrderedJobs oj
    JOIN LastSuccessful ls
    ON ls.job_id = oj.job_id
    WHERE oj.rn = 1
    AND oj.run_status <> 1
    AND ls.rn = 1;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Michael:

    Thank you for the suggestion, but unfortunately this brings back all jobs that failed regardless of the date, so it will show the last time a job failed, even if it was a few weeks ago, but the Last_Successful_Date will be greater.

  • AMCiti wrote:

    Michael:

    Thank you for the suggestion, but unfortunately this brings back all jobs that failed regardless of the date, so it will show the last time a job failed, even if it was a few weeks ago, but the Last_Successful_Date will be greater.

    Duh.  Whoops. Phil's solution works fine!

     

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Phil:

    After playing with that query, I notice it gives me all the "Step_Names" as being "(Job Outcome)" and the Job_Descriptions are all of the job itself, but not the step that failed.

    I would really like to get the Error Message and Step Name of the part of the job that failed to display.  I believe I can do this by doing a sub_query in the final select statement, but wasn't sure if this is the best method or not and not quite understanding how to exactly get this.

    • This reply was modified 7 months, 1 week ago by  AMCiti.
  • Tks a lot, i'm having question like u!

    Dang Tin Cho Thue Can Ho Chung Cu Mien Phi- Thuecanho123com

  • AMCiti wrote:

    Phil:

    After playing with that query, I notice it gives me all the "Step_Names" as being "(Job Outcome)" and the Job_Descriptions are all of the job itself, but not the step that failed.

    I would really like to get the Error Message and Step Name of the part of the job that failed to display.  I believe I can do this by doing a sub_query in the final select statement, but wasn't sure if this is the best method or not and not quite understanding how to exactly get this.

    That is a bit more involved, especially given the ability to tweak the 'On failure action' for each step. As I do not have a load of failed jobs on which to test, I don't feel confident that I could code a properly working solution for you.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil:

    I understand and just very appreciative of all the help you always provide.

  • What about this one?

    SELECT 
    j.job_id AS FailedJobId,
    j.name AS FailedJobName,
    jh.instance_id AS FailedJobInstance,
    jh.run_date AS FailedJobLastRunDate,
    jh.run_time AS FailedJobLastRunTime,
    js.step_id AS FailedStepNbr,
    js.step_name AS FailedStepName,
    jh.message AS FailedStepError

    FROM
    dbo.sysjobs j
    INNER JOIN
    dbo.sysjobhistory jh
    ON jh.job_id = j.job_id
    CROSS APPLY (
    SELECT TOP(1)
    instance_id,
    step_id,
    step_name
    FROM
    msdb.dbo.sysjobsteps
    WHERE
    job_id = j.job_id
    AND step_id = jh.step_id
    ORDER BY
    instance_id DESC
    ) AS js
    WHERE
    jh.run_status = 0
    AND NOT EXISTS (
    SELECT *
    FROM dbo.sysjobhistory
    WHERE job_id = j.job_id
    AND ((run_date > jh.run_date) OR (run_date = jh.run_date AND run_time > jh.run_time))
    AND run_status = 1
    )
    AND jh.instance_id = js.instance_id
  • ... or maybe this one?

    select
    js.job_id,
    j.name,
    js.last_outcome_message,
    jst.subsystem,
    jh.step_id,
    jh.step_name,
    jh.message,
    lgr.last_run_date
    from [dbo].[sysjobservers] js
    inner join [dbo].[sysjobs] j on j.job_id=js.job_id
    cross apply (
    select top(1) *
    from [dbo].[sysjobactivity]
    where job_id = j.job_id
    order by [session_id] desc
    ) ja
    cross apply (
    select top(1) *
    from [dbo].[sysjobhistory]
    where job_id=j.job_id and step_id=ja.last_executed_step_id
    order by instance_id desc
    ) jh
    inner join [dbo].[sysjobsteps] jst
    on jst.job_id=j.job_id and jst.step_id=ja.last_executed_step_id
    outer apply (
    select top(1) *
    from [dbo].[sysjobservers]
    where job_id=js.job_id
    and server_id=js.server_id
    and js.last_run_outcome = 1
    order by last_run_date desc, last_run_time desc
    ) lgr
    where js.last_run_outcome = 0
    and js.server_id=0

    I actually "think" this one may be better than the last one I submitted, but you'll need to test them, because I don't have many failed jobs for testing either.

    The first one relied somewhat on the instance, but I found out that this instance isn't a jobrun instance, but something else. It's pretty confusing how these tables relate to each other.

    • This reply was modified 7 months, 1 week ago by  kaj. Reason: typo

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply