WQL query to create SCCM collection based on patch deployment status

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

inner join   SMS_SUMDeploymentAssetDetails on   SMS_SUMDeploymentAssetDetails.ResourceID = SMS_R_System.ResourceId 

where SMS_SUMDeploymentAssetDetails.AssignmentID = "10700151"   and SMS_SUMDeploymentAssetDetails.StatusType = "1"

Status type.

Value Status type

1         Success

2         InProgress

4         Unknown

5         Error

With Specific Deployment status 

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 offer.AssignmentID IN ('16794400') AND offer.StatusDescription = 'Pending system restart'

If you like to test it via Powershell to find out the column name to filter, try like below.

#Patches 

$SCCMPrimaryServername = 'server-105'

$SiteCode = 'AOL'

Get-WmiObject -ComputerName $SCCMPrimaryServername -Namespace root\sms\site_$SiteCode -Class SMS_SUMDeploymentAssetDetails  -Filter 'AssignmentID = "19700191"' | select DeviceName