Here’s a quick blog post to give you a couple of collections queries based on the status or description of a deployment.
For example, you might want to target devices based on the returned Message ID or StatusDescription and using these criteria will help you with this.
Query based on MessageID
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 = '<AdvertisementID>' and LastStatusMessageID = <MessageID>
Query based on StatusDescription
select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from sms_r_system as sys inner join SMS_ClassicDeploymentAssetDetails as offer on sys.ResourceID=offer.DeviceID WHERE DeploymentID = '<DeploymentID>' AND StatusDescription = "<StatusDescription>"
Insert your values in the fields marked with < >
Hope these help
SCCMentor - Paul Winstanley 