In this exercise, you will create a Configuration Manager 2007 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.
|Before starting this exercise, you should go through the first six exercises to learn about the report elements, the properties for a report, and the different ways to create the report SQL statement. For a possible solution to this exercise, see Advanced Exercise 2 Solution: Create a New Report for Hardware Inventory.|
Use the following report requirements to create the new report. Create the report SQL statement using your preferred method.
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
- v_R_System: This SQL view contains all of the discovered system
resources. For more information about this SQL view, see Discovery
- v_RA_System_SMSInstalledSites: This SQL view contains the
installed site for all client computers. For more information about
this SQL view, see Discovery
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
- SMS_Installed_Sites0 AS [Site Code] from
- LastHWScan AS [Last HWScan] from
- 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.
The Configuration Manager report should contain a report prompt for the computer name. For more information about creating a report prompt for the computer name, see Exercise 5: Create a New Report and New SQL Statement Created in Microsoft SQL Server Management Studio.
Create a report link with type Link to Computer Details. The computer name column is 1.