I've built a report to show just the network drivers on a system, with the primary aim of tracking down machines that might benefit from a driver update.
Any suggestions for improvements?
Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
tblNetworkAdapter.Name As NetworkAdapter,
tblNetworkAdapter.Manufacturer As AdapterManufacturer,
tblNetworkAdapter.MACAddress,
tblNetworkAdapter.Speed As BitsPerSecond,
tblNetworkAdapter.NetConnectionID As ConnectionID,
tblNetworkAdapter.Lastchanged,
tblPnPSignedDrivers.DeviceID,
tblPnPSignedDrivers.DriverName,
tblPnPSignedDrivers.FriendlyName,
tblPnPSignedDrivers.DriverDate,
tblPnPSignedDrivers.DriverVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblPnPSignedDrivers On
tblAssets.AssetID = tblPnPSignedDrivers.AssetID And
tblPnPSignedDrivers.FriendlyName = tblNetworkAdapter.Name
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName,
NetworkAdapter
