last successful backup

[M_henriksen]

Hi all.

I have a daily report with backup failures in a sql query, but i would like a column where i can see the date for the last successful backup.

Can somebody give me a hint in the right direction

Thanks in advance

Morten

[new2nbu]

Hello Morten,

I've been working on a similar report - but have been doing it rhough a custom report template.  Would you be will willing to share your SQL code you've been using on your report?  Thank you.

[smakovits]

This is report that currently use, The only issue is the for vmWare policies it shows the media/master instead of the client name. Maybe you can help me with that?

SELECT domain_MasterServer.friendlyName as "MasterServer", domain_JobArchive.policyName as "Policy", domain_JobArchive.clientName as "Client"
FROM domain_JobArchive, domain_MasterServer
WHERE domain_JobArchive.masterServerId = domain_MasterServer.id
    AND domain_JobArchive.isValid = 1 AND domain_JobArchive.type = 0 AND domain_JobArchive.statusCode not in (0, 1)
    AND domain_JobArchive.endTime > NOMTIMETOUTCBIGINT(DATEADD(hour, -24, getdate()))
EXCEPT
SELECT domain_MasterServer.friendlyName as "MasterServer", domain_JobArchive.policyName as "Policy", domain_JobArchive.clientName as "Client"
FROM domain_JobArchive, domain_MasterServer
WHERE domain_JobArchive.masterServerId = domain_MasterServer.id
    AND domain_JobArchive.isValid = 1 AND domain_JobArchive.type = 0 AND domain_JobArchive.statusCode in (0, 1)
    AND domain_JobArchive.endTime > NOMTIMETOUTCBIGINT(DATEADD(hour, -24, getdate()))

[M_henriksen]

#kdorsainvil i cant see that your sql is what i'am asking for help about?

[new2nbu]

Thanks for sharing the SQL command.  To help with the VM Client name - in other reports I have found that selecting Job Directory will give the VMclient name with the backup id - so this may be helpful .

[smakovits]

Morton, Can you copy the current SQL statement that you are using?

[M_henriksen]

SELECT
networkName as 'Master Server',
max(UTCBigIntToNomTime(domain_Job.startTime)) as 'Start Time',
clientName as 'Client Name',
policyName as "Policy Name",
statusCode as "Status Code"

FROM domain_Job,domain_MasterServer,lookup_jobtype
WHERE
domain_MasterServer.id = domain_Job.masterserverId
and domain_Job.scheduleName not like '%hour'
and domain_Job.scheduleName not like '%ERROR'
and domain_Job.scheduleName != 'online'
and lookup_jobtype.name = 'Backup'
and statusCode not in (0,1,190,230,240,1000) and policyName NOT LIKE 'SLP_%' and type IN (0,22,28)

AND DATEDIFF(hour,UTCBigIntToNomTime(startTime), GETDATE()) <= 24

group by "Client Name","Status Code","Master Server","Policy Name"
order by networkName,clientName ASC