Following up the recent post about identifying full folder path in SCCM for certain drivers, that can be found over here:
I decided to post another, modified SQL query which hopefully will help you in this situation:
Delete Collection Error - References to Other Collections Block the Delete Collection Action
We know, that the references to the collection we want to remove in other collections are blocking the possibility to remove our collection. A task to find those collections might not be trivial if you don't know where in SCCM folder structure do they reside. Query below will recursively find the full folder path to those collections based on the regular expressions.
Usage:
Usage:
Replace
the _CollectionRegexp_ string with the regular expression matching
your needs depending on the collections you are looking for.
Obviously the query should be run via SQL Management Studio or any other similar tool letting you execute T-SQL queries.
Obviously the query should be run via SQL Management Studio or any other similar tool letting you execute T-SQL queries.
Code:
SELECT ROW_NUMBER() OVER (ORDER BY v_Collection.Name,vSMS_Folders.ContainerNodeID) AS [Row], v_Collection.Name, vSMS_Folders.ContainerNodeID,v_Collection.Name AS Folder INTO
#Temp
FROM v_Collection
JOIN vFolderMembers on
v_Collection.CollectionID = vFolderMembers.InstanceKey
JOIN vSMS_Folders ON
vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID
WHERE v_Collection.Name LIKE '_CollectionRegexp_'
AND vFolderMembers.ObjectTypeName
= 'SMS_Collection_Device'
ORDER BY v_Collection.Name
DECLARE @It INT=1
WHILE (@It <= (SELECT COUNT(*) FROM #Temp))
BEGIN
DECLARE @ContainerID INT=(SELECT
ContainerNodeID FROM #Temp WHERE Row=@It)
DECLARE @ContainerIDBeg INT=(SELECT
ContainerNodeID FROM #Temp WHERE Row=@It)
DECLARE @ContainerFullName VARCHAR(MAX)
DECLARE @ContainerName VARCHAR(MAX)
WHILE (SELECT ParentContainerNodeID FROM
vSMS_Folders WHERE ContainerNodeID=@ContainerID) != 0
BEGIN
SET @ContainerName = (SELECT Name FROM
vSMS_Folders WHERE ContainerNodeID=@ContainerID)
IF (@ContainerID=(SELECT TOP 1 ContainerNodeID FROM
#Temp))
SET @ContainerFullName =
@ContainerName
ELSE
SET @ContainerFullName =
@ContainerName + '\'
+ @ContainerFullName
SET @ContainerID = (SELECT ParentContainerNodeID FROM
vSMS_Folders WHERE ContainerNodeID=@ContainerID)
END
SET @ContainerFullName = (SELECT Name FROM
vSMS_Folders WHERE ContainerNodeID=@ContainerID) + '\' + @ContainerFullName
UPDATE #Temp
SET
Folder = @ContainerFullName
WHERE Row = @It
SET @It += 1
SET @ContainerFullName = ''
END
SELECT Name,Folder FROM #Temp
DROP TABLE #Temp
