Last 24 hours backup report
Hi all.
I need some help with my backup report, to group statuscode for each server if it have failed more times.
Also i can't find a solution on only get erros where no backup have return statuscode 0 or 1. ex. backup job have failed at 10:00 but have run successfull at 12:00 then i don't want it to be in the report.
Now | |||||
friendlyName | clientName | policyName | statuscode | Days since Last successful | Last successful Date |
Earth | Fileserv01 | WIN_FS | 58 | 5 | 10-03-2015 16:48 |
Earth | Fileserv01 | WIN_FS | 54 | 5 | 10-03-2015 23:40 |
Earth | Fileserv02 | WIN_FS | 196 | 8 | 02-03-2015 07:45 |
After | |||||
friendlyName | clientName | policyName | statuscode | Days since Last successful | Last successful Date |
Earth | Fileserv01 | WIN_FS | 54, 58 | 5 | 10-03-2015 16:48 |
Earth | Fileserv02 | WIN_FS | 196 | 8 | 02-03-2015 07:45 |
Query so far.
SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
A.statuscode as 'Status Code',
( 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 ) ),
( select UtcBigIntToNomTime(MAX(BKP.endTime)) 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.masterServerId = B.id AND A.id = A.parentjobid AND
DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
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 = A.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
CKP.scheduleType = A.scheduleType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2)
Group by A.clientName,A.masterServerId,A.statusCode,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY B.friendlyName , A.clientName
Is that it?
SELECT DISTINCT B.friendlyName as 'Master Server', A.clientName as 'Client Name', A.policyName as 'Policy Name', LIST(A.statuscode) as 'Status Code', ( 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 ) ), ( select UtcBigIntToNomTime(MAX(BKP.endTime)) 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.masterServerId = B.id AND A.id = A.parentjobid AND DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24 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 = A.parentJobId AND CKP.clientId = A.clientId AND CKP.policyId = A.policyId AND CKP.policyName = A.policyName AND CKP.policyType = A.policyType AND CKP.scheduleType = A.scheduleType AND ( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND A.scheduleName != 'user_backup' AND A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND A.scheduletype not in (-1,2) Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid ORDER BY B.friendlyName , A.clientName