Forum Discussion
M_henriksen
9 years agoLevel 4
Hi,
Try this
SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
LIST(DISTINCT A.statuscode) as 'Status Code',
( select ( CASE WHEN DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) like '16%%%' THEN '-'
ELSE CAST (DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) AS NUMERIC (20)) END ) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
--CKP.policyId = A.policyId AND
--CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ),
( select ( CASE WHEN UtcBigIntToNomTime(MAX(BKP.endTime)) like '1970%' THEN 'Never been backed up'
ELSE CAST (UtcBigIntToNomTime(MAX(BKP.endTime)) AS Varchar(50)) END ) as 'Last successful'
from domain_JobArchive BKP
where BKP.masterServerId = A.masterServerId AND
BKP.Id = BKP.parentJobId AND
BKP.clientId = A.clientId AND
--BKP.policyId = A.policyId AND
--BKP.policyName = A.policyName AND
BKP.policyType = A.policyType AND
( BKP.statusCode = 0 OR BKP.statusCode = 1 ) )
FROM "domain_JobArchive" A , "domain_masterserver" B
WHERE A.id = A.parentjobid AND A.masterServerId = B.id AND
DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 23
AND
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.Id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
--CKP.policyId = A.policyId AND
--CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND
--A.statusCode not in (0,1,190,199,230,239,240,800,1000)
A.statusCode not in (0,1)
AND A.policyName LIKE '%ORA%'
AND A.policyName NOT LIKE 'SLP_%' AND
A.scheduletype not in (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY A.policyName,A.clientName
Related Content
- 10 years ago