Opscenter Custom Report to exclude few error codes
- 9 years ago
select
domain_JobArchive.clientName AS 'Client Name',
domain_JobArchive.policyName AS "Policy Name",
domain_JobArchive.scheduleName AS "Schedule Name",
UTCBigIntToNomTime(domain_JobArchive.startTime ) AS "Job Start Time",
UTCBigIntToNomTime(domain_JobArchive.ENDTime ) AS "Job End Time",
convert(varchar(8), dateadd(SECOND,NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.ENDTime), '1970-01-01'), 108) AS "Job Duration",
domain_JobArchive.filesBackedUp AS "Job File Count",
cast((domain_JobArchive.bytesWritten/1048576) as numeric (20,2)) AS "Post Deduplication Size",
domain_JobArchive.statusCode AS "Status Code",
(CASE
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 1 THEN 'Partial'
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode > 1 THEN 'Failed'
ELSE 'Failed'
END) as "Job Status",
domain_MASterServer.friENDlyName AS "Master Server"
FROM domain_JobArchive , domain_MASterServer
WHERE domain_MASterServer.id = domain_JobArchive.mASterServerId
AND domain_JobArchive.isValid = '1'
AND DATEDIFF(month,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1
ORDER BY "Job Start Time" ASC