System Center Configuration Manager – All Servers and All Workstations Collections with ProductType


When I go out to customers,  I always like to see how the top collections are populated (All Servers or All Workstations).

capture20160506081202286

This is something most customers do immediately after setting up Configuration Manager, build an all desktops collection and all servers to divide clients and set permissions. Even though this is such a common action within ConfigMgr, I feel most admins don’t put enough foresight into these collections.

You are probably asking, Cameron, “Why does it matter?”

Well the answer is simple: Performance and Security

Performance

Performance matters when you have several thousand collections. Writing the most efficient query, especially at the top for your collection tree, is important. You can waste collection evaluation cycles with poorly written queries, which can end up being detrimental in a large scale environment. What makes an efficient query? Well, obviously the variables of the situation depend, but just know:

  • Integer comparison is faster than a string comparison
  • Equals are faster than like

Now this doesn’t fit every scenario but I highly recommend using the Collection Evaluation tool in the Configuration Manager 2012+ toolkit. This will tell you the length of time it takes to run your query in a collection, and the state of all other collection.

capture20160506100805949

Security

Security matters, especially when you need to lock down who can access different collections and manage those collections.  RBAC has been a great addition but still the most common action here is separate workstations from servers. What about Domain Controllers? For customers who use ConfigMgr to manage and patch their Domain Controllers, who has access to deploy to your DCs? Can they deploy packages, applications or compliance settings? Something to keep in mind when managing the most important servers in your entire infrastructure.

Ok enough talk, let’s get this going!

 

Microsoft Windows NT % Server\Workstation %

The most common method I have seen to date going all the way back to SMS 2003 is to use the System Resource – Operating System Name and Version, like below.

capture20160502034219181

 

While this works, the question I pose, is this the best solution? This would give you all the workstation operating systems records, and we can do something very similar to give us all the server clients as well. How do you separate out your domain controllers? I don’t want to write a sub-select query or query other properties having to use the % and is like operator. The simplest, most efficient way I have found is using an attribute called ProductType.

 

Win32_OperatingSystem – ProductType

If you use your favorite search engine to query Win32_OperatingSystem, a MSDN page should come up in the results. On that page it explains the values of ProductType.

ProductType has 3 simple values based on the type of OS SKU.

1 = Workstations

2 = Domain Controller

3 = Member Server

 

In Practice

I build 4 collections ALL of them limited to All Desktop and Server Clients. (Here is where I deleted the paragraph dedicated to telling you why you should never use All Systems, but I decided to spare you.) 🙂 Below is screenshots on how to create the different queries needed.

All Desktop Clients (All Workstation Client)

capture20160502033546788

capture20160502033427520

 

All Non-DC Server Clients

capture20160502033635373

 

All Domain Controller Clients

capture20160502033727828

 

The fourth collection is optional but I believe it can be handy when you need a limiting collection for a global server deployment.

All Server Clients

capture20160502033352164

 

Notice I am using the Include Collection instead of running another query. Two points here:

1) Why write a query that has to be processed twice when you don’t have too?

2) I have found in my lab, the include collection was 1.2 seconds faster in evaluation time then writing a query with multiple values.

Your millage may vary but I am sure you will see similar results.

 

The Caveat

I know it seemed too good to be true right? The biggest thing to remember here is Win32_OperatingSystem is collected via Hardware Inventory. Which means until we have a client on a machine, we do not have this data to populate the collections. Now in my humble opinion, this is OK. There is no point in creating deployments to machines without clients, since they can’t run the deployment. This can also uncover any client health issues if collection counts are lower than expected.

 

Hope you enjoyed and thanks everyone for reading!

Cameron

Comments (5)

  1. cron22 says:

    Nice post here. Very informative.

  2. deployboy says:

    I like it :). Thanks for the article. DO you have any metrics of how fast a collection query ought to be, given a certain size? e.g. if you have 5000 systems and All Workstations takes 6 seconds is that good or bad? I know there are many variables at work but a rough idea would be good.

    1. Cameron Cox says:

      Glad you enjoyed the blog. I don’t have any trending data, as you said many variables like SQL hardware can have a big impact in total evaluation time. Though given your example, 6 seconds sounds a bit high but that depends on the query. To put this in perspective, I have seen collections pull in 200K clients in about 10 seconds. If the query is simple, it should be relatively quick. A good idea to test your queries, if you are concerned, is to build them row by row. Add each element of your criteria 1 at a time, and see if there is a jump in how long the query takes. If you find the first few steps take .5 seconds and the last step takes 5.5 seconds, you might see if there is a better way to write the query or eliminate a portion of the query, like using a limiting collection.

  3. lthomas.85 says:

    Linux/Unix servers also have ProductType set to 3 (Server) so they will appear in your Server collection.

    If you want to stick with this method and you have Linux/Unix systems, I would also add OPERATING_SYSTEM.OSType = 18 to your query

    ie:
    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
    INNER JOIN SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
    WHERE (SMS_G_System_OPERATING_SYSTEM.ProductType = 3 AND SMS_G_System_OPERATING_SYSTEM.OSType = 18)

    and ProductType = 2 for Domain Controllers

Skip to main content