use msdb
go

-- create variables to store dates since MSDB dates aren't in a standard datetime format
-- I'm looking at the last 30 days here. Change that to suit your needs
declare @historyage int
declare @historydate int
select @historyage = 30
select @historydate = datepart (year, cast(datediff(day, @historyage, current_timestamp) as datetime))*10000
+ datepart (month, cast(datediff(day, @historyage, current_timestamp) as datetime))*100
+ datepart (day, cast(datediff(day, @historyage, current_timestamp) as datetime))


-- next, use the same formula to construct the date to use for analyzing recent runs
declare @analysisage int
declare @analysisdate int
select @analysisage = 1
select @analysisdate = datepart (year, cast(datediff(day, @analysisage, current_timestamp) as datetime))*10000
+ datepart (month, cast(datediff(day, @analysisage, current_timestamp) as datetime))*100
+ datepart (day, cast(datediff(day, @analysisage, current_timestamp) as datetime))


select job_id, step_id, avg(run_duration) average
into #recentaverages
from sysjobhistory 
where run_date > @historydate
and step_id > 0
group by job_id, step_id
order by job_id, step_id 




--select sjh.job_id, sjh.step_id, sjh.run_date, sjh.run_time, sjh.run_duration, ra.average
--from sysjobhistory sjh
--inner join #recentaverages ra
--on sjh.job_id = ra.job_id
--and sjh.step_id = ra.step_id
--where run_date >= @analysisdate
--and sjh.run_duration > ra.average
--and sjh.run_duration > 10



select sj.name, sjs.step_id, sjs.step_name, run_date, run_time, sjh.run_duration, ra.average
from sysjobhistory sjh
inner join #recentaverages ra
on sjh.job_id = ra.job_id
and sjh.step_id = ra.step_id
inner join sysjobsteps sjs
on sjh.job_id = sjs.job_id
and sjh.step_id = sjs.step_id
inner join sysjobs sj
on sj.job_id = sjs.job_id
where run_date = @analysisdate
and sjh.run_duration > ra.average
and sjh.run_duration > 10
order by sj.name, sjs.step_id

drop table #recentaverages