08-07-2013 08:02 AM
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
08-08-2013 07:35 AM
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.
08-21-2013 08:05 AM
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()))
08-22-2013 01:58 AM
#kdorsainvil i cant see that your sql is what i'am asking for help about?
08-22-2013 04:30 AM
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 .
08-22-2013 05:18 AM
Morton, Can you copy the current SQL statement that you are using?
08-23-2013 04:31 AM
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