SCCM SQL Query to get all resource and status of All deployments for a specified package and program

Use the built‑in report “All system resources for a specified package and program deployment in a specified state” as the model. It shows all resources (clients) and their deployment state for a given package/program deployment.

To build an equivalent SQL query, join the classic software distribution deployment views (for package/program) with status message views. The documentation provides the key building blocks:

1. Get all deployments (advertisements) for a package/program and their target collections:

SELECT ADV.AdvertisementID,
       ADV.AdvertisementName,
       COL.CollectionID,
       COL.Name AS CollectionName
FROM v_Advertisement ADV
INNER JOIN v_Collection COL
    ON ADV.CollectionID = COL.CollectionID
ORDER BY ADV.AdvertisementID;

2. Get status messages per deployment and resource, including the attribute value (for example, package ID, collection ID, user, etc.):

SELECT  SM.Component,
        SM.MessageID,
        COUNT(*) AS [Count],
        SMA.AttributeValue,
        SM.MachineName
FROM v_StatusMessage SM
LEFT OUTER JOIN v_StatMsgAttributes SMA
    ON SM.RecordID = SMA.RecordID
GROUP BY SM.Component,
         SM.MessageID,
         SM.MachineName,
         SMA.AttributeValue
ORDER BY SM.Component,
         SM.MessageID;

Combine these patterns by:

1. Filtering v_Advertisement on the specific PackageID and ProgramName.
2. Joining the resulting AdvertisementID to the appropriate deployment/status summary views (for example, the same views used by the “All system resources for a specified package and program deployment in a specified state” report) and then to v_R_System for resource details.
3. Using v_StatusMessage + v_StatMsgAttributes if detailed status messages per resource are required.

If PowerShell is preferred, use the cmdlet that exposes classic software distribution deployment status directly:

# By deployment (advertisement) ID
Get-CMPackageDeploymentStatus -DeploymentId "<DeploymentID>"

# Or by package ID
Get-CMPackageDeploymentStatus -PackageId "<PackageID>"

This returns the status of classic software distribution deployments, which can be filtered or exported instead of writing a custom SQL query.

References:

• List of reports in Configuration Manager
• Sample queries for application management in Configuration Manager
• Sample queries for status and alerts in Configuration Manager
• Get-CMPackageDeploymentStatus