Querying msdb for job history

Posted: 2013.02.21 in 2005, 2008, Reporting Services, SQL Server 2005, SQL Server 2008

I recently had the need to create an SSRS report showing the status of some jobs from the 10 last days, so I wrote this script that I thought could come in handy to others as well. The contents should be pretty self explanatory except maybe for the RowNum column which is there to separate each iteration of the job:

WITH cte_jobhistory AS (
		--> Creating a cte only to make the columns run_date and run_time into a "datetime-friendly" format  
		--  so it can be used for filtering
		SELECT 
			RunDateString = SUBSTRING(CAST(run_date as VARCHAR(20)), 1, 4) + '-' +  SUBSTRING(CAST(run_date as VARCHAR(20)), 5, 2) + '-' + SUBSTRING(CAST(run_date as VARCHAR(20)), 7, 2),
			RunTimeString = SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 5, 2),
			*
		FROM msdb.dbo.sysjobhistory
		)
SELECT 
	a.name,
	RowNum = ROW_NUMBER() OVER (PARTITION BY a.name, b.step_id ORDER BY instance_id DESC),
	b.step_id,
	b.step_name,
	b.message,
	RunStatus = CASE b.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeded' 
		WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Cancelled' 
		WHEN 4 THEN 'In Progress' 
		END,
	b.run_duration,
	b.RunDateString,
	b.RunTimeString
FROM msdb.dbo.sysjobs a
	INNER JOIN cte_jobhistory b
		ON a.job_id = b.job_id
WHERE CONVERT(datetime, b.RunDateString) > GETDATE()-10
ORDER BY a.name ASC, b.instance_id DESC
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s