Last 24 hours backup report

[M_henriksen]

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

[VoropaevPavel · Accepted solution]

 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

[M_henriksen]

HI VoropaevPavel.

I didn't know about the "LIST" function, thanks for sharing!

Yes its almost what i want, but is it possible to have unique values only?

Br Morten

[M_henriksen]

I found the solution to my question above

LIST(DISTINCT A.statuscode) as 'Status Code',

[VoropaevPavel]

BTW, really nice report you made I'm going to use it myself.

Thanks.

[M_henriksen]

Thanks Pavel :)