Export (0) Print
Expand All

Exercise 5: Create a New Report and New SQL Statement Created in Microsoft SQL Server Management Studio

Applies To: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2

In this exercise, you will create a SQL statement in Microsoft SQL Server Management Studio, copy the SQL statement into a new Configuration Manager report, and configure the report properties.

To create a SQL statement using Query Designer in Microsoft SQL Server Management Studio

  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. Navigate to ServerName \ Databases \ SMS_Sitecode \ Views.

    The SQL views should be listed in the Object Explorer Details tab in the center pane.

  4. Scroll down and right-click v_R_System, and then click Open View.

    A new tab is created, and the records from the v_R_System SQL view are displayed in the Results pane for the tab.

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

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

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

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

  8. In the Criteria pane, right-click the row with the asterisk (*), and then click Delete. The asterisk specifies that all columns for the view should display in the query results.

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

  10. Click the Views tab, scroll down and click v_Collection, and then click Add.

  11. Scroll down and click v_FullCollectionMembership, and then click Add.

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

  13. In the Diagram pane, select the ObjectName check box in v_SecuredObjects.

  14. In the Diagram pane, highlight the ResourceID column in v_R_System and then drag it to the ResourceID column in v_FullCollectionMembership. This creates an inner join between the two views using the ResourceID column.

  15. In the Diagram pane, highlight the CollectionID column in v_FullCollectionMembership and then drag it to the CollectionID column in v_Collection. This creates an inner join between the two views by using the CollectionID column.

  16. In the Diagram pane, select the CollectionID check box in the v_FullCollectionMembership view object.

  17. In the Diagram pane, select the Name, LastRefreshTime, and LastMemberChangeTime check boxes in the v_Collections view object.

  18. In the Criteria pane, click the Output column in the Netbios_Name0 row. This will clear the check box so that the Netbios_Name0 column will not display in the query results.

  19. In the Criteria pane, type Collection ID in the Alias column for the CollectionID row.

  20. In the Criteria pane, type Collection Name in the Alias column for the Name row.

  21. In the Criteria pane, type Last Refresh in the Alias column for the LastRefreshTime row.

  22. In the Criteria pane, type Last Member Change in the Alias column for the LastMemberChangeTime row.

  23. In the Criteria pane, type LIKE ‘ComputerName in the Filter column for the Netbios_Name0 row. For example, type LIKE ‘VistaClient1’, where VistaClient1 is the name of a Configuration Manager client. This filters the query results so that objects only for the VistaClient1 client will be displayed.

    The Netbios_Name0 column from v_R_System is what will be used as the report prompt variable, but because variables are not supported in Query Designer, a static value is added temporarily for testing.

    The following SQL statement displays in the SQL pane:

    SELECT v_FullCollectionMembership.CollectionID AS [Collection ID],

      v_Collection.Name AS [Collection Name], v_Collection.LastRefreshTime AS

      [Last Refresh], v_Collection.LastMemberChangeTime AS [Last Member Change]

    FROM v_R_System INNER JOIN v_FullCollectionMembership ON

      v_R_System.ResourceID = v_FullCollectionMembership.ResourceID INNER JOIN

      v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID

    WHERE (v_R_System.Netbios_Name0 LIKE 'VistaClient1')

  24. Run the query by clicking the Execute icon (the red exclamation point). All collections that contain the specified client are displayed, by collection ID and name, as well as the last collection refresh and last membership change dates.

  25. In the SQL pane, highlight the SQL statement, right-click the highlighted SQL statement, and then click Copy.

To create a new 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 the Reports node, point to New, click Report, and in the New Report Wizard type Collections for a specific computer in the Name text box.

  4. Select Software Distribution - Collections from the Category drop-down list.

  5. Click Edit SQL Statement to open the Report SQL Statement dialog box. A default SQL query statement populates the SQL statement text box.

  6. Paste the SQL statement from the SQL pane in Query Designer into the SQL statement text box, replacing the default SQL statement.

  7. Change the static NetBIOS name value to a variable by replacing LIKE 'ComputerName with @Client. The condition line in the SQL statement is now WHERE (v_R_System.Netbios_Name0 = @Client).

  8. Click Prompts, and click the New Prompt icon (the yellow asterisk) to create a new report prompt.

  9. Type Client in the Name text box (where Client is the name of the variable defined in the SQL statement: @Client), type Computer Name in the Prompt text text box, and select Provide a SQL statement. Adding a SQL statement for the report prompt allows you to click Values when running a report and browse for computers.

  10. In the SQL statement section, type the following SQL statement:

    if (@__filterwildcard = ‘’)

    ImportantImportant
    ‘’ is two apostrophes.

      SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS

      ORDER By SYS.Netbios_Name0

    else

      SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS

      WHERE SYS.Netbios_Name0 like @__filterwildcard

    ImportantImportant
    __ is two underscores.

      ORDER By SYS.Netbios_Name0

    When running the report, the preceding SQL statement is used when clicking Values. If there is no text in the Computer Name text box, all NetBIOS names will be listed; otherwise, the list will be filtered by the text in the Computer Name text box. The @__filterwildcard variable stores the text entered in the text box.

    TipTip
    When creating new reports, go to an existing report that has a report prompt that is similar to what you need and copy the SQL statement from the existing report to the new report. For example, the preceding prompt SQL statement was copied from the Processor information for a specific computer report. There are many existing reports that prompt for computer name, user name, package, collection, site, and so on.

  11. Click OK to close the Prompt Properties dialog box, click OK to close the Prompts dialog box, and then click Next on the General page.

  12. Click Next on the Display page.

  13. On the Links page, select Link to another report in the Link type drop-down list.

  14. Click Select to open the Select Report dialog box.

  15. Scroll down and select All resources in a specific collection, and then click OK. The report prompt for the All resources in a specific collection report automatically displays in the Prompts section.

    noteVisual Basic Note
    When linking to another report, verify that the appropriate column is listed for the report prompt. For this report prompt, column 1 is listed as the column that will provide the value for the report prompt. This is correct because column 1 in the report SQL statement provides the collection ID, which is what the ID report prompt requires.

  16. Click Next on the Links page, click Next on the Security page, and then click Close on the Confirmation page to complete the wizard.

  17. In the Reports pane, find Collections for specific computer, right-click the report, and then click Run. The Report Information window displays as well as a report prompt for the Computer Name.

  18. Click Values without entering a value in the Computer Name text box. If the report prompt SQL statement is working properly, a list of all client computers should display.

  19. Select a computer from the list, and then click Display. The report should display the collections in which the client computer is a member.

  20. Click a report link for one of the collections, and verify that the All resources in a specific collection report opens correctly.

  21. Close the report windows.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft