Preston is a Configuration Manager administrator who needs to create a report that displays the file name and version, as well as the start and end time, for files that are part of his software metering rules and run on client computers for specific users. He wants the query results sorted by file name.

Preston first creates a SQL statement that retrieves the data that he needs. To do this, he opens Query Designer from within SQL Server Business Intelligence Development Studio. The specific steps to create SQL statements using Query Designer are outlined in Query Design Tools. To help him determine which views and associated columns to use when creating his SQL statement, Preston also reviews the following topics in this documentation:

• Software Metering Views
  
• Security View Sample Queries
  

He uses the following steps to create his SQL statement using Query Designer.

To create a SQL statement using Query Designer

1. In SQL Server Business Intelligence Development Studio, navigate to Data Connections \ ServerName.SiteDatabaseName.dbo \ Views.

2. Find and right-click the v_MeterData view, and then click Show Results. 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. 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.

7. In the Diagram pane, click the StartTime and EndTime columns in the v_MeterData view object.

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

9. Click the Views tab, scroll down and click v_MeteredFiles, and then click Add.

10. Click v_MeteredUser, and then click Add.

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

12. In the Diagram pane, highlight the FileID column in v_MeterData and then drag it to the MeteredFileID column in v_MeteredFiles. This sets up an INNER JOIN between the two views by using the FileID and MeteredFileID columns.

13. In the Diagram pane, highlight the MeteredUserID column in v_MeterData and then drag it to the MeteredUserID column in v_MeteredUser. This sets up an INNER JOIN between the two views by using the MeteredUserID column.

14. In the Diagram pane, select the FileName check box in v_MeteredFiles.

15. In the Diagram pane, select the UserName check box in v_MeteredUsers.

16. In the Diagram pane, select the UserName check box in v_MeteredUsers.

17. In the Criteria pane, clear the check box in the Output column for the UserName row. The user name will be used as the report prompt but will not be displayed in the query results.

18. In the Criteria pane, highlight the FileName row and drag it above the StartTime row. This makes FileName the first column in the query results.

19. In the Criteria pane, click the Sort Order column and then type 1, or select 1 from the drop-down list, for the FileName row. This sorts the results by the file name in ascending order.

20. In the Criteria pane, click the Filter column in the UserName row and type LIKE Administrator. This filters the query results so that metered files display only for the Administrator account.

    The UserName column from v_MeteredUser is what will be used as the report prompt variable, but because variables are not supported in Query Designer, Preston adds Administrator for the user name to test the query.

    The following SQL statement displays in the SQL pane:

    SELECT v_MeteredFiles.FileName, v_MeterData.StartTime, v_MeterData.EndTime

    FROM v_MeterData INNER JOIN v_MeteredFiles ON

      v_MeterData.FileID = v_MeteredFiles.MeteredFileID INNER JOIN

      v_MeteredUser ON v_MeterData.MeteredUserID = v_MeteredUser.MeteredUserID

    WHERE (v_MeteredUser.UserName LIKE 'Administrator')

    ORDER BY v_MeteredFiles.FileName

Preston runs the SQL statement to make sure it returns the expected results. After verifying the query results, he is now ready to create a new Configuration Manager report by using the SQL statement that he has created. He performs the following steps in the Configuration Manager console to create the new report.

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 for the new report, type Metered Files for a Specific User in the Name text box.

4. Select Software Metering from the Category drop-down list.

5. Type This report lists all metered files for a specific user in the Comment text box.

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

7. Copy the SQL statement from the SQL pane in Query Designer, and paste it into the SQL statement text box, replacing the default SQL statement.

8. Change the static user name value to a variable by replacing LIKE ‘Administrator' with @User. The condition line in the SQL statement is now WHERE (v_MeteredUser.UserName = @User).

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

10. Type User in the Name text box (where User is the name of the variable defined in the SQL statement: @User), type User Name in the Prompt text text box, check Allow an empty value, and then leave the Provide a SQL statement check box clear. Adding a SQL statement for the report prompt allows you to use wildcards when entering the report prompt value.

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, click Next on the Links page, click Next on the Security page, and then click Close on the Confirmation page to complete the wizard.

Preston now has the report that he needs to display Configuration Manager metered files for specific users. He can later choose to add a link to another report and set up security for the report, but for now, this report achieves his primary objective.

Other Resources

Scenarios for Creating Custom Reports