cancel
Showing results for 
Search instead for 
Did you mean: 

Reporting - success & post failed jobs

opsmaxy
Not applicable

Hi

I need some help with creating query.

I need report that ll give me input status by policy name... 

 - if job is successfully finished

 - if job is still runing

 - if job was repeated and how it was repeated (manually or automatic) and then if it was successful or failed

 - and if posible if it was successful job to check if it was done within RPO time.

I would need for one week separately for each day.

My query till now:

SELECT domain_Job.policyName AS "Policy Name",

CASE WHEN (UTCBigintToNOMTime(domain_Job.endTime)-(UTCBigintToNOMTime(domain_Job.startTime))<0 THEN "running"
ELSE "end"
AS "Status",

CASE WHEN domain_Job.statusCode in (0,1) AND status="end" THEN "success"
WHEN domain_Job.statusCode in (0,1) AND status="running" THEN "run"
ELSE "failed"
END AS "End status"

FROM domain_job
WHERE datediff(day,UTCBigintToNOMTime(domain_Job.startTime),CURRENT DATE)<=7
AND datediff(day,UTCBigintToNOMTime(domain_Job.startTime),CURRENT DATE)>=0
AND domain_Job.type=0
AND domain_Job.isValid=1;

Thanks for any help ;)

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

This is what I came up with :)

 

SELECT
domain_Job.Policyname as "Policy Name",
UTCBigintToNOMTime(domain_Job.endTime) as "End Time",
lookup_jobstate.name as "Job State",
domain_Job.statuscode "Status",
domain_Job.attemptcount as "Attempts",
lookup_BackupType.name as "Job Type"
FROM domain_Job, lookup_BackupType, lookup_jobstate
WHERE
(domain_Job.backuptype=lookup_BackupType.id AND lookup_BackupType.id not in (-1))
AND
(domain_Job.state=lookup_jobstate.id AND domain_Job.state in (0,1,2,3,4,5,100))
AND
domain_Job.id=domain_job.parentjobid
AND
DATEDIFF(day,UTCBigIntToNomTime(domain_Job.endTime), GETDATE()) <= 7
ORDER BY  "End Time"

View solution in original post

2 REPLIES 2

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

This is what I came up with :)

 

SELECT
domain_Job.Policyname as "Policy Name",
UTCBigintToNOMTime(domain_Job.endTime) as "End Time",
lookup_jobstate.name as "Job State",
domain_Job.statuscode "Status",
domain_Job.attemptcount as "Attempts",
lookup_BackupType.name as "Job Type"
FROM domain_Job, lookup_BackupType, lookup_jobstate
WHERE
(domain_Job.backuptype=lookup_BackupType.id AND lookup_BackupType.id not in (-1))
AND
(domain_Job.state=lookup_jobstate.id AND domain_Job.state in (0,1,2,3,4,5,100))
AND
domain_Job.id=domain_job.parentjobid
AND
DATEDIFF(day,UTCBigIntToNomTime(domain_Job.endTime), GETDATE()) <= 7
ORDER BY  "End Time"

KDob
Level 3
Partner Accredited

Nice!  Always good to know how many attempts were necessary to get that "Code 0"!

Appreciate your efforts :)