Let’s find out the ConfigMgr 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.
With the Introduction of Applications in SCCM since 2012 RTM version a lot has changed, new features have been introduced , now most of the organisation follow to create application instead of Packages and achieve their requirements easily but one thing has become difficult for Admins is the reporting (SQL queries).
Note: To troubleshoot Application Model issues at client end follow the link Deep Dive Application Model Troubleshooting client end.
In this Post I will be sharing a Configuration Manager SQL query which will provide you Deployment status for an Individual Application corresponding to a Collection. (Same as we see in out Monitoring node).
- I will also be sharing how can we create a Pivot table in Report builder and utilize that.
This SQL Query will help you in using ‘IIF’ syntax and declare the states accordingly (In last post we used ‘When’ and ‘then’ syntax).
- 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.
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
- Copy and Paste the Query in Report builder
- Save it and create a matrix as in my previous post to get the Raw Data.
- To create a Pivot table for the status tab, click on the Insert>>Matrix>>Matrix Wizard.
- 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 Pivot table.
- Here I am considering ‘Status’ as row and ‘Hostnames’ as the values, but we want count of hostnames against Status, so drag and drop accordingly.
- Now click on the arrow next to ‘Computer_Name’ and select count.
- Click on next >>next and done.
- Modify the color and settings accordingly.
Let’s see how the result looks like for the deployment status