Here’s a simple collection will get you by if you need to create a collection based on the success of a Software Update deployment. For example, you’re using servicing to push out Feature Updates and want to deploy some software post devices upgrading.
select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from sms_r_system as sys inner join SMS_SUMDeploymentAssetDetails as offer on sys.ResourceID=offer.ResourceID WHERE AssignmentID = '<INSERT ASSIGNMENT ID>' AND StatusType = 1
If you’re not sure how to find the AssigmentID then you can go to the collection you have deployed against. Click the Deployments tab then right click the columns to bring up the selector. Choose AssignmentID and you’ll then see the ID listed against the deployment.
Insert your values in the fields marked with < >
Hope this helps.
SCCMentor - Paul Winstanley 
hi Paul
this i a great info can i run like WQL?
greatly appreciate for your time and skills
It is WQL Franco. Cheers Paul
can you include the column for status?
Hi Paul, Can you do the same based on the failure? To query the devices with errors? Cheers.
Hi Paul,
thanks, that really helped me!
Here are two examples of what I have build with this:
Get all Clients with a specific Error:
select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from sms_r_system as sys inner join SMS_SUMDeploymentAssetDetails as offer on sys.ResourceID=offer.ResourceID WHERE AssignmentID = ” AND StatusType = 5 AND StatusErrorCode = -2016410844
StatusType = 5 is for errors in general
StatusErrorCode = -2016410844 is error 0X87D00324 “The application was not detected after installation completed.” You have to convert 87D00324 to decimal (use the programmer calculator for this, select DWORD and insert it as HEX) to get the number.
Get all Clients which are pending a reboot:
select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from sms_r_system as sys inner join SMS_SUMDeploymentAssetDetails as offer on sys.ResourceID=offer.ResourceID WHERE AssignmentID = ” AND LastEnforcementMessageDesc = ‘Pending system restart’
Nice one Igor. Thanks for the comment
Is that worked @igor..i am also looking for a query to create device collection based on SU scan status
Yes, that worked for me. I’m already using it again for this years feature update (we only do one feature update a year).
Do you need help with a specific error code?