ConfigMgr Reporting – List operating systems and service packs – by collection

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

In this example we will be creating a report which will list operating systems and service packs by collections. When the report is ran it will prompt for a collection to be selected.

The report will also be linked to ‘Computer information for a specific computer’ report, allowing you to see detailed information on the systems listed.

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. List operating systems and service packs – by collection
7. Select a category, e.g. Software Updates – A. Compliance
8. 
9. Click on the ‘Edit SQL Statement’ button
10. Under ‘SQL statement’ enter the following code:

11. SELECT 
        rsys.Name0 AS 'Computer Name', Caption0 as 'Operating System',CSDVersion0 as 'Service Pack'
    FROM
        v_R_System rsys
        JOIN v_GS_OPERATING_SYSTEM os ON rsys.ResourceID = os.ResourceID
        JOIN v_FullCollectionMembership fcm on os.ResourceID = fcm.ResourceID
    WHERE
        fcm.CollectionID =  @CollectionID
    ORDER BY
        rsys.Name0

12. 
13. Click on the ‘Prompts’ button
14. Click on the ‘New’ button (Yellow star icon)
15. 
16. Under ‘Name’ enter CollectionID
17. Under ‘Prompt text’ enter Collection
18. Place a tick next to ‘Provide a SQL statement’
19. Click on the ‘Edit SQL Statement’ button
    
20. Under ‘SQL statement’ enter the following code:

21. begin
    if (@__filterwildcard = '')
    select CollectionID, Name from v_Collection order by Name
    else
    select CollectionID, Name from v_Collection
    WHERE CollectionID like @__filterwildcard
    order by Name
    end

22. Click ‘OK’ to save the changes
23. 
24. Click ‘OK’ several times to save the changes and return to the ‘New Report Wizard’
25. Click ‘Next’ then ‘Next’ again
26. Under ‘Link type’ select ‘Link to another report’
27. 
28. Under ‘Report’ click on the ‘Select’ button
29. From the reports list, select ‘Computer information for a specific computer’
30. 
31. Click ‘OK’ to close the window
32. 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. The report will load. You will need to use the ‘Values’ button to select the collection
5. 
6. Click ‘Display’ to display the report
7. The report will list all the systems in the collection, displaying their operating system version and service pack level.
8. 
9. To get detailed information on each system click on the arrow to the left of the system name.