Forum Discussion

opsmaxy's avatar
10 years ago

Reporting - success & post failed jobs

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 ;)

  • 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"

  • 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"

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

    Appreciate your efforts :)