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:
This is fantastic! Thank you!ReplyDelete
Awesome, glad you liked it!ReplyDelete
Great scripts! Just an FYI, your second and third examples are exactly the same.ReplyDelete
You are correct sir... fixed!Delete
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
Answer posted here:ReplyDelete
Saved my morning!!!ReplyDelete
Glad to help!Delete
This comment has been removed by the author.ReplyDelete
I was about to respond and then you removed your comment. I assume you were able to figure out your issue on your own?
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 courseReplyDelete
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
You are the real MVP!! thank you.ReplyDelete
Or you could simply specify the following:ReplyDelete
Criterion Type: Simple Value
Where: ---> Attribute Class: Operating System
Operator: Is Like
Value: %Windows 8.1%
Much easier and much more convenient.
Value can obviously be the following as well:
You get the idea :P
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
Hy, I've just used this query to exclude servers from client collection (of course I used "is NOT like" "%Server% as value).ReplyDelete
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.
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
Thanks for the feedback.Delete
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. :-)
Glad you figured it out!Delete
Thanks you made my work day!!!ReplyDelete
Query is for windows 8 & windows 8.1 any difference or the same one??ReplyDelete
They are different, the Windows 8.1/Server 2012 R2 numeric "like" value for "OperatingSystemNameandVersion" would be "%6.3%"ReplyDelete