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