SCCM SQL Query to Report Application Deployment Status Per Device

 

SCCM SQL Query to Report Application Deployment Status Per Device

In enterprise environments, monitoring application deployment results is crucial for ensuring successful software rollouts and troubleshooting failed installations. System Center Configuration Manager (SCCM) provides a rich set of SQL views that allow administrators to extract detailed deployment information.

This blog post walks through a powerful SQL query that retrieves comprehensive deployment status details for applications assigned to specific devices or users.

---

🔍 Purpose of the Query

The purpose of this query is to generate a detailed report that includes:

• Device and user information
• Operating System details
• Assigned application and collection name
• Deployment status (e.g., Success, In Progress, Error)
• Last enforcement and compliance message timestamps

This helps administrators quickly determine the current state of application deployments across targeted machines.

---

📄 The SQL Query

SELECT DISTINCT

    vrs.Name0 AS [Computer Name],

    vgos.Caption0 AS [OS],

    vrs.User_Name0 AS [User Name],

    vrs.Ad_site_name0 AS [AD Site Name],

    lac.DisplayName AS [Application Name],

    CollectionName,

    IIF(

        vAppDeploymentResultsPerClient.EnforcementState = 1001, 'Installation Success',

        IIF(

            vAppDeploymentResultsPerClient.EnforcementState >= 1000 AND vAppDeploymentResultsPerClient.EnforcementState < 2000 AND vAppDeploymentResultsPerClient.EnforcementState <> 1001, 'Installation Success',

            IIF(

                vAppDeploymentResultsPerClient.EnforcementState >= 2000 AND vAppDeploymentResultsPerClient.EnforcementState < 3000, 'In Progress',

                IIF(

                    vAppDeploymentResultsPerClient.EnforcementState >= 3000 AND vAppDeploymentResultsPerClient.EnforcementState < 4000, 'Requirements Not Met',

                    IIF(

                        vAppDeploymentResultsPerClient.EnforcementState >= 4000 AND vAppDeploymentResultsPerClient.EnforcementState < 5000, 'Unknown',

                        IIF(

                            vAppDeploymentResultsPerClient.EnforcementState >= 5000 AND vAppDeploymentResultsPerClient.EnforcementState < 6000, 'Error',

                            'Unknown'

                        )

                    )

                )

            )

        )

    ) AS [Status],

    LastEnforcementMessageTime AS [LastEnfMessageTime],

    LastComplianceMessageTime AS [LastComMessageTime]

FROM dbo.v_R_System AS vrs

LEFT JOIN (dbo.vAppDeploymentResultsPerClient

    LEFT JOIN v_CIAssignment ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID)

    ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID

LEFT JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.CI_ID = dbo.vAppDeploymentResultsPerClient.CI_ID

LEFT JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws ON vgws.ResourceID = vrs.ResourceID

LEFT JOIN v_FullCollectionMembership coll ON coll.ResourceID = vrs.ResourceID

LEFT JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos ON vgos.ResourceID = vrs.ResourceID

LEFT JOIN v_CICurrentComplianceStatus ci2 ON ci2.CI_ID = vAppDeploymentResultsPerClient.CI_ID AND ci2.ResourceID = vrs.ResourceID

WHERE

    vAppDeploymentResultsPerClient.AssignmentID IN ('ID') AND

    vrs.Name0 IN ('User Name') AND

    CollectionName = 'Collection'

---

🔧 Query Breakdown

• v_R_System (vrs): Core system view providing details like computer name, user name, and AD site.
• vAppDeploymentResultsPerClient: Contains application deployment status per client.
• fn_ListApplicationCIs(1033): Retrieves application names based on configuration item (CI) IDs.
• v_GS_OPERATING_SYSTEM (vgos): Provides OS information of the client.
• v_CIAssignment: Stores deployment assignment details.
• v_CICurrentComplianceStatus: Shows compliance status for the CI.
• v_FullCollectionMembership: Maps devices to their collections.

---

📊 Deployment Status Mapping

Enforcement State Range	Status Description
= 1001	Installation Success
1000–1999 (≠1001)	Installation Success
2000–2999	In Progress
3000–3999	Requirements Not Met
4000–4999	Unknown
5000–5999	Error
Other	Unknown

---

🎯 Use Case Scenario

This query is useful when you want to:

• Audit application deployment success/failure
• Troubleshoot issues in a specific device or collection
• Generate deployment compliance reports
• Identify pending or in-progress installations

Simply replace the following placeholders in the WHERE clause:

• 'ID' → Your application deployment AssignmentID
• 'User Name' → List of device names or users
• 'Collection' → Target SCCM collection name

---

📝 Final Notes

• Ensure that you run this query in the SCCM database context (typically CM_<SiteCode>).
• Add additional filters or joins (like deployment types or deadlines) based on your reporting needs.
• You can export this data to Power BI or Excel for further visualization.

---