The following scenario provides an example of how to clone an existing Configuration Manager 2007 report and modify the report's SQL statement in the SQL Server 2005 Query Designer.

Modifying a Clone of an Existing Configuration Manager Report

Taylor is a Configuration Manager administrator who needs to create a report for her manager that shows all of the active clients in the Configuration Manager environment that are running Windows Server 2003. Specifically, she needs the system name, operating system name (Windows Server 2003, Enterprise Edition, Windows Server 2003, Standard Edition, and so forth), service pack level, the site code, the domain, and the last logged-on user.

She looks at the existing Configuration Manager reports and can’t find one that has all the data that she needs, but she does find the Computers with a specific operating system report. This report gives her the computer name, operating system name, and service pack level, as well as additional data that she doesn't need. This report can be used, but it will need to be modified to filter the data by active Configuration Manager clients, as well as to add the site code about where the client is installed, the client domain, and the last logged-on user. Taylor performs the following procedure to clone this report so that she can modify it to fit her needs.

To clone a predefined report

  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 to open the Clone Report dialog box.

  4. In the New report name text box, type Active Clients running Windows Server 2003.

    Note
    The name that you type needs to be different from the cloned report because they are in the same category. Reports can be named the same as others only if they are in different categories. The report name should be as descriptive as possible so that it can be easily found later.

  5. Click Edit SQL statement to open the Report SQL Statement dialog box.

  6. Highlight the SQL statement, and copy the SQL statement to the clipboard.

Taylor prefers to edit her report SQL statements using Query Designer in SQL Server 2005 and performs the following procedure.

To copy the report SQL statement to Query Designer

  1. In Microsoft SQL Server Management Studio, navigate to Databases \ SMS_SiteCode \ Views.

  2. Right-click any view from the list, and then click Open View. The records for the view are displayed in the Results pane.

  3. Click the Query Designer menu item, highlight Pane, and then click Diagram.

  4. Click the Query Designer menu item, highlight Pane, and then click Criteria.

  5. Click the Query Designer menu item, highlight Pane, and then click SQL.

    The view objects are displayed in the Criteria pane, the SQL statement is displayed in the SQL pane, and the results are listed in the Results pane.

  6. Paste the report SQL statement, which was previously copied to the clipboard, into the SQL pane, replacing the existing SQL statement.

    The SQL statement displays as follows:

    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

Taylor notices that a report prompt variable named @os is in the query, and she knows that variables aren’t supported in Query Designer. She doesn't need a variable for the operating system, so she replaces that with a static value for Windows Server 2003. She doesn’t need the Version0, InstallDate0, LastBootUpTime0, WindowsDirectory0, or Description0 columns in her report, so she removes them from the SQL statement, and she changes the caption for the Netbios_Name0, Caption0, and CSDVersion0 columns to better describe them. She performs the following procedure to make these changes.

To modify the report SQL statement in Query Designer

  1. In the SQL pane, replace LIKE @os with LIKE '%Server 2003%'.

  2. In the Diagram pane, uncheck Version0, InstallDate0, LastBootUpTime0, WindowsDirectory0, and Description0 from the OPSYS view object. The columns are removed from the SQL statement.

  3. In the Criteria pane, type Operating System in the Alias column for the Caption0 row.

  4. In the Criteria pane, type Computer Name in the Alias column for the Netbios_Name0 row.

  5. In the Criteria pane, type Service Pack in the Alias column for the CSDVersion0 row.

    The SQL statement displays as follows:

    SELECT SYS.Netbios_Name0 AS [Computer Name],OPSYS.Caption0 AS [Operating System],

      OPSYS.CSDVersion0 AS [Service Pack]

    FROM v_R_System SYS

    JOIN v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID=OPSYS.ResourceID

    WHERE OPSYS.Caption0 LIKE '%Server 2003%'

    ORDER BY [Computer Name]

Taylor now needs to add columns for site, domain name, and user name, and she needs to add a condition to filter for active clients. She knows that this information is most likely added to the Configuration Manager database during the discovery process. In the Configuration Manager 2007 SQL Server Views section of this documentation, Taylor finds information about the v_ResourceAttributesMap view in Discovery Views. She wants to verify that the SQL view contains what she needs, so she displays the v_ResourceAttributesMap view by performing the following procedure.

