SCCM Unused Packages
This is a t-sql query to help find Packages which you are no longer using so they can be properly organized/categorized/deleted.
Thanks to John Nelson (Number 2) and Christjan Schumann for doing the heavy lifting. I only put the two puzzle pieces together.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 UNION ALL --child folders (recursive) SELECT 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 |
References: