SCCM Application Deployment Status using SQL Query Custom Report

Let’s find out the ConfigMgr or SCCM Application Deployment Status using Custom Report, SQL query, and report builder. We have many other SQL queries and other custom reports we shared with the HTMD Forum community.

In this post, you will learn how to make a working Application Deployment SQL query to find the deployment status from your configuration manager (a.k.a ConfigMgr) database. You can customize the query as per your need.

Introduction

With the Introduction of Applications in SCCM since the 2012 RTM version, a lot has changed, new features have been introduced, now most the organizations follow to create applications instead of Packages and achieve their requirements easily, but one thing that has become difficult for Admins is the reporting (SQL queries).

Note: To troubleshoot Application Model issues at the client end, follow the Deep Dive Application Model Troubleshooting client end link.

Patch My PC

In this post, I will be sharing a Configuration Manager SQL query that will provide you Deployment status for an Individual Application corresponding to a Collection. (Same as we see in the Monitoring node).

  • I will also be sharing how can we create a Pivot table in Report builder and utilize that.

SQL Query

This SQL Query will help you use ‘IIF’ syntax and declare the states accordingly (In the last post, we used ‘When’ and ‘then’ syntax). Let’s find SCCM Application Deployment Status SQL Query Custom Report.

  • Open the SQL Management Studio.
  • Connect your Database Engine.
  • Right Click on your database CM_XXX and click on ‘New Query’
  • Copy the following SQL query to find the report of application deployment with collection details.
  • Click on the Execute button.
ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder SCCM Application Deployment Status  SQL Query Custom Report
SCCM Application Deployment Status SQL Query Custom Report

SQL Query – Application Deployment Status – ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder

ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
SCCM Application Deployment Status SQL Query Custom Report

Note! – The Report provides output as- 1. Computer Name 2. OS 3. User name 4. Status

SELECT distinct
vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,'Requirements Not Met ', IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
FROM dbo.v_R_System AS vrs
INNER JOIN (dbo.vAppDeploymentResultsPerClient
INNER JOIN v_CIAssignment
ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID)
ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac
ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws
ON vgws.ResourceID=vrs.resourceid
INNER JOIN v_FullCollectionMembership coll
ON coll.ResourceID = vrs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos
ON vgos.ResourceID = vrs.ResourceID
WHERE lac.DisplayName= 'provide your application name here'
and CollectionName = 'provide your collection name here'

Using Report Builder

On how to use a Report Builder, follow my Post. Let’s learn how to use the report builder to create custom reports in SCCM.

  • Copy and Paste the Query in Report builder
ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder

Save it and create a matrix as in my previous post to get the Raw Data.

Additional Tip – SCCM Application Deployment Status Report

To create a Pivot table for the status tab, click on the Insert>>Matrix>>Matrix Wizard.

ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
Additional Tip – SCCM Application Deployment Status Report

Again, select the same dataset and click next. Now on this page, you have to see which value you want against which row, same as in the Pivot table.

Here I am considering ‘Status’ as row and ‘Hostnames’ as the values, but we want a count of hostnames against Status, so drag and drop accordingly.

ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
Report Builder – Arrange fields – ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder.

Now click on the arrow next to ‘Computer_Name’ and select count.

ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
Report Builder – Count number of machines – ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder

Click on next >>next and done. Modify the color and settings accordingly.

Result

Let’s see how the result looks like for the deployment status

ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
SQL Query – Application Deployment Status – ConfigMgr Application Deployment Status using Custom Report, SQL query, and report builder
StatusCount
Error2
In Progress3
Installation Success8
Unknown11
Total24

Resources

Author

Hi, My Name is Ankit Shukla, and I have been working in IT since 2012. For the last 7 years, I have been working on SCCM/MECM. In these years have worked on different features of this tool, including Migration/designing the infrastructure/OSD/Custom SQL Reporting/Client-Side troubleshooting, and others. With my Blog Posts, you will find my experience on the issues which I have faced and solutions for them. The major objective of these posts is to reach out to the audience who are working as Support team members as SCCM Admins in their organizations.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.