Storage Type Registry Keys Overview
Hardware ComponentsFind SSD or HDD Details from Computers with Powershell in Your Network
Lansweeper always scans drive information from Windows computers with great details like the model, size, type, filesystem and more. One particular data point has been more difficult to accurately retrieve and that is the type of drive. With SSD drives becoming the standard, knowing which devices or servers are using which type of drive is important in order to know what kind of storage is installed.
The report below uses data from registry keys mentioned in the Pro Tips #16 blog post and provides an overview of all the data retrieved with up to 10 separate drives per device and their storage type.
Storage Type Registry Keys Overview Query
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.Version,
tblAssets.SP,
SubQuery1.DriveName,
SubQuery1.DriveType,
SubQuery2.DriveName As DriveName2,
SubQuery2.DriveType As DriveType2,
SubQuery3.DriveName As DriveName3,
SubQuery3.DriveType As DriveType3,
SubQuery4.DriveName As DriveName4,
SubQuery4.DriveType As DriveType4,
SubQuery5.DriveName As DriveName5,
SubQuery5.DriveType As DriveType5,
SubQuery6.DriveName As DriveName6,
SubQuery6.DriveType As DriveType6,
SubQuery7.DriveName As DriveName7,
SubQuery7.DriveType As DriveType7,
SubQuery8.DriveName As DriveName8,
SubQuery8.DriveType As DriveType8,
SubQuery9.DriveName As DriveName9,
SubQuery9.DriveType As DriveType9,
SubQuery10.DriveName As DriveName10,
SubQuery10.DriveType As DriveType10,
Case
When TsysLastscan.Lasttime < GetDate() - 1 Then
'Last registry scan more than 24 hours ago! Information may not be up-to-date. Try rescanning this machine.'
End As Comment,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
TsysLastscan.Lasttime As LastRegistryScan,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'0') SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'1') SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'2') SubQuery3 On SubQuery3.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'3') SubQuery4 On SubQuery4.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'4') SubQuery5 On SubQuery5.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'5') SubQuery6 On SubQuery6.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'6') SubQuery7 On SubQuery7.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'7') SubQuery8 On SubQuery8.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'8') SubQuery9 On SubQuery9.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Value,
SubString(tblRegistry.Value, CharIndex('FriendlyName=', tblRegistry.Value)
+ Len('FriendlyName='), CharIndex(';', tblRegistry.Value) -
CharIndex('FriendlyName=', tblRegistry.Value) - Len('FriendlyName=')) As
DriveName,
SubString(tblRegistry.Value, CharIndex('MediaType=', tblRegistry.Value) +
Len('MediaType='), CharIndex('}', tblRegistry.Value) -
CharIndex('MediaType=', tblRegistry.Value) - Len('MediaType=')) As
DriveType
From tblRegistry
Where tblRegistry.Regkey Like '%System\HDD' And tblRegistry.Valuename =
'9') SubQuery10 On SubQuery10.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName