{"id":55953,"date":"2008-03-21T00:21:00","date_gmt":"2008-03-21T00:21:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/03\/21\/hey-scripting-guy-how-can-i-use-both-and-and-or-operators-in-a-single-wmi-query\/"},"modified":"2008-03-21T00:21:00","modified_gmt":"2008-03-21T00:21:00","slug":"hey-scripting-guy-how-can-i-use-both-and-and-or-operators-in-a-single-wmi-query","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-use-both-and-and-or-operators-in-a-single-wmi-query\/","title":{"rendered":"Hey, Scripting Guy! How Can I Use Both AND and OR Operators in a Single WMI Query?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! I\u2019ve looked through the entire Script Center but I can\u2019t 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’ )<\/B>; unfortunately, though, I can\u2019t figure out the correct syntax. Is there a way to mix AND and OR clauses in a single query?

— MW<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019s baseball career. \u201cThe Hey, Scripting Guy!<\/I> column hardly ever has scripting stuff in it,\u201d you\u2019re thinking, \u201cbut it always<\/I> has something about the Scripting Son and his baseball exploits. I\u2019m worried; is there something wrong with the Scripting Son?\u201d<\/P>\n

Well, needless to say, there are all sorts<\/I> of things wrong with the Scripting Son.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. 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 \u2013 possibly 70 or 80 \u2013 wins for the Scripting Son.<\/P>\n

Although we might mention that the Scripting Son is<\/I> a good 8 inches taller than the Scripting Dad. Not that we\u2019d use that as an excuse, mind you. Just like we wouldn\u2019t use the excuse that poor old Dad has to work \u2013 hard<\/I> \u2013 each and every day just to put food on the table, and that it\u2019s difficult to work hard all day and then be expected to play basketball. Or the fact that \u2013 well, like we said, we\u2019re not the type to make excuses.<\/P>\n

Still, the Scripting Son does<\/I> seem to foul his father quite a bit, don\u2019t you think? And, on top of that he \u2013 well, you get the idea.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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<\/I> fun, sitting inside watching the rain rather than sitting outside in the sunshine watching baseball. But it\u2019s really not as much fun as it sounds.)<\/P>\n

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

Note<\/B>. So how many career victories did his father<\/I> have as a pitcher? Let\u2019s put it this way: ever hear the old saying, \u201cThose who can\u2019t do, teach\u201d? Turns out that every now and then old sayings are spot-on, if you know what we mean.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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\u2019t be anywhere near<\/I> as much fun as a script that can mix AND and OR clauses in a single WMI query:<\/P>

strComputer = “.”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n
Set colFiles = objWMIService.ExecQuery _\n    (“Select * From CIM_DataFile Where (Drive = ‘C:’ OR Drive = ‘D:’) AND Extension = ‘doc'”)<\/p>\n
For Each objFile In colFiles\n    Wscript.Echo objFile.Name\nNext\n<\/PRE>\n
As you can see, there isn\u2019t 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:<\/P>
strComputer = “atl-fs-001”\n<\/PRE>\n
That brings us to our WMI query:<\/P>
Set colFiles = objWMIService.ExecQuery _\n    (“Select * From CIM_DataFile Where (Drive = ‘C:’ OR Drive = ‘D:’) AND Extension = ‘doc'”)\n<\/PRE>\n
As MW noted, in his original script he tried a query similar to this:<\/P>
Set colFiles = objWMIService.ExecQuery _\n    (“Select * From CIM_DataFile Where (Drive = ‘C:’ OR ‘D:’) AND Extension = ‘doc'”)\n<\/PRE>\n
That\u2019s not going to work. In fact, MW most likely got an error message similar to this:<\/P>
C:\\Scripts\\Test.vbs(8,1) (null): 0x80041017\n<\/PRE>\n
So what\u2019s the problem? The problem is that WMI requires you to be very specific when it comes to putting together a Where clause. It\u2019s not sufficient to use syntax like this: Drive = ‘C:’ OR ‘D:’<\/B>. Instead, you need to be more specific; that is, you need to indicate which property can have the value C:and<\/I> which property can have the value D:<\/I>. In other words, you need to do this: <\/P>
(Drive = ‘C:’ OR Drive = ‘D:’)\n<\/PRE>\n
If you wanted to add a few other drives to the list then, again, you need to specifically add the property name (Drive<\/B>) before each allowed value:<\/P>
(Drive = ‘C:’ OR Drive = ‘D:’ OR Drive = ‘E:’ or Drive = ‘F:’)\n<\/PRE>\n
Etc., etc. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. So how many of these AND and OR clauses could<\/I> you string together? Well, there is<\/I> a limit, although we don\u2019t 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\u2019t 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?<\/P>\n
And no, please don\u2019t send that in as a Hey, Scripting Guy!<\/I> question. We have no idea what the answer to that one would be.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
You might have noticed, too that we put our OR clause in parentheses:<\/P>
(“Select * From CIM_DataFile Where (Drive = ‘C:’ OR Drive = ‘D:’) AND Extension = ‘doc'”)\n<\/PRE>\n
Do the parentheses really matter? Yes, they do<\/I> 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:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
Select all the instances of the CIM_DataFile<\/B> class.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Select a subset of those instances; that is, take only those files where the Drive is equal to C:<\/I> or D:<\/I>.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Select a subset of those instances; that is, take only the those files on drive C or D where the Extension<\/B> is equal to doc<\/I>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
That\u2019s good: that\u2019s just exactly what we want<\/I> WMI to do.<\/P>\n
Now, suppose we put the parentheses in a different place. Same query, different set of parentheses:<\/P>
(“Select * From CIM_DataFile Where Drive = ‘C:’ OR (Drive = ‘D:’ AND Extension = ‘doc’)”)\n<\/PRE>\n
WMI will parse this<\/I> query like so:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
Select all the instances of the CIM_DataFile class.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Select a subset of those instances; that is, take only those files where the Drive is equal to C:<\/I>. The net effect here? Take all the files from drive C, regardless of the file extension.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
In addition to all the files on drive C, take any files where the Drive is equal to D:<\/I> and the Extension is equal to doc<\/I>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
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:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
NOT<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
AND<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
OR<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
In our case, that\u2019s going to lead to trouble. Without the parentheses our query would look like this:<\/P>
(“Select * From CIM_DataFile Where Drive = ‘C:’ OR Drive = ‘D:’ AND Extension = ‘doc'”)\n<\/PRE>\n
Because WMI processes the AND operator before it process the OR operator that means this query will be parsed as though we\u2019d written it like this:<\/P>
(“Select * From CIM_DataFile Where Drive = ‘C:’ OR (Drive = ‘D:’ AND Extension = ‘doc’)”)\n<\/PRE>\n
As we\u2019ve already seen, that query isn\u2019t going to return the results we\u2019re looking for. The moral of the story? Always<\/I> 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?<\/P>\n
As for the rest of the script, well, that\u2019s 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<\/B> of each file as we go:<\/P>
For Each objFile In colFiles\n    Wscript.Echo objFile.Name\nNext\n<\/PRE>\n
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.<\/P>\n
That\u2019s 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\u2019t 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\u2019t hurt.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I\u2019ve looked through the entire Script Center but I can\u2019t 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 = […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[3,4,5,6],"class_list":["post-55953","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-vbscript","tag-wmi"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I\u2019ve looked through the entire Script Center but I can\u2019t 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 = […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55953"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55953\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}