Find 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