Ops Center SQL query or suggestions for reporting on long running active jobs

[Tony_Defibaugh1]

All I am trying to find a method on reporting on active jobs that have been running for longer than say 24 hours.  I am interested in getting the job id, client name, time the job has been active, attempt number.   None of the canned reports do what I need since they will show longest running jobs but can't be set to only show active job state.  I am in a similar boat using the analytic created reports as well.  So my last resort now is a sql generated report.  Any assistance would be appreciated.

[M_henriksen · Accepted solution]

Hi Tony.

Here is the threshold report we are using, maybe you can use it.

SELECT
B.networkName as 'Master Server',
A.id as 'Job ID',
A.clientName as 'Client',
A.policyName as 'Policy',
UTCBigIntToNomTime(A.startTime) as 'Start Time',
lookup_JobState.name as 'Job State'
FROM  "domain_Job" A , "domain_masterserver" B , lookup_JobState
WHERE
B.id = A.masterServerId AND
A.state IN (0,1,2,4,5) AND
lookup_JobState.id = A.state AND
DATEDIFF(hour,UTCBigIntToNomTime(A.startTime), GETDATE()) >= 24
order by B.networkName, "Start Time"

Br Morten

[Tony_Defibaugh1]

That is awesome! It gives me something to work with thank you very much.