In this exercise, you will create a SQL statement in SQL Server Business Intelligence Development 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 SQL Server Business Intelligence Development Studio

  1. On the computer running SQL Server 2005, click Start, click Run, type devenv, and then click OK. SQL Server Business Intelligence Development Studio opens.

  2. Navigate to Data Connections \ Server Name.SMS_Site Code.dbo \ Views.

  3. Expand Views, scroll down and right-click v_Update_ComplianceStatusAll, and then click Show Results. The SQL statement results display on the active tab.

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

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

  6. 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.

  7. 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.

  8. Click the Views tab, scroll down and click v_R_System, and then click Add.

  9. Scroll down and click v_UpdateInfo, and then click Add.

  10. In the Add Table dialog box, click Close. The three view objects should display in the Diagram pane.

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

  12. In the Diagram pane, highlight the CI_ID column in v_UpdateInfo and then drag it to the CI_ID column in v_Update_ComplianceStatusAll. This creates an inner join between the two views by using the CI_ID column.

  13. In the Diagram pane, select the BulletinID, ArticleID, InfoURL, DatePosted, Title, and Description check boxes in v_UpdateInfo.

    Tip
    The order in which you select the view columns is the order in which they display in the Results pane and in the report. After selecting the view columns, you can change the order easily by dragging and dropping a row to a new location in the Criteria pane.

  14. In the Diagram pane, select the Netbios_Name check box in v_R_System.

  15. In the Diagram pane, select the Status check box in v_Update_ComplianceStatusAll.

  16. 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.

  17. 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.

  18. In the Criteria pane, click the Output column in the Status 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 =2 in the Filter column for the Status row. Status = 2 indicates that the software update is required on the client.

    Tip
    You can see the state message definitions in the v_StateNames view. TopicType = 500 indicates the group of state messages that can be created as a result of the scan for software updates compliance on the client. StateID = 2 is defined as “Update is required”. The v_Update_ComplianceStatusAll view contains all of the TopicType = 500 state messages for the site.

  20. In the Criteria pane, select Descending in the Sort Type column for the DatePosted row.

  21. In the Criteria pane, select 1 in the Sort Order column for the DatePosted row. This specifies that the SQL statement results will be sorted in descending order by the DatePosted date.

    The following SQL statement displays in the SQL pane:

    SELECT v_UpdateInfo.BulletinID, v_UpdateInfo.ArticleID, v_UpdateInfo.InfoURL,

      v_UpdateInfo.DatePosted, v_UpdateInfo.Title, v_UpdateInfo.Description

    FROM v_Update_ComplianceStatusAll INNER JOIN v_UpdateInfo ON

      v_Update_ComplianceStatusAll.CI_ID = v_UpdateInfo.CI_ID

      INNER JOIN v_R_System ON v_Update_ComplianceStatusAll.ResourceID = v_R_System.ResourceID

    WHERE (v_R_System.Netbios_Name0 LIKE 'VistaClient1') AND

      (v_Update_ComplianceStatusAll.Status = 2)

    ORDER BY v_UpdateInfo.DatePosted DESC

  22. Run the query by clicking the Execute icon (the red exclamation point). All required software updates for the specified client should display in the Results pane.

  23. 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 Required updates for a specific computer in the Name text box.

  4. Select Software Updates – A. Compliance 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 = ‘’)

    Important
    ‘’ 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

    Important
    __ 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.

    Tip
    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. Click Next on the Links page.

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

  15. In the Reports pane, find Required updates for a 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.

  16. 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.

  17. Select a computer from the list, and then click Display. The report should display the required software updates applicable for the client computer.

  18. Close the report windows.

See Also