Monday, May 19, 2014

SCCM WQL Query: Dealing with X86 and X64 Systems

This one actually comes by request in the comments section of a previous post. I haven't bothered to post on this subject because I was of the opinion that plenty of other blogs had already covered this scenario. However, you asked so I suppose it doesn't hurt to go over how I address OS Architecture in my WQL queries. As with my WQL queries for Windows 8-2012 I just create a series of basic core queries that I then just "tweak" to fit my needs. Here they are:

Basic "OS Architecture" Queries (returns All Systems for specified architecture):

select distinct 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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC"

select distinct 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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "X64-based PC"

OS Architecture Queries filtered by OS Type (Client/Server):
Note how we look for any systems that are NOT servers to get our list of clients.

select distinct 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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption not like "%Server%"

select distinct 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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Server%"

OS Architecture Queries filtered by OS Version:

select distinct 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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows 7%"

In the above code just specify desired OS Architecture for "SMS_G_System_COMPUTER_SYSTEM.SystemType" and substitute desired OS version for "SMS_G_System_OPERATING_SYSTEM.Caption". For example:

  • Use "%2000%" for Windows 2000
  • Use "%Windows XP%" for Windows XP
  • Use "%Server 2003%" for Windows 2003
  • Use "%Vista%" for Windows Vista
  • Use "%Serverr 2008%" for Windows 2008 Non-R2 (No, the extra "r" is not a typo)
  • Use "%Windows 7%" for Windows 7
  • Use "%Server 2008 R2%" for Windows Server 2008 R2
  • Use "%Windows 8%" for Windows 8
  • Use "%Server 2012%" for Windows 2012

Hope you guys find this useful, and if anyone knows a better method feedback is always welcome.

8 comments:

  1. Good breakdown; much clearer than other articles, many thanks.

    ReplyDelete
  2. Have you noticed that the X86 and X64 totals don't match the total number of workstations when using this for collections?

    ReplyDelete
  3. Yes, that behavior can happen if you have some machines that don't have SCCM clients installed that were pulled in from A/D. These queries rely on information in the database that is supplied by the Hardware inventory. If the hardware inventory has not run, the items being queried for won't exist. In practice I've found it to not really matter because if you have systems without SCCM clients well, anything you'd try to push wouldn't work anyway.

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

    ReplyDelete
    Replies
    1. is there anyway of separating "%Windows 7%" for Windows 7 by OS architecture

      Delete
  5. Sorry I’m not following, I felt like that was clearly explained in the above post. If you can clarify exactly what is it you don’t understand I’ll try to answer your questions.

    ReplyDelete
  6. sorry, novice here. was missing something but I've managed to figure it out. Thanks

    ReplyDelete