SCCM Unused Packages

Find Unused Packages in SCCM

DECLARE @ObjectTypes TABLE (ObjectType INT PRIMARY KEY, TypeDescription VARCHAR(46));
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(2,’Package’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(3,’Advertisement’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(7,’Query’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(8,’Report’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(9,’MeteredProductRule’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(11,’ConfigurationItem’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(14,’OperatingSystemInstallPackage’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(17,’StateMigration’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(18,’ImagePackage’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(19,’BootImagePackage’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(20,’TaskSequencePackage’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(21,’DeviceSettingPackage’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(23,’DriverPackage’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(25,’Driver’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(1011,’SoftwareUpdate’);
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(2011,’ConfigurationItem (Configuration baseline)’);

WITH fldr AS (
–top level folders (anchor)
SELECT CAST(‘\’+f.Name AS VARCHAR(512)) AS Folder, f.ContainerNodeID AS ID, f.ParentContainerNodeID AS ParentID,
ot.ObjectType, ot.TypeDescription
FROM dbo.vSMS_Folders f
JOIN @ObjectTypes ot ON f.ObjectType = ot.ObjectType
WHERE f.ParentContainerNodeID = 0
–child folders (recursive)
CAST(Parent.Folder+’\’+Child.Name AS VARCHAR(512)) AS Folder, Child.ContainerNodeID AS ID,
Child.ParentContainerNodeID AS ParentID, ot.ObjectType, ot.TypeDescription
FROM dbo.vSMS_Folders Child
JOIN @ObjectTypes ot ON Child.ObjectType = ot.ObjectType
JOIN fldr AS Parent ON Child.ParentContainerNodeID = Parent.ID AND Child.ObjectType = Parent.ObjectType

SELECT F.TypeDescription [Node], F.Folder, p.PackageID, p.Manufacturer, p.Name, Version, p.Language, p.Description,
PkgSourcePath AS [Source Path], LastRefreshTime,
(SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = p.PackageID) AS [No of DPs]
FROM v_Package p
left join vFolderMembers M on M.InstanceKey = P.PackageID
left join fldr F on F.ID = M.ContainerNodeID
WHERE p.PackageType = 0
AND p.PackageID NOT IN (SELECT PackageID FROM v_Advertisement)
AND p.PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo)
AND F.Folder NOT Like ‘\Archive%’
ORDER BY F.TypeDescription, F.Folder, p.Manufacturer, p.Name

Source :


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

Up ↑

%d bloggers like this: