In this exercise, you will create a Configuration Manager 2007 report that displays the name and description of the configuration baselines that target a specific computer and whether the computer returns compliant or noncompliant for the baseline.

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 1 Solution: Create a New Report for Desired Configuration Management.

Report Requirements

Use the following report requirements to create the new report. Create the report SQL statement by 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_CICurrentComplianceStatus: This SQL view contains compliance information for all configuration items. For more information about this SQL view, see Desired Configuration Management Views.
  • v_LocalizedCIProperties: This SQL view contains the localized titles and descriptions for the configuration items. For more information about this SQL view, see Desired Configuration Management Views.
  • v_R_System: This SQL view contains all of the discovered system resources. For more information about this SQL view, see Discovery Views.

JOINS in the SQL Statement

Create the following JOINS in the SQL statement:

  • v_CICurrentComplianceStatus is joined to v_ConfigurationItems by using the CI_ID column.
  • v_CICurrentComplianceStatus is joined to v_LocalizedCIProperties by using the CI_ID column.
  • v_CICurrentComplianceStatus is joined to v_R_System by using the ResourceID column.

Columns in the SQL Statement

Use the following report columns, in the order listed:

  1. ComplianceStateName from v_CICurrentComplianceStatus
  2. DisplayName from v_LocalizedCIProperties
  3. Description from v_LocalizedCIProperties
  4. Netbios_Name0 from v_R_System (Not displayed)
  5. CIType_ID from v_ConfigurationItems (Not displayed)

Sort the data in ascending order, using the Netbios_Name0 column.

Filters in the SQL Statement

The report SQL statement should meet the following filtering criteria:

  • Select only configuration items that have a Configuration Baseline type. You can filter specifically on configuration baselines by selecting the CIType_ID and adding =2 to the Filter column.
    Note
    The v_CITypes SQL view lists the different configuration item types and the associated CIType_ID for each.
  • Create a filter for the computer name. For example, add LIKE ‘Client1’ to the Filter column for Netbios_Name0. Later, in the report wizard, you will replace the computer name with a report prompt variable.

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

There is no report link for this report.

See Also