I have a SQL query listed below that gives me the last successful full backup within the last 7 days for a VADP VSA VM backup. However, now I need this query to give me the backups for a server with an iDataAgent backup. Any help with modifying this query would be appreciated.
select DISTINCT vm.clientname
,vm.idataagent
,vm.instance
,vm.backupset
,vm.subclient
,vm.data_sp
,max(dateadd(s,vm.startdateunixsec,'1970-01-01 07:00:00')) as 'Last Successful Backup'
,vm.backuplevel
,max(vm.jobid) as 'Job id'
from CommCellBackupInfo vm INNER JOIN APP_VMPROP app on vm.jobid = app.jobId
INNER JOIN APP_ClientGroupAssoc a ON app.VMclientId = a.clientId
INNER JOIN APP_Client c on app.VMclientId = c.id
where startdate >= (dateadd(dd,-30,GETDATE())) /*and clientname = '$g'*/ and jobstatus = 'Success'
and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
GROUP BY clientname,idataagent,instance,backupset,subclient,data_sp,backuplevel
UNION
select DISTINCT vm.vmname as clientname
,'Virtual Server' as idataagent
,vm.virtualizationclient as instance
,vm.backupset
,vm.subclient
,vm.data_sp
,max(dateadd(s,convert(int,vm.startdateunixsec),'1970-01-01 07:00:00')) as 'Last Successful Backup'
,vm.backuplevel
,max(vm.jobid) as 'Job id'
from CommCellVMBackupInfo vm INNER JOIN APP_ClientGroupAssoc a ON vm.vmclientid = a.clientId
inner join APP_Client c on vm.vmclientid = c.id
where startdate >= (dateadd(dd,-30,GETDATE())) /*and vmname = 'aauth02apwxa'*/ and vmstatus = 'Success'
and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
GROUP BY vmname,virtualizationclient,backupset,subclient,data_sp,backuplevel
Charles Lavender