sp_job_steps_history : Job History for Steps

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_job_steps_history]    Script Date: 02/08/2012 08:13:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_job_steps_history]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_job_steps_history]
GO


/*
--all failed steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = null

--all failed specific steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = 15

--outcome of the specific step for all jobs all dates
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = null , @stepid = 15

--outcome of the specific step for all jobs specific date
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = null , @stepid = 15

--all failed steps for given day
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given day specific step
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = 13

--all failed steps for given job specific date
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given job all dates
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = null, @runstatus = 0 , @stepid = null

--specific job status for given dae
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = null , @stepid = null

*/
CREATE PROCEDURE dbo.sp_job_steps_history
(
@jobname nvarchar(256) = null
,@rundate int = null
,@runstatus int = 0
,@stepid int = null
)
as
BEGIN
/*
@runstatus:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
*/

/*
set @jobname = null
set @rundate = null
set @runstatus = 3
*/

SELECT j.name JobName,h.step_id, h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where (j.name like @jobname or @jobname is null)
and (h.run_date = @rundate or @rundate is null)
and (h.run_status = @runstatus or @runstatus is null)
and (h.step_id = @stepid or @stepid is null)
ORDER BY j.name, h.run_date, h.run_time

END
GO