Advanced Exercise 2 Solution: Create a New Report for Hardware Inventory in Configuration Manager

To create the Hardware Inventory Information report

1. In the Configuration Manager console, click Monitoring.

2. In the Monitoring workspace, expand Reporting, and then click Reports.

3. In the Home tab, in the Create group, click Create Report.

4. On the Information page of the Create Report Wizard, select SQL-based Report, and then supply the following information:

   - **Name:** Enter **Hardware Inventory Information**.
   
   - **Description:** Enter **Displays the computer name, site code, the date of the last scan for hardware inventory, and the number of days since the last scan.**
   
   - **Server:** This field is automatically entered. Ensure that it matches the name of your Reporting Server.
   
   - **Path:** Click **Browse** to select the folder in which the new report will be stored. For this exercise, select **Hardware - General**.
   

5. To continue, click Next.

6. On the Summary page of the Create Report Wizard, review the information and then click Next.

7. On the Completion page of the Create Report Wizard, review the actions that were taken, and then click Close. Report Builder now opens to allow you to construct the report.

8. Next, you must create the datasets that this report will use to return results for the report. This report uses two datasets. The first of these is used to list computer names that can be selected to use as a basis for the report. The second contains the SQL statements for the report itself.

9. In the Report Data pane, right-click Datasets and then click Add Dataset.

10. On the Query page of the Dataset Properties dialog box, supply a name for the dataset, or use the default name, and then select Use a dataset embedded in my report.

11. In the Data source drop-down list, select the data source you want to use for the report. This is typically automatically generated and will begin with AutoGen_.

12. Select a query type of Text, and then enter the following query in the Query field.

     SELECT DISTINCT SYS.Netbios_Name0 
       from v_R_System SYS WHERE SYS.Client0=1 
       ORDER By SYS.Netbios_Name0
    

13. Click OK to close the Dataset Properties dialog box. The new dataset, named by default DataSet1 is now displayed in the Datasets node of the Report Data pane.

    You have now created the query that the report parameter will use to return the available client names from which you can choose to run the report.

14. Next create the parameter that the report will use to let you select the computer that will be reported on.

    In the Report Data pane, right-click Parameters, and then click Add Parameter.

15. On the General page of the Report Parameter Properties dialog box, change the value in the Prompt field to read Computer name.

16. On the Available Values page of the Report Parameter Properties dialog box, select Get values from a query.

17. Select the following values:

    • Dataset: Choose DataSet1

    • Value field: Choose Netbios_Name0

    • Label field: Choose Netbios_Name0

18. Click OK to close the Report Parameter Properties dialog box. The new parameter ReportParameter1 is displayed in the Parameters node of the Report Data pane.

19. At this point, run the report to check the parameter is working correctly. On the Home tab, in the Views group, click Run.

20. Verify that the Computer name field is shown. When you click this field, you should see all Windows client computers in the drop-down list.

21. On the Home tab, in the Views group, click Design to return to the design view.

22. Now, you must create the main dataset for the report.

    In the Report Data pane, right-click Datasets and then click Add Dataset.

23. On the Query page of the Dataset Properties dialog box, supply a name for the dataset, or use the default name, and then select Use a dataset embedded in my report.

24. In the Data source drop-down list, select the data source you want to use for the report. This is typically automatically generated and will begin with AutoGen_.

25. Select a query type of Text, and then enter the following query in the Query field.

     SELECT v_R_System.Netbios_Name0 AS [Computer Name], 
       v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 AS [Site Code], 
       v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan], 
       DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan] 
     FROM v_GS_WORKSTATION_STATUS INNER JOIN v_R_System ON 
       v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceID 
       INNER JOIN v_RA_System_SMSInstalledSites ON 
       v_R_System.ResourceID = v_RA_System_SMSInstalledSites.ResourceID 
     ORDER BY [Last HWScan] DESC 
    

26. Click OK to close the Dataset Properties dialog box.

27. On the Insert tab, in the Data Regions group, click Table, and then click Table Wizard.

28. On the New Table or Matrix page of the wizard, select Choose an existing dataset in this report or a shared dataset, select DataSet2 and then click Next.

29. On the Arrange fields page of the wizard, drag Computer_Name, Site_Code, Last_HWScan and Days_Since_Last_HWScan from the Available fields pane, to the Values pane.

30. Click Next to see a preview of your report, and then click Next again.

31. On the Choose a style page of the wizard, choose one of the available themes for the report, and then click Finish.

32. On the Home tab, in the Views group, click Run.

33. In the Computer name field, select a computer from the drop-down list, and then click View Report.

34. Verify that the data in the report is as expected.

35. Save and close the report in Report Builder.

36. The new report is now available in the Configuration Manager console.

See Also

Advanced Exercise 2: Create a New Report for Hardware Inventory in Configuration Manager