Advertisements executed in the last N days

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.


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',
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 ''
       (case when (0x00001000&ProgramFlags)!=0 then '*' else ' ' end)
order by adv.PresentTime DESC


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s