There's actually a really easy way to do this without installing SSRS or any other tools. I used SQL Profiler, ran the reports stated, and got the code which I copied below. You will want this copied in a scheduled job and have it send out the mail from within the job and you're done!
This is the query passed for the 'jobs history' report:
exec sp_executesql @stmt=N'begin try
use msdb;
Select (dense_rank() over(order by s1.job_id))%2 as l1
, (dense_rank() over(order by s1.job_id,s2.step_id))%2 as l2
, s1.job_id as [Job Id]
, s1.name as [Job Name]
, s2.step_id as [Step Id]
, s2.step_name as [Step Name]
, case when ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) ) is null
then 0.0
else ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) )
end as [Total Executions]
, Case when ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and s3.run_status = 0 and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) ) is null
then 0.0
else ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and s3.run_status = 0 and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) )
end as [Failed Executions]
, case when ( Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) ) is NULL
then 0.0
else ( Select avg((((run_duration)/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) )
end as [Average Run Duration]
, case when (Select avg(retries_attempted+0.0) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) ) is null
then 0.0
else (Select avg(retries_attempted+0.0) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and run_status <> 2 and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7 ) )
end as [Average Retries Attempted]
from sysjobs s1
inner join sysjobsteps s2 on ( s1.job_id = s2.job_id )
order by s1.job_id, s2.step_id
end try
begin catch
select -100 as l1
, 1 as l2, 1 as [Job Id], 1 as [Job Name],1 as [Step Id],1 as [Step Name]
, ERROR_NUMBER() as [Total Executions]
, ERROR_SEVERITY() as [Failed Executions]
, ERROR_STATE() as [Average Run Duration]
, ERROR_MESSAGE() as [Average Retries Attempted]
end catch',@params=N''
This is the query for 'top jobs: 20 most frequently failing jobs'
exec sp_executesql @stmt=N'begin try
use msdb;
Select top 20 sj.job_id as [Job ID]
, sj.name as [Job Name]
, sl.name as [Owner Name]
, jh.run_status
, (Select count(*) from sysjobschedules js1 where js1.job_id = sj.job_id ) as [Schedules Count]
, (Select count(*) from sysjobsteps js2 where js2.job_id = sj.job_id ) as [Steps Count]
, (Select count(*) from sysjobhistory jh1 where jh1.job_id=sj.job_id and jh1.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh1.run_date) ), getdate()) < 7 ) as [Execution Count]
, (Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7 ) as [Average Run Duration]
, (Select avg(retries_attempted+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7 ) as [Average Retries Attempted]
, Count(*) as [Failure Count]
, 1 as l1
from sysjobhistory jh
inner join sysjobs sj on ( jh.job_id = sj.job_id )
inner join sys.syslogins sl on ( sl.sid = sj.owner_sid )
where jh.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh.run_date) ), getdate()) < 7 and jh.run_status = 0
group by sj.job_id, sj.name, sl.name, jh.run_status
order by [Failure Count] desc
end try
begin catch
select 1 as [Job ID],1 as [Job Name],1 as [Owner Name],1 as run_status,1 as [Schedules Count],1 as [Steps Count]
, ERROR_NUMBER() as [Execution Count]
, ERROR_SEVERITY() as [Average Run Duration]
, ERROR_STATE() as [Average Retries Attempted]
, ERROR_MESSAGE() as [Failure Count]
, -100 as l1
end catch',@params=N''
This is the query for '20 most frequently ran jobs':
exec sp_executesql @stmt=N'begin try
use msdb;
Select top 20 sj.job_id as [Job ID]
, sj.name as [Job Name]
, sl.name as [Owner Name]
, (Select count(*) from sysjobschedules js1 where js1.job_id = sj.job_id ) as [Schedules Count]
, (Select count(*) from sysjobsteps js2 where js2.job_id = sj.job_id ) as [Steps Count]
, (Select count(*) from sysjobhistory jh1 where jh1.job_id=sj.job_id and jh1.step_id = 0 and jh1.run_status = 0 and datediff( day, convert(datetime, convert( varchar, jh1.run_date) ), getdate()) < 7 ) as [Failure Count]
, (Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7 ) as [Average Run Duration]
, (Select avg(retries_attempted+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7 ) as [Average Retries Attempted]
, Count(*) as [Execution Count]
, 1 as l1
from sysjobhistory jh
inner join sysjobs sj on ( jh.job_id = sj.job_id )
inner join sys.syslogins sl on ( sl.sid = sj.owner_sid )
where jh.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh.run_date) ), getdate()) < 7
group by sj.job_id, sj.name, sl.name
order by [Execution Count] desc
end try
begin catch
select 1 as [Job ID],1 as [Job Name],1 as [Owner Name],1 as run_status,1 as [Schedules Count],1 as [Steps Count]
, ERROR_NUMBER() as [Execution Count]
, ERROR_SEVERITY() as [Average Run Duration]
, ERROR_STATE() as [Average Retries Attempted]
, ERROR_MESSAGE() as [Failure Count]
, -100 as l1
end catch',@params=N''
20 slowest jobs:
exec sp_executesql @stmt=N'begin try
use msdb;
Select top 20 sj.job_id as [Job ID]
, sj.name as [Job Name]
, sl.name as [Owner Name]
, (Select count(*) from sysjobschedules js1 where js1.job_id = sj.job_id ) as [Schedules Count]
, (Select count(*) from sysjobsteps js2 where js2.job_id = sj.job_id ) as [Steps Count]
, (Select count(*) from sysjobhistory jh1 where jh1.job_id=sj.job_id and jh1.step_id = 0 and jh1.run_status = 0 and datediff( day, convert(datetime, convert( varchar, jh1.run_date) ), getdate()) < 7 ) as [Failure Count]
, (Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7 ) as [Average Run Duration]
, (Select avg(retries_attempted+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7 ) as [Average Retries Attempted]
, Count(*) as [Execution Count]
,1 as l1
from sysjobhistory jh
inner join sysjobs sj on ( jh.job_id = sj.job_id )
inner join sys.syslogins sl on ( sl.sid = sj.owner_sid )
where jh.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh.run_date) ), getdate()) < 7
group by sj.job_id, sj.name, sl.name
order by [Average Run Duration] desc
end try
begin catch
select 1 as [Job ID],1 as [Job Name],1 as [Owner Name],1 as run_status,1 as [Schedules Count],1 as [Steps Count]
, ERROR_NUMBER() as [Execution Count]
, ERROR_SEVERITY() as [Average Run Duration]
, ERROR_STATE() as [Average Retries Attempted]
, ERROR_MESSAGE() as [Failure Count]
, -100 as l1
end catch',@params=N''
