Generate Agent Job Report We have a few jobs that run nightly that the status has to be reported every hour.  I am trying to find in MSDB where I can capture the information below hourly and send out an excel file.

Server          Job Order - Name        NormalStartTime NormalEndTime   ActualStartTime ActualEndTime
Server A        1. JobA     11:00:00 PM     11:05:00 PM     11:00:00 PM     11:06:15 PM
Server A        2. JobB     11:05:00 PM     11:15:00 PM     11:06:16 PM     11:31:09 PM
Server A        3. JobC     11:15:00 PM     11:45:00 PM     11:31:09 PM     12:52:04 AM
Server A        4. JobD     11:45:00 PM     11:50:00 PM     12:52:04 AM     12:53:08 AM
Server A        5. JobE     11:50:00 PM     12:00:00 AM     12:53:09 AM     12:56:59 AM


Server = Server Name
Job Order - Name = This is the list of Jobs I want to search for.  The 1-5 is not in the job name.
NormalStartTime = Earliest Start time
NormalEndTime = Latest End Time
ActualStartTime = The time the job started on today's run
ActualEndTime = The time the job finished today's run
Below is the script as per your need 


SELECT h.SERVER AS [Server Name]
  ,j.NAME AS [Job Name]
  ,CONVERT(TIME, a.start_execution_date) AS [Normal Start Time]
  ,CONVERT(TIME, a.last_executed_step_date) AS [Normal End Time]
  ,CONVERT(TIME, a.run_requested_date) AS [Actual Start Time]
  ,CONVERT(TIME, a.stop_execution_date) AS [Actual End Time]
  ,h.run_time
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobactivity AS a ON h.job_id = a.job_id
WHERE h.run_date = (
      SELECT CONVERT(VARCHAR, GETDATE(), 112)
      )
  AND H.run_time &gt;= (
      SELECT REPLACE(REPLACE(CONVERT(TIME(0), CONVERT(TIME, DATEADD(HOUR, - 1, GETDATE()))), '.', ''), ':', '')
      )
  AND h.step_id = 1
  AND a.start_execution_date IS NOT NULL
  AND a.last_executed_step_date IS NOT NULL
  AND a.run_requested_date IS NOT NULL
  AND a.stop_execution_date IS NOT NULL