• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

get jobs in progress

Hello,

I have to modify this query because jobs in progress are not in the result :
set nocount on; select distinct REPLACE(REPLACE(REPLACE(j.name,'[',''),']',''),' ', '')  +'|'+  case h.run_status when 0 then 'Failed' when 1 then 'Successful' when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus from msdb..sysjobhistory h, msdb..sysjobs j where j.job_id = h.job_id and h.run_date = (select max(hi.run_date) from msdb..sysjobhistory hi where h.job_id = hi.job_id and hi.run_date is not null) and instance_id = (select max(hi.instance_id) from msdb.dbo.sysjobhistory hi where h.job_id = hi.job_id) order by 1

Open in new window


How can I modify it?

Thanks
0
bibi92
Asked:
bibi92
  • 3
  • 2
1 Solution
 
Brian CroweCommented:
start with this and narrow down the columns you want as status doesn't make much sense for a job in progress.

SELECT *
FROM msdb.dbo.sysjobs_view AS J
INNER JOIN msdb.dbo.sysjobactivity AS A
	ON J.job_id = A.job_id
WHERE A.run_Requested_date IS NOT NULL
	AND A.stop_execution_date IS NULL

Open in new window

0
 
bibi92Author Commented:
Thanks but I have to keep the query for zabbix monitoring and check job status.
0
 
Brian CroweCommented:
I see...give me a sec to play with it.
0
 
Brian CroweCommented:
try this...

SELECT J.job_id, J.name,
      CASE
            WHEN JA.start_execution_date IS NOT NULL AND JA.stop_execution_date IS NULL THEN 'In Progress'
            WHEN JH.run_status = 0 THEN 'Failed'
            WHEN JH.run_status = 1 THEN 'Successful'
            WHEN JH.run_status = 3 THEN 'Cancelled'
            ELSE 'Not Run'
      END AS JobStatus
FROM msdb.dbo.sysjobactivity AS JA
LEFT OUTER JOIN msdb.dbo.sysjobhistory AS JH
      ON JA.job_history_id = JH.instance_id
INNER JOIN msdb.dbo.sysjobs AS J
      ON J.job_id = JA.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
0
 
bibi92Author Commented:
Thanks, it works
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now