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"