SCCM Report Advertisement detailed

This report will show you the current status of your running advertisements:





select 
adv.AdvertisementName, adv.Comment,
       pkg.Name as C001, pgm.ProgramName, adv.SourceSite, coll.Name as C002, 
               (CASE WHEN adv.IncludeSubCollection!=0 then '*' else '' END) as IncludeSubCollection, 
CASE WHEN AssignedScheduleEnabled != 0 or
                                      (AdvertFlags & 0x720) != 0
THEN '*'
ELSE ''
END AS C063,
       (case when (0x00001000&ProgramFlags)!=0 then '*' else ' ' end) as C004,
count(*) as C005,
sum(case LastAcceptanceState when 2 then 1 else 0 end) as C006,
round(100.0*sum(case LastAcceptanceState when 2 then 1 else 0 end)/count(*),1) as C007,
sum(case LastState when 13 then 1 else 0 end) as C008,
round(100.0*sum(case LastState when 13 then 1 else 0 end)/count(*),1) as C009,
sum(case LastState when 11 then 1 else 0 end) as C010,
round(100.0*sum(case LastState when 11 then 1 else 0 end)/count(*),1) as C011,
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
group by adv.AdvertisementID, adv.AdvertisementName, 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.AdvertisementName