June 8, 2010 at 11:35 pm
Has anyone looked at doing this.
I was imagining something along the lines of.
Lines appear RED if they are not in a last run state of 1.
Or if there is no Information then the line appears with a title but blank
something like this
Job Name Last run state last run time Last five results
USER DB backup 1 today 10am 1,1,1,5,1
USER_DB Tlogs 2 today 11:15 1,2,2,2,2,
June 8, 2010 at 11:51 pm
yes I have.
What bothers me a bit is that you didn't provide any attempt data from your side.
This post sort of looks like you want us to do the thinking for you, and provide a working script?
But, I am in a very good mood today, so here goes...
1) write a sql query that would extract and display the info you need in your report.
2) create a new report, with your datasource specified, or use a parameter for a server name if you want to.
3) populate the columns with the your query.
4) change the color fx of the column where the 1 or the 0 would be the deciding factor...
eg. =iif (Fields!LastRunStatus.Value <> 1, "Red","Transparent")
Keep in mind this is on the BACKGROUNDCOLOUR Property, and not the column as text itself.
remember, next time, show us your attempts and we'll assist. 😉
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 9, 2010 at 12:12 am
Sorry guys I am very new to DBA in general and as such haven't really thought to much about it besides the MSDB tables -
And yes I thought maybe some one had already coded it etc and hence I would not try to reinvent the wheel.
I'll post my prelim queries up tomorrow, however they are very rough and newbie like
June 9, 2010 at 12:18 am
I'll post my prelim queries up tomorrow, however they are very rough and newbie like
This is exactly why we would help you, because you are in need of some.
But it's fine, post your "rough" code, and we'll see from there.
start by fine tuning your query to only the results you need, in the format you need it.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 10, 2010 at 12:32 am
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_status,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
where h.run_date >(Convert(Char(10),(DATEADD(dd,-7,GETDATE())),112))
ORDER BY h.instance_id DESC
This is what I have so far - I wondered why no one had replied, but I forgot to do the last reply button.
Getting the date in the right format took around 1/2 a day
June 10, 2010 at 12:53 am
now create a new report project, paste your query to populate the output fields and columns, and then design as you please.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 10, 2010 at 12:55 am
People are always willing to respond or help to people who try their best and if the fail to get what they need, I am sorry if I said something wrong
But if you google you would get lots of article and lots info on it, on SSC you have really great books by Bradd and other great people, to learn more about DBA activities you can try this book SQL Server Tacklebox
May be this query would help you
select
sj.name as [Job Name],
sjh.step_name,
case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end as [Status],
substring(cast(run_date as varchar(20)),5,2)+'/'+
substring(cast(run_date as varchar(20)),7,2)+'/'+
substring(cast(run_date as varchar(20)),0,5) as run_date,
run_time,--(format HHMMSS)
run_duration,--(format HHMMSS)
convert( varchar(8000), message) as [message],
sjh.instance_id
FROM
msdb.dbo.sysjobs as sj WITH (NOLOCK)
JOIN
msdb.dbo.sysjobhistory as sjh WITH (NOLOCK)
ON sj.job_id = sjh.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON sj.job_id = s.job_id
AND sjh.step_id = s.step_id
where
run_date between convert( varchar, dateadd(day,-1,getdate()), 112)
and convert( varchar, getdate(), 112)
and sjh.run_status = 0
-- and sj.name in (@jobs)
-- and (sj.name like '%back%' or sj.name like '%compression%')
order by run_date DESC,[Job Name], run_time desc, sjh.step_id
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
June 13, 2010 at 2:14 pm
If you want a free, out of the box, "health" collector you could try SQLH2 (on codeplex). It's open source (licensed under Ms-Pl) so you can add/remove the logging features you don't want.
The reports looks awful, but you can create your own if you want to. It comes with a ER diagram so creating your own reports is pretty easy.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply