See this related query: What's a good tool for visualizing SQL Agent jobs?
I'm not after a visual tool however, since I am looking to build my own visualisation. This is my starting point, but you'll agree it's sorely lacking.
select TOP(1000) h.run_date, min(h.run_time), j.name, j.description, max(h.run_duration) run_duration from msdb..sysjobhistory h join msdb..sysjobs j on j.job_id = h.job_id group by h.run_date, j.name, j.description, h.instance_id, j.job_id having max(h.run_duration) > 1000 -- << -- 10 minutes encoded in HHMMSS (digits) order by h.run_date desc, 2 desc
The crux is that there does not seem to be a key to the
sysjobhistory table to identify it as a particular "instance" of a
sysjob. For example, a job that started at 12:30 with 4 steps gets inserted into
sysjobhistory 4 times with different instance_ids (unique). They all tie to the
sysjob.job_id value, but that's the schedule, not the invocation of the job. Is there another table in which I should look?
This is probably fairly simple, since SSMS is able to do it easily.