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.
No comments:
Post a Comment