Checking the Full Folder Path of Collection Objects in SCCM

    Following up the recent post about identifying full folder path in SCCM for certain drivers, that can be found over here:

Checking the Full Folder Path of Driver Objects in SCCM
 

    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:

    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.

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