How to pull hardware inventory from Configuration Manager with read-only SQL views

Why the views, and which ones

Microsoft documents SQL Server views as the reporting interface for the site database: the console and the reporting services point query views, and custom reports are built on them. Views prefixed v_GS_ hold the current hardware inventory and views prefixed v_HS_ hold the history, and every one of them joins to the rest on ResourceID. Microsoft calls v_R_System one of the most often used views when joining, because it lists every discovered system with its name, client flag, operating system and user. The v_R_System_Valid view drops obsolete and decommissioned records, which is usually what you want.

The reasons to stay on the views rather than the underlying tables come from practitioners as much as from Microsoft. Recast Software and the standard Configuration Manager reference books both note that table schemas change across updates and that direct table reads can take locks the site does not expect. The views are the stable contract; the tables are an implementation detail.

One query that returns the refresh columns

Microsoft's own sample joins the operating system view to the system view: SELECT SYS.Name0, OS.Caption0, OS.CSDVersion0, OS.ResourceID FROM v_GS_OPERATING_SYSTEM OS INNER JOIN v_R_System SYS ON OS.ResourceID = SYS.ResourceID. The query below extends that pattern to the columns a refresh decision needs, using the same documented views. Column suffixes carry the 0 that Configuration Manager appends to inventory properties, and left joins keep devices that are missing one class of inventory in the result rather than dropping them.

SELECT
  SYS.Name0                    AS DeviceName,
  SYS.ResourceID,
  CSP.Vendor0                  AS Manufacturer,
  CSP.Name0                    AS Model,
  ENC.SerialNumber0            AS SerialNumber,
  ENC.ChassisTypes0            AS ChassisType,
  OS.Caption0                  AS OperatingSystem,
  OS.Version0                  AS OSVersion,
  OS.InstallDate0              AS OSInstallDate,
  BIOS.ReleaseDate0            AS BIOSReleaseDate,
  CPU.Name0                    AS Processor,
  CPU.NumberOfCores0           AS Cores,
  MEM.TotalPhysicalMemory0     AS TotalMemoryKB,
  TPM.SpecVersion0             AS TPMSpecVersion,
  VID.Name0                    AS VideoController,
  VID.AdapterRAM0              AS VideoMemoryBytes,
  WS.LastHWScan                AS LastHardwareScan
FROM v_R_System_Valid SYS
LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT CSP ON CSP.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE ENC         ON ENC.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OS          ON OS.ResourceID  = SYS.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS                 ON BIOS.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_PROCESSOR CPU                ON CPU.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY MEM            ON MEM.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_TPM TPM                      ON TPM.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_VIDEO_CONTROLLER VID         ON VID.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS WS        ON WS.ResourceID  = SYS.ResourceID
WHERE SYS.Client0 = 1
ORDER BY SYS.Name0;

Reading the result for a refresh decision

Devices with more than one processor or video controller row return more than one line; the processor view is one row per socket and the video controller view is one row per adapter, so a workstation with an integrated and a discrete GPU appears twice. Keep the rows, because the second one is the discrete card you are looking for. Memory arrives in kilobytes from the X86 memory view; divide by 1,048,576 to read it in gigabytes.

Not every class is collected by default. Microsoft notes that some hardware inventory classes must be enabled in client settings before the views fill, and battery and TPM are two that are often off. If a column comes back empty for the whole fleet, check Administration, then Client Settings, then Hardware Inventory, and give the site a cycle to collect before you export. The LastHWScan column tells you which devices have not reported recently, and those rows deserve a note rather than a verdict.

Installed software and what is actually used

Workload fit needs two more views. The installed programs list lives in v_Add_Remove_Programs, with 64-bit entries in v_GS_ADD_REMOVE_PROGRAMS_64 and Asset Intelligence categorization in v_GS_INSTALLED_SOFTWARE where that feature is enabled. Software metering, in v_GS_CCM_RECENTLY_USED_APPS, records what has run and when, which is the difference between a CAD workstation and a workstation with CAD installed. A device whose metering shows nothing heavier than a browser and an office suite in 180 days is a right-size or redeploy candidate whatever its model says.

For the installed programs export, join v_Add_Remove_Programs to v_R_System_Valid on ResourceID and keep the display name, version, publisher and install date columns, filtered to the publishers of your engineering, development and design applications. For the metering export, keep the executable name, product name, last used time and launch count from the recently used applications view. Devices with the application installed but a last used time older than 180 days, or no row at all, are the ones to mark. If a column name differs in your site, the v_GroupMap and v_GroupAttributeMap views list every inventory class and attribute the site collects, which is the documented way to discover the schema rather than guessing.

Two more notes from practice. The v_HS_ history views hold earlier scans of the same classes, so a device whose memory total dropped between scans has lost a module and belongs on the reliability list. And a Configuration Manager fleet attached to Intune through tenant attach gets Endpoint analytics for its clients, which adds startup and reliability data to everything above without installing anything new.

Keeping it read-only

Ask your Configuration Manager database administrator to grant a dedicated reporting account SELECT access to the views used by the query. Built-in reports are filtered by the role-based access of the user running them; a direct view query is not, so scope the account to the reporting replica if you run one, and keep it dedicated so it can be audited and revoked. Export the result to CSV and the hardware half of a refresh decision is done.

From here the steps are the same as for an Intune fleet: score each device on age against class life, workload fit, performance, reliability and Windows 11 readiness, and give it one of the four verdicts. Our sample report shows the output on a fictional fleet. If you would rather we run it, the initial report takes the CSV from this query plus your installed software and metering exports through a secure link.

Common questions