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. SCCM-ReportSP1
  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. SCCM-ReportSP2
  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. SCCM-ReportSP3
  13. Click on the ‘Prompts’ button
  14. Click on the ‘New’ button (Yellow star icon)
  15. SCCM-ReportSP4
  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. SCCM-ReportSP5
  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. SCCM-ReportSP6
  28. Under ‘Report’ click on the ‘Select’ button
  29. From the reports list, select ‘Computer information for a specific computer’
  30. SCCM-ReportSP7
  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. SCCM-ReportSP8
  4. The report will load. You will need to use the ‘Values’ button to select the collection
  5. SCCM-ReportSP9
  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. SCCM-ReportSP10
  9. To get detailed information on each system click on the arrow to the left of the system name.