Following up the recent life hacks series post about finding certain drivers in SCCM, that can be found over here:
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.
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:
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!
Code:
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
