Hey, Scripting Guy! How Can I Use Both AND and OR Operators in a Single WMI Query?

ScriptingGuy1

Hey, Scripting Guy! I’ve looked through the entire Script Center but I can’t find an example of what I need to do: find all the .DOC files on drives C and D, but using a single WMI query. In other words, I want to write a WMI query similar to this: Where ( Drive = ‘C:’ or ‘D:’ ) and ( Extension = ‘doc’ ); unfortunately, though, I can’t figure out the correct syntax. Is there a way to mix AND and OR clauses in a single query?

— MW

Hey, MW. No doubt a lot of you have been wondering why the Scripting Guy who writes this column has been so silent when it comes to the Scripting Son’s baseball career. “The Hey, Scripting Guy! column hardly ever has scripting stuff in it,” you’re thinking, “but it always has something about the Scripting Son and his baseball exploits. I’m worried; is there something wrong with the Scripting Son?”

Well, needless to say, there are all sorts of things wrong with the Scripting Son.

As it turns out, though, the biggest problem with the Scripting Son has to do with geography: he lives in the Seattle area, where baseball season primarily consists of games being rained out and postponed until a later date. (Yes, that sounds fun, sitting inside watching the rain rather than sitting outside in the sunshine watching baseball. But it’s really not as much fun as it sounds.)

Yesterday afternoon, however, the Scripting Son finally got to take the mound. The result? 5 innings pitched, 2 hits, no walks, no earned runs … and one easy victory for the Juanita Rebels. The Scripting Son didn’t have a single strikeout (something of an anomaly), but, then again, he didn’t need any strikeouts: he kept throwing the fastball in on the batter’s hands and getting easy pop-ups and ground-outs. Just the way his father taught him.

At any rate, the Rebels are now 2-0 for the season, and looking forward to a showdown on Friday against the undefeated Redmond Mustangs (boo, hiss). That should be fun although, in all honesty, it won’t be anywhere near as much fun as a script that can mix AND and OR clauses in a single WMI query:

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFiles = objWMIService.ExecQuery _
    (“Select * From CIM_DataFile Where (Drive = ‘C:’ OR Drive = ‘D:’) AND Extension = ‘doc'”)
For Each objFile In colFiles
    Wscript.Echo objFile.Name
Next

As you can see, there isn’t much to this script. We start out by connecting to the WMI service on the local computer, although we could just as easily run the script against a remote computer. To locate all the .DOC files on drives C and D of a remote computer just assign the name of that computer to the variable strComputer:

strComputer = “atl-fs-001”

That brings us to our WMI query:

Set colFiles = objWMIService.ExecQuery _
    (“Select * From CIM_DataFile Where (Drive = ‘C:’ OR Drive = ‘D:’) AND Extension = ‘doc'”)

As MW noted, in his original script he tried a query similar to this:

Set colFiles = objWMIService.ExecQuery _
    (“Select * From CIM_DataFile Where (Drive = ‘C:’ OR ‘D:’) AND Extension = ‘doc'”)

That’s not going to work. In fact, MW most likely got an error message similar to this:

C:\Scripts\Test.vbs(8,1) (null): 0x80041017

So what’s the problem? The problem is that WMI requires you to be very specific when it comes to putting together a Where clause. It’s not sufficient to use syntax like this: Drive = ‘C:’ OR ‘D:’. Instead, you need to be more specific; that is, you need to indicate which property can have the value C:and which property can have the value D:. In other words, you need to do this:

(Drive = ‘C:’ OR Drive = ‘D:’)

If you wanted to add a few other drives to the list then, again, you need to specifically add the property name (Drive) before each allowed value:

(Drive = ‘C:’ OR Drive = ‘D:’ OR Drive = ‘E:’ or Drive = ‘F:’)

Etc., etc.

You might have noticed, too that we put our OR clause in parentheses:

(“Select * From CIM_DataFile Where (Drive = ‘C:’ OR Drive = ‘D:’) AND Extension = ‘doc'”)

Do the parentheses really matter? Yes, they do matter. When parsing a query WMI will always perform the task in parentheses before it does anything else. In this case, that means WMI will parse the query like this:

That’s good: that’s just exactly what we want WMI to do.

Now, suppose we put the parentheses in a different place. Same query, different set of parentheses:

(“Select * From CIM_DataFile Where Drive = ‘C:’ OR (Drive = ‘D:’ AND Extension = ‘doc’)”)

WMI will parse this query like so:

In other words, by moving the parentheses we end up with all the files from drive C plus all the .DOC files from drive D. Yuck. The reason, of course, is that parentheses definitely make a difference. If you leave out the parentheses altogether WMI will use the following order of precedence when parsing a query:

In our case, that’s going to lead to trouble. Without the parentheses our query would look like this:

(“Select * From CIM_DataFile Where Drive = ‘C:’ OR Drive = ‘D:’ AND Extension = ‘doc'”)

Because WMI processes the AND operator before it process the OR operator that means this query will be parsed as though we’d written it like this:

(“Select * From CIM_DataFile Where Drive = ‘C:’ OR (Drive = ‘D:’ AND Extension = ‘doc’)”)

As we’ve already seen, that query isn’t going to return the results we’re looking for. The moral of the story? Always use parentheses in your Where clauses. In fact, always use parentheses in everything you do, whether it involves scripting or not. Why take any chances, right?

As for the rest of the script, well, that’s a bit anti-climactic; all we do is set up a For Each loop to loop through the collection of files, echoing back the Name of each file as we go:

For Each objFile In colFiles
    Wscript.Echo objFile.Name
Next

But, then again, echoing back the Name of each .DOC file on drives C and D is all we really wanted to do in the first place.

That’s all we have time for today; after all, Juanita is playing Garfield this afternoon, and the Scripting Guy who writes this column needs to pack up his things and head for Seattle. Before you ask, no, the reason the Scripting Guy who writes this column enjoys watching high school baseball isn’t simply because, three times a week for the duration of the season, it lets him sneak out of the office early. But it definitely doesn’t hurt.

ScriptingGuy1

Follow