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.

Important
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.

Report Requirements

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 Views.
  • v_R_System: This SQL view contains all of the discovered system resources. For more information about this SQL view, see Discovery Views.
  • 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.

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:

  1. Netbios_Name0 AS [Computer Name] from v_R_System
  2. SMS_Installed_Sites0 AS [Site Code] from v_RA_System_SMSInstalledSites
  3. LastHWScan AS [Last HWScan] from v_GS_WORKSTATION_STATUS
  4. 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. 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.

Report Link

Create a report link with type Link to Computer Details. The computer name column is 1.

See Also