SSD or HDD Powershell Audit
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 matches it with the model information that Lansweeper scans by default. If a match is found, the storage type is listed as discovered with PowerShell.
SSD or HDD Installed Query
Select distinct 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,
tblFloppy.Name,
tblFloppy.Model as DriveModel,
SubQuery1.DriveType as StorageType,
tblFloppy.Partitions,
tblFloppy.SerialNumber,
Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As [Size in GB],
tblFloppy.Status,
tblFloppy.InterfaceType,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.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 In ('0', '1', '2', '3', '4', '5', '6', '7', '8',
'9')) SubQuery1 On SubQuery1.DriveName = tblFloppy.Model
Where tblAssetCustom.State = 1
order by Domain, AssetName