To display SQL view records in SQL Server Management Studio

  1. In Microsoft SQL Server Management Studio, navigate to Databases \ SMS_SiteCode \ Views.

  2. Find and right-click the v_ResourceAttributeMap view, and then click Open View. A new tab is opened, and the records for the view are displayed in the Results pane.

The list contains discovered attributes, which Taylor can use to see whether what she needs is in the discovery data. She knows from the documentation that systems are ResourceType 5, so she ignores all of the records that are listed as ResourceType 2, 3, or 4 (Unknown System, User Group, and User resources, respectively). From the output, she finds the Active0, User_Domain0, User_Name0, and SMSInstalledSites0 column names that should provide the data she needs. Each of these columns is found in the v_R_System view, except for the SMSInstalledSites0 column, which is an array and is in the v_RA_System_SMSInstalledSites view. This was identified by looking at the ArrayTableName column of the v_ResourceAttributesMap view.

Taylor returns to the tab with her SQL statement, adds the new columns, changes the caption for the columns to better describe them, and creates a condition that will retrieve only active clients. She performs the following procedure to make these changes.

To add columns to the report SQL statement and create a new condition

  1. In the Diagram pane, select Active0, User_Name0, and User_Domain0 from the SYS view object.

  2. Right-click anywhere in the Diagram pane, and click Add Table to open the Add Table dialog box.

  3. Click the Views tab, scroll down and click v_RA_System_SMSInstalledSites, and then click Add. The v_RA_System_SMS_InstalledSites view object is added to the Diagram pane.

  4. In the Add Table dialog box, click Close.

  5. In the Diagram pane, highlight the ResourceID column from the SYS view object and then drag it to the ResourceID column of the v_RA_System_SMS_InstalledSites view object. This sets up an INNER JOIN between the two views by using the ResourceID column.

  6. In the Diagram pane, select SMSInstalledSites0 from the v_RA_System_SMS_InstalledSites view object.

  7. In the Criteria pane, type User Name in the Alias column for the User_Name0 row.

  8. In the Criteria pane, type Domain in the Alias column for the User_Domain0 row.

  9. In the Criteria pane, type Installed Site in the Alias column for the SMSInstalledSites0 row.

  10. In the Criteria pane, clear the check box in the Output column for the Active0 row. The Active0 column will not display in the query results but can be used to filter the data.

  11. In the Criteria pane, type =1 in the Filter column for the Active0 row. This adds a condition to the SQL statement that limits the query results to only active clients.

    The SQL statement displays as follows:

    SELECT SYS.Netbios_Name0 AS [Computer Name],OPSYS.Caption0 AS [Operating System],

    OPSYS.CSDVersion0 AS [Service Pack], SYS.User_Name0 AS [User Name],

    SYS.User_Domain0 AS Domain,

    v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 AS [Installed Site]

    FROM v_R_System AS SYS INNER JOIN

      v_GS_OPERATING_SYSTEM AS OPSYS ON SYS.ResourceID = OPSYS.ResourceID

      INNER JOIN v_RA_System_SMSInstalledSites ON

      SYS.ResourceID = v_RA_System_SMSInstalledSites.ResourceID

    WHERE (OPSYS.Caption0 LIKE '%Server 2003%') AND (SYS.Active0 = 1)

    ORDER BY SYS.[Computer Name]

Taylor runs the query and verifies that the data she needs is displayed in the Results pane of Query Designer. She copies the SQL statement from Query Designer and pastes it into the Configuration Manager report. Because Taylor is no longer using the report prompt, she deletes it. Her report does not have a report link, so Taylor is finished with her custom report. She completes her report by performing following procedure.

To copy the SQL statement and complete the report

  1. Highlight the SQL statement in the SQL pane, and then copy the SQL statement to the clipboard.

  2. Return to the Report SQL Statement dialog box in the Active Clients running Windows Server 2003 report properties. Minimize any windows that are displayed over the dialog box.

  3. Paste the SQL statement into the SQL statement text box, replacing the original SQL statement.

  4. Click Prompts to open the Prompts dialog box.

  5. Highlight the existing prompt in the Prompts section, and then click the Delete icon (red X).

  6. Click OK to exit the Prompts dialog box, and then click Yes to exit the Linked Reports and Dashboards dialog box.

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

  8. Click OK to exit the Active Clients running Windows Server 2003 report properties and complete the report.

See Also