This query lists all failed deployments for package/programs on Servers.
declare @__timezoneoffset int select @__timezoneoffset = DateDiff(ss,getutcdate(),getdate()); select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0,site.SMS_Installed_Sites0, Client_Type0, ainfo.AdvertisementName, LastStatusMessageID, LastStatusMessageIDName, DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime, stat.AdvertisementID, LastExecutionResult, LastExecutionContext, sys.Operating_System_Name_and0 from v_ClientAdvertisementStatus stat join v_AdvertisementInfo ainfo on stat.AdvertisementID=ainfo.AdvertisementID join v_R_System sys on stat.ResourceID=sys.ResourceID left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID where stat.LastState='11' /* 11 = failed */ and sys.Operating_System_Name_and0 like '%server%' order by sys.Netbios_Name0