I wrote a view that joins the sysjob, sysjobactivity, sysjobhistory and syscategories tables together to give me a resultset that looks like the Job Activity Monitor. I wanted this so I could retrieve a specific resultset to log the status of certain jobs daily, added to other daily checks to provide a dashbpard report......
Anyway, I have copied the view below. However I am finding that the view does not return any jobs that are currently running, and shown in the job Activity Monitor with a Status of "executing", which I assume is equivalent to a run_status of 4 (in progress). When I search the sysjobactivity and sysjobhistory I cannot see the row.
Does anyone have any idea what is wrong with my view or where this row resides?
ps - the RANK is done so taht I return only the latest Job Activity, but even when I search without the RANK I cannot find it.
WITH latest_job_activity AS
SELECT job_id, start_execution_date, next_scheduled_run_date, job_history_id, RANK()
OVER (Partition By job_id ORDER BY start_execution_date DESC) AS Rank
WHERE Rank = 1
SELECT DISTINCT sj.[name] AS 'JobName',
WHEN 1 THEN 'Enabled'
END AS 'Enabled',
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'In Progress'
END AS 'RunStatus' ,
sjh.[run_status] AS 'RunStatusNumber',
start_execution_date AS 'LastRun',
--Last Run Duration HHMMSS Converted to 00:00:00
next_scheduled_run_date AS 'NextRun',
sc.name AS 'Category'
-- From Joined System Tables: Sysjobs, Sysjobschedules, Sysjobhistory, syscategories.
FROM msdb..sysjobs sj
JOIN latest_job_activity lja
ON sj.job_id = lja.job_id
JOIN msdb..sysjobhistory sjh
ON lja.job_history_id = sjh.instance_id
JOIN msdb..syscategories sc
ON sj.category_id = sc.category_id