Use this query to help determine if a PC has a CD or DVD drive installed.

 

set nocount on
SELECT case
When CD.DeviceID0 Like ‘%CD-RW%’ then ‘CD -RW’
When CD.DeviceID0 Like ‘%CDRW%’ then ‘CDRW’
When CD.DeviceID0 Like ‘%CDRW%’ then ‘CDRW’
when CD.DeviceID0 like ‘%DVD-ROM%’ then ‘DVD’
When CD.DeviceID0 Like ‘%DVD-RAM%’ then ‘DVD RAM’
When CD.DeviceID0 Like ‘%DVDRAM%’ then ‘DVD RAM’
When CD.DeviceID0 Like ‘%DVD+-RW%’ then ‘DVD +- RW’
When CD.DeviceID0 Like ‘%_DVDRW_%’ then ‘DVD +- RW’
When CD.DeviceID0 Like ‘%DVD+RW%’ Then ‘DVD +RW’
When CD.DeviceID0 Like ‘%DVD-RW%’ then ‘DVD -RW’
When CD.DeviceID0 Like ‘%DVD_RW%’ then ‘DVD RW’
When CD.DeviceID0 Like ‘%CD/DVD%’ then ‘DVD’
When CD.DeviceID0 Like ‘%DVDRRW%’ then ‘DVD RRW’ — What is RRW?
When CD.DeviceID0 Like ‘%RW/DVD%’ then ‘CD RW / DVD’ — Guess
When CD.DeviceID0 Like ‘%CD-WRITER%’ then ‘CD Writer’
When CD.DeviceID0 Like ‘%DVD-WRITER%’ then ‘DVD Writer’
When CD.DeviceID0 Like ‘%DVD_WRITER%’ then ‘DVD Writer’
When CD.DeviceID0 Like ‘%COMBO_SOHC%’ then ‘DVD/CD-RW’
When CD.DeviceID0 Like ‘%CDW/DVD%’ then ‘DVD/CD-RW’
When CD.DeviceID0 Like ‘%DVD-R%’ then ‘DVD -R’
When CD.DeviceID0 Like ‘%DVD_SOHD%’ then ‘DVD/CD-RW’ — google results
When CD.DeviceID0 Like ‘%DVDRW_LDW%’ then ‘DVD/CD-RW’ — google results
When CD.DeviceID0 Like ‘%_DW-%’ then ‘DVD/CD-RW’ — google results
When CD.DeviceID0 Like ‘%_DW-%’ then ‘DVD/CD-RW’ — google results
When CD.DeviceID0 Like ‘%COMBO_SOSC%’ then ‘DVD +- RW’ — google results
When CD.DeviceID0 Like ‘%CD-R%’ then ‘CD-R’
When CD.DeviceID0 Like ‘%CRW%’ then ‘CD-R’
when CD.DeviceID0 like ‘%_VD-%’ then ‘DVD’
when CD.DeviceID0 like ‘%_DV-%’ then ‘DVD’
when CD.DeviceID0 like ‘%VCD%’ then ‘VCD’
when CD.DeviceID0 like ‘%CR%’ then ‘CD-R’
when CD.DeviceID0 like ‘%CD_ROM-DRIVE%’ then ‘CD-ROM’
when CD.DeviceID0 like ‘%CD_ROM_DRIVE%’ then ‘CD-ROM’
when CD.DeviceID0 like ‘%_CD-%’ then ‘CD-ROM’
when CD.DeviceID0 like ‘%CDU%’ then ‘CD-ROM’
else CD.DeviceID0
end as ‘CDRom’,
count(‘CD Rom’)as ‘Total’
into #CD
FROM v_GS_CDROM CD
GROUP BY CD.DeviceID0
ORDER BY ‘CD Rom’

set nocount off
Select #cd.CDRom as ‘Drive’, sum(#Cd.total) as ‘Total’
From #CD
group by #cd.CDRom
order by #cd.CDRom

drop table #cd