David Burrows (3/11/2013)
SELECT DISTINCT j.name as job_name,
msdb.dbo.agent_datetime(run_date, run_time) AS [run_datetime],
DATEADD(second,DATEDIFF(second,0,msdb.dbo.agent_datetime(19000101, run_duration)),msdb.dbo.agent_datetime(run_date, run_time)) AS [run_endtime]
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification'
AND j.name <> 'syspolicy_purge_history'
ORDER BY run_endtime DESC
Note that this uses an undocumented system function
That looks nice - I could have done with that undocumented function way back when, it would have saved me writing it.
Isn't there still an issue that this gives data for individual jobs steps, as well as for whole jobs, and doesn't identify which is which? If time for the job is required, shouldn't the where clause include the condition step_id = 0, and if the time for individual steps as well as for the whole job is required shouldn't step_id be included in both the group by and select clauses? And if only steps and not whole jobs are reuired, those additions to the group by and select clauses plus a step_id > 0 condition in the where clause?
Tom