SCCM sql query deployment status of application

Question

Can you provide me a SCCM SQL query to get the report of all the applications' deployment to a collection with all the status?

Answer 1

Hi, @CoolSK2

Thank you for posting in Microsoft Q&A forum.

You may try below query, replace <Collection Name> with the name of the collection you want to query. This query will return the collection name, collection ID, application name, deployment state, compliance state, and last state change time for each deployment to the specified collection.

SELECT 
    v_Collection.Name AS 'Collection Name',
    v_Collection.CollectionID AS 'Collection ID',
    v_AppDeploymentAssetDetails.AppName AS 'Application Name',
    v_DeploymentSummary.StateName AS 'Deployment State',
    v_DeploymentSummary.ComplianceStateName AS 'Compliance State',
    v_DeploymentSummary.LastStateChangeTime AS 'Last State Change Time'
FROM 
    v_Collection 
    JOIN v_FullCollectionMembership ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID 
    JOIN v_AppDeploymentAssetDetails ON v_FullCollectionMembership.ResourceID = v_AppDeploymentAssetDetails.ResourceID 
    JOIN v_DeploymentSummary ON v_AppDeploymentAssetDetails.MachineID = v_DeploymentSummary.MachineID 
WHERE 
    v_Collection.Name = '<Collection Name>'
ORDER BY 
    v_Collection.Name, 
    v_AppDeploymentAssetDetails.AppName, 
    v_DeploymentSummary.StateName

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".