cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Nigel_Proctor
Engaged Sweeper
Hi,

Is there anyway to report on machines that have SSD's installed, we want to be able to report on which machines currently have them installed.

Thanks

Nigel
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You can filter on SSD drives through the drive model. Please find an example report below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblFloppy.InterfaceType As [HD interface],
tblFloppy.Manufacturer As [HD manufacturer],
tblFloppy.Model As [HD model],
tblFloppy.Name,
Cast(Cast(tblFloppy.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HD Size (GB)],
tblFloppy.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblFloppy.Model Like '%ssd%' Or tblFloppy.Model Like '%solid-state%') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
tblFloppy.Name

View solution in original post

12 REPLIES 12
Mister_Nobody
Honored Sweeper II

Latest version:

 

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.Memory,
  tblAssets.Processor,
  tblProcessor.NumberOfCores * tblAssets.NrProcessors As TotalCores,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.IPAddress As [IP Address],
  tblAssets.Lastseen,
  tblADusers.Title,
  tblADusers.Department,
  tblFloppy.Model As SSDModel,
  tblFloppy.FirmwareRevision,
  tblFloppy.SerialNumber,
  tblFloppy.Size,
  tblFloppy.InterfaceType
From tblAssets
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID And
      tblFloppy.InterfaceType <> 'USB' And (tblFloppy.Model Like 'OCZ%' Or
        tblFloppy.Model Like '%TL100%' Or tblFloppy.Model Like 'Plextor PX%' Or
        Replace(tblFloppy.Model, ' ', '') Like 'KingstonS%' Or
        tblFloppy.Model Like '%Solid%' Or tblFloppy.Model Like '%Patrio%' Or
        tblFloppy.Model Like '%udinfo%' Or tblFloppy.Model Like '%THNSN%' Or
        tblFloppy.Model Like '%NVMe%' Or tblFloppy.Model Like '%MZ%' Or
        tblFloppy.Model Like '% run %' Or tblFloppy.Model Like '% meta %' Or
        tblFloppy.Model Like '% mega %' Or tblFloppy.Model Like '%MTFD%' Or
        tblFloppy.Model Like '%amd %' Or tblFloppy.Model Like 'SanDisk%' Or
        tblFloppy.Model Like 'NE-%' Or tblFloppy.Model Like '%smartbuy%' Or
        tblFloppy.Model Like 'PH_-%' Or tblFloppy.Model Like 'gigabyte %' Or
        tblFloppy.Model Like 'Apacer%' Or Replace(tblFloppy.Model, ' ',
        '') Like 'ADATA%' Or Replace(tblFloppy.Model, ' ', '') Like 'ToshibaA%'
        Or tblFloppy.Model Like 'SanDis SD%' Or tblFloppy.Model Like 'WDC WDS%'
        Or Replace(tblFloppy.Model, ' ', '') Like '%SSD%')
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username
  Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Order By tblAssets.AssetID

 

Mister_Nobody
Honored Sweeper II
2 Feliciano
Sorry that I misled you.

In LS query we have to specify Hard Drive Model ID but not Series.
Series are stored in http://www.hdsentinel.com/compatibility_ssd.php .
And Model ID are stored in HDD/SSD database http://www.hdsentinel.com/storageinfo.php?lang=en .
But at moment I don't know how to get all data with SSD to analyze.

