Here is a SQL query to return all resources with a particular WUA Scan error code.
select v_R_System.ResourceID,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0 from v_R_System inner join v_UpdateScanStatus on v_UpdateScanStatus.ResourceId = v_R_System.ResourceId where v_UpdateScanStatus.LastErrorCode = '-2147467259'
Unfortunately this doesn't really help us at all if we want to perform any kind of actions on these resources.
I found this site Creating Collections using SQL which out lines an unsupported but highly effective method for using a SQL query to drive a collections membership. Below is the specific SQL I used to get the results I wanted.
Get Collection ID SQL Query
select CollectionID from v_Collections where CollectionName = 'CollectionName'
Update Collection Rules SQL Query
Update Collection_Rules_SQL set SQL = 'select SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System JOIN v_UpdateScanStatus on v_UpdateScanStatus.ResourceId = SMS_R_System.ItemKey where v_UpdateScanStatus.LastErrorCode = -2147467259' where CollectionID = 16777359
