Jobs running for more than 16 hrs

[HealthNBUAdmin]

Good Morning All,

I am looking for two custom SQL Query's for OpsCenter 7.6 that will output all backup jobs that have been running for more than 16 hrs.  This would cover all jobs in the privious 24 hrs.  First query would be for all non-NDMP servers and second query would be for only NDMP servers.

Any Help would be appreciated.

[M_henriksen]

hi,

I think this is what you a requesting.

SELECT
B.friendlyname 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 A.policyName NOT LIKE 'SLP_%' AND
lookup_JobState.id = A.state AND
DATEDIFF(hour,UTCBigIntToNomTime(A.startTime), GETDATE()) >= 20

order by B.networkName, A.clientName, "Start Time"

[HealthNBUAdmin]

Cool M_henriksen,  I will run it today and check it out.

[HealthNBUAdmin]

M_henriksen,

The report ran for jobs running longer than 20 hrs, and that is easily changed to 16 hrs, no problem.

However, I need 2 query's for this.  The first would be one that shows no NDMP Servers and one that shows only NDMP Servers.

What needs to be added to this script to accomplish this?  This will be two separate reports.

Thank you for all the help thus far,

HealthNBUAdmin

[M_henriksen]

Here is the query for no NDMP

SELECT
B.friendlyname 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 A.policyName NOT LIKE 'SLP_%' AND
lookup_JobState.id = A.state AND
subType != 19 AND
DATEDIFF(hour,UTCBigIntToNomTime(A.startTime), GETDATE()) >= 16

order by B.networkName, A.clientName, "Start Time"

And here it is for only NDMP

SELECT
B.friendlyname 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 A.policyName NOT LIKE 'SLP_%' AND
lookup_JobState.id = A.state AND
subType = 19 AND
DATEDIFF(hour,UTCBigIntToNomTime(A.startTime), GETDATE()) >= 16

order by B.networkName, A.clientName, "Start Time

[HealthNBUAdmin]

M_henriksen,

These worked perfectly!!!!  Thank you very much for your help.  You are a true master and huge help!!!!

Thank you,

HealthNBUAdmin