SSD report

[Nigel_Proctor]

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

[Mister_Nobody]

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]

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]

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]

(
   (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]

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]

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]

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]

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]

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...