These 3 views work together to aggregate and simplify SQL Agent Job execution details. They are useful for analyzing job interaction patterns (like overlapping job steps), performance details, or just to get a quick peek at what SQL Agent Jobs are doing.

if exists (select * from sys.views where object_id = OBJECT_ID(N'[dbo].[VW_JOBS_HIST_BASE]'))
    begin
        drop view dbo.VW_JOBS_HIST_BASE
    end;
go

create view dbo.VW_JOBS_HIST_BASE
/**
 * Name      : dbo.[VW_JOBS_HIST_BASE].sql
 * Author    : Brad Joss (bradjoss@hotmail.com)
 * Purpose   : View to standardize common details extracted from SQL Agent Job
 *             run histories. Several components of this view were taken from
 *             publically posted articles. Sadly, I did a poor job of documenting
 *             the source of those articles at the time I coded this, sorry.
 *
 * A "private" view used by the other two as a basis for historical information.
 *
 * Based on View for SQL Agent Job Histories:
 * http://www.codeproject.com/Tips/671292/View%ADfor%ADSQL%ADAgent%ADJob%ADHistories
 *
 * Change Date   Change By                           Change Description
 * -----------   -------------                       ---------------------------
 * 6/25/13       Brad Joss (bradjoss@hotmail.com)    Inital Creation
 * 3/12/14       Brad Joss                           [msdb].[dbo].[agent_datetime]
 * 3/14/16       Jon LaBelle                         Don't lock on system table reads.
 */
as
with cte
     as (
        select msdb.dbo.agent_datetime (run_date, run_time) as exec_dtm,
             -- Convert runtime duration to seconds
             ((run_duration / 1000000) * 86400 + (((run_duration - ((run_duration / 1000000) * 1000000)) / 10000) * 3600) + (((run_duration - ((run_duration / 10000) * 10000)) / 100) * 60) + (run_duration - (run_duration / 100) * 100)) as run_dur_sec,
             -- Switch status to text
             case run_status
                 when 0
                     then 'Failed'
                 when 1
                     then 'Succeeded'
                 when 2
                     then 'Retry'
                 when 3
                     then 'Cancelled'
                 when 4
                     then 'In Progress'
             end as run_status_desc,
             -- Define what "success" means for any given execution so later we
             -- can write queries that say show me the querys that were
             -- successful, or moreover, show me average runtimes of successful
             -- queries, ignoring failures.
             CAST(case run_status
                      when 0
                          then 0 --'Failed'
                      when 1
                          then 1 --'Succeeded'
                      when 2
                          then 0 --'Retry'
                      when 3
                          then 0 --'Cancelled'
                      when 4
                          then null -- 'In Progress' -- In progress, we don't know
                  end as bit) as is_success,
             -- Tidy up the messages to improve readability, and make suitable
             -- for copy and pasting results into Excel for analysis
             -- TODO: Alter these as needed to make messages pretty
             LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(message, 'Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153)', ''), 'Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Message 8625)', ''), 'Unable to open Step output file.', ''), '[SQLSTATE 01000] (Message 0)', ''), 'The step succeeded.', ''), '.', ' '))) as fmt_message,
             *
         from msdb..sysjobhistory with (nolock)
         where LOWER(step_name) <> '(job outcome)')
     select *,
         -- For any given job step, I frequenetly want to get the fastest,
         -- slowest and most recent runtimes. While these add overhead, given
         -- this is primarily a diagnostic view, they make composing the outer
         -- queries cleaner and easier.
         ROW_NUMBER() over(partition by job_id, step_id order by run_duration asc) as step_run_dur_asc,
         ROW_NUMBER() over(partition by job_id, step_id order by run_duration desc) as step_run_dur_desc,
         ROW_NUMBER() over(partition by job_id, step_id order by ISNULL(exec_dtm, CAST('1/1/1900' as datetime)) desc, run_time desc) as step_run_date_desc
     from cte;
go
-- select top 1 * from dbo.VW_JOBS_HIST_BASE;
-- go

if exists (select * from sys.views where object_id = OBJECT_ID(N'[dbo].[VW_JOBS_HIST]'))
    begin
        drop view dbo.VW_JOBS_HIST
    end;
go

