The task of managing your Microsoft Endpoint Configuration Manager, which was also called System Center Configuration Manager can be daunting at times.  Even so when you have lots of different Operating Systems and applications to manage.

Some environment can be straight forward for IT Administrators to deal with, but some can take time and also need advance knowledge. My goal while setting up MECM Server was to make the collections auto populate with the endpoints according to the collection and purpose.

Google was my bible for this, but I’d like to thank all the people that wrote these queries. I can’t quote who wrote them as I found these queries all over the internet and I’d like to share them all in one place for you now.

To use these queries, just create/edit your collections, choose type as query, show query and paste whichever you need into it.

Notes: Please bear in mind that these are working in my environment, but might not work for yours.

Table of Contents

Query Collection List

Windows Server 2012 R2

				
					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.3%"

Windows Server 2016

				
					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 "%10.0%"

Windows Server 2019

				
					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 OperatingSystemNameandVersion like '%Server%' and build = '10.0.17763'

Windows Server 2022

				
					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 OperatingSystemNameandVersion like '%Server%' and build = '10.0.20348'

Windows Server Domain Controllers

				
					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_COMPUTER_SYSTEM ON  SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId    WHERE SMS_G_System_COMPUTER_SYSTEM.Roles LIKE "%Domain_Controller%"

All Windows RDS Servers

				
					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_SERVER_FEATURE on SMS_G_System_SERVER_FEATURE.ResourceId = SMS_R_System.ResourceId  where SMS_G_System_SERVER_FEATURE.Name = "Remote Desktop Session Host"

All Exchange Servers

				
					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_SERVICE on    SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId    where SMS_G_System_SERVICE.Name like "MSExchange%"

All Windows Servers

				
					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 OperatingSystemNameandVersion like '%Server%'

Hyper-V Servers

				
					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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.DisplayName like "Hyper-V Virtual Machine Management" and SMS_R_System.OperatingSystemNameandVersion like "%Server%"

All Windows 10 Workstations

				
					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 = "Microsoft Windows NT Workstation 10.0"

Windows 10 Version 21H2

				
					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.BuildNumber = "19044"

Windows 10 Version 22H2

				
					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.BuildNumber = "19045"

Windows 11 Version 22H2

				
					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.BuildNumber = "22621"

Final note

As you can see, these queries are straight forward and you can just import them into your collections and not worry about manually adding your newly deployed server version x into collection x. The queries will do the magic for you.

That’s it for today, and hope you come back for more Information Technology related posts.