Thursday, July 11, 2013

SCCM WQL Query: Windows 8 and Server 2012 Systems

This weekend we'll be upgrading our SCCM 2012 server to SCCM 2012 SP1. In addition to a number of new features this update also adds support for Windows 8 and Server 2012 systems. To support these new OSes I've created new WQL queries to build collections for these operating systems. While I was doing that I took the opportunity to just clean up all my OS version collection queries. I came up with a system that (I feel) works for just about any OS version-specific deployment scenario you might face. It involves the use of 3 core queries that you just "tweak" to fit your needs. Here they are:

Basic "OS Type" Query (substitute "%Server%" for "%Workstation% for Server OSes):

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%"

OS version "LIKE" query (Further refines the above query by OS version):

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server%" and SMS_R_System.OperatingSystemNameandVersion like "%6.2%"

OS version "NOT LIKE" query (Returns the opposite of the above query):

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Server%" and SMS_R_System.OperatingSystemNameandVersion not like "%6.2%"

To to use these queries to create a collection for any OS version you need simply substitute the desired OS version number in the above code. For example:

  • Use "%5.0%" for Windows 2000
  • Use "%5.1%" for Windows XP
  • Use "%5.2%" for Windows 2003
  • Use "%6.0%" for Windows Vista-2008 Non-R2
  • Use "%6.1%" for Windows 7-2008 R2
  • Use "%6.2%" for Windows 8-2012

Using these three core queries I put together a fantastic set of OS Type and Version collections that (so far) have suited practically all my OS-specific deployment needs:


23 comments:

  1. Great scripts! Just an FYI, your second and third examples are exactly the same.

    ReplyDelete
  2. I have an additional need, to grab x86 or x64 and I can't seem to get that to work. How would this be added in your sql query above?

    ReplyDelete
  3. Answer posted here:

    http://www.acupofit.com/2014/05/sccm-wql-query-dealing-with-x86-and-x64.html

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hi Tobi,

      I was about to respond and then you removed your comment. I assume you were able to figure out your issue on your own?

      Delete
  5. Don't forget that for server 2012 R2 machines you need to use %6.3%, took me a while to remember this as only have R2 servers and with 6.2 nothing was showing up of course

    ReplyDelete
  6. Correct, I just didn't see much point in updating the post as thats just something that would come with time. The build numbers will constantly update as Microsoft releases new versions of Windows. I'm sure Windows 9 will up the numbers too. Thanks for the observation.

    ReplyDelete
  7. You are the real MVP!! thank you.

    ReplyDelete
  8. Or you could simply specify the following:

    Criterion Type: Simple Value
    Where: ---> Attribute Class: Operating System
    Attribute: Name

    Operator: Is Like
    Value: %Windows 8.1%

    Much easier and much more convenient.

    Value can obviously be the following as well:

    %Windows 7%
    %Server 2008%
    etc...

    You get the idea :P

    ReplyDelete
    Replies
    1. Correct but this way has its issues too. Especially when dealign with the non R2 version of Windows 2008 (I have to maintain a few of these in our environment for some legacy apps that will only run on a 32-bit Server OS). The OS attribute for Windows 2008 has a "registered trademark" symbol in in that returns as "Serverr" (note the double 'r', not a typo) instead of "Server". I just find the version numbers to be cleaner and more predictable.

      Delete
  9. Hy, I've just used this query to exclude servers from client collection (of course I used "is NOT like" "%Server% as value).

    In this case, clients which have been imported to SCCM, but not installed, yet, are not appearing int the target collection.

    This seems to be a bug, because at properties window of this clients, there is no value available at "Operating System name and version".

    Can anybody confirm this "bug" and maybe give any solution (without creating a separated Server collection, which could be excluded by "exclude collection").

    Thanks for any feedback.

    ReplyDelete
    Replies
    1. I have not experienced this so I cannot speak to it. I can say that (in my case) I almost never use the "is NOT like" option (even though its listed here). Typically I just create a collection of All Servers and just add that collection as an exclude for any collections where I want servers omitted. You just have to go with what works for you.

      Delete
    2. Thanks for the feedback.
      I already got an answer which may explain the reasons behind this behaviour:

      "...the value of SMS_R_System.OperatingSystemNameandVersion for imported devices is NULL. That's why they don't show..."

      Think this is the reason....even if I would understand, that "is NOT %SEVER%" includes all "NULL" Values...but it seems that it isn't. :-)

      Thank you!

      Delete
    3. Glad you figured it out!

      Delete