December 15, 2009 at 12:06 am
Comments posted to this topic are about the item Job Status Report with Error Detail
December 15, 2009 at 3:41 am
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
December 15, 2009 at 4:21 am
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!
December 15, 2009 at 4:56 am
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]
December 15, 2009 at 6:18 am
Fantastic solution!
Implemented straight away!
Thanks a lot for the useful information.
December 15, 2009 at 6:47 am
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]
December 15, 2009 at 6:57 am
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.
December 15, 2009 at 7:22 am
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 :).
December 15, 2009 at 7:46 am
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.
December 15, 2009 at 7:55 am
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.
December 15, 2009 at 8:00 am
makes sense 🙂 thanks for the reply.
December 15, 2009 at 8:13 am
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
December 15, 2009 at 8:16 am
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
December 15, 2009 at 8:30 am
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
December 15, 2009 at 8:40 am
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 27 total)
You must be logged in to reply to this topic. Login to reply