I have to write a query to generate the job name, min duration, max duration, avg duration, schedule (Daily, Weekly, etc), and frequency (every hour, once a day, etc)
I have the query already to get me the job name, min duration, max duration, avg duration. I cannot find a system table that holds the schedule and frequency information.
sysjobschedules just shows you the next run time and run date
Does anyone have any insight that might help me with this
Here is what I have so far
USE msdb
GO
select job_id, name into #jobs from sysjobs where enabled = 1
GO
select job_id,
run_date,
run_time,
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) as 'run_duration'
into #jobhistory from sysjobhistory
where step_id = 0
select a.name as 'JobName',
max(b.run_duration) as 'MaxDurationMinutes',
min(b.run_duration) as 'MinDurationMinutes',
avg(b.run_duration) as 'AvgDurationMinutes'
from #jobs a, #jobhistory b where a.job_id = b.job_id group by a.name
order by a.name
drop table #jobs
drop table #jobhistory