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