Detect SSD drive

You can use the following query in SCCM Reporting to detect if a hard drive is SSD or not. Most harddrive manufacturers are nice enough to put the initials SSD within the drive name, which helps us as Windows and therefore WMI & SCCM do not distinguish between spinning and solid state disks at any useful place in the environment.

If your environment has SSD drives not captured here, simply add the model names to the case statement. I’ve given examples of using both LIKE and EQUAL statements, to help you out if you’re new to SQL.

SELECT DISTINCT
cs.Model0 AS 'Model', sys.Name0 AS 'Machine Name',
sys.User_Name0 AS 'Last Logged on User', sys.User_Domain0 AS 'Domain',
sys.AD_Site_Name0 AS 'AD Site', ld.Name0 AS 'Drive Letter',
CASE
when vdisk.Model0 like '%SSD%' then 'Known SSD Drive'
when vdisk.Model0 = 'LITEONIT LF-64M1S' or
Vdisk.Model0 ='LITEONIT LFT-128M2S' then 'Known SSD Drive'
else vdisk.Model0 END as 'SSD Drive?',
REPLACE(CONVERT(varchar, cast(ld.Size0 AS money),1), '.00', '') as 'Total Drive Space on C: in MB',
REPLACE(CONVERT(varchar, cast((mem.TotalPhysicalMemory0 / 1024) as money),1), '.00', '') AS 'Total Ram Installed in MB'


FROM v_R_System AS sys INNER JOIN
v_GS_COMPUTER_SYSTEM AS cs ON sys.ResourceID = cs.ResourceID INNER JOIN
v_GS_LOGICAL_DISK AS ld ON sys.ResourceID = ld.ResourceID INNER JOIN
v_GS_X86_PC_MEMORY AS mem ON sys.ResourceID = mem.ResourceID INNER JOIN
v_gs_Disk as vdisk on sys.resourceid = vdisk.resourceid

WHERE (vdisk.Model0 NOT LIKE '%USB%')
AND (vdisk.Model0 NOT LIKE '%SD MEMORY%')
AND (vdisk.Model0 <> 'SMART')
AND (sys.Active0 = 1) AND (sys.Decommissioned0 = 0)
AND (sys.SMBIOS_GUID0 IS NOT NULL) AND (ld.Name0 = 'C:')

crazycurt June 4, 2015 / 12:37 pm

Steven, the code here is really cool but I had to tweak it. In the select statement, “vdisk.Model0 <&gt” was not correct – I assume you ment vdisk.Model0 not like ‘SMART’. Can you correct this?

LikeLike

Reply
Jeff September 23, 2015 / 10:51 am

Here is a larger list to consider (dd = disk_data)…

AND ( — ignore ssd, hybrid, vm, external, raid, and oddball drives
DD.Model0 not like ‘%ssd%’
AND DD.Model0 not like ‘%card%’
AND DD.Model0 not like ‘%sd disk%’
AND DD.Model0 not like ‘%sd scsi%’
AND DD.Model0 not like ‘%sd[0-9]%’
AND DD.Model0 not like ‘%external%’
AND DD.Model0 not like ‘%sandisk%’
AND DD.Model0 not like ‘%micron%’
AND DD.Model0 not like ‘%o2micro%’
AND DD.Model0 not like ‘%portable%’
AND DD.Model0 not like ‘%hynix%’
AND DD.Model0 not like ‘%qnap%’
AND DD.Model0 not like ‘%ctfdda%’
AND DD.Model0 not like ‘%dell perc%’
AND DD.Model0 not like ‘%liteon%’
AND DD.Model0 not like ‘%flash%’
AND DD.Model0 not like ‘%freeagent%’
AND DD.Model0 not like ‘%mirror%’
AND DD.Model0 not like ‘%raid%’
AND DD.Model0 not like ‘%usb%’
AND DD.Model0 not like ‘%virtual%’
AND DD.Model0 not like ‘%zip%’
AND DD.Model0 not like ‘%adata%’
AND DD.Model0 not like ‘gb%’
AND DD.Model0 not like ‘ocz%’
AND DD.Model0 not like ‘%kingston%’
AND DD.Model0 not like ‘patriot%’
AND DD.Model0 not like ‘ST[0-9][0-9][0-9][A-Z]M000%’
AND DD.Model0 not like ‘%ram%’
AND DD.PNPDeviceID00 not like ‘%usb%’
AND DD.PNPDeviceID00 not like ‘%msata%’
)

LikeLike

Reply
adi89 January 14, 2016 / 6:55 am

Hi! Can someone please post the final version which is working in SCCM 2012R2?
Thank you

LikeLike

Reply