07-19-2019 05:33 AM
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"
07-23-2019 12:15 PM - edited 07-23-2019 12:30 PM
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