Find advertisements (packages) where a system reported a status in the last N days. For example, what advertisements were applied last night? Or in the last week? Includes old advertisements that were recently applied, so it helps with cleanup of old packages.
Does not show Windows Updates.
[SQL CODE] [DESIGNED FOR A REPORT] Declare @__timezoneoffset INT Declare @__Days INT set @__timezoneoffset = -7 Set @__Days = 7 select adv.AdvertisementName, pkg.Name as C001, pgm.ProgramName, coll.Name as C002, adv.PresentTime as 'Creation Time', count(*) as 'Total', sum(case LastState when 13 then 1 else 0 end) as 'Success', Sum(case LastAcceptanceState when 0 then 1 else 0 end) as 'No Status', sum(case LastState when 8 then 1 else 0 end) as 'Waiting', sum(case LastState when 11 then 1 else 0 end) as 'Fail', sum(case LastAcceptanceState when 2 then 1 else 0 end) as 'Reject', adv.AdvertisementID from v_Advertisement adv join v_Package pkg on adv.PackageID=pkg.PackageID join v_Program pgm on adv.PackageID=pgm.PackageID and adv.ProgramName=pgm.ProgramName join v_Collection coll on adv.CollectionID=coll.CollectionID join v_ClientAdvertisementStatus stat on adv.AdvertisementID=stat.AdvertisementID where DATEDIFF(hour, DATEADD(ss,@__timezoneoffset,stat.LastStatusTime),GETDATE())<24 * @__Days group by adv.AdvertisementID, adv.AdvertisementName, adv.PresentTime, adv.Comment, pkg.Name, pgm.ProgramName, adv.SourceSite, coll.Name, adv.IncludeSubCollection, (CASE WHEN adv.IncludeSubCollection!=0 then '*' else '' END), CASE WHEN AssignedScheduleEnabled != 0 or (AdvertFlags & 0x720) != 0 THEN '*' ELSE '' END, (case when (0x00001000&ProgramFlags)!=0 then '*' else ' ' end) order by adv.PresentTime DESC