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:


Posted by at
Labels: , ,

25 comments:

  1. UnknownMarch 26, 2014 at 11:26 AM

    This is fantastic! Thank you!

    ReplyDelete
  2. ZeusABJMarch 27, 2014 at 12:14 PM

    Awesome, glad you liked it!

    ReplyDelete
  3. ThomasApril 22, 2014 at 8:08 AM

    Thumbs up!

    ReplyDelete
  4. ZeusABJApril 22, 2014 at 3:56 PM

    ;)

    ReplyDelete
  5. UnknownApril 29, 2014 at 12:00 PM

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

    ReplyDelete
  6. Sticky FusionMay 13, 2014 at 3:02 PM

    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
  7. ZeusABJMay 19, 2014 at 3:51 PM

    Answer posted here:

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

    ReplyDelete
  8. UnknownMay 23, 2014 at 9:55 AM

    Saved my morning!!!

    ReplyDelete
  9. TobiAugust 20, 2014 at 6:49 AM

    This comment has been removed by the author.

    ReplyDelete
    Replies
    1. ZeusABJAugust 20, 2014 at 10:14 AM

      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
  10. UnknownSeptember 22, 2014 at 8:59 AM

    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
  11. ZeusABJSeptember 29, 2014 at 9:20 AM

    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
  12. FSFebruary 18, 2015 at 7:46 PM

    You are the real MVP!! thank you.

    ReplyDelete
  13. Jurie BothaJuly 6, 2015 at 5:52 AM

    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. ZeusABJJuly 6, 2015 at 9:58 AM

      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
  14. Jörn G.July 6, 2015 at 9:24 AM

    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. ZeusABJJuly 6, 2015 at 10:02 AM

      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. Jörn G.July 7, 2015 at 4:48 AM

      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. ZeusABJJuly 7, 2015 at 9:05 AM

      Glad you figured it out!

      Delete
  15. UnknownSeptember 28, 2015 at 12:59 PM

    Thanks you made my work day!!!

    ReplyDelete
  16. RafeeqMay 10, 2016 at 6:43 AM

    Awesome

    ReplyDelete
  17. loveroseFebruary 14, 2018 at 12:33 AM

    Query is for windows 8 & windows 8.1 any difference or the same one??

    ReplyDelete
  18. ZeusABJFebruary 14, 2018 at 2:04 PM

    They are different, the Windows 8.1/Server 2012 R2 numeric "like" value for "OperatingSystemNameandVersion" would be "%6.3%"

    ReplyDelete

Subscribe to: Post Comments (Atom)