Also in DB there are some different details - spaces, dashes.
Feliciano
Engaged Sweeper
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Lastseen,
tblFloppy.InterfaceType As [HD interface],
tblFloppy.Manufacturer As [HD manufacturer],
tblFloppy.Model As [HD model],
tblFloppy.Name,
Cast(Cast(tblFloppy.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HD Size (GB)],
tblFloppy.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where ((tblFloppy.Model Like '%SSD%') Or (tblFloppy.Model Like '%Solid State%')
Or (tblFloppy.Model Like '%OADATA S%') Or
(tblFloppy.Model Like '%ADTDTA XPG%') Or (tblFloppy.Model Like '%ADTron X%')
Or (tblFloppy.Model Like '%Asus Phison%') Or (tblFloppy.Model Like
'%Buffalo Silicon%') Or (tblFloppy.Model Like '%Corsair P128%') Or
(tblFloppy.Model Like '%Corsair Extreme%') Or
(tblFloppy.Model Like '%Corsair Reactor%') Or
(tblFloppy.Model Like '%Crucial CT%') Or
(tblFloppy.Model Like '%CSX Memory%') Or
(tblFloppy.Model Like '%G.Skill Falcon%') Or (tblFloppy.Model Like
'%G.Skill FM%') Or (tblFloppy.Model Like '%G.Skill Titan%') Or
(tblFloppy.Model Like '%Indilinx Barefoot%') Or
(tblFloppy.Model Like '%Indilinx Everest%') Or
(tblFloppy.Model Like '%Intel%') Or (tblFloppy.Model Like '%JMicron JMF%')
Or (tblFloppy.Model Like '%KingSpec KSD%') Or
(tblFloppy.Model Like '%Kingston HyperX%') Or
(tblFloppy.Model Like '%Kingston S%') Or (tblFloppy.Model Like '%M4-CT%') Or
(tblFloppy.Model Like '%Marvell 88SS%') Or (tblFloppy.Model Like '%MTFD%')
Or (tblFloppy.Model Like '%MZ%') Or (tblFloppy.Model Like '%MTron MSD%') Or
(tblFloppy.Model Like '%OCZ%') Or (tblFloppy.Model Like '%Patriot Inferno%')
Or (tblFloppy.Model Like '%Patriot Warp%') Or
(tblFloppy.Model Like '%Patriot Memory%') Or (tblFloppy.Model Like
'%Patriot Torqx%') Or (tblFloppy.Model Like '%Patriot Zephyr%') Or
(tblFloppy.Model Like '%Phison PS3%') Or (tblFloppy.Model Like '%RunCore%')
Or (tblFloppy.Model Like '%Samsung 810%') Or (tblFloppy.Model Like
'%Samsung 830%') Or (tblFloppy.Model Like '%Samsung 840%') Or
(tblFloppy.Model Like '%Samsung 850%') Or
(tblFloppy.Model Like '%Samsung 950%') Or
(tblFloppy.Model Like '%Samsung 951%') Or
(tblFloppy.Model Like '%SAMSUNG MMC%') Or
(tblFloppy.Model Like '%Samsung NVMe%') Or
(tblFloppy.Model Like '%Sandforce SF') Or
(tblFloppy.Model Like '%SanDisk SD%%') Or
(tblFloppy.Model Like '%SILICONMOTION SM%') Or
(tblFloppy.Model Like '%Silicon Power%') Or (tblFloppy.Model Like
'%Stec Pata%') Or (tblFloppy.Model Like 'SuperTalent MasterDrive%') Or
(tblFloppy.Model Like '%TL100%') Or (tblFloppy.Model Like '%THNSN%') Or
(tblFloppy.Model Like 'Toshiba T6UG1XBG%') Or
(tblFloppy.Model Like 'Toshiba TC58NCF%') Or (tblFloppy.Model Like
'Transcend%')) And tblAssetCustom.State = 1
Order By [HD model],
tblFloppy.Name
Mister_Nobody
Honored Sweeper II

(
(tblFloppy.Model Like 'OCZ%')
Or (tblFloppy.Model Like '%TL100%')
Or (tblFloppy.Model Like 'Kingston S%')
Or (tblFloppy.Model Like '%Solid%')
Or (tblFloppy.Model Like '%SSD%')
Or (tblFloppy.Model Like '%THNSN%')
Or (tblFloppy.Model Like '%NVMe%')
Or (tblFloppy.Model Like '%MZ%')
Or (tblFloppy.Model Like '%MTFD%')
Or (tblFloppy.Model Like 'Plextor PX%')
Or (tblFloppy.Model Like 'SanDisk S%')
)
Feliciano
Engaged Sweeper
This is an example of a result of some models:


TOSHIBA-TL100
SAMSUNG MZ7LN256HCHP
SAMSUNG MZNLN256HMHQ
TOSHIBA THNSNJ256GMCU
TOSHIBA THNSNJ256GMCT
KINGSTON SKC400S37256G
KINGSTON SV300S37A120G
INTEL SSDSC2BF180A4H
NVMe SAMSUNG MZFLV256
NVMe SAMSUNG MZFLV128
NVMe THNSN5256GPU7

RCorbeil
Honored Sweeper II
Watch your WHERE clause.
Where
(tblFloppy.Model Like 'OCZ%')
OR (tblFloppy.Model Like 'Kingston S%')
OR (tblFloppy.Model Like '%Solid State%')
OR (tblFloppy.Model Like '%SSD%')
OR (tblFloppy.Model Like '%MZNLN%')
OR (tblFloppy.Model Like '%MZFLV%')
OR (tblFloppy.Model Like '%MZ7LN%')
OR (tblFloppy.Model Like '%THNSN%')
OR (tblFloppy.Model Like '%TL100%')
OR (tblFloppy.Model Like '%NVMe%' And tblAssetCustom.State = 1)

looks like you mean it to read
Where
(tblAssetCustom.State = 1) AND
( (tblFloppy.Model Like 'OCZ%')
OR (tblFloppy.Model Like 'Kingston S%')
OR (tblFloppy.Model Like '%Solid State%')
OR (tblFloppy.Model Like '%SSD%')
OR (tblFloppy.Model Like '%MZNLN%')
OR (tblFloppy.Model Like '%MZFLV%')
OR (tblFloppy.Model Like '%MZ7LN%')
OR (tblFloppy.Model Like '%THNSN%')
OR (tblFloppy.Model Like '%TL100%')
OR (tblFloppy.Model Like '%NVMe%')
)
Feliciano
Engaged Sweeper
Thanks for your help!!!

This is my improved query:




Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblFloppy.InterfaceType As [HD interface],
tblFloppy.Manufacturer As [HD manufacturer],
tblFloppy.Model As [HD model],
tblFloppy.Name,
Cast(Cast(tblFloppy.Size As bigint) / 1024 / 1024 / 1024 As numeric)
As [HD Size (GB)],
tblFloppy.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where
(tblAssetCustom.State = 1) AND
( (tblFloppy.Model Like 'OCZ%')
OR (tblFloppy.Model Like 'Kingston S%')
OR (tblFloppy.Model Like '%Solid State%')
OR (tblFloppy.Model Like '%SSD%')
OR (tblFloppy.Model Like '%MZNLN%')
OR (tblFloppy.Model Like '%MZFLV%')
OR (tblFloppy.Model Like '%MZ7LN%')
OR (tblFloppy.Model Like '%THNSN%')
OR (tblFloppy.Model Like '%TL100%')
OR (tblFloppy.Model Like '%NVMe%')
)
Order By tblAssets.AssetName,
tblFloppy.Name
Mister_Nobody
Honored Sweeper II
We have about 6000 disks total and I filtered only our SSD models - about 300 disks. So you have to adapt filter to your SSD models.

For your situation with Samsung-based and Micron/Crucial I see next solution(add conditions):
(tblFloppy.Model Like '%MZ%') Or
(tblFloppy.Model Like '%MTFDDAV%')

About Liteon - can you show more information from built-in report "Asset: Harddrive Serial Number":

SerialNumber
HD Model
HD Manufacturer

As for SanDisk - my models have "SSD" marker.

P.S. List of SSD models http://www.hdsentinel.com/compatibility_ssd.php
fjca
Champion Sweeper II
I've just surveyed our 7000 hard disk report (sadly, mostly traditional ones) and would add the follwoing entries:

a) LITEON
b) MTFDDAV (for Micron/Crucial)
c) Sandis (for Sandisk, I have one model reporting Sandis, and other models Sandisk)
d) MZ-5S71000 (Dell OEM'ed Samsung Server Grade drives)

d) And for last, I have one machine reporting a SAMSUN MZNLN256HCHP.
It could be a firmware glitch, or maybe a case like c), but I only have one, so cannot compare.

Not that easy, I know...