Job Status Report with Error Detail

  • royce.bacon

    SSC Eights!

    Points: 990

    Comments posted to this topic are about the item Job Status Report with Error Detail

  • Drew Salem

    Ten Centuries

    Points: 1060

    Great post. I've been meaning to look at how this is done with RS. When I wrote a few articles back in April about using nest stored procs to collect various data, such as failed jobs and their error logs, using the "SCOME" technique, I had a tonne of enquiries on how it could be done using Reporting Services instead of ASP.Net, but I never got round translating them as I was too busy. There was a big and heated debate about why use raw .Net when RS was available. So I used SCOME to create the suite of reports that make up SQLServerMonitor.com to show its flexibility. If you find any of these useful and have the time, maybe you could translate some of these reports to using Reporting Services so I can direct the enquiries to your posts!

    Drew

  • Cameron Mayfield

    SSC Enthusiast

    Points: 105

    Absolutely Fantatastic information. I will be putting this report together ASAP. You have done an incredible job not only with the creation of the report but in explaining with great detail the steps to replicate. Thanks for a fantastic article and a wonderful idea!

  • Rob Fisk

    SSCommitted

    Points: 1841

    Nice one. Where was this a couple of weeks ago when I was looking to build a job dashboard.

    I actually ended up taking things to bits, breaking it down and building it back up in pieces.

    I used far more CTEs than I needed but at the time needed to see what was going on.

    I actually did the dashboard in PHP because I am just getting to grips with .net and had all the fancy stuff for hover tips and expanding sections already.

    It doesn't have a grouped history as with the article though. Just displays all currently active jobs, and looks at the status of their most recent run.

    ;WITH fullJobHistory AS

    (

    --Just so I can use a nice date everywhere else

    SELECT CAST(CAST(run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(run_time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS datetime) AS RunDate,

    *

    FROM msdb.dbo.sysjobhistory

    ),

    baseJobRecord AS

    (

    --the top level entries and number them in reverse

    SELECT ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY Rundate DESC) AS [jobNumber],

    *

    FROM fullJobHistory

    WHERE step_id=0

    )

    ,

    stepFailures AS

    (

    --get the failure messages for each failed bit

    SELECT o.job_id,

    o.jobNumber,

    f.[message] AS [stepError]

    FROM baseJobRecord o

    LEFT JOIN baseJobRecord o2

    ON o2.job_id = o.job_id

    AND o2.jobNumber = o.jobNumber - 1

    JOIN fullJobHistory f

    ON f.job_id = o.job_id

    AND f.step_id > 0

    AND f.run_status = 0

    AND (o2.instance_id IS NULL

    OR f.instance_id BETWEEN o.instance_id AND o2.instance_id)

    WHERE o.run_status = 0

    AND o.jobNumber = 1

    GROUP BY o.job_id,

    o.jobNumber,

    f.[message]

    )

    --bung it all together in an output.

    SELECT DISTINCT

    o.run_status AS [Status],

    j.name AS [Job Name],

    o.RunDate AS [Last Ran],

    --concatenate individual step errors

    --using br/hr here since it is for a web tooltip

    STUFF((SELECT '<br /><hr />' + CASE

    WHEN o2.run_status = 1 THEN NULL

    ELSE isnull(s.stepError,o2.[message])

    END

    FROM baseJobRecord o2

    LEFT JOIN stepFailures s

    ON s.job_id = o2.job_id

    AND s.jobNumber = o2.jobNumber

    WHERE o2.job_id = o.job_id

    AND o2.jobNumber = o.jobNumber

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,12,'')

    AS [Error]

    FROM msdb.dbo.sysjobs j

    JOIN baseJobRecord o

    ON o.job_id = j.job_id

    AND o.jobnumber = 1

    WHERE j.enabled = 1

    ORDER BY 1,2

    Will definitely be looking at your Reporting services bits though.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • rommelpos@gmail.com

    Mr or Mrs. 500

    Points: 555

    Fantastic solution!

    Implemented straight away!

    Thanks a lot for the useful information.

  • Rob Fisk

    SSCommitted

    Points: 1841

    The one bit in my script that people might find useful in the one from the article is the conversion from the silly* integers to real dates.

    The below is actually what you will see in the profiler as EM/SSMS views a job:

    CAST(CAST(run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(run_time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS datetime) AS RunDate

    *OK, so there is the very sensible reason that the way it is stored is very small.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • royce.bacon

    SSC Eights!

    Points: 990

    Drew, I will take a look at those when I get a chance (next year...LOL) and see if I find some of them useful.

  • Drew Salem

    Ten Centuries

    Points: 1060

    Fair enough. I kind of of said to everyone I'd look into it, but it seemed a bit silly having to find time to re-engineer the technique to use RS. I'll direct them all to you :).

  • l543123

    SSC Eights!

    Points: 934

    nice one.. but why do you need this.. cant we just use email notification when jobs fail and use SSMS - job activity monitor to monitor jobs.

  • royce.bacon

    SSC Eights!

    Points: 990

    l543123 (12/15/2009)


    nice one.. but why do you need this.. cant we just use email notification when jobs fail and use SSMS - job activity monitor to monitor jobs.

    Some of us don't have SSMS installed for various reasons for one thing - like our small shop. Email notices are certainly useful and I use them in conjunction with this report. I find the report gives me a good quick summary of what has run and which jobs have failed and I find that useful. Also this report is sent to others in the group who act as my backup and the email notices aren't always sent to everyone.

  • l543123

    SSC Eights!

    Points: 934

    makes sense 🙂 thanks for the reply.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    l543123 (12/15/2009)


    nice one.. but why do you need this.. cant we just use email notification when jobs fail and use SSMS - job activity monitor to monitor jobs.

    i like seeing everything in one email daily or reading it on the website. otherwise you get email clutter.

    and if i'm in the mood on the train ride to work, i'll read the report on my iphone

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    nice post

    there was a similar article a few weeks ago that i extended for the same thing. big difference is i import sysjobs and jobhistory daily for all monitored servers and my report has the reason for the failure in the report.

    later on i plan to extend it with data like average run time to give a heads up if a job suddenly takes longer to run.

    and to get around the annoying int to date conversion i added a date column myself

  • Gator650

    SSC Veteran

    Points: 205

    A shared datasource with SA credentials on a report server.... what's the worst that could happen? 😉

    While I understand the simplicity and brevity you were going for for the purposes of this article.... wouldn't it at least make more sense to grab the data with a job, dump into a repository and have the report just use read only credentials on that repository?

    Just having an account be owner over MSDB didn't give you the data you needed?

    Near Kindest Regards, Gator

  • royce.bacon

    SSC Eights!

    Points: 990

    Gator-221056 (12/15/2009)


    A shared datasource with SA credentials on a report server.... what's the worst that could happen? 😉

    While I understand the simplicity and brevity you were going for for the purposes of this article.... wouldn't it at least make more sense to grab the data with a job, dump into a repository and have the report just use read only credentials on that repository?

    Just having an account be owner over MSDB didn't give you the data you needed?

    I know that isn't the ideal situation from a security perspective. I tried various alternatives that didn't seem to work and I don't recall all of what I tried at this time. I do have a special account "ReportViewer" that I use for other reports that I was planning to get it, or maybe a different one, to work for this report when I have a chance.

Viewing 15 posts - 1 through 15 (of 28 total)

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