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

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

  • Comments 1
  • Likes
Hey, Scripting Guy! Question

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

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note. Beginning with the fact that he now beats his father regularly any time they play basketball. A few days ago Dad sank a three-pointer to win the game, a victory which makes the season tally 2 wins for Dad and 50 or 60 – possibly 70 or 80 – wins for the Scripting Son.

Although we might mention that the Scripting Son is a good 8 inches taller than the Scripting Dad. Not that we’d use that as an excuse, mind you. Just like we wouldn’t use the excuse that poor old Dad has to work – hard – each and every day just to put food on the table, and that it’s difficult to work hard all day and then be expected to play basketball. Or the fact that – well, like we said, we’re not the type to make excuses.

Still, the Scripting Son does seem to foul his father quite a bit, don’t you think? And, on top of that he – well, you get the idea.

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.

Note. So how many career victories did his father have as a pitcher? Let’s put it this way: ever hear the old saying, “Those who can’t do, teach”? Turns out that every now and then old sayings are spot-on, if you know what we mean.

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.

Note. So how many of these AND and OR clauses could you string together? Well, there is a limit, although we don’t remember what it is. (1,024 maybe?) To be perfectly honest, however, the upper limit for the number of conditions you can string together in a single Where clause shouldn’t matter: if you have a Where clause that needs to specify 1,024 conditions you should probably think about a different way to approach the problem. How in the world could you ever hope to maintain a Where clause that stretched over hundreds of lines of code?

And no, please don’t send that in as a Hey, Scripting Guy! question. We have no idea what the answer to that one would be.

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:

Select all the instances of the CIM_DataFile class.

Select a subset of those instances; that is, take only those files where the Drive is equal to C: or D:.

Select a subset of those instances; that is, take only the those files on drive C or D where the Extension is equal to doc.

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:

Select all the instances of the CIM_DataFile class.

Select a subset of those instances; that is, take only those files where the Drive is equal to C:. The net effect here? Take all the files from drive C, regardless of the file extension.

In addition to all the files on drive C, take any files where the Drive is equal to D: and the Extension is equal to doc.

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:

NOT

AND

OR

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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • SELECT * FROM Win32_NTLogEvent Where ((EventCode=517 OR EventCode=529) AND TimeGenerated> 20120523111756.789654+330)