SQL Queries Configuration Manager: Reporting Device Make and Model

Three simple SQL queries for pulling make, model, and architecture counts straight out of the Configuration Manager database — handy for a quick hardware inventory summary without building a full report.

Count of all models

SELECT Model0 AS Model, COUNT(*) AS Count
FROM v_GS_COMPUTER_SYSTEM
GROUP BY Model0

Count of all makes/manufacturers

SELECT manufacturer0 AS Make, COUNT(*) AS Count
FROM v_GS_COMPUTER_SYSTEM
GROUP BY manufacturer0

Count of architectures

SELECT systemtype0 AS Architecture, COUNT(*) AS Count
FROM v_GS_COMPUTER_SYSTEM
GROUP BY systemtype0

Run any of these directly in SQL Server Management Studio against your CM database. They’re also a good base for a Power BI or SSRS dashboard — point either tool at the same v_GS_COMPUTER_SYSTEM view and you can turn these grouped counts into a chart with very little extra work.

Resources

• SQL
• query
• WQL
• our SQL & WQL query library
• SCCM Collection Query for Devices with specific software installed and version or below
• SCCM Collection Query for Virtual Machines
• SCCM – Active Directory Security Group Query for User Collection
• SCCM – Create a device collection based on Domain Controller role
• SCCM – Hardware and Client Version SQL Query
• SCCM – SQL Query for all Laptops
• SCCM WQL Query – All Windows 10 Installs
• SCCM SQL Query – Count Computers by Model
• SCCM WQL Query – No ConfigMgr Client Installed
• SCCM WQL Query – Clients without latest version installed
• SCCM WQL Query – All Inactive Clients
• SQL Server Configuration Manager cannot connect to WMI provider fix
• SCCM – 52 Operational Collections
• SCCM Collection Query – Virtual Machines
• SCCM Collection based on Active Directory Organisation Unit (OU) Membership
• Browse our General Tech Accessories picks on Amazon