Checking the Full Folder Path of Driver Objects in SCCM

    Following up the recent life hacks series post about finding certain drivers in SCCM, that can be found over here:

 Life Hacks - Checking the Presence of Certain Drivers in SCCM
 

    I decided to post about the follow up question that I received afterwards. As we already identified, that certain drivers exist in the infrastructure, the next question is - where are they? The answer to this is not too obvious, as finding the full folder path in SCCM requires looping through the database with a SQL query, that will iterate towards the parent folder until such exists. With the below query you can identify this rather than checking folder by folder in the SCCM Console. Below you can find the exemplary output of the query:

 Exemplary Output of the Path of Driver Objects SQL query

Usage:

    Replace the _DriverModelRegexp_ string with the regular expression matching your needs depending on the model of the drivers 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.

Additional Notes:

    Interesting fact about this query - it does find the drivers not only if they are present as an object with an exact same name as the queried one. It also finds the drivers that are compatible with the queried ones even with the other name. Apparently this is the way that the information is stored in SCCM database. You can see the example below, as in the query above I was looking for Allied Telesis AT-2911xx Gigabit Fiber Ethernet. The results of the query pointed me to the following folder with the following 4 objects inside:

Returned Folder Does Not Contain Allied Telesis AT-2911 Drivers

    However when you looks closer and check the Applicability tab in the dirver's properties you see, that they are actually compatible with AT-2911xx Gigabit Fiber Ethernet. Very convenient!

 Returned Drivers are compatible with Allied Telesis AT-2911 Ones

Code:

SELECT ROW_NUMBER() OVER (ORDER BY ModelName,vSMS_Folders.ContainerNodeID) AS Row,ModelName,vSMS_Folders.ContainerNodeID,ModelName AS Folder INTO #Temp

FROM v_CI_DriverModels

JOIN vFolderMembers ON v_CI_DriverModels.CI_UniqueID = vFolderMembers.InstanceKey

JOIN vSMS_Folders ON vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID

WHERE ModelName LIKE '_DriverModelRegexp_'

ORDER BY ModelName

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 ModelName,Folder FROM #Temp

DROP TABLE #Temp