Basically this will give you the initial dataset you're after:
select j.name as 'JobName', run_date, run_time, msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime', run_duration From msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id where j.enabled = 1 --Only Enabled Jobs order by JobName, RunDateTime desc
You'll have to do a CTE on that to get the info you need.
There are different length returns, returned in the run_duration column. 2 digits are seconds, 3 digits are single digit minutes and seconds.
Sign in to comment
i have this so far. already wondering what run_duration on the job history step is. it always seems to be in whole numbers whatever it is. and so far i dont see an instance that ties the steps to a specific run of the job. but am looking further.
select sj.name,s.* from sysjobhistory s join sysjobs sj on sj.job_id=s.job_id where name like '...%'