SMS - SCCM Advertisement Dashboard report

Some customers could ask for a live dashboard to follow a specific deployment. This one will help.

It's a detailed dashboard pointing to the deployment advertisement:

*****************************************************************************
declare @Total int
declare @Accepted int
declare @AdvName VARCHAR(100)
set @AdvName = 'type here your advertisement name'

SELECT @Total=count(*), @Accepted=sum(case LastState when 0 then 0 else 1 end)
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)

SELECT LastAcceptanceStateName as 'Status', count(*) as 'Number of Resources', 
      ROUND(100.0*count(*)/@Total,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastAcceptanceStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID

SELECT LastStateName as 'Status of Targeted Resources', count(*) as 'Number of Resources', 
       ROUND(100.0*count(*)/@Accepted,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID


SELECT a.Netbios_name0 as 'Host Name', a.Resource_Domain_OR_Workgr0,
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
pkg.Name AS 'Package Name',
adv.ProgramName,
advstate.LastAcceptanceStatusTime,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceMessageIDname,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastExecutionResult,
advstate.LastStatusTime, 
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE ADV.AdvertisementName like @AdvName

order by advstate.LaststateName

*****************************************************************************

the result will be like this: