Advanced Exercise 2: Create a New Report for Hardware Inventory in Configuration Manager
Updated: June 24, 2015
Applies To: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager
In this exercise, you will create a Configuration Manager report that displays the computer name, site code, the date of the last scan for hardware inventory, and the number of days since the last scan for a specified computer.
Important
Before you begin this exercise, you should review the basic exercises to learn about the report elements, the properties for a report, and the different ways to create the report SQL statement.
Report Requirements
Use the following report requirements to create the new report.
SQL Server Views in the SQL Statement
Use the following Configuration Manager SQL views when creating the report SQL statement:
v_GS_WORKSTATION_STATUS: This SQL view contains the date and time of the last scan for hardware inventory reported by client computers. For more information about this SQL view, see Hardware Inventory Views in Configuration Manager.
v_R_System: This SQL view contains all of the discovered system resources. For more information about this SQL view, see Discovery Views in Configuration Manager.
v_RA_System_SMSInstalledSites: This SQL view contains the installed site for all client computers. For more information about this SQL view, see Discovery Views in Configuration Manager.
JOINS in the SQL Statement
Create the following JOINS in the SQL statement:
v_GS_WORKSTATION_STATUS is joined to v_R_System by using the ResourceID columns.
v_RA_System_SMSInstalledSites is joined to v_R_System by using the ResourceID columns.
Columns in the SQL Statement
Use the following report columns, in the order listed:
Netbios_Name0 AS [Computer Name] from v_R_System
SMS_Installed_Sites0 AS [Site Code] from v_RA_System_SMSInstalledSites
LastHWScan AS [Last HWScan] from v_GS_WORKSTATION_STATUS
DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan]
Note
This report integrates two SQL functions to determine the difference between the last hardware scan date and the current date. To display this column, you can copy the whole line into the SQL statement, or you can copy DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) into the Column column and Days Since Last HWScan into the Alias column in Query Designer.
Sort the data in descending order, using the LastHWScan column.
Filters in the SQL Statement
The report SQL statement does not contain any filters.
Report Prompts
The Configuration Manager report should contain a report prompt for the computer name that will be reported on.
Solution
See Advanced Exercise 2 Solution: Create a New Report for Hardware Inventory in Configuration Manager for detailed information about how to create this report.