create view dbo.VW_JOBS_HIST
/**
 * Name      : dbo.[VW_JOBS_HIST].sql
 * Author    : Brad Joss
 * Purpose   : View that exposes SQL agent job history details in a more
 *             usable format.
 *
 * Displays one row for each job step execution.
 *
 * - Database Name
 * - Bit flag indicating if job will run against currently selected DB
 * - Job and Step Names and IDs
 * - Job created and modified dates
 * - Bit flag indicating if the job is enabled
 * - Step start and end times
 * - Step run time in seconds and formatted as HH:MM:SS
 * - Step results including status code, status details, messages and success indicator bit
 * - Step command
 * - Notification details
 * - Default sort order to make order by easier
 *
 * Based on View for SQL Agent Job Histories:
 * http://www.codeproject.com/Tips/671292/View%ADfor%ADSQL%ADAgent%ADJob%ADHistories
 *
 * Change Date   Change By                           Change Description
 * -----------   -------------                       ---------------------
 * 6/25/13       Brad Joss (bradjoss@hotmail.com)    Inital Creation
 * 3/14/16       Jon LaBelle                         Don't lock on system table reads.
 */
as
with jobs
     as (select j.job_id,
             sjs.step_id,
             j.enabled as job_enabled,
             j.name as job_name,
             j.description as job_description,
             sjs.step_name,
             sjs.command as step_command,
             sjs.database_name as [database],
             j.date_created,
             j.date_modified
         from msdb..sysjobs as j with (nolock)
         inner join msdb..sysjobsteps as sjs with (nolock)
             on sjs.job_id = j.job_id)
     select j.[database],
         j.job_name,
         j.step_name,
         j.step_id,
         j.job_enabled,
         h.exec_dtm,
         DATEADd(ss, h.run_dur_sec, h.exec_dtm) as end_dtm,
         h.run_dur_sec,
         -- Format durations as HH:MM:SS
         CONVERT(varchar(8), DATEADD(ss, h.run_dur_sec, '1/1/2000'), 108) as run_dur_hhmmss,
         h.run_status,
         h.run_status_desc,
         h.is_success,
         -- Make filtering easier later on
         CAST(case
                  when j.[database] = DB_NAME()
                      then 1
                  else 0
              end as bit) as in_db,
         -- Remove newline and tab characters so results can easily be pasted
         -- into Excel for analysis
         REPLACE(REPLACE(REPLACE(j.job_description, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as job_description,
         REPLACE(REPLACE(REPLACE(j.step_command, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as step_command,
         j.date_created,
         j.date_modified,
         REPLACE(REPLACE(REPLACE(h.fmt_message, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as message,
         h.sql_message_id,
         h.sql_severity,
         h.operator_id_emailed,
         h.operator_id_netsent,
         h.operator_id_paged,
         h.retries_attempted,
         j.job_id,
         -- Default sort order
         ROW_NUMBER() over(order by j.[database] asc,
                                    j.job_name asc,
                                    j.step_id asc,
                                    h.exec_dtm desc) as sort
     from jobs as j
     left outer join dbo.VW_JOBS_HIST_BASE as h
         on j.job_id = h.job_id
            and j.step_id = h.step_id;
go
-- select * from VW_JOBS_HIST where in_db = 1 order by sort;
-- go
--
-- select *
-- from VW_JOBS_HIST
-- where step_name like N'SSIS BM%'
-- order by exec_dtm desc;
-- go

if exists (select * from sys.views where object_id = OBJECT_ID(N'[dbo].[VW_JOBS]'))
    begin
        drop view dbo.VW_JOBS
    end;
go

create view dbo.VW_JOBS
/**
 * Name      : dbo.VW_JOBS.sql
 * Author    : Brad Joss
 * Purpose   : View to simplify examining SQL Agent Jobs for performance,
 *             auditing and debugging.
 *
 * Displays one row for each job step, aggregating historical information in
 * line. Key result columns include:
 *
 * - Database Name
 * - Bit flag indicating if job will run against currently selected DB
 * - Job and Step Names and IDs
 * - Job created and modified dates
 * - Bit flag indicating if the job is enabled
 * - Step command
 * - First, last and next execution times
 * - Last execution runtime in seconds and formatted as HH:MM:SS
 * - Last execution status and message(s)
 * - Number of schedules created for this job
 * - Execution total, success and fail counts
 * - Min, Max, Average and Median runtime durations in seconds and formatted as HH:MM:SS
 * - Min, Max and Average successful-only runtime durations in seconds and formatted as HH:MM:SS
 * - If job step is currently running, running Session ID, status and program name
 * - Checksum of key job details, including Job, Step and DB names, Step Command and step sequence ID
 *
 * Based on View for SQL Agent Job Histories:
 * http://www.codeproject.com/Tips/671292/View%ADfor%ADSQL%ADAgent%ADJob%ADHistories
 *
 * Change Date   Change By                           Change Description
 * -----------   -------------                       --------------------------------------
 * 6/25/13       Brad Joss (bradjoss@hotmail.com)    Inital Creation
 * 1/7/14        Brad Joss                           Bug Fix: exec_nbr_desc returns null if no successful runs of
 *                                                   of the job, and the job step then is filtered off by
 *                                                   the WHERE exec_nbr_desc = 1 statement.
 * 3/12/14       Brad Joss                           [msdb].[dbo].[agent_datetime] -- Thanks to Paw Jershauge
 * 5/30/15       Brad Joss                           [msdb].[dbo].[agent_datetime](NULLIF(next_run_date,0),NULLIF(next_run_time,0)) -- Thanks to Henrik
 * 3/14/16       Jon LaBelle                         Don't lock on system table reads.
 */
as
with nrt
     as (
     -- Get next run time for job based on scheduled times
     select job_id,
         MIN(next_run_time) as next_run_time, -- Next run time
         COUNT(*) as job_schedules -- Count of different schedules there are for this particular job
     from (
         -- Convert the msdb formatted time to a standard DateTime
         select job_id,
             msdb.dbo.agent_datetime
         (NULLIF(next_run_date, 0), NULLIF(next_run_time, 0)) as next_run_time
         from msdb..sysjobschedules
     ) as t
     -- We are only interested in future times...
     where next_run_time > GetDate()
     group by
         job_id),
     cte
     as (select
             j.job_id,
             sjs.step_id,
             j.enabled,
             nrt.next_run_time,
             nrt.job_schedules,
             j.name as job_name,
             j.description as job_description,
             sjs.step_name,
             sjs.command as step_command,
             h.exec_dtm,
             h.run_dur_sec,
             h.run_status,
             h.run_status_desc,
             h.message,
             sjs.database_name as [database],
             j.date_created,
             j.date_modified,
             -- Format a value so if the job is currently running, we can match
             -- the job and step name as it would appear in dm_exec_sessions
             'SQLAgent - TSQL JobStep (Job '+ISNULL(CONVERT(varchar(2000), cast(j.job_id as binary(16)), 1), 'NULL')+' : Step '+CAST(sjs.step_id as varchar(2000))+')' as step_program_name,
             case
                 -- If has no history, note that the null record is the "first"
                 -- record so it will not be filtered off later on.
                 when h.job_id is null
                      and h.step_id is null
                     then 1
                 else h.step_run_date_desc
             end as exec_nbr_desc
         from msdb..sysjobs as j with (nolock)
         inner join msdb..sysjobsteps as sjs with (nolock)
             on sjs.job_id = j.job_id
         left outer join nrt
             on nrt.job_id = j.job_id
         left outer join VW_JOBS_HIST_BASE as h
             on j.job_id = h.job_id
                and sjs.step_id = h.step_id)
     select
         c1.[database] as db,
         -- To make filtering easier, see if the job is wired to run agains the
         -- current DB
         CAST(case
                  when c1.[database] = DB_NAME()
                      then 1
                  else 0
              end as bit) as in_db,
         c1.job_name, -- I will assume step name and job name don't have tab/newline chars
         c1.step_name,
         c1.step_id,
         c1.enabled,
         c1.step_command,
         c1.run_dur_sec as last_exec_dur_sec,
         CONVERT(varchar(8), DATEADD(ss, c1.run_dur_sec, '1/1/2000'), 108) as last_exec_dur_hhmmss,
         c1.run_status as last_exec_status,
         -- Remove newline and tab chars to make pasting into Excel easier.  I
         -- would like to do this for c1.step_command too, but I use that in the
         -- job search function and don't want to corrupt the text value that is
         -- being searched.
         REPLACE(REPLACE(REPLACE(hgb.last_message, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') as last_message,
         c1.job_schedules,
         hgb.exec_cnt,
         hgb.exec_cnt_success,
         hgb.exec_cnt - hgb.exec_cnt_success as exec_cnt_fail,
         hgb.first_exec,
         hgb.last_exec,
         c1.next_run_time as next_exec,
         hgb.min_run_dur_sec,
         hgb.max_run_dur_sec,
         hgb.avg_run_dur_sec,
         hgb.med_run_dur_sec,
         hgb.min_run_dur_success_sec,
         hgb.max_run_dur_success_sec,
         hgb.avg_run_dur_success_sec,
         -- Format times as HH:MM:SS
         CONVERT(varchar(8), DATEADD(ss, hgb.min_run_dur_sec, '1/1/2000'), 108) as min_run_dur_hhmmss,
         CONVERT(varchar(8), DATEADD(ss, hgb.max_run_dur_sec, '1/1/2000'), 108) as max_run_dur_hhmmss,
         CONVERT(varchar(8), DATEADD(ss, hgb.avg_run_dur_sec, '1/1/2000'), 108) as avg_run_dur_hhmmss,
         CONVERT(varchar(8), DATEADD(ss, hgb.med_run_dur_sec, '1/1/2000'), 108) as med_run_dur_hhmmss,
         CONVERT(varchar(8), DATEADD(ss, hgb.min_run_dur_success_sec, '1/1/2000'), 108) as min_run_dur_success_hhmmss,
         CONVERT(varchar(8), DATEADD(ss, hgb.max_run_dur_success_sec, '1/1/2000'), 108) as max_run_dur_success_hhmmss,
         CONVERT(varchar(8), DATEADD(ss, hgb.avg_run_dur_success_sec, '1/1/2000'), 108) as avg_run_dur_success_hhmmss,
         c1.date_created as job_created,
         c1.date_modified as job_modified,
         c1.job_id,
         c1.step_program_name,
         -- If job is currently running, get running details, else will be null
         ssn.session_id as running_session_id,
         ssn.status as running_status,
         -- Compute a checksum that boils down the job step to a hash value
         CAST(CHECKSUM(ISNULL(c1.[database], '')+'|'+ISNULL(c1.job_name, '')+'|'+ISNULL(c1.step_name, '')+'|'+ISNULL(CAST(c1.step_id as varchar(32)), '')+'|'+ISNULL(c1.step_command, '')) as bigint) as checksum_hash
     from cte as c1
     left outer join
         (
         -- Aggregate job status for analysis --
         select
             h2.job_id,
             h2.step_id,
             -- Total execution count
             count(*) as exec_cnt,
             -- Successful execution counts
             SUM(CAST(ISNULL(is_success, 0) as int)) as exec_cnt_success,
             min(exec_dtm) as first_exec,
             max(exec_dtm) as last_exec,
             min(run_dur_sec) as min_run_dur_sec,
             max(run_dur_sec) as max_run_dur_sec,
             avg(run_dur_sec) as avg_run_dur_sec,
             -- Compute the median as the middle value, or in the case of even #
             -- of records, the average of the two in the middle
             avg(case
                     when run_dur_sec is not null
                          and ABS(h2.step_run_dur_asc - h2.step_run_dur_desc) <= 2
                         then run_dur_sec
                     else null
                 end) as med_run_dur_sec,
             -- Get min/max/avg value only when the run was successful
             -- TODO: In 2012, use IIF
             min(case
                     when is_success = 1
                         then run_dur_sec
                     else null
                 end) as min_run_dur_success_sec,
             max(case
                     when is_success = 1
                         then run_dur_sec
                     else null
                 end) as max_run_dur_success_sec,
             avg(case
                     when is_success = 1
                         then run_dur_sec
                     else null
                 end) as avg_run_dur_success_sec,
             -- Get the message from the last run
             MAX(case
                     when step_run_date_desc = 1
                         then fmt_message
                     else null
                 end) as last_message
         from VW_JOBS_HIST_BASE as h2
         group by
             h2.job_id,
             h2.step_id
     ) as hgb
         on c1.job_id = hgb.job_id
            and c1.step_id = hgb.step_id -- To see if the job step is currently running
     left outer join sys.dm_exec_sessions as ssn
         on ssn.program_name = c1.step_program_name
     where exec_nbr_desc = 1 -- Only get the last run details for each job. Use the _HIST view for full details.
           or exec_nbr_desc is null; -- Or has not run
go

-- ============================================================================
-- Example Usage:
-- ============================================================================
-- select job_name,
--     step_name,
--     step_id,
--     first_exec as first_exec_datetime,
--     last_exec as last_exec_datetime,
--     exec_cnt,
--     exec_cnt_success,
--     exec_cnt_fail,
--     avg_run_dur_sec,
--     avg_run_dur_hhmmss,
--     last_exec_status,
--     case last_exec_status
--         when 0
--             then 'Failed'
--         when 1
--             then 'Succeeded'
--         when 2
--             then 'Retry'
--         when 3
--             then 'Cancelled'
--         when 4
--             then null
--     end as last_exec_status_desc,
--     last_exec_dur_sec,
--     last_exec_dur_hhmmss,
--     min_run_dur_sec,
--     min_run_dur_success_hhmmss,
--     max_run_dur_sec,
--     max_run_dur_hhmmss,
--     current_datetime = getdate()
-- from VW_JOBS
-- where job_name like N'MY_JOB_NAME_FILTER';