SCCM Application Deployment Status using SQL Query Custom Report

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

In this post, I will explain how to create 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 according to your needs.

Since the 2012 RTM version, the introduction of applications in SCCM has changed a lot. New features have been introduced. Now, most organizations create applications instead of Packages to achieve their requirements quickly, but one thing that has become difficult for Admins is reporting (SQL queries).

In this post, I will share a Configuration Manager SQL query that will provide you with the Deployment status for an Individual Application corresponding to a Collection (the same as we see in the Monitoring node). I will also share how we can create and use a Pivot table in Report Builder.

Patch My PC
Index
SQL Query -SCCM Application Deployment Status
Using Report Builder
Additional Tip – SCCM Application Deployment Status
Result -SCCM Application Deployment Status
SCCM Application Deployment Status using SQL Query Custom Report – Table 1

SQL QuerySCCM Application Deployment Status

This SQL Query will help you use the ‘IIF’ syntax and declare the states accordingly (In the last post, we used ‘When’ and ‘then’ syntax). Let’s find the 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 application deployment report with collection details.
  • Click on the Execute button.
SCCM Application Deployment Status using SQL Query Custom Report - Fig.1
SCCM Application Deployment Status using SQL Query Custom Report – Fig.1

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

SCCM Application Deployment Status using SQL Query Custom Report - Fig.2
SCCM Application Deployment Status using SQL Query Custom Report – Fig.2

The Report provides output as

  • Computer Name
  • OS
  • User name
  • 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

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

Adaptiva

Copy and Paste the Query into the Report Builder

SCCM Application Deployment Status using SQL Query Custom Report - Fig.3
SCCM Application Deployment Status using SQL Query Custom Report – Fig.3

As in my previous post, save it and create a matrix to get the Raw Data.

Additional Tip – SCCM Application Deployment Status

Click the Insert>>Matrix>>Matrix Wizard to create a Pivot table for the status tab.

SCCM Application Deployment Status using SQL Query Custom Report - Fig.4
SCCM Application Deployment Status using SQL Query Custom Report – Fig.4

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

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

SCCM Application Deployment Status using SQL Query Custom Report - Fig.5
SCCM Application Deployment Status using SQL Query Custom Report – Fig.5

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

SCCM Application Deployment Status using SQL Query Custom Report - Fig.6
SCCM Application Deployment Status using SQL Query Custom Report – Fig.6

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

ResultSCCM Application Deployment Status

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

SCCM Application Deployment Status using SQL Query Custom Report - Fig.7
SCCM Application Deployment Status using SQL Query Custom Report – Fig.7
StatusCount
Error2
In Progress3
Installation Success8
Unknown11
Total24
SCCM Application Deployment Status using SQL Query Custom Report – Table 2

Resources

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Ankit Shukla, an IT professional with over seven years of experience, has worked on SCCM/MECM since 2012. During this time, he has extensively dealt with various aspects of the tool, such as migration, infrastructure designing, OSD, custom SQL reporting, and client-side troubleshooting. Ankit’s blog posts reflect his hands-on experience addressing issues and providing practical solutions. The primary aim of his posts is to cater to the audience, comprised of support team members and SCCM admins in organizations.

1 thought on “SCCM Application Deployment Status using SQL Query Custom Report”

  1. Thanks, very interesting.
    Is there a way to have the installation status of the single devices of the collection with, if in error, the error and the description?

    Reply

Leave a Comment

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