Get Job Status using sysjobactivity

Get jobs currently running and jobs completed today (only the latest completion time if multiple) 

--

-- Get status on jobs using sysjobactivity. 

--

-- Get jobs currently running

--

DECLARE @TODAY DATE = GETDATE();

SELECT 

j.[name] AS JOB_NAME

, a.job_id

, a.run_requested_date

, a.run_requested_source

, a.start_execution_date

, a.last_executed_step_id

, a.last_executed_step_date

FROM msdb.dbo.sysjobactivity a

JOIN msdb.dbo.sysjobs j

ON j.job_id = a.job_id

WHERE a.session_id = (

SELECT MAX(session_id)

FROM msdb.dbo.syssessions

) 

AND a.start_execution_date IS NOT NULL

AND a.stop_execution_date IS NULL

ORDER BY a.start_execution_date;

--

-- Get jobs with current status completed (only shows the latest)

--

SELECT 

j.[name] AS JOB_NAME

, a.job_id

, a.run_requested_date

, a.run_requested_source

, a.start_execution_date

, a.stop_execution_date

, a.job_history_id

, a.next_scheduled_run_date

FROM msdb.dbo.sysjobactivity a

JOIN msdb.dbo.sysjobs j

ON j.job_id = a.job_id

WHERE a.session_id = (

SELECT MAX(session_id)

FROM msdb.dbo.syssessions

) 

AND a.start_execution_date IS NOT NULL

AND a.stop_execution_date > @TODAY

ORDER BY a.stop_execution_date DESC;