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

WQL query to create SCCM collection based on Package 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_ClassicDeploymentAssetDetails on SMS_ClassicDeploymentAssetDetails.DeviceID = SMS_R_System.ResourceId 

where SMS_ClassicDeploymentAssetDetails.DeploymentID = 'ABC20140' and SMS_ClassicDeploymentAssetDetails.StatusType = '1'

Status type.

Value Status type

1 Success

2 InProgress

4 Unknown

5 Error

If you like to test it via powershell,

#Package 

$SCCMPrimaryServername = 'Server-25'

$SiteCode = 'AOL'

Get-WmiObject -ComputerName $SCCMPrimaryServername -Namespace root\sms\site_$SiteCode -Class SMS_ClassicDeploymentAssetDetails  -Filter 'DeploymentID = "AOL20140"' | select Devicename,StatusDescription

WQL query to create SCCM collection based on Application 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_AppDeploymentAssetDetails   on   SMS_AppDeploymentAssetDetails.MachineID = SMS_R_System.ResourceId 

where SMS_AppDeploymentAssetDetails.AssignmentID = "100000003"   and SMS_AppDeploymentAssetDetails.StatusType = "1"

Application status type. Possible values are:

Value Application status

1 Success

2 InProgress

3 RequirementsNotMet

4 Unknown

5 Error

If you like to test it via Powershell,

#Application 

$SCCMPrimaryServername = 'Server-25'

$SiteCode = 'AOL'

Get-WmiObject -ComputerName $SCCMPrimaryServername -Namespace root\sms\site_$SiteCode -Class SMS_AppDeploymentAssetDetails -Filter 'AssignmentID = "29725319"' | select MachineName