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_ConfigurationItems: This SQL view contains all of the
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:
- ComplianceStateName from
v_CICurrentComplianceStatus
- DisplayName from v_LocalizedCIProperties
- Description from v_LocalizedCIProperties
- Netbios_Name0 from v_R_System (Not
displayed)
- 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.