Hi All
Ive been trying to get a list of the jobs and their details on various servers etc, and have trawled the net for bits and pieces to
compile the below job script. Has anyone got a better one? or perhaps tips to improve this one?
Ive been trying to get a list of the jobs and their details on various servers etc, and have trawled the net for bits and pieces to
compile the below job script. Has anyone got a better one? or perhaps tips to improve this one?
Code:
SELECT
'Job Type' = CASE server_id
WHEN 0 THEN 'SingleServer'
ELSE 'MultiServer'
END,
'Target Server Name' = CASE server_id
WHEN 0 THEN @@servername
WHEN 999999 THEN '???'
ELSE isnull( server_name,'')
END,
'Job Name' = name,
'Job Enabled' = CASE enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
else ''
END,
'Job Result' = CASE js_last_run_outcome
WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'SUCCEEDED'
WHEN 2 THEN 'RETRY'
WHEN 3 THEN 'CANCELLED'
WHEN 5 THEN 'NO HISTORY'
else '?'
END,
'Scheduled Y/N' = CASE schedenabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
else ''
END,
'Start Frequency' = CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq interval'
WHEN 64 THEN 'Run when SQLServerAgent service starts'
WHEN 128 THEN 'Run when the computer is idle'
else ''
END,
'Occurs Every'= isnull(freq_subday_interval ,0), --Specified units.
'Frequency Sub Day'= Case freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
else ''
End,
'Schedules Start Date' = case len(schedules_active_start_date)
when 8 then (left(schedules_active_start_date,4) + '-' + right(left(schedules_active_start_date,6),2) + '-' + right(schedules_active_start_date,2))
else ''
end,
'Schedules Start Time' = CASE len(schedules_active_start_time)
WHEN 3 THEN cast('00:0'
+ Left(right(schedules_active_start_time,3),1)
+':' + right(schedules_active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(schedules_active_start_time,4),2)
+':' + right(schedules_active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(schedules_active_start_time,5),1)
+':' + Left(right(schedules_active_start_time,4),2)
+':' + right(schedules_active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(schedules_active_start_time,6),2)
+':' + Left(right(schedules_active_start_time,4),2)
+':' + right(schedules_active_start_time,2) as char (8))
else ''
END,
'Last Run Start Date' = case len(js_last_run_date)
when 8 then (left(js_last_run_date,4) + '-' + right(left(js_last_run_date,6),2) + '-' + right(js_last_run_date,2))
else ''
end,
'Last Run Time' = CASE len(js_last_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(js_last_run_time,3),1)
+':' + right(js_last_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(js_last_run_time,4),2)
+':' + right(js_last_run_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(js_last_run_time,5),1)
+':' + Left(right(js_last_run_time,4),2)
+':' + right(js_last_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(js_last_run_time,6),2)
+':' + Left(right(js_last_run_time,4),2)
+':' + right(js_last_run_time,2) as char (8))
else ''
END,
'Max Run Duration' = CASE len(max_run_duration)
WHEN 3 THEN cast('00:0'
+ Left(right(max_run_duration,3),1)
+':' + right(max_run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(max_run_duration,4),2)
+':' + right(max_run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(max_run_duration,5),1)
+':' + Left(right(max_run_duration,4),2)
+':' + right(max_run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(max_run_duration,6),2)
+':' + Left(right(max_run_duration,4),2)
+':' + right(max_run_duration,2) as char (8))
else ''
END,
'Last Run Duration' = CASE len(js_last_run_duration)
WHEN 3 THEN cast('00:0'
+ Left(right(js_last_run_duration,3),1)
+':' + right(js_last_run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(js_last_run_duration,4),2)
+':' + right(js_last_run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(js_last_run_duration,5),1)
+':' + Left(right(js_last_run_duration,4),2)
+':' + right(js_last_run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(js_last_run_duration,6),2)
+':' + Left(right(js_last_run_duration,4),2)
+':' + right(js_last_run_duration,2) as char (8))
else ''
END
from
(
SELECT
isnull(sjs.server_id,999999)as server_id ,
sts.server_name,
sj.name,
sj.enabled,
sjsc.enabled AS schedenabled,
sjsc.freq_type ,
sjsc.freq_subday_interval,
sjsc.freq_subday_type ,
sjsc.active_start_date AS schedules_active_start_date,
sjsc.active_start_time AS schedules_active_start_time,
Q1.run_duration AS max_run_duration,
sjs.last_run_duration AS js_last_run_duration,
Q2.run_status AS run_status,
sjs.last_run_outcome AS js_last_run_outcome,
sjs.last_run_date AS js_last_run_date,
sjs.last_run_time AS js_last_run_time
FROM msdb.dbo.systargetservers sts RIGHT OUTER JOIN
msdb.dbo.sysjobservers sjs ON sts.server_id = sjs.server_id RIGHT OUTER JOIN
msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules sjsc ON sj.job_id = sjsc.job_id LEFT OUTER JOIN
(SELECT job_id, MAX(run_duration) AS run_duration
FROM msdb.dbo.sysjobhistory
GROUP BY job_id) Q1 ON sj.job_id = Q1.job_id LEFT OUTER JOIN
(SELECT J1.job_id, J1.run_status, 'Last Duration' = J1.run_duration
FROM msdb.dbo.sysjobhistory J1 INNER JOIN
(SELECT job_id, 'Instance_id' = MAX(instance_id)
FROM msdb.dbo.sysjobhistory
WHERE step_id = 1
GROUP BY job_id) T1 ON J1.job_id = T1.job_id AND J1.instance_id = T1.instance_id) Q2 ON sj.job_id = Q2.job_id
)as JobsQueryMain
order by server_id,name