Drivers in SCCM that a computer model can use

-- Drivers in SCCM that a computer model can use

declare @ResourceID bigint = (select top 1 ResourceID from v_GS_COMPUTER_SYSTEM cs where cs.Manufacturer0 = 'Hewlett-Packard' and cs.Model0 = 'HP Compaq Elite 8300 SFF')

declare @targetPlatform nvarchar(25) = 'All x64 Windows 7 Client'

SELECT DISTINCT DriverTable.CI_ID AS CI_ID

, DriverTable.DriverName as DriverName

, DriverTable.DriverClass as DriverClass

, DriverTable.ProviderName as ProviderName

, DriverTable.DriverVersion as DriverVersion

, DriverTable.IsEnabled as DriverEnabled0

FROM

 

(

SELECT DISTINCT LTRIM(SubString) AS HardwareID

FROM v_GS_SYSTEM_DEVICES Devices

INNER JOIN v_R_System System

ON Devices.ResourceID=@ResourceID

CROSS APPLY dbo.fn_SplitString(IsNull(HardwareIDs0+','+CompatibleIDs0,IsNull(HardwareIDs0,CompatibleIDs0)), ',')

) AS DeviceTable

INNER JOIN

(

SELECT Drivers.CI_ID,

locCI.DisplayName as DriverName,

Drivers.DriverClass as DriverClass,

Drivers.DriverProvider as ProviderName,

Drivers.DriverVersion as DriverVersion,

CIs.IsEnabled as IsEnabled,

HardwareIds.HardwareID

FROM v_CI_DriversCIs AS Drivers

LEFT JOIN v_CI_ApplicablePlatforms AS Platforms

ON Drivers.CI_ID=Platforms.CI_ID

INNER JOIN v_CI_DriverHardwareIDs AS HardwareIds

ON Drivers.CI_ID=HardwareIds.CI_ID

INNER JOIN v_ConfigurationItems AS CIs

ON Drivers.CI_ID=CIs.CI_ID

INNER JOIN v_LocalizedCIProperties_SiteLoc AS locCI

ON CIs.CI_ID=locCI.CI_ID

WHERE Platforms.OSDisplayName=@targetPlatform OR

Platforms.OSDisplayName IS NULL

) DriverTable

ON DeviceTable.HardwareID = DriverTable.HardwareID

ORDER BY 2, 3, 4, 5
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s