Check SQL Agent Job Exceution Status and Messages Generated with a sql script

Question: How can I check SQL Agent Job Execution  Status using t-sql?  I want to be able to report on the Job Steps execution status , run time duration and Message Generated.

Do you have  a script example , which returns the above values based on the last 24 hrs.

Answer:

SELECT 
@@servername AS 'Server_Name',
j.NAME AS 'Job_Name',
CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
     END AS ExecutionStatus

    ,s.step_id AS 'Step'
    ,s.step_name AS 'Step_Name'
    ,msdb.dbo.agent_datetime(run_date, run_time) AS 'Run_DateTime'
    ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100 + 31) / 60) AS 'Run_DurationMinutes'
     ,h.message AS 'Message_Generated'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
    AND s.step_id = h.step_id
    AND h.step_id <> 0
WHERE j.enabled = 1 --Only Enabled Jobs
  and h.run_status = 0
  and DATEDIFF(hh, msdb.dbo.agent_datetime(run_date,run_time),  GETDATE()) <= 24
ORDER BY Job_Name    ,Run_DateTime DESC

Extra reading on SQL Agent Jobs

List enabled status of SQL Server Agent Jobs (SQL Server DBA)

SQL Server Agent Top Tips (SQL Server DBA)

Send sql server agent job history through email notification