{"id":8751,"date":"2012-07-11T00:01:00","date_gmt":"2012-07-11T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2012\/07\/11\/use-powershell-and-avoid-three-gotchas-with-wql-where-clauses\/"},"modified":"2012-07-11T00:01:00","modified_gmt":"2012-07-11T00:01:00","slug":"use-powershell-and-avoid-three-gotchas-with-wql-where-clauses","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-and-avoid-three-gotchas-with-wql-where-clauses\/","title":{"rendered":"Use PowerShell and Avoid Three Gotcha's with WQL Where Clauses"},"content":{"rendered":"

Summary<\/b>: Microsoft Scripting Guy, Ed Wilson, shows you how to avoid three potential issues when using WQL data queries with Windows PowerShell.<\/p>\n

\"Hey, Hey, Scripting Guy! Yesterday you said there were three basic WQL keywords: Select<\/b>, From<\/b>, and Where<\/b>. You talked about Select<\/b> and From<\/b>. I guess you are going to talk about Where<\/b> today? Am I right?<\/p>\n

—BJ<\/p>\n

\"Hey, Hello BJ,<\/p>\n

Microsoft Scripting Guy, Ed Wilson, is here. The good news is that it is not nearly as hot today as it was yesterday. The bad news is that the humidity is hovering around 80 percent, and that makes it 87 degrees Fahrenheit (30.5 degrees Celsius according to my conversion module<\/a>). Last night, the Scripting Wife, Brian from the Charlotte PowerShell User Group, and I went out to celebrate me turning in the last  chapter of my Windows PowerShell 3.0 Step By Book that will be published by Microsoft Press (preorder from Amazon is available now<\/a>). Brian volunteered to do a peer review for my next book. Pretty exciting stuff.<\/p>\n

Yes, BJ, today we need to talk about the WQL Where<\/b> <\/i>statement.<\/p>\n

Note   <\/b>This is the second blog in a series about using the WMI Query language. You should definitely read the first blog in the series before continuing today. Yesterday, I talked about Three Easy Ways to Use PowerShell and WQL to Get WMI Data<\/a>.<\/p>\n

Using the basic WQL Where <\/i>statement<\/h2>\n

Typing the Where<\/b> <\/i>statement in a WQL query is easy; it is what comes after it that can be a bit of a bother. There are three main gotcha’s when it comes to working with the WQL Where<\/b> <\/i>statement. These problem areas are listed here.<\/p>\n

    \n
  1. You need to know the format of the value to use in creating the filter.<\/li>\n
  2. You need to know the exact property name that you want to use.<\/li>\n
  3. You need to know the correct operator to use.<\/li>\n<\/ol>\n

    With these three concerns, you can surmise that there are three parts in the basic Where<\/b> <\/i>statement (four parts if you add in the Where<\/b> <\/i>keyword). These parts are shown here.<\/p>\n\n\n\n\n
    \n

    Where <\/i><\/b><\/b><\/p>\n<\/td>\n

    		\n

    Property name<\/b><\/p>\n<\/td>\n

    		\n

    Operator<\/b><\/p>\n<\/td>\n

    		\n

    Value<\/b><\/p>\n<\/td>\n<\/tr>\n

    \n

    Where<\/p>\n<\/td>\n

    		\n

    Name<\/p>\n<\/td>\n

    		\n

    =<\/p>\n<\/td>\n

    		\n

    “notepad”<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Gotcha #1: What does that value look like?<\/h2>\n

    The following code provides an example of using the above Where<\/b> <\/i>clause in an actual WQL query:<\/p>\n

    PS C:\\> $name = “Select * from win32_Process where name = ‘notepad'”<\/p>\n

    PS C:\\> Get-WmiObject -Query $name<\/p>\n

    Interestingly enough, this code also illustrates Gotcha <\/i>#3: You need to know the format of the value to use in creating the filter. For example, when the previous command runs, no data returns. Because the first command starts an instance of Notepad, the query should <\/i>return data. But it does not.<\/p>\n

    Note   <\/b>When working with WMI, and especially with WQL, do not forget that you are also using Windows PowerShell. Often if a WQL query does not work as expected, the results are more easily obtainable via “standard” Windows PowerShell techniques. Unless you are returning massive amounts of data from across bandwidth-constrained remote systems, it is rarely cost effective, in terms of productivity, to waste hours of your time trying to perfect a complicated and convoluted WQL query when there is a perfectly acceptable “pure Windows PowerShell” alternative.<\/p>\n

    As a quick check, I retrieve all instances of the entire Win32_Process<\/b> object, and then I use the Select-Object <\/b>cmdlet to choose only the name of the process. Then I use Where-Object<\/b> to find processes with a name that contains the letters “notepad” in the process name. The code is shown here:<\/p>\n

    notepad<\/p>\n

    $name = “Select * from win32_Process where name = ‘notepad'”<\/p>\n

    gwmi -Query $name<\/p>\n

    gwmi win32_process | select name | where { $_.name -match ‘notepad’}<\/p>\n

    The code and resulting output are shown in the image that follows.<\/p>\n

    \"Image<\/a><\/p>\n

    Ah ha! The values that WMI expect for the name property include the file extension. When the query changes to include the .exe extension in the file name, the query works as expected. The revised code is shown here:<\/p>\n

    $name = “Select * from win32_Process where name = ‘notepad.exe'”<\/p>\n

    gwmi -Query $name<\/p>\n

    The query and the associated results are shown in the image that follows.<\/p>\n

    \"Image<\/a><\/p>\n

    Gotcha #2: What’s the property name I need?<\/h2>\n

    One problem with using the Where<\/b> <\/i>statement to filter the number of returned instances, is that you need to know the property name. In addition to just getting a valid property name, you need to get the property name to return the sort of data that you want. For example, the following query does not return any instances:<\/p>\n

    PS C:\\> $service = “Select name from win32_service where status = ‘running'”<\/p>\n

    PS C:\\> get-wmiobject -Query $service<\/p>\n

    PS C:\\><\/p>\n

    It is possible to use the Get-Member<\/b> cmdlet to view the properties of interest. An example of this technique is shown here:<\/p>\n

    PS C:\\> Get-WmiObject win32_service | Get-Member -MemberType property s*<\/p>\n

       TypeName: System.Management.ManagementObject#root\\cimv2\\Win32_Service<\/p>\n

    Name                    MemberType Definition<\/p>\n

    —-                    ———- ———-<\/p>\n

    ServiceSpecificExitCode Property   System.UInt32 ServiceSpecificExitCode {get;set;}<\/p>\n

    ServiceType             Property   System.String ServiceType {get;set;}<\/p>\n

    Started                 Property   System.Boolean Started {get;set;}<\/p>\n

    StartMode               Property   System.String StartMode {get;set;}<\/p>\n

    StartName               Property   System.String StartName {get;set;}<\/p>\n

    State                   Property   System.String State {get;set;}<\/p>\n

    Status                  Property   System.String Status {get;set;}<\/p>\n

    SystemCreationClassName Property   System.String SystemCreationClassName {get;set;}<\/p>\n

    SystemName              Property   System.String SystemName {get;set;}<\/p>\n

    The problem is distinguishing the difference between State<\/b> <\/i>and Status<\/b>. <\/i>Although it is true that looking up the WMI class on MSDN will provide the answer, it is also true that Windows PowerShell can often provide answers faster and easier than opening a browser, navigating to MSDN, looking up the WMI class, finding the article, and reviewing the documentation. Sometimes answers are as easy as piping the results of a WMI query to the Format-List<\/b> cmdlet as shown here:<\/p>\n

    Get-WmiObject win32_service | Format-List *<\/p>\n

    The output from the command is shown here:<\/p>\n

    \"Image<\/a><\/p>\n

    So, the problem with the previous query was choosing the wrong <\/i>property. To find out if a service is running or not, use the State<\/b> property, not the Status<\/b> property. This is an easy mistake to make, which is alleviated when a sampling of the properties and their associated values is made. Here is the revised query:<\/p>\n

    $service = “Select name from win32_service where state = ‘running'”<\/p>\n

    get-wmiobject -Query $service<\/p>\n

    Gotcha #3: What does the operator look like?<\/h2>\n

    This might not be a gotcha if it were not for the fact that Windows PowerShell uses distinct operators. In fact, the WQL operators appear to be much more “standard” than those used by Windows PowerShell. The issue, of course, is that when using Windows PowerShell to do WMI, using WQL mixed operators often enter the equation. Here is a table of WQL comparison operators:<\/p>\n\n\n\n\n\n\n\n\n\n\n
    \n

    Operator<\/strong><\/p>\n<\/td>\n

    		\n

    Meaning<\/strong><\/p>\n<\/td>\n<\/tr>\n

    \n

    =<\/p>\n<\/td>\n

    		\n

    Equal<\/p>\n<\/td>\n<\/tr>\n

    \n

    <> <\/p>\n<\/td>\n

    		\n

    Not equal<\/p>\n<\/td>\n<\/tr>\n

    \n

    < <\/p>\n<\/td>\n

    		\n

    Less than<\/p>\n<\/td>\n<\/tr>\n

    \n

    > <\/p>\n<\/td>\n

    		\n

    Greater than<\/p>\n<\/td>\n<\/tr>\n

    \n

    <=<\/p>\n<\/td>\n

    		\n

    Less than or equal<\/p>\n<\/td>\n<\/tr>\n

    \n

    >=<\/p>\n<\/td>\n

    		\n

    Greater than or equal<\/p>\n<\/td>\n<\/tr>\n

    \n

    !=<\/p>\n<\/td>\n

    		\n

    Not equal<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    There are other operators, but these are used for making comparisons. The following query selects the name and the priority from processes (Win32_Process<\/b>) where the priority of the process is greater than or equal to 11. The Get-WmiObject<\/b> cmdlet runs the query. The code is shown here:<\/p>\n

    $highPriority = “Select name, priority from Win32_Process where priority >= 11”<\/p>\n

    Get-WmiObject -Query $highPriority<\/p>\n

    The query and the output associated with the query are shown in the following image.<\/p>\n

    \"Image<\/a><\/p>\n

    BJ, that is all there is to avoiding three main gotcha’s when using the Where<\/b> clause in WQL. WQL Week will continue tomorrow when I will talk more about using the Where<\/b> clause.<\/p>\n

    I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

    Ed Wilson, Microsoft Scripting Guy<\/b> <\/p>\n","protected":false},"excerpt":{"rendered":"

    Summary: Microsoft Scripting Guy, Ed Wilson, shows you how to avoid three potential issues when using WQL data queries with Windows PowerShell.  Hey, Scripting Guy! Yesterday you said there were three basic WQL keywords: Select, From, and Where. You talked about Select and From. I guess you are going to talk about Where today? Am […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[3,4,45,6],"class_list":["post-8751","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell","tag-wmi"],"acf":[],"blog_post_summary":"

    Summary: Microsoft Scripting Guy, Ed Wilson, shows you how to avoid three potential issues when using WQL data queries with Windows PowerShell.  Hey, Scripting Guy! Yesterday you said there were three basic WQL keywords: Select, From, and Where. You talked about Select and From. I guess you are going to talk about Where today? Am […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/8751","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\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=8751"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/8751\/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=8751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=8751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=8751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}