Endpoint Insights
How to Create a Collection for Workstations
Topics: Endpoint Insights
This might seem like an odd blog post to write. Why not simply search for all workstations with “Windows 7%” and “Windows 10%” in your query? Once you have this information, then you could create a collection for workstations. Yes, that does work, but here’s the problem: you are causing your SQL Server to do more work than it needs to do. Plus, what happens when you add another workstation operating system (OS) like Windows 8 into the mix? More work!
In this blog post, I explain in more detail why it is bad to create collection queries using the percentage symbol “%” in them. Instead, I show you a very simple query that grabs all workstation OS without being hard on SQL Server or your SCCM site server. After that, all you need to do is create the collection for workstations.
The Problem with the Percentage Symbol “%”
Here I go, getting into the nitty gritty of SQL Server and how indexes work in a database! Most of you know what the “white pages” are, right? It’s that 3-inch thick book that lives near your parent’s home phone. Think of the white pages as a database because that’s exactly what it is, a database.
To help prove my point, SQL Server indexes are similar to the white pages of a telephone directory, I did a quick Bing search for SCCM WQL workstations. I found this link to ConfigMgr WQL Queries on the Microsoft TechNet Gallery site. The very first query listed is, “All Active Windows Workstations (Laptops/Desktops).”
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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (“3”, “4”, “5”, “6”, “7”, “15”, “16”, “8”, “9”, “10”, “11”, “14”)
and SMS_R_System.OperatingSystemNameandVersion like “Microsoft Windows NT Workstation%”
and SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1
and SMS_R_System.Client = 1
and SMS_R_System.Obsolete = 0
and SMS_R_System.Active = 1
This query is looking at the System Enclosure and the attribute Chassis Type. I can write a whole blog post on why this query will NOT find all workstations, but that is for another day. The reason I am showing you this query is for you to take a look at the highlighted section.
Before I go back to my point about the white pages, what does the percentage symbol “%” do in this query? Let’s talk about SQL Server Indexes and Table Scans.
What are SQL Server Indexes?
According to Tutorials Point, “Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.”
What are Table Scans?
Now, let’s look at table scans. MSSQLTips.com says, “An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records. A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query. The reason you would want to find and fix your scans is because they generally require more I/O and also take longer to process.”
What Does the Percentage Symbol “%” Do in the SCCM WQL Query?
In a nutshell, it means that you are scanning for all records that start with “Microsoft Windows NT Workstation.” This is not as efficient as requesting all records that contain, “Workstation,” but more on that later.
Coming back to my white pages analogy, I want to find all rows with “Jones%” and finding the first Jones is fairly easy. Why? For the most part, SQL Server indexes, similar to the white pages in a telephone directory, are ordered last name first. This is not the case, however, in the SCCM database, and for that matter, the OperatingSystemNameandVersion column is not indexed in SQL Server. This means that when you use the percentage symbol %, SCCM must look at every row in the database in order to see if the value, Jones%, is there. In other words, this requires lots of I/O.
How Can We Make this Query Better to Create a Collection for Workstations?
Use the SystemRole column in the System (SMS_G_System_SYSTEM) view. Below is a screenshot of the System Role column in Resource Explorer. Yes, you are seeing it correctly. The column name is one-word in the database and WQL, and two words in Resource Explorer.
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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId
where
SMS_G_System_SYSTEM.SystemRole = “Workstation”
and SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1
and SMS_R_System.Client = 1
and SMS_R_System.Obsolete = 0
and SMS_R_System.Active = 1
Notice that I didn’t change much. I removed the ChassisType filter in the where clause because there are workstations with other chassis types. I also removed the OS name. Instead, the updated query looks for the “Workstation” system role. These simple changes make the query more efficient on your SQL Server and SCCM site server.
With the results, you can now go ahead and create a collection for workstations with the knowledge that you are not putting any undue stress on the SQL Server.
Would you like me to do a blog post about why a query that looks for the System Enclosure attribute ChasisType will not find all workstations? Let me know by contacting me at @GarthMJ.
