SCCM Reporting: All Succeeded or Reboot Pending Status from advertisements using a specific package

We are progressing well with the IE 11 upgrade globally and all kinks are fixed for the most part.

Here's a query I use have used often when looking at the overall deployments success status for a large deployment project such as this.   We have over 12000+ machines to deploy to and for this project it was done with care from all the incompatibility and other environmental things that's not set for IE 11 until after IE 11 is installed on the system.   So this came in handy again for advertisements from testing, pilot, and production deployments and there are still more to be created!

This is a sub-query that pulls the advertisements status of 'Succeeded' and 'Reboot Pending' from the advertisements IDs tied to the package you're using.  In this instance it's both IE 11 packages.


Code below:

SELECT     v_R_System.Name0, vSMS_ClientAdvertisementStatus.LastStateName, vSMS_ClientAdvertisementStatus.LastStatusTime,
                      vSMS_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName, v_Advertisement.CollectionID, v_Collection.Name,
                      vSMS_ClientAdvertisementStatus.LastStatusMessageID
FROM         vSMS_ClientAdvertisementStatus INNER JOIN
                      v_R_System ON vSMS_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID AND
                      ((vSMS_ClientAdvertisementStatus.LastStateName = 'Succeeded' OR
                      vSMS_ClientAdvertisementStatus.LastStateName = 'Reboot Pending') and vSMS_ClientAdvertisementStatus.LastStatusMessageID != '10040') INNER JOIN
                      v_Advertisement ON vSMS_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN
                      v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
WHERE     (vSMS_ClientAdvertisementStatus.AdvertisementID IN
                          (SELECT     adv.AdvertisementID
                            FROM          v_Advertisement AS adv INNER JOIN
                                                   v_Package AS pkg ON adv.PackageID = pkg.PackageID INNER JOIN
                                                   v_Collection AS v_Collection_1 ON adv.CollectionID = v_Collection_1.CollectionID
                            WHERE      (pkg.PackageID = 'XXX00853') OR
                                                   (pkg.PackageID = 'XXX00854')))