In this exercise, you will create a clone of a Configuration Manager report, copy the SQL statement from the cloned report to Query Designer in Microsoft SQL Server Management Studio, modify the SQL statement, and then replace the report SQL statement with the modified SQL statement.

To clone a Configuration Manager report and copy the SQL statement

  1. Open the Configuration Manager console.

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

  3. Find and right-click the Computers with a specific operating system report, and then click Clone.

  4. In the New report name text box, type Computers with a specific operating system (custom), and then click OK. The cloned report is displayed in the Reports pane of the console.

  5. Find and right-click the Computers with a specific operating system (custom) report, and then click Properties. The properties dialog box opens for the report.

  6. Click Edit SQL Statement. The Report SQL Statement dialog box opens.

  7. In the SQL statement section, highlight the entire SQL statement, right-click the highlighted SQL statement, and then click Copy. Keep the Report SQL Statement dialog box open. You will return to this dialog box after modifying the SQL statement in Query Designer.

To copy the SQL statement from a report to Query Designer in SQL Server Management Studio and modify the SQL statement

  1. On the computer running SQL Server 2005, click Start, click Run, type sqlwb, and then click OK. Microsoft SQL Server Management Studio opens.

  2. Click Connect using the default values.

  3. Click the New Query icon on the Standard toolbar.

  4. Select the SMS_SiteCode database from the drop-down list located on the SQL Editor toolbar. By default, master is displayed.

  5. Choose Query from the menu, and then click Design Query in Editor. The Query Designer opens, with the Add Table dialog box open.

  6. Click Close to exit the Add Table dialog box. The Query Designer contains three panes: the Diagram pane is at the top, the Criteria pane is in the center, and the SQL pane is at the bottom.

  7. Delete the contents in the SQL pane. By default, you will delete SELECT and FROM.

  8. Right-click anywhere in the SQL pane, and then click Paste. The SQL statement from the cloned report is pasted in the SQL pane. The following SQL statement should be displayed:

    Select SYS.Netbios_Name0,OPSYS.Caption0 as C054, OPSYS.Version0,

      OPSYS.CSDVersion0, OPSYS.InstallDate0, OPSYS.LastBootUpTime0,

      OPSYS.WindowsDirectory0, OPSYS.Description0

    from v_R_System SYS

    join v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID

    where OPSYS.Caption0 like @os

    order by SYS.Netbios_Name0

    Note
    If the SQL statement is no longer in the clipboard, go back to the Report SQL Statement dialog box and recopy the SQL statement.

  9. Click anywhere in the Diagram or Criteria panes to refresh Query Designer.

    Tip
    The individual panes in Query Designer can be resized to display more or less content. The view objects displayed in the Diagram pane can also be resized.

  10. In the Diagram pane, review the following elements:

    1. Two view objects open for the SYS (alias for v_R_System) and OPSYS (alias for v_GS_OPERATING_SYSTEM) views.

    2. Each view object lists the columns for the view. Columns with a check mark are displayed as part of the SQL statement results.

    3. The line that connects the two view objects indicates a JOIN between the views. Notice that the line connects the ResourceID columns for both views. This means that the views are joined by using the ResourceID column.

  11. In the Criteria pane, review the following elements:

    1. All of the columns that have a check mark in the Diagram pane are also listed on the rows of the Criteria pane.

    2. The Alias column specifies alternative text for a view column. When an alias is specified, the alias will display for the column title in the SQL statement results instead of the column name.

    3. The Table column specifies the source SQL view for the column.

    4. The Output column specifies whether the column should be displayed in the SQL statement results. For example, you can select a column to filter on and not display the column in the SQL statement results.

    5. The Sort Type and Sort Order columns specify how the query results will be sorted. In this SQL statement, the results are sorted by the Netbios_Name0 column and in ascending order.

    6. The Filter column specifies a filter for the SQL statement results. This report contains LIKE @os as the filter.

      Variables, such as @os, are not supported in Query Designer, so when working with a SQL statement from a Configuration Manager report that contains a report prompt variable, you must change the variable to a static value. This is demonstrated in the next step.

  12. In the Criteria pane, replace LIKE @os with LIKE ‘Microsoft%’. The SQL statement in the SQL pane is automatically updated to reflect the change.

  13. Click OK to exit Query Designer. The SQL statement is displayed in the active tab.

  14. Choose Query from the menu, and then click Execute. The SQL statement results are displayed in the Results pane. Notice that the operating system for each computer listed starts with Microsoft.

  15. Highlight the SQL statement in the active tab, choose Query from the menu, and then click Design Query in Editor. The Query Designer opens.

  16. Right-click in an open area of the Diagram pane, and then click Add Table. The Add Table dialog box opens.

  17. Click the Views tab, scroll down and select v_RA_System_IPAddresses from the list of views, and then click Add. The view object displays in the Diagram pane.

    Note
    The v_RA_System_IPAddresses view is an array view that contains the IP addresses for discovered resources. For more information about discovery views, see Discovery Views.

  18. Click Close to exit the Add Table dialog box.

  19. Arrange the SYS and v_RA_System_IPAddresses view objects in the Diagram pane so that the ResourceID columns are visible in each.

  20. Drag the ResourceID column from the v_RA_System_IPAddresses view object to the ResourceID column in the SYS view object. A line with a diamond in the middle should display between the view objects connected by the ResourceID columns. This indicates that an INNER JOIN has been created between the views by using the ResourceID column.

  21. In the v_RA_System_IPAddresses view object, select the IP_Addresses0 column. A check mark should be displayed in the IP_Addresses0 box, and the SQL statement in the SQL pane should be updated.

  22. In the SYS view object, select User_Name0.

  23. In the OPSYS view object, clear Description0, InstallDate0, LastBootUpTime0, and WindowsDirectory0 to remove the columns from the SQL statement.

  24. In the Criteria pane, highlight the User_Name0 row by clicking the box on the far left of the row, and then drag the User_Name0 row between the Netbios_Name0 and Caption0 rows.

    Tip
    The order of the columns is easily modified by dragging rows to the desired locations.

  25. In the Criteria pane, enter the following values in the Alias column:

    1. For Netbios_Name0, type Computer Name.

    2. For User_Name0, type User.

    3. For Caption0, type Operating System.

    4. For Version0, type OS Version.

    5. For CSDVersion0, type Service Pack.

    6. For IP_Addresses0, type IP Address.

  26. Click OK to exit Query Designer. The SQL statement is displayed in the active tab.

  27. Choose Query from the menu, and then click Execute. The SQL statement results are displayed in the Results pane.

    Note
    Because some computers report more than one IP address, they might be listed more than once in the SQL statement results.

  28. In the SQL statement, replace LIKE ‘Microsoft%’ with LIKE @os. This puts the report prompt variable back into the SQL statement.

  29. Highlight the SQL statement, right-click the highlighted SQL statement, and then click Copy.

To paste the new SQL statement into the Configuration Manager report and modify the report properties

  1. Minimize the Microsoft SQL Server Management Studio to return to the Report SQL Statement dialog box for the Computers with a specific operating system (custom) report.

  2. In the SQL statement section, highlight the entire SQL statement and press the DELETE key to delete the existing SQL statement.

  3. Right-click in the SQL statement section, and then click Paste. The SQL statement from Query Designer should display in the SQL statement section.

  4. Click OK to exit the Report SQL Statement dialog box.

  5. Click the Links tab.

  6. In the Link type drop-down list, select Link to Computer Details.

  7. In the Computer name column, type 1. In this report, column 1 of the query results contains the computer name.

  8. Click OK to exit the Computers with a specific operating system (custom) Properties dialog box.

To run the cloned report

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

  2. Find and right-click the Computers with a specific operating system (custom) report, and then click Run. The Report Information window displays as well as a report prompt for the operating system.

  3. Click Values, and then select an operating system from the list.

  4. Click Display to open the report. Review the report columns, and confirm that the modified columns are displayed.

  5. Click the report link icon for one of the client computers. The Computer Details Web page should display for the selected computer.

  6. Close the report windows.

See Also