Creating SCCM collection based on AD group and Advertisement status

I have been using AD groups to add PCs to collections in SMS/SCCM for many years.  This has always worked out well for us since helpdesk technicians could easily deploy software just by adding a computer account to a group in AD.  SCCM would take care of the rest giving us pretty much automated deployments of software.

A change I have always wanted to make was to have the computer removed from the collection when the advertisement was successfully run.  After a bit of help I was able to create a query that does just that.

select

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,

SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,

SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client

from

SMS_R_System where SMS_R_System.SystemGroupName = "Domain\\GroupName"

and SMS_R_System.ResourceId not in

(

select SMS_R_System.ResourceID  FROM SMS_R_System

inner join SMS_ClientAdvertisementStatus

on SMS_R_System.ResourceID=SMS_ClientAdvertisementStatus.ResourceID

WHERE SMS_ClientAdvertisementStatus.AdvertisementID = 'ADV10004'

and SMS_ClientAdvertisementStatus.LastStateName = "Succeeded"

)