SQL Query to select files with specific extensions on Local Drives only

Question

I'm looking for a way to get files with specific extensions on the local drives only from WMI database.

SELECT * 
FROM CIM_DataFile 
WHERE Extension = 'vbs' 
      AND Drive = UCASE(SELECT Caption 
                        FROM Win32_LogicalDisk 
                        WHERE Description = 'Local Fixed Disk')

I've came up with this idea above but it does not return any results. Let me know if I'm doing something wrong or if there is an easier way to do this.

Answer 1

So I was trying to find a way to use a JOIN or IN operation on WMI with no success. Then I find this WQL (SQL for WMI) which seems to show the operations allowed on WMI queries.

So whitout this operations the only way I could do what you want was with two queries like this:

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select name from "&_
                           " Win32_LogicalDisk where MediaType <> 0",,48)

For Each objItem in colItems

    Set colItems2 = objWMIService.ExecQuery("Select * " &_ 
                                            "  from CIM_DataFile " &_ 
                                            " where Extension='vbs' " &_
                                            "   and Drive ='" & objItem.Name & "' ",,48)

    For Each objItem2 in colItems2
        Wscript.Echo "Extension: " & objItem2.Extension
        Wscript.Echo "FileName: " & objItem2.FileName
        Wscript.Echo "FileSize: " & objItem2.FileSize
        Wscript.Echo "FileType: " & objItem2.FileType
        Wscript.Echo "Name: " & objItem2.Name
        Wscript.Echo "#################################################"
    Next

Next

When I was working with the WMI I've find out a simple tool which helps a lot and it is just a file HTA named ScriptomaticV2. You can find it here on Microsoft Download Center Scriptomatic 2.0. It is a self-extract file.