In this post I will be sharing the queries which can be used for SCCM collection query for Site System Server Roles. We can create different queries for specific system role installed on Windows Server.

How to create Configuration Manager query based collection for Site System Roles

For big infrastructures, there could be heaps of Site System Server installed, hence to look for list of all Servers with specific role is an important factor to consider. Below mentioned queries and the tip shared in the last will be helpful to see those lists.

Login to Configuration Manager console, and navigate to \Assets and Compliance\Overview\Device Collections, right click Device Collections > Create Device Collections, under Membership Rules, we will click on Add Rule > Query Rule. This is where we will be providing the name and Query Statement

Query Rule Properties

Create SCCM Collection for Management Point Role

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 SystemRoles like '%SMS Management Point%'

Create SCCM Collection for Distribution Point Role

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 SystemRoles like '%SMS Distribution Point%'

Create SCCM Collection for Software Update Point Role (SUP Role)

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 SystemRoles like '%SMS Software Update Point%'

Create SCCM Collection for SQL Server Role

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 SystemRoles like '%SMS SQL Server%'

Create SCCM Collection for SQL Server Role

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 SystemRoles like '%SMS SRS Reporting Point%'

Create SCCM Collection for Site Server

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 SystemRoles like '%SMS Site Server%'

Create SCCM Collection for Site System

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 SystemRoles like '%SMS Site System%'

How to find list of Site System Roles in SCCM Console

Finally very trick to share, we can quickly get the list of all Site System Server roles we have in our infrastructure.

Launch Configuration Manager Admin Console, navigate to \Administration\Overview\Site Configuration\Servers and Site System Roles, right click Servers and Site System Roles and select any of the option to display list of servers with specific Site System Server role installed:

Application Catalog web Service point
Application Catalog website point
Asset Intelligence synchronization point
Certificate Registration Point
Cloud management gateway connection point
Distribution point
Endpoint Protection point
Enrollment point
Enrollment proxy point
Fallback status point
Management point
Reporting services point
Data Warehouse service point
Software update point
State migration point
SMS Provider

Management point

Once Management point is selected, we will see list of all servers in right pane under \Administration\Overview\Site Configuration\Servers and Site System Roles\Management point.

Servers and Site System Roles

Same can be done for any other Site System Role.