In this exercise, you will modify a Configuration Manager report and then run the modified report. The report SQL statement will be modified in the report properties to remove an existing report column and add two new report columns.

To modify a Configuration Manager report
  1. Open the Configuration Manager console.

  2. Navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reports.

  3. Right-click Processor information for a specific computer, and then click Properties. The Processor information for a specific computer Properties dialog box opens.

  4. Click Edit SQL Statement. The Report SQL Statement dialog box opens and the following SQL statement is displayed in the SQL statement section:

    SELECT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0, Processor.DeviceID0

    FROM v_R_System SYS

    JOIN v_GS_PROCESSOR Processor on SYS.ResourceID=Processor.ResourceID

    WHERE SYS.Netbios_Name0 LIKE @variable

    ORDER BY SYS.Netbios_Name0

    You can see from the SELECT statement that the Netbios_Name0, Name0, MaxClockSpeed0, and DeviceID0 report columns will be displayed when running this report.

  5. Change the SQL statement to remove the DeviceID0 column from the report. After making this change, the SELECT statement should look like the following:

    SELECT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0

  6. Add the Manufacturer0 and Version0 columns to the SQL statement from the v_GS_PC_BIOS view as follows:

    1. In the SQL statement section of the dialog box, add a comma after Processor.MaxClockSpeed0 and leave the cursor focus in the space after the comma.

    2. In the Views section, scroll down and select v_GS_PC_BIOS.

    3. In the Columns section, select the Manufacturer0 column, and then click Insert.

    4. In the SQL statement section, add a comma after Manufacturer0 and leave the cursor focus in the space after the comma.

    5. In the Columns section, select Version0, and then click Insert. The new columns are added to the SQL statement.

    6. In the SQL statement section, qualify the column names by adding the v_GS_PC_BIOS view to the column names. Using qualifiers specifies which view the column is from and avoids any confusion when joining views with the same column names. When added, the SELECT statement should look like the following:

      SELECT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0, v_GS_PC_BIOS.Manufacturer0, v_GS_PC_BIOS.Version0

      Note
      You can type the qualifier into the SQL statement or put the cursor focus just before the column name, double-click v_GS_PC_BIOS in the Views section, and then type a period between the view and column name.
    7. In the SQL statement section, add a line to the SQL statement just before the WHERE clause, and then type the following in the empty line to join the v_R_System view with the v_GS_PC_BIOS view:

      JOIN v_GS_PC_BIOS ON SYS.ResourceID = v_GS_PC_BIOS.ResourceID

      Note
      The JOIN clause combines records from the two SQL views. For more information about the JOIN clause, see SQL Statement Reference.
    8. In the SQL statement section, verify that the SQL statement looks like the following:

      SELECT SYS.Netbios_Name0, Processor.Name0, Processor.MaxClockSpeed0,

       v_GS_PC_BIOS.Manufacturer0, v_GS_PC_BIOS.Version0

      FROM v_R_System SYS JOIN v_GS_PROCESSOR Processor on

       SYS.ResourceID=Processor.ResourceID

       JOIN v_GS_PC_BIOS ON SYS.ResourceID = BIOS.ResourceID

      WHERE SYS.Netbios_Name0 LIKE @variable

      ORDER BY SYS.Netbios_Name0

      Note
      In this SQL statement, the SYS alias is used for the v_R_System SQL view, which is defined as part of the FROM clause. When defining the alias, the alias can be used to qualify the columns throughout the SQL statement instead of using the SQL view name. The v_GS_PC_BIOS SQL view does not have an alias defined, and therefore the view name must be used to qualify the columns in this SQL statement.
  7. Click OK, and then click OK to exit the properties for the report.

To run the modified Configuration Manager report
  1. Navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reports.

  2. Right-click Processor information for a specific computer, and then click Run.

  3. Click Values, select a computer from the list, and then click Display. The modified report displays with the Netbios_Name0, Name0, MaxClockSpeed0, Manufacturer0, and Version0 columns.

    Note
    If you receive an error when trying to run the report, go back to the report SQL statement and make sure it matches the one listed above.
  4. Close the report windows.

See Also