SQL to identify USB drive letters
Hi guys....
I am VERY new to using SQL under VBS (or at all!).
I am trying to detect which of 4 given USB devices are connected to the PC (using the Plug'N'Play id of the USB devices), and what their drive letters are.
The following code runs to completion, but returns no results.
I THINK the SQL statement is syntactically correct, but don't have any experience of the "Win32_USBHub" or "Win32_DiskDrive" 'table' (is 'table' the correct word?).
My code follows.
Can any kind soul help me get it working?
NOTE: I've tried using both "conQueryDrives" and "conQueryUSBs" in the SQL - conQueryDrives takes appreciably longer, but still has 0 results.
Code:
Option Explicit
'*
'** Script to identify the PnPId attached to an USB port.
'*
Const conComputer = "."
Const conQueryUSBs = "SELECT * FROM Win32_USBHub"
Const conQueryDrives = "SELECT * FROM Win32_DiskDrive"
Dim arrDevDets()
Dim colDiskDrives, objDrive
Dim colLogicalDisks, objLogicalDisk
Dim colPartitions, objPartition
Dim intMax
Dim intPtr
Dim objWMIService
Dim strDeviceID
Dim strQuery
Dim strPNPSearch
'*
'** Set up the known substrings of the possible USB
'** devices.
'*
ReDim arrDevDets(3)
arrDevDets(0) = "575837314143324C33323636"
arrDevDets(1) = "575832314143325935373537"
arrDevDets(2) = "575834314142323738313037"
arrDevDets(3) = "575838314143324130323234"
'*
'** Set up the SQL to recover the USB details.
'*
strPNPSearch = ""
intMax = UBound(arrDevDets)
For intPtr = 0 To intMax
strPNPSearch = strPNPSearch & "PNPDeviceId like '" & _
arrDevDets(intPtr) & "'"
If intPtr < intMax Then strPNPSearch = strPNPSearch & " OR "
Next
' strQuery = conQueryUSBs & " WHERE " & strPNPSearch
strQuery = conQueryDrives & " WHERE " & strPNPSearch
Call MsgBox(strQuery,vbInformation,"DIAGNOSTIC")
' WScript.Quit
Set objWMIService = GetObject("winmgmts:\\" & _
conComputer & _
"\root\cimv2")
Set colDiskDrives = objWMIService.ExecQuery(strQuery)
For Each objDrive In colDiskDrives
call MsgBox("Physical Disk: " & _
objDrive.Caption & _
" -- " & _
objDrive.DeviceID)
strDeviceID = Replace(objDrive.DeviceID, "\", "\\")
Set colPartitions = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_DiskDrive.DeviceID=""" & _
strDeviceID & """} WHERE AssocClass = " & _
"Win32_DiskDriveToDiskPartition")
For Each objPartition In colPartitions
Call MsgBox("Disk Partition: " & objPartition.DeviceID)
Set colLogicalDisks = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_DiskPartition.DeviceID=""" & _
objPartition.DeviceID & """} " & _
"WHERE AssocClass = Win32_LogicalDiskToPartition")
For Each objLogicalDisk In colLogicalDisks
Call MsgBox("Logical Disk: " & objLogicalDisk.DeviceID)
Next 'objLogicalDisk
Next 'objPartition
Next 'objDrive
WScript.Quit
Re: SQL to identify USB drive letters
Have you taken a look at this example from MSDN?
I noticed that your original code forms an incomplete query. I used a WScript.Echo instead of a MsgBox. That allows me to output to the console.
PHP Code:
SELECT * FROM Win32_DiskDrive
WHERE
PNPDeviceId like '575837314143324C33323636' OR PNPDeviceId like '575832314143325935373537'
OR PNPDeviceId like '575834314142323738313037' OR PNPDeviceId like '575838314143324130323234' OR PNPDeviceId like ''
So I did away with the where clause of the query to see what happens when I run it without the where clause.
PHP Code:
Option Explicit
'*
'** Script to identify the PnPId attached to an USB port.
'*
Const conComputer = "."
Const conQueryUSBs = "SELECT * FROM Win32_USBHub"
Const conQueryDrives = "SELECT * FROM Win32_DiskDrive"
Dim arrDevDets()
Dim colDiskDrives, objDrive
Dim colLogicalDisks, objLogicalDisk
Dim colPartitions, objPartition
Dim intMax
Dim intPtr
Dim objWMIService
Dim strDeviceID
Dim strQuery
Dim strPNPSearch
'*
'** Set up the known substrings of the possible USB
'** devices.
'*
ReDim arrDevDets(4)
arrDevDets(0) = "575837314143324C33323636"
arrDevDets(1) = "575832314143325935373537"
arrDevDets(2) = "575834314142323738313037"
arrDevDets(3) = "575838314143324130323234"
'*
'** Set up the SQL to recover the USB details.
'*
strPNPSearch = ""
intMax = UBound(arrDevDets)
For intPtr = 0 To intMax
strPNPSearch = strPNPSearch & "PNPDeviceId like '" & _
arrDevDets(intPtr) & "'"
If intPtr < intMax Then strPNPSearch = strPNPSearch & " OR "
Next
'strQuery = conQueryUSBs ' " WHERE " & strPNPSearch
strQuery = conQueryDrives '& " WHERE " & strPNPSearch
WScript.Echo strQuery
' WScript.Quit
Set objWMIService = GetObject("winmgmts:\\" & _
conComputer & _
"\root\cimv2")
Set colDiskDrives = objWMIService.ExecQuery(strQuery)
WScript.Echo colDiskDrives.Count
For Each objDrive In colDiskDrives
WScript.Echo ("Physical Disk: " & _
objDrive.Caption & _
" -- " & _
objDrive.DeviceID)
strDeviceID = Replace(objDrive.DeviceID, "\", "\\")
Set colPartitions = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_DiskDrive.DeviceID=""" & _
strDeviceID & """} WHERE AssocClass = " & _
"Win32_DiskDriveToDiskPartition")
For Each objPartition In colPartitions
WScript.Echo ("Disk Partition: " & objPartition.DeviceID)
Set colLogicalDisks = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_DiskPartition.DeviceID=""" & _
objPartition.DeviceID & """} " & _
"WHERE AssocClass = Win32_LogicalDiskToPartition")
For Each objLogicalDisk In colLogicalDisks
WScript.Echo ("Logical Disk: " & objLogicalDisk.DeviceID)
Next 'objLogicalDisk
Next 'objPartition
Next 'objDrive
WScript.Quit
After doing away with the where clause, I am getting an output that looks like this.
Code:
Physical Disk: WDC WD2500BEKT-75A25T0 -- \\.\PHYSICALDRIVE0
Disk Partition: Disk #0, Partition #0
Disk Partition: Disk #0, Partition #1
Logical Disk: C:
Physical Disk: SD Memory Card -- \\.\PHYSICALDRIVE1
Disk Partition: Disk #1, Partition #0
Logical Disk: F:
Physical Disk: hp v220w USB Device -- \\.\PHYSICALDRIVE2
Disk Partition: Disk #2, Partition #0
Logical Disk: H:
Physical Disk: JetFlash TS4GJFV60 USB Device -- \\.\PHYSICALDRIVE3
Disk Partition: Disk #3, Partition #0
Logical Disk: G:
It is giving me the drive letters and the captions. It is also listing my SD card, but the caption for SD card doesn't contain the letters "USB".
Another thing I observed by reading MSDN is that Win32_PnPEntity is the class that contains PNPDeviceId as a member. That is the reason the query fails to return anything.
Re: SQL to identify USB drive letters
Thanks abhijit!
As I said, "ExecQuery" is new to me, and thanks to your MSDN reference I've lots of interesting reading and research ahead.
I did have a solution similar to your example, but it ran like an arthritic slug on my Development PC, which is why I was trying to return in the "outer" search only those devices with a relevant PNPId.
I'll have a look at the Win32_PnPIdentity class.
Thanks again for taking the time to help me with my problem.
Steve
