Let’s find out more details about SCCM Apps in this post. SQL Query All SCCM Applications with no Deployments. SCCM provides a management insights feature to deliver these types of information in the console itself.
You can read more about management insights from SCCM Management Insights – A Deep Dive Experience For IT Admins.
Hello, this is my first post here, I have more than 13 years of experience in ConfigMgr. I will start a series of posts related to SQL and WQL queries (SQL Query) to help SCCM admins.
Related post – SCCM Management Insights – Healthy SCCM Infra
Introduction SQL Query All SCCM Applications with no Deployments
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 and too many steps. Also, there are chances of Human Error in Manual cleanup/deletion.
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 that involved incomplete infrastructure cleanup, starting with Application Cleanup as 1st task.
Follow the thread to get regular updates. Query Written by Karthikeyan A.
Application Cleanup All Applications with no deployments or task sequences Linked status.
SQL Query – SQL Query All SCCM Applications with no Deployments
Use the SSMS to run the following SQL Query to find All SCCM Applications with no Deployments (ConfigMgr).
apps.DisplayName as 'ApplicationName',
apps.Softwareversion as 'Version',
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 ass.AssignmentName IS NULL
and tspr.PackageID IS NULL
and apps.NumberOfDeployments = 0
and apps.NumberOfDependentTs = 0
order by apps.DisplayName
My name is Deepak Rai, and I am a Technical Lead on SCCM and Intune with more than 14 years of experience in IT. My main domain is SCCM (AKA ConfigMgr, CB, MECM, etc.), Intune, and Azure (Runbooks). I have worked on several platforms (Active Directory, Exchange, Veritas NETBACKUP, Symantec Backup Exec, NDMP devices Like Netapp, EMC Data Domain, Quantum using Backup Exec 2010 and 2012, HP storage works 4048 MSL G3, Data Deduplication related troubleshooting.) in these 13 years but at last ended up to the technology from which I started as IT Engineer (SCCM).