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.

Jorge Campos · Accepted Answer · 2014-07-24T17:23:47.987

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.

Well, this code still searches inside the mapped network drives. So that's not exactly what I wanted. — user2629636, Jul 24 '14 at 17:08
on the other hand, I just realized the only missing part was "Description = 'Local Fixed Disk'" on the first query, so it works now, and I approve your answer :) thanks a lot! — user2629636, Jul 24 '14 at 17:19
@user2629636 I wil add an edit to list only local disks. Doing tests here I've found out that network drives has the `MediaType = 0` — Jorge Campos, Jul 24 '14 at 17:23
And about those `,,48` I really don't know and the link about this method `ExecQuery` on the Microsoft is broken. I know that they are optional. — Jorge Campos, Jul 24 '14 at 17:24

score -1 · Answer 2 · answered Jul 24 '14 at 14:14

-1

Try something like this:

SELECT CASE
WHEN RIGHT(RTRIM(FileName), 4) = '.vbs' THEN FileName
END
FROM CIM_DataFile

Make sure to change the paramater names for your specification.

answered Jul 24 '14 at 14:14

Rob

169
1
3
12

SELECT * FROM CIM_DataFile WHERE Extension = 'vbs' This part works just fine, no issues. SELECT Caption FROM Win32_LogicalDisk WHERE Description = 'Local Fixed Disk' This part works too. I don't know why both do not work when embedded. – user2629636 Jul 24 '14 at 14:17
The OP wants the files that is on his `local drives only` as he stated. – Jorge Campos Jul 24 '14 at 14:18

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

2 Answers2