OpsCenter job State count Query

Hello,

I'm Currently working on an Query to give a "snapshot" of the Current state of all jobs for the past 8 hours.

The problem im running in to is it wont include Active jobs (or any jobs that havent ended) I presume this is due to domain_Job.endTime being NULL for these jobs as they havent ended. but i can quiet figure out how to incorperate that

 

SELECT

domain_MasterServer.friendlyName as "Master Server" ,

COUNT (CASE WHEN domain_job.state = '1' THEN 1 END ) AS Active ,

COUNT(CASE WHEN domain_Job.state = '3' THEN 1 END ) AS Done ,

COUNT(CASE WHEN domain_Job.state = '0' THEN 1 END ) AS Queued ,

COUNT(CASE WHEN domain_Job.state = '2' THEN 1 END ) AS "Waiting Retry" ,

COUNT(CASE WHEN domain_Job.state = '4' THEN 1 END ) AS Suspended ,

COUNT(CASE WHEN domain_Job.state = '5' THEN 1 END ) AS Incomplete ,

COUNT(CASE WHEN domain_Job.state = '100' THEN 1 END ) AS Cancelled ,

COUNT(CASE WHEN domain_Job.state = '3' and domain_Job.statusCode >1 THEN 1 END ) AS Failed

FROM

domain_MasterServer , domain_job

WHERE

domain_MasterServer.id = domain_Job.masterServerId

AND

DATEDIFF(hour,UTCBigIntToNomTime(domain_Job.endTime), GETDATE()) <= 8

 

Group By

"Master Server"

1 Reply

Re: OpsCenter job State count Query

Have you tried to grab the max endTime, you'd have to change your query around a little though.

select DATEDIFF(hour,UTCBigIntToNomTime(MAX(domain_Job.endTime)), GETDATE()) <= 8