SQL Query to Get All SCCM Apps/Package Content Details

Photo of author
By Jeff LeBlanc
0

Anyone managing SCCM/MECM in a large enterprise environment probably knows the challenge of dealing with package content.

Often SCCM Administrators are not the same people who package and distribute content to workstations. The problem is the teams putting content in often don’t do a great job of retiring old content and moving it out. The result can be hundreds of gigs and hundreds or even thousands of unnecessary objects left in SCCM that aren’t needed.

This query gives us a full view of ALL of the package related content in SCCM including:

  • Applications
  • Packages
  • Task Sequences
  • Boot Images
  • Driver packages
  • OS Image packages
  • OS Upgrade packages
  • Software Updates packages

It also returns other useful information like whether a package is referenced in a Task Sequence and whether it has an active Deployment or not. It also returns the # of DPs the content has been targeted to.

The query can take a few mins to run in a large SCCM environment but when it’s done, I do a “Select All” on the results and then do a “Copy with Headers” and paste into Excel. Enable filters and then filter on the “Type” and then on “In Task Sequence?” or “Active Deployment?” to find objects not in use that can likely be deleted.

After identifying content to remove, you may need to reach out to stakeholders to get agreement to delete. They may not be comfortable with just deleting all of the content so that’s where we bring in the “puppy dog close”. Try it and see how it goes! You can always bring it back. =)

Rather than just deleting everything outright, start with putting the objects into a “Containment” state. What this means is creating a separate folder for it in SCCM and then running a PowerShell script to move the objects into the folder and remove it from Distribution Points. The script can also remove any deployments and update the Comment/Description with “Containment <date>”. Create an operational policy that says it’s OK to delete all content in Containment after it has been there for 60 days or more.

See my other post PowerShell Script to Move Apps/Packages to Containment for more details about a Containment script/process you might use.

Here’s the SQL query to identify ALL software currently in your SCCM environment.

/* **************************************************************************************************************************************************
    GET LIST OF ALL CONTENT (PACKAGES, APPLICATIONS, TASK SEQUENCES, OS IMAGE, OS UPGRADE, SOFTWARE UPDATES, BOOT IMAGES
***************************************************************************************************************************************************** */
 
SELECT DISTINCT pkg.Name, pkg.PackageID, pkg.Version, pkg.Manufacturer, pkg.SourceDate, CAST(SMSPackages.SourceSize / 1024 as numeric(36,2)) AS 'Size (MB)',

/* SET PACKAGE TYPE */
CASE 
	WHEN pkg.PackageType = 0 THEN 'Package'
	WHEN pkg.PackageType = 3 THEN 'Driver'
	WHEN pkg.PackageType = 4 THEN 'Task Sequence'
	WHEN pkg.PackageType = 5 THEN 'Software Update'
	WHEN pkg.PackageType = 7 THEN 'Virtual Package'
	WHEN pkg.PackageType = 8 THEN 'Application'
	WHEN pkg.PackageType = 257 THEN 'OS Image'
	WHEN pkg.PackageType = 258 THEN 'Boot Image'
	WHEN pkg.PackageType = 259 THEN 'OS Image Source'
END AS 'Type',
 
