Handy Software Delivery Report with Collection Filter
After being overall disappointed with the built-in Software Delivery execution reports, I came up with the following. It seems to execute fairly quickly and has some decent filters in it. It also demonstrates how to include a Collection filter and various parameters. I'll paste the SQL query and attach the actual report .XML file too so you don't have to re-create all the parameters. Note that the way it is currently written only displays the most recent execution of any Program within a Package; we don't typically include multiple Programs in our SWD packages, with some exception.
select distinct vc.[Name] AS 'Computer Name', vc.[Domain], swd.[LoggedInUser] AS 'Logged On User', swd.AdvertisementName AS 'SWD Task Name', swd.PackageName AS 'SWD Package Name', max(swd.Start) AS 'Start Time', swd.[End] AS 'End Time', swd.[ExecutionNumber] AS '# of Runs', datediff(ss, swd.[Start], swd.[End]) AS 'Run Time (s)', swd.Status, swd.ReturnCode AS 'Exit Code', vc.[Guid] FROM Inv_Aex_SWD_Execution_Summary swd RIGHT OUTER JOIN vComputer vc ON vc.Guid = swd.[_ResourceGuid] INNER JOIN CollectionMembership cm ON vc.Guid = cm.ResourceGuid AND cm.CollectionGuid = '%_Collection%' WHERE 1 = 1 AND swd.PackageId = %_PackageId% AND swd.Status IN (%_Status%) AND swd.[Start] > %_StartTime% GROUP BY vc.Name, vc.Domain, swd.[LoggedInUser], swd.AdvertisementName , swd.PackageName, swd.[ExecutionNumber], swd.[Start], swd.[End], swd.Status, swd.ReturnCode, vc.[Guid] ORDER BY max(swd.Start) DESC, vc.[Name] ASC