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.

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. 
6. Under ‘Name’ enter in the desired name, e.g. Count of Models
   
7. Select a category, e.g. Device Management
   
8. 
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. 
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. 
4. Click ‘Display’ to display the report
5. The report will list all systems from the database and display a count to the right.