SQL to identify USB drive letters

[Apprentice13]

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.

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

[abhijit]

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.

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.

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.

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.

[Apprentice13]

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