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

What you are looking for is located in the [CommCellBackupInfo] view.

I modified the query but I still can't get a backup report for the servers with iDataAgent backups. New SQL query below.

select DISTINCT clientname
                           ,clientname
                           ,idataagent 
                           ,backupset
                           ,subclient
                           ,instance
                           ,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 jobstatus = 'Success'
  and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
GROUP BY clientname,idataagent,instance,backupset,subclient,data_sp,backuplevel

You will need one query to the MAX Date from CommCellBackupInfo for a VM Client ID where the idataagent/subclient equals virtual server agent, plus another query for the MAX Date for the Client ID where the idataagent/subclient equals File System.  Then join these two in query that returns the max date from both queries.

For example (it is late here and I don't have a cv ssms in front of me, so pardon the heavy use of psuedocode - you will need to heavily modify this :( sorry).

SELECT V.ClientId, V.BackupDate, F.ClientId, F.BackupDate

FROM (

SELECT max(vm.jobdate) BackupDate, VM.clientid

FROM commcellbackupinfo vm

INNER JOIN APP_CLIENT GROUP ASSOCIATION a ON vm.clientId = a.clientId

where startdate >= (dateadd(dd,-30,GETDATE())) and jobstatus = 'Success'

and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
and vm.idataagent = "virtual server agent"
GROUP BY VM.ClientId
) V
LEFT JOIN (
SELECT max(FS.jobdate) BackupDate, FS.clientid

FROM commcellbackupinfo fs

INNER JOIN APP_CLIENT GROUP ASSOCIATION a ON fs.clientId = a.clientId

where startdate >= (dateadd(dd,-30,GETDATE())) and jobstatus = 'Success'

and a.clientGroupId in (Select id From APP_ClientGroup Where name = 'AutomationGroup')
and fs.idataagent = "Windows File System"
GROUP BY FS.ClientId
) F ON V.clientid = F.clientid