Advanced exercise 2: Create a new report for hardware inventory in Configuration Manager

2022-10-04

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 Server views when creating the report SQL statement:

v_GS_WORKSTATION_STATUS: This SQL Server view contains the date and time of the last scan for hardware inventory reported by client computers. For more information about this SQL Server view, see Hardware Inventory Views in Configuration Manager.
v_R_System: This SQL Server view contains all of the discovered system resources. For more information about this SQL Server view, see Discovery Views in Configuration Manager.
v_RA_System_SMSInstalledSites: This SQL Server view contains the installed site for all client computers. For more information about this SQL Server 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 Server 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.

Share via