November 15, 2015 at 10:17 pm
Hi,
I am working on a script.
I want to get all jobs status i.e. is it executed successfully or failed or cancelled or running. If it succesful, how much time it took to ran, if it is still running, on what time the job started and the job is enabled or disabled..
How can I get the another column of "duration" if it ran successfully, the start time if it running..
USE MSDB
SELECT name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status]
,CASE WHEN SJH.run_status=0 THEN 'Failed'
WHEN SJH.run_status=1 THEN 'Succeeded'
WHEN SJH.run_status=2 THEN 'Retry'
WHEN SJH.run_status=3 THEN 'Cancelled'
ELSE 'Unknown'
END [Job Outcome]
FROM sysjobhistory SJH
JOIN sysjobs SJ
ON SJH.job_id=sj.job_id
WHERE step_id=0
AND DATEADD(S,
(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate())
ORDER BY name,run_date,run_time
November 16, 2015 at 11:38 am
Column run_duration is in sysjobhistory.
EDIT: You also already have the start time. It's column run_time in sysjobhistory. Just pull it separately. Do a CASE to check the job status, the display duration or start time accordingly.
You may want these as separate columns or use them in the same column. It's up to you. Look up sysjobhistory in Books Online to see the description of all the columns.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply