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.
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.
Application Cleanup All Applications with no deployments or task sequences Linked status.
select apps.DisplayName as 'ApplicationName', apps.Softwareversion as 'Version', pkg.PackageID, 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', apps.NumberOfDeployments, apps.NumberOfDependentTs 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 where 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