/* SET PLATFORM TYPE */
CASE 
	WHEN pkg.PackageType = 8 AND pkg.Name LIKE 'Win7%' THEN 'WIN7'
	WHEN pkg.PackageType = 8 AND pkg.Name LIKE 'Win10%' THEN 'WIN10'
	WHEN pkg.PackageType = 8 AND pkg.Name LIKE 'Win11%' THEN 'WIN11'
	WHEN pkg.PackageType = 8 AND pkg.Name LIKE 'SRV2012R2%' THEN 'SRV2012'
	WHEN pkg.PackageType = 8 AND pkg.Name LIKE 'SRV2016%' THEN 'SRV2016'
	WHEN pkg.PackageType = 8 AND pkg.Name LIKE 'SRV2019%' THEN 'SRV2019'
  
	-- Set Platform for Boot Images
	WHEN pkg.PackageType = 258 THEN 'ALL'
 
	-- Set Platform for Drivers
	WHEN pkg.PackageType = 3 AND pkg.Name LIKE '%Win7%' THEN 'WIN7'
	WHEN pkg.PackageType = 3 AND pkg.Name LIKE '%Win10%' THEN 'WIN10'
	WHEN pkg.PackageType = 3 AND pkg.Name LIKE '%Win11%' THEN 'WIN11'
	WHEN pkg.PackageType = 3 AND pkg.Name LIKE '%Win2012R2%' THEN 'SRV2012'
	WHEN pkg.PackageType = 3 AND pkg.Name LIKE '%Win2016%' THEN 'SRV2016'
	WHEN pkg.PackageType = 3 AND pkg.Name LIKE '%Win2019%' THEN 'SRV2019'
 
	-- Set Platform for OS Images
	WHEN pkg.PackageType = 257 AND pkg.Name LIKE '%Windows_7%' THEN 'WIN7'
	WHEN pkg.PackageType = 257 AND pkg.Name LIKE '%Windows_10%' THEN 'WIN10'
	WHEN pkg.PackageType = 257 AND pkg.Name LIKE '%Windows_11%' THEN 'WIN11'
	WHEN pkg.PackageType = 257 AND pkg.Name LIKE '%Server 2012 R2%' THEN 'SRV2012'
	WHEN pkg.PackageType = 257 AND pkg.Name LIKE '%Server 2016%' THEN 'SRV2016'
	WHEN pkg.PackageType = 257 AND pkg.Name LIKE '%Server 2019%' THEN 'SRV2019'
 
	-- Set Platform for OS Image Source
	WHEN pkg.PackageType = 259 AND pkg.Name LIKE '%Win7%' THEN 'WIN7'
	WHEN pkg.PackageType = 259 AND pkg.Name LIKE '%Win10%' THEN 'WIN10'
	WHEN pkg.PackageType = 259 AND pkg.Name LIKE '%Win11%' THEN 'WIN11'
	WHEN pkg.PackageType = 259 AND pkg.Name LIKE '%Server 2012 R2%' THEN 'SRV2012'
	WHEN pkg.PackageType = 259 AND pkg.Name LIKE '%Server 2016%' THEN 'SRV2016'
	WHEN pkg.PackageType = 259 AND pkg.Name LIKE '%Server 2019%' THEN 'SRV2019'
 
	-- Set Platform for Software Updates
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Windows 7 (x64)%' THEN 'WIN7'
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Windows 10 (x64)%' THEN 'WIN10'
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Windows 11 (x64)%' THEN 'WIN11'
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Office 365%' THEN 'WIN10'
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Server 2012%' THEN 'SRV2012'
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Server 2016%' THEN 'SRV2016'
	WHEN pkg.PackageType = 5 AND pkg.Name LIKE '%Server 2019%' THEN 'SRV2019'
 
	-- Set Platform for Task Sequences
	WHEN pkg.PackageType = 4 AND pkg.Name LIKE '%Win7%' THEN 'WIN7'
	WHEN pkg.PackageType = 4 AND pkg.Name LIKE '%Win10%' THEN 'WIN10'
	WHEN pkg.PackageType = 4 AND pkg.Name LIKE '%Win11%' THEN 'WIN11'
	WHEN pkg.PackageType = 4 AND pkg.Name LIKE '%ConfigMgr DP%' THEN 'SRV2019'
	WHEN pkg.PackageType = 4 AND pkg.Name LIKE '%Server 2012 R2%' THEN 'SRV2012'
	WHEN pkg.PackageType = 4 AND pkg.Name LIKE '%Server 2019%' THEN 'SRV2019'
ELSE '' 
END AS 'Platform',

-- Check for TS References
CASE 
	WHEN pkg.PackageID IN (SELECT DISTINCT v_TaskSequenceReferencesInfo.ReferencePackageID FROM v_TaskSequenceReferencesInfo LEFT OUTER JOIN
                      v_Package ON v_TaskSequenceReferencesInfo.ReferencePackageID = v_Package.PackageID) THEN 'YES'
	WHEN pkg.PackageID IN (SELECT DISTINCT v_TaskSequenceAppReferencesInfo.RefAppPackageID FROM v_TaskSequenceAppReferencesInfo LEFT OUTER JOIN
                      v_Package ON v_TaskSequenceAppReferencesInfo.RefAppPackageID = v_Package.PackageID) THEN 'YES'
ELSE 'NO'
END AS [In Task Sequence?],


-- Active Deployment
CASE
	WHEN pkg.PackageID IN (SELECT PackageID FROM v_Advertisement WHERE ExpirationTimeEnabled = 0 OR (ExpirationTimeEnabled <> 0 AND ExpirationTime > GETDATE())) THEN 'YES'
	WHEN pkg.PackageID IN (SELECT pkg.PackageID FROM v_Package AS pkg LEFT OUTER JOIN dbo.fn_ListLatestApplicationCIs(1033) AS app ON pkg.SecurityKey = app.ModelName WHERE pkg.PackageType = 8 AND IsDeployed = 1) THEN 'YES'
ELSE 'NO'
END AS 'Active Deployment?',
 

-- GET # DPs Targeted
PkgRootSum.Targeted AS '# DPs', --PkgRootSum.Installed, PkgRootSum.Failed,
 
-- GET OBJECT PATH
-- UPPER(fm.ObjectPath) AS 'Object Path',
 
-- GET Comment
CASE 
	WHEN pkg.PackageType = 8 THEN app.AdminComments
	ELSE pkg.Description
END AS 'Description'

FROM v_Package AS pkg LEFT OUTER JOIN 
SMSPackages ON pkg.PackageID = SMSPackages.PkgID LEFT OUTER JOIN
vFolderMembers AS fm ON pkg.PackageID = fm.InstanceKey LEFT OUTER JOIN
v_PackageStatusRootSummarizer AS PkgRootSum ON pkg.PackageID = PkgRootSum.PackageID LEFT OUTER JOIN
v_Applications AS app ON pkg.Name = app.DisplayName

ORDER BY Type, pkg.Name

Happy computing!

Leave a Comment