SCCM Application Deployment Custom Report SQL Query | ConfigMgr

5
SCCM Application Deployment Custom Report SQL Query ConfigMgr

Let’s find out SCCM Application Deployment Custom Report SQL Query with Collection. I have seen many questions in HTMDForum that we want the application related reports, how to write a SQL query to write a report on app deployment etc…

In this post, you will learn how to make a working Configuration Manager SQL query to find application status directly database. You can customize the query as per your need.

The following is the sample query that shall help you to understand the views which are required to get the ConfigMgr Application report working.

NOTE!Thanks to Karthik for sharing this query in the Forum.howtomanage.com thread – https://forum.howtomanagedevices.com/endpointmanager/configuration-manager/is-there-any-sql-query-for-finding-the-status-of-deployed-application-in-sccm/

SQL Query

Once you have initiated the client push installation method. You can track the status of the application deployment using the following query. The following is one of the many ways to get the app deployment status from ConfigMgr.

  • Open the SQL Management Studio.
  • Click on the New Query button.
  • Select the CM_MEM database from the drop-down menu.
    • MEM is the ConfigMgr site code.
  • Copy the following SQL query to find the report of application deployment with collection details.
  • Click on the Execute button.
SCCM Application Deployment Custom Report SQL Query
SCCM Application Deployment Custom Report SQL Query

NOTE! – This report includes the following details ..

  • Deployment Types -> Available or Required
  • Deployment Time
  • Application Creation Time
  • Last Modified Time
  • Last Modify by
  • Etc…
Select
Pac.PackageID as 'App_ID',
col.CollectionID as 'AppCollection_ID',
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
CASE when col.CollectionType = 1 Then 'User' when col.CollectionType = 2 Then 'Device' Else 'Others' End as 'CollType',
CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall’' Else 'Others' End as 'DeploymentType',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 
Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose',
Ds.DeploymentTime as 'AvailableTime',
Ds.EnforcementDeadline as 'RequiredTime',
Vaa.CreationTime as 'CreatedOn',
Vaa.LastModificationTime as 'LastModifiedOn',
Vaa.LastModifiedBy as 'LastModifiedBy'
from v_DeploymentSummary Ds
left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
left join v_Package Pac on Vaa.ApplicationName = Pac.Name
left join v_collection col on Ds.CollectionName = col.Name
Where Ds.FeatureType = 1
order by Ds.DeploymentTime desc

Results – Application Deployment Custom Report

The SQL query for application deployment status is very helpful in terms of troubleshooting SCCM app deployment issues. I have seen many scenarios where SCCM admins are struggling to get the overview details about the apps deployed, modified details.

SCCM Application Deployment Custom Report SQL Query
SCCM Application Deployment Custom Report SQL Query
App_IDAppCollection_IDApplicationNameCollectionNameCollTypeDeploymentTypePurposeAvailableTimeRequiredTimeCreatedOnLastModifiedOnLastModifiedBy
MEM0001FMEM00014Microsoft EdgeHTMD CollectionDeviceInstallRequired41:00.041:00.041:49.041:49.0MEMCM\anoop
MEM0001EMEM00014ConfigMgr Admin ConsoleHTMD CollectionDeviceInstallAvailable01:00.0NULL01:47.001:47.0MEMCM\anoop
MEM00016SMSDM003Firefox 75.0All Desktop and Server ClientsDeviceInstallRequired36:00.036:00.036:07.007:05.0MEMCM\anoop
MEM00015SMSDM003Firefox v74All Desktop and Server ClientsDeviceInstallRequired02:00.002:00.002:20.002:20.0MEMCM\anoop
SCCM Application Deployment Custom Report SQL Query

Resources

5 COMMENTS

  1. Anoop, This provides when the deployment was created and the information of that said Application. Trying to figure out where\how to create a report where the end user can put in application and tell which devices have it?

  2. Thanks Anoop! I would have never found CollectionType, DesiredConfigType and DeploymentIntent due to the output types. Very Useful report! do you know of better schemas?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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