SCCM Advertisement Status Failed systems Collection

To create a collection based on failed advertisement systems.

 

SELECT sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client FROM sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID
WHERE AdvertisementID = ‘CEN12345' and LastStateName = "Failed"