The following scenario provides an example of how to modify an existing Configuration Manager report when it doesn't retrieve all the desired data. The Report SQL Statement dialog box is used to modify the SQL statement in this scenario.
|Writing SQL statements in the Report SQL Statement dialog box can be difficult and requires previous SQL experience. The tools that come with Microsoft SQL Server 2005 make writing SQL statements much easier. For a step-by-step procedure using Query Designer in the SQL Server 2005 Management Studio, see How to Create or Modify a Report SQL Statement Using Query Designer. Real-world scenarios for using Query Designer are presented in Scenario 2: Clone an Existing Configuration Manager Report and Modify the SQL Statement in Query Designer and Scenario 3: Create a Report SQL Statement in Query Designer and Create a New Configuration Manager Report. Additional scenarios are available in Exercises 3 through 6 in Exercises for Creating Custom Reports.|
Modifying an Existing Configuration Manager Report
Lucy is a Configuration Manager administrator who needs to create a report that shows all Configuration Manager clients with less than a certain amount of disk space. Specifically, she needs the system name, system IP address, drive letter, file system, hard drive size, and hard drive free space.
She looks at the existing Configuration Manager reports and finds one called Computers with low free disk space (less than specified MB free) that gives her almost all the information she needs. The only item missing from this report is a column for the system's IP address. Lucy knows that this information is stored in the v_RA_System_IPAddresses view, based on information she obtained from the topic Discovery Views. Lucy has experience writing SQL queries in Configuration Manager and doesn't need the help from one of the query tools to modify the SQL statement in this report. She performs the following procedure to modify the report to meet her needs.
To modify a predefined report
Navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reports. The Configuration Manager reports display in the Report pane.
Right-click Computers with low free disk space (less than specified MB free), and then click Properties to open the properties page for the report.
Click Edit SQL statement to open the Report SQL Statement dialog box.
In the SQL statement section of the dialog box, click between SELECT SYS.Name and SYS.Sitecode at the beginning of the SQL statement. This is where the IP Address column will be inserted into the SQL statement.
In the Views section of the dialog box, scroll down, click the v_RA_System_IPAddresses view, and then click Insert. The view name is inserted into the SQL statement, and the available columns display in the Columns section of the dialog box.
Add a period (.) after the SQL view without adding any spaces.
Click IP_Addresses0 in the Columns section of the dialog box, and then click Insert.
After the new column is inserted into the SQL statement, add a comma (,) after the new column to complete the insertion.
In the SQL statement section of the dialog box, click just before the WHERE clause. The v_RA_System_IPAddresses and v_FullCollectionMembership SQL views need to be joined by using the ResourceID column.
Type JOIN v_RA_System_IPAddresses ON SYS.ResourceID = v_RA_System_IPAddresses.ResourceID, and then press ENTER. This joins the v_RA_System_IPAddresses view with the v_FullCollectionMemembership view by using the ResourceID column.
The modified SQL statement should now look like the following:
SELECT SYS.Name, v_RA_System_IPAddresses.IP_Addresses0,
SYS.SiteCode, LDISK.Description0, LDISK.DeviceID0, LDISK.VolumeName0,
FROM v_FullCollectionMembership SYS
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
JOIN v_RA_System_IPAddresses ON SYS.ResourceID = v_RA_System_IPAddresses.ResourceID
LDISK.DriveType0 = 3 AND
LDISK.FreeSpace0 < @variable AND
SYS.CollectionID = @CollID
ORDER BY SYS.Name
Click OK to close the Report SQL Statement dialog box, and click OK to close the Computers with low free disk space (less than specified MB free) Properties dialog box.
The report has now been modified to list the IP address for the Configuration Manager clients in the second column of the query results.
TasksHow to Create or Modify a Report SQL Statement Using Query Designer
Scenario 2: Clone an Existing Configuration Manager Report and Modify the SQL Statement in Query Designer
Scenario 3: Create a Report SQL Statement in Query Designer and Create a New Configuration Manager Report