How to Build a Custom SCCM Report with SQL

SCCM ships with hundreds of built-in reports, but sooner or later you need something specific. A report combining device compliance, software inventory, and last-checkin time in one view that no built-in report quite covers. Building it yourself in SQL Server Reporting Services (SSRS) is genuinely approachable once you know where the relevant data lives in the ConfigMgr database.

Finding the right tables and views

The ConfigMgr database exposes a large set of SQL views specifically designed for reporting (prefixed v_) rather than the raw underlying tables, always query these views, not the base tables directly, since the views handle a lot of the joins and formatting for you and are far more stable across SCCM versions. v_R_System holds core device records, v_GS_COMPUTER_SYSTEM holds hardware inventory, and v_ClientCollectionMembers maps devices to collections.

A simple starting query

A basic device inventory query combining name, last logon, and OS version looks like:

SELECT Name0, User_Name0, Operating_System_Name_and0, Client_Version0 FROM v_R_System WHERE Active0 = 1

Build up from something this simple rather than starting with a complex multi-join query. Get a basic version working and returning correct data before adding compliance status, software inventory, or collection membership joins on top.

Building the report in SSRS

Use the SQL Server Report Builder (a free, standalone tool) pointed at the SCCM SQL Server’s reporting database, rather than editing reports directly in the SCCM console’s limited built-in editor. Once built, upload it into the SCCM console under Monitoring > Reporting, and it appears alongside the built-in reports with the same filtering and export options.

Don’t query the live production database directly for anything heavy

For reports that will run frequently or query large datasets, use the dedicated SQL Server Reporting Point database replica rather than hitting the primary site database directly. This protects SCCM’s own performance from being impacted by reporting queries, which is exactly the scenario the reporting point replica exists to prevent.

For official guidance, see Microsoft’s Configuration Manager documentation.