ConfigMgr Reporting – List and count all system models

The following guide will demonstrate how to create a new report using Microsoft System Center Configuration Manager R3.

In this example we will be creating a report which lists all system models and displays a count.

SCCM-CountModels1


How to create a report using SCCM R2:

  1. With the Configuration Manager Console open,
  2. Example the ‘Computer Management’ node, then ‘Reporting’ and ‘Reports’
  3. Right click on ‘Reports’ (or your custom reporting folder)
  4. Select ‘New’ then ‘Report’
  5. SCCM-ReportSP1
  6. Under ‘Name’ enter in the desired name, e.g. Count of Models
  7. Select a category, e.g. Device Management
  8. SCCM-ReportSP2
  9. Click on the ‘Edit SQL Statement’ button
  10. Under ‘SQL statement’ enter the following code:
  11. SELECT CSYS.Model0, Count(*) as 'Count' 
    FROM v_GS_COMPUTER_SYSTEM CSYS
    JOIN v_FullCollectionMembership fcm on CSYS.ResourceID=fcm.ResourceID
    WHERE fcm.CollectionID='SMS00004'
    GROUP BY CSYS.Model0
    ORDER BY Count DESC

  12. SCCM-ReportSP3
  13. Click ‘OK’ to return to the ‘New Report Wizard’
  14. Click ‘Next’ then ‘Next’ again
  15. Click ‘Next’, ‘Next’ then ‘Close’

The run the report:

  1. Navigate to where the report was saved/created
  2. Right click on the report and select ‘Run’
  3. SCCM-ReportSP8
  4. Click ‘Display’ to display the report
  5. The report will list all systems from the database and display a count to the right.

SCCM-CountModels1