ConfigMgr SQL Query: Retrieving Site Roles from the Database

Configuration Manager stores its site system role assignments in the site database, and you can query them directly with SQL rather than clicking through the console’s Site Configuration node one role at a time.

The Query

select SiteCode, RoleName, ServerName from SysResList

Run this against your ConfigMgr site database (typically named CM_<sitecode>) using SQL Server Management Studio, or any SQL client account with at least read access to that database.

What You Get

Each row returned maps a site system role (for example, Management Point, Distribution Point, Software Update Point, or SQL Server) to the server it’s installed on and the site code it belongs to — handy for a quick inventory across a multi-site hierarchy without opening the console at all, or for feeding into your own documentation/reporting. This is one of ConfigMgr’s long-standing internal SQL views, and it has stayed present and unchanged through every Current Branch release we’ve checked it against — Microsoft hasn’t published any deprecation or renaming notice for it. As with any query against the internal schema (rather than a documented WMI class), it’s still worth a quick test run against your own site database before relying on it in an automated script, since Microsoft doesn’t formally support the internal SQL views the way it supports WMI/SMS Provider classes.

Filtering to a Specific Role

If you only care about one role type — say, every Distribution Point in the hierarchy — add a WHERE clause:

select SiteCode, RoleName, ServerName from SysResList where RoleName = 'SMS Distribution Point'

Common RoleName values include SMS Distribution Point, SMS Management Point, SMS Software Update Point, SMS SQL Server, and SMS Provider. Running the unfiltered query first is the easiest way to see the exact role-name strings in use on your own hierarchy, since a couple of these have changed capitalisation or wording across older versus newer builds.

If You Don’t Have Direct SQL Access

If you don’t have direct SQL access to the site database, your ConfigMgr administrators can grant read-only access, or you can get the same information (just less conveniently for bulk export) from the console under Administration > Site Configuration > Servers and Site System Roles.

Resources

• query
• WQL
• our SQL & WQL query library
• Browse our Windows Admin Toolkit picks on Amazon