Show All “Succeeded” Advertisement Counts for the Past 30 Days

We rely on Run Advertised Programs (RAP) quite heavily at Dell – we have approximately 400 applications available in RAP, available to almost all systems.  I was asked to provide a quick report that shows the number of successful installs for each Advertisement over the past 30 days, so I thought I would share with you!

SELECT     COUNT(*) AS Count, v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName
FROM         v_ClientAdvertisementStatus LEFT OUTER JOIN
                      v_Advertisement ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID
WHERE     (v_ClientAdvertisementStatus.LastStateName = 'succeeded') AND (v_ClientAdvertisementStatus.LastStatusTime > DATEADD(day, - 30, GETDATE()))
GROUP BY v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName
ORDER BY v_Advertisement.AdvertisementName

Enjoy!