Hey, Scripting Guy! How can I search for multiple file extensions in a single WQL query?

— AH

Hey, AH. If we understand your question correctly, you want to know if it’s true that the Seattle Seahawks are playing in the Super Bowl this Sunday. Yes, it is true: our very own Seahawks will be taking on … well, some second-place team. This is actually a great time for sports fans to be living in Seattle: the Seahawks are in the Super Bowl; the University of Washington men’s basketball team is ranked 15^th in the country; the UW’s women’s volleyball team won the national championship; the Seattle Sonics –

Um, did we mention that the women’s volleyball team won the national championship?

Actually, upon further review we suspect that maybe you’d like to know how to search for multiple file extensions in a single WQL query. Well, then why didn’t you say so:

strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)

Set colFiles = objWMIService.ExecQuery _
    (“Select * from CIM_Datafile Where Extension = ‘mp3’ OR Extension = ‘wma'”)
For Each objFile in colFiles
    Wscript.Echo “Name: ” & objFile.Name
Next

As you can see, this is about as simple a WMI script as you can get. We start out by connecting to the WMI service on the local computer. We then issue a query that looks for files of two types: those with the file extension mp3 and those with the file extension wma. After retrieving the files we then set up a For Each loop to walk through the collection and echo back the name of each file.

There are two important things to note here. First, the name of the WMI property is Extension (and no, we don’t know why they didn’t call it FileExtension). Second, keep in mind that we are searching for files with the extension mp3; we are not searching for files with the extension .mp3. Make sure you leave the dot out; otherwise your script won’t return any data.

So what’s the secret to searching for multiple file extensions? Well, think about your task: you want a list of files that have the file extension mp3 or that have the file extension wma. When you write the task out like this the key word is the word or; that’s also true when you write out the WQL query. Let’s take a look at the query again:

Set colFiles = objWMIService.ExecQuery _
    (“Select * from CIM_Datafile Where Extension = ‘mp3’ OR Extension = ‘wma'”)

There you have it: Where Extension = ‘mp3’ OR Extension = ‘wma’. The key is remembering that you want file extension mp3 or you want file extension wma. Often-times people make the mistake of thinking, “I want all the files that have the file extension mp3 and I want all the files that have the file extension wma.” As a result, they end up writing a query like this:

Set colFiles = objWMIService.ExecQuery _
    (“Select * from CIM_Datafile Where Extension = ‘mp3’ AND Extension = ‘wma'”)

We don’t have any idea what files you have stored on your computer, but we already know how many files this query will find: 0. Why? Because what we’re saying here is this: find all the files that have the file extension mp3 and that also have the file extension wma. Needless to say, there aren’t going to be many files with two file extensions. Don’t get AND and OR mixed-up. Use OR when you want to expand the search, AND when you want to contract the search.

Note, too that you need to specify the property each time: Extension = ‘mp3’ OR Extension = ‘wma’. If you try to take shortcuts your script won’t work: Extension = ‘mp3’ OR ‘wma’.

Finally, we should point out that you can use the OR operator with things other than file extensions. For example, here’s a query that returns a list of all the services that are either stopped or paused:

Set colFiles = objWMIService.ExecQuery _
    (“Select * from Win32_Service Where State = ‘Stopped’ OR State = ‘Paused'”)

Likewise you can include more than just two OR clauses. Also interested in files with the extension wav? Then just add it to the query:

Set colFiles = objWMIService.ExecQuery _
    (“Select * from CIM_Datafile Where Extension = ‘mp3’ OR Extension = ‘wma’ OR Extension = ‘wav'”)

Oh, and one other thing: Go Seahawks!