SCCM SQL Query – Count Computers by Model

SQL Query: Count Computers by Hardware Model

If you want a quick breakdown of how many of each hardware model you have across your Configuration Manager estate — useful for hardware refresh planning or just getting a sense of what you’re managing — this SQL query against the Configuration Manager database gives you a model-by-model count:

SELECT CS.Model0, COUNT(*) AS DeviceCount
FROM v_GS_COMPUTER_SYSTEM CS
INNER JOIN v_FullCollectionMembership FCM ON CS.ResourceID = FCM.ResourceID
WHERE FCM.CollectionID = 'SMS00001'
GROUP BY CS.Model0
ORDER BY DeviceCount DESC

This scopes the count to the “All Systems” collection (SMS00001) by default — swap in a different Collection ID if you want to scope it to a specific business unit, site, or device collection instead. Sorting by DeviceCount DESC puts your most common models at the top, which is usually what you want when planning bulk driver packages or refresh budgets around your most numerous hardware. Build this as an SSRS report if you want it available on demand without going back into SQL Server Management Studio each time, or run it directly against the database if you just need a one-off count.

Still Current on Configuration Manager 2603 (Updated for 2026)

The v_GS_COMPUTER_SYSTEM view and its Model0 column are unchanged on Configuration Manager 2603, the current release as of August 2026, so this query needs no rewriting to keep working. If you want a fuller breakdown, pair Model0 with Manufacturer0 in the GROUP BY clause — the same technique used in the manufacturer-count version of this query — so you can tell apart, say, a “Latitude 5440” model number that could technically exist across more than one OEM naming scheme. Worth also cross-checking results against v_R_System.AgentTime or the client’s last hardware inventory date if a model count looks suspiciously low — a stale inventory scan will under-report a model that’s actually widespread but just hasn’t phoned home recently.

Resources

• query
• WQL
• our SQL & WQL query library