Automation tails from the fox hole

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.

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%’
)