For a Configuration Manager inventory, start with the site database’s documented views: use v_R_System for discovered server identity and v_RA_System_SystemRoles for associated site system roles, joined by ResourceID. Add DP-specific information from v_ContentDistributionReport only when needed. The exact role labels and available columns can vary by deployment, so inspect the target database schema before treating a query as production-ready.
Choose what the report should answer
“Server details with roles” can mean a compact inventory of which servers have DP, MP or SUP roles, or it can mean operational details such as distribution status or software-update compliance. These are different report scopes: role membership alone does not establish that a role is healthy or that clients are compliant.
As an Amazon Associate I earn from qualifying purchases.
| Report scope | Useful shape | Data to investigate |
|---|---|---|
| Role inventory | One row per server with role columns, or one row per server-role association | v_R_System and v_RA_System_SystemRoles, joined by ResourceID. Microsoft documents the views and join key, but not universal role-value labels for every release. Microsoft’s discovery views documentation. |
| Distribution point details | One row per distribution point, with the documented DP-specific fields available in the deployment | v_ContentDistributionReport, which includes distribution point information such as server host and site code. Validate column names and intended meaning in the target schema. Microsoft’s content management views documentation. |
| SUP membership | Show whether a server has the Software Update Point role | Role association data. A SUP integrates with WSUS to provide updates to Configuration Manager clients; membership is not update compliance. Microsoft’s site system roles planning documentation. |
| Update compliance or deployment status | Operational update data rather than only server-role presence | Software update views such as v_UpdateCIs and v_UpdateInfo, commonly related through CI_ID; live status may require additional status views. Microsoft’s software updates views documentation. |
Build the inventory from documented views
- Confirm the site database and schema. Configuration Manager reports query SQL Server views in the site database. Use the view documentation and the schema in the target deployment rather than assuming private table structures or copying an unverified query. Microsoft documents schema views for finding available views, columns and categories: Schema views in Configuration Manager.
- Establish server identity. Use
v_R_Systemfor discovered system identity. Choose the identity fields needed by readers, and confirm their exact column names in the target schema. - Attach role associations. Join
v_RA_System_SystemRolesto the system identity view onResourceID. Microsoft documents this relationship and join key, but the evidence does not establish a single set of role-value labels that applies to every installed release. - Control row multiplication. A server can have multiple roles. If the role view returns multiple associations for a resource, a direct join can produce multiple rows for that server. Decide deliberately whether to keep one row per server-role association or aggregate/pivot role values into columns. Verify the result against known servers before publishing it.
- Add only the details needed. For DP host and site-code information, investigate
v_ContentDistributionReportand validate its columns. For operating system identity, Microsoft’s report-model example joinsv_R_Systemtov_GS_OPERATING_SYSTEMbyResourceID: Creating custom report models for Configuration Manager.
This is a schema-first approach, not a universal ready-to-run SQL statement. The available documentation establishes the view families and join patterns, but not a release-specific query with validated role labels and output columns for every site.
Free tools Windows power users keep installed
One-click scans. No signup required.
Choose SQL-based or model-based reporting
Configuration Manager supports SQL-based reports, where a SQL statement retrieves the requested data, and model-based reports, where authors select model fields. A SQL-based report gives direct control over joins, aggregation and row shape. A model-based report is appropriate when the required fields are available through a suitable model. Microsoft’s model example uses v_R_System and v_GS_OPERATING_SYSTEM joined on ResourceID.
#1 Best Overall
Reports are managed through the reporting services point, which integrates Configuration Manager with SQL Server Reporting Services. Microsoft documents support at primary sites and the central administration site; verify platform support and reporting services point configuration for the environment before deployment. See Microsoft’s reporting prerequisites and reporting operations and maintenance.
Quick Recap
Best Value
Rank #4
Rank #3
Author, deploy and verify permissions
- Prepare a report folder. Decide where the custom report belongs and confirm access to that folder.
- Create the report. Use the Configuration Manager reporting workflow to create a SQL-based or model-based report. Creating a report requires Site Modify permission and Modify Report permission for the destination folder.
- Validate results as the intended audience. Configuration Manager applies role-based filtering to report output. An administrator’s results may differ from those visible to a less-privileged user, so verify the report using the audience’s access context. See Microsoft’s reporting planning documentation.
- Protect custom work during maintenance. If modifying predefined reports, Microsoft advises backing up custom reports before installing a site update. Prefer a separate custom report when that meets the need.
Match the layout to the audience
- Infrastructure inventory: use one row per server with role columns when readers need to scan the fleet quickly; aggregate associations deliberately.
- Role troubleshooting: use one row per server-role association if readers need to filter or inspect individual roles without pivoting.
- Distribution operations: include DP-specific fields from the content management view family, and keep distribution or content status distinct from simple role presence.
- Update operations: add the relevant software-update and status views only when the question is compliance or deployment state, not merely whether a server hosts a SUP.
- Hardware or OS context: enrich identity with operating-system information only if it improves the inventory’s intended use.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
