SQL Query All SCCM Applications with no Deployments | ConfigMgr

Hello, this is my first post here, I have more than 13 years of experience in ConfigMgr. I’m going to start a series of posts related SQL and WQL queries (SQL Query) to help SCCM admins.

Related post – SCCM Management Insights – Healthy SCCM Infra


When it comes to your SCCM Infrastructure cleanup we all try to push the task to the next available engineer because of the manual efforts involved as well as too many steps. Also there are chances of Human Error in Manual cleanup/deletion.

Patch My PC


Scope: This work instruction applies to the ConfigMgr Delivery Team. It doesn’t cover other team tasks.

We will try to cover all those steps involved in complete infrastructure cleanup starting with Application Cleanup as 1st task.

Follow the thread to get regular updates. Query Written by Karthikeyan A.

1E Nomad
All Applications with no deployments SQL Query
All Applications with no deployments SQL Query

Application Cleanup All Applications with no deployments or task sequences Linked status.

SQL Query

 apps.DisplayName as 'ApplicationName',
 apps.Softwareversion as 'Version',
 CASE pkg.PackageType
 WHEN 0 THEN 'Package'
 WHEN 3 THEN 'Driver'
 WHEN 4 THEN 'TaskSequence'
 WHEN 5 THEN 'SoftwareUpdate'
 WHEN 6 THEN 'DeviceSettings'
 WHEN 7 THEN 'Virtual'
 WHEN 8 THEN 'Application'
 WHEN 257 THEN 'Image'
 WHEN 258 THEN 'BootImage'
 WHEN 259 THEN 'OSInstall'
 END AS 'PackageType',
 apps.NumberOfDeploymentTypes as 'NoofDT',
 from fn_ListLatestApplicationCIs(1033) apps
 left join v_TaskSequencePackageReferences tspr on tspr.ObjectID = apps.ModelName
 left join vSMS_ApplicationAssignment ass on ass.AssignedCI_UniqueID = apps.CI_UniqueID
 left join v_Package pkg on pkg.SecurityKey = apps.ModelName
 PackageType = 8
 and apps.IsLatest=1
 and ass.AssignmentName IS NULL
 and tspr.PackageID IS NULL
 and apps.NumberOfDeployments = 0
 and apps.NumberOfDependentTs = 0
 and apps.IsLatest=1
 order by apps.DisplayName


8 thoughts on “SQL Query All SCCM Applications with no Deployments | ConfigMgr”

  1. I agree that this query is helpful in identifying “empty” (for lack of a better term) applications that are just taking up space in your ConfigMgr (EndpointMgr) environment… but there is a scenario where you do not want to delete an application that you’re no longer deploying — if you have a newer application that you have supersedence configured for to ensure that the ConfigMgr client removes the previous app and installs the latest one.

    In those cases, you just simply need to monitor compliance for the previous application and as soon as you have no instances of it in your environment, you can remove the supersedence configuration on the newer application and delete the older application with no deployments.

  2. v_Package pkg on pkg.SecurityKey = apps.ModelName

    this information shows information only for applications as apps.model name contains only scope*** and no id’s contains, which is not available for other packages, drivers etc…


Leave a Comment

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