cancel
Showing results for 
Search instead for 
Did you mean: 

Report to get jobs/clients that ran for more than 24 hours at the end each month from Ops Center

Peaches_K
Level 3

Hi,

We have a report that runs daily to show the jobs that are active for more than 24 hours.

At the end of each month, we would like to know the list jobs/clients that ran for >24 hours. Can you Please suggest anything for this.

SQL query to show jobs that are active for more than 24 hours which is run on a daily basis :


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"

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Tape_Archived
Moderator
Moderator
   VIP   

If my earlier query does not work, try this:

Reports => Create New Report => Create a Custom Report => Default & Tabular => TimeFrame and Filters (Job Duration = From 24 to 100 hours => Select Columns You Need => Next

I'am sure this will help you.

View solution in original post

3 REPLIES 3

Tape_Archived
Moderator
Moderator
   VIP   

I am using Opscenter 7.7.3 and create a report that show jobs that ran more than 24 hours to 100 hours for last one month. This is just for backup type of jobs and no duplications.

select DISTINCT TOP 100 START AT 1 domain_Client.id as "domain_Client.id",domain_Policy.name as "domain_Policy.name",nb_JobFilesArchive.fileinfo as "nb_JobFilesArchive.fileinfo",domain_JobArchive.bytesWritten as "domain_JobArchive.bytesWritten",domain_JobArchive.filesBackedUp as "domain_JobArchive.filesBackedUp",adjust_timestamp_dst(domain_JobArchive.startTime ) as "domain_JobArchive.startTime",adjust_timestamp_dst(domain_JobArchive.endTime ) as "domain_JobArchive.endTime",(case when domain_JobArchive.state =106 then 3 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =0 then 0 when domain_JobArchive.state =3 and domain_JobArchive.statusCode =1 then 1 when domain_JobArchive.state =3 and domain_JobArchive.statusCode>1 then 2 else -1 end) as "jobExitStatus",NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime) as "backupJobDuration",domain_Schedule.name as "domain_Schedule.name",nb_JobAttemptArchive.destStorageUnit as "nb_JobAttemptArchive.destStorageUnit",domain_Entity.name as "domain_Entity.name" from domain_Client , domain_JobArchive , domain_Schedule , domain_MasterServer , domain_Policy , nb_JobFilesArchive , nb_JobAttemptArchive , domain_ScheduledJob , nb_JobArchive , domain_Entity where domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName and domain_Client.masterServerId = domain_ScheduledJob.masterServerId and domain_Client.name = domain_ScheduledJob.clientName AND domain_Schedule.masterServerId = domain_ScheduledJob.masterServerId and domain_Schedule.policyName = domain_ScheduledJob.policyName and domain_Schedule.policyDomainName = domain_ScheduledJob.policyDomainName and domain_Schedule.policyVersionNo = domain_ScheduledJob.policyVersionNo and domain_Schedule.name = domain_ScheduledJob.scheduleName and domain_MasterServer.id = domain_Client.masterServerId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName AND domain_JobArchive.masterServerId = nb_JobArchive.masterServerId and domain_JobArchive.clientName = nb_JobArchive.clientName and domain_JobArchive.id = nb_JobArchive.id AND nb_JobArchive.masterServerId = nb_JobAttemptArchive.masterserverId and nb_JobArchive.clientName = nb_JobAttemptArchive.clientName and nb_JobArchive.id = nb_JobAttemptArchive.jobId and domain_Policy.masterServerId = domain_Schedule.masterServerId and domain_Policy.name = domain_Schedule.policyName and domain_Policy.policyDomainName = domain_Schedule.policyDomainName and domain_Policy.versionNo = domain_Schedule.policyVersionNo and domain_MasterServer.id = domain_Policy.masterServerId and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo AND domain_JobArchive.masterServerId = nb_JobArchive.masterServerId and domain_JobArchive.clientName = nb_JobArchive.clientName and domain_JobArchive.id = nb_JobArchive.id AND nb_JobArchive.masterServerId = nb_JobAttemptArchive.masterserverId and nb_JobArchive.clientName = nb_JobAttemptArchive.clientName and nb_JobArchive.id = nb_JobAttemptArchive.jobId and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_MasterServer.id = domain_JobArchive.masterServerId AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and nb_JobArchive.masterServerId = nb_JobAttemptArchive.masterserverId and nb_JobArchive.clientName = nb_JobAttemptArchive.clientName and nb_JobArchive.id = nb_JobAttemptArchive.jobId AND domain_JobArchive.masterServerId = nb_JobArchive.masterServerId and domain_JobArchive.clientName = nb_JobArchive.clientName and domain_JobArchive.id = nb_JobArchive.id AND domain_JobArchive.masterServerId = nb_JobFilesArchive.masterserverId and domain_JobArchive.clientName = nb_JobFilesArchive.clientName and domain_JobArchive.id = nb_JobFilesArchive.jobId and domain_MasterServer.id = domain_Policy.masterServerId AND domain_Policy.masterServerId = domain_Schedule.masterServerId and domain_Policy.name = domain_Schedule.policyName and domain_Policy.policyDomainName = domain_Schedule.policyDomainName and domain_Policy.versionNo = domain_Schedule.policyVersionNo and domain_Schedule.masterServerId = domain_JobArchive.masterServerId and domain_Schedule.policyName = domain_JobArchive.policyName and domain_Schedule.policyDomainName = domain_JobArchive.policyDomainName and domain_Schedule.policyVersionNo = domain_JobArchive.policyVersionNo and domain_Schedule.name = domain_JobArchive.scheduleName AND domain_JobArchive.masterServerId = nb_JobArchive.masterServerId and domain_JobArchive.clientName = nb_JobArchive.clientName and domain_JobArchive.id = nb_JobArchive.id AND nb_JobArchive.masterServerId = nb_JobAttemptArchive.masterserverId and nb_JobArchive.clientName = nb_JobAttemptArchive.clientName and nb_JobArchive.id = nb_JobAttemptArchive.jobId and domain_MasterServer.id = domain_JobArchive.masterServerId AND domain_JobArchive.masterServerId = nb_JobArchive.masterServerId and domain_JobArchive.clientName = nb_JobArchive.clientName and domain_JobArchive.id = nb_JobArchive.id AND nb_JobArchive.masterServerId = nb_JobAttemptArchive.masterserverId and nb_JobArchive.clientName = nb_JobAttemptArchive.clientName and nb_JobArchive.id = nb_JobAttemptArchive.jobId and ( (domain_Client.id = domain_Entity.id) ) AND ( ( ( (domain_JobArchive.endTime BETWEEN '137066566231630000' AND '137090722231630000') ) AND ( (domain_Schedule.type IN (0 )) ) AND ( (domain_JobArchive.type IN (0 )) ) AND ( (NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime) >= '86400.0') AND (NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime) <= '360000.0') ) AND ( (domain_JobArchive.isValid = '1') ) AND ( ( (domain_MasterServer.id IN (61 )) ) ) ) ) ORDER BY UPPER("domain_Entity"."name" ) ASC

Tape_Archived
Moderator
Moderator
   VIP   

If my earlier query does not work, try this:

Reports => Create New Report => Create a Custom Report => Default & Tabular => TimeFrame and Filters (Job Duration = From 24 to 100 hours => Select Columns You Need => Next

I'am sure this will help you.

Thanks, this will be very useful.