Modify a Predefined SMS 2003 Report

  • Article

The following scenario provides an example of how to modify a predefined report when it doesn't retrieve all the desired data. The Report SQL Statement dialog box is used to modify the SQL query statement in this scenario.

Modifying an Existing SMS 2003 Report

Lucy is an SMS administrator who needs to create a report to show all SMS clients that have less than a certain amount of memory. 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 SMS 2003 predefined 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 from the Discovery View Reference in Appendix A. Lucy has experience writing SQL queries in SMS 2003 and doesn't need the help of one of the query tools to modify the query in this report. She performs the following procedure to modify the report to meet her needs.

To modify a predefined report

  1. Right-click the Computers with low free disk space (less than specified MB free) report, point to All tasks, and then click Properties toopen the properties page for the report.

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

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

  4. In the Views section of the dialog box, scroll down, and then click the v_RA_System_IPAddresses view. The available columns now appear in the Columns section of the dialog box.

  5. Click IP_Addresses0 in the Columns section of the dialog box, and then click the Insert button.

  6. After the new column is inserted into the SQL statement, add a comma after the new column to complete the insertion.

  7. In the SQL statement section of the dialog box, click just before the WHERE statement. A join needs to be defined here for the v_RA_System_IPAddresses view by using the ResourceID column.

  8. Type INNER 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 query statement now looks like this:

    SELECT SYS.Name,IP_Addresses0, SYS.SiteCode, LDISK.Description0,
   LDISK.DeviceID0, LDISK.VolumeName0, LDISK.FileSystem0,
   LDISK.Size0,LDISK.FreeSpace0

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 WHERE LDISK.DriveType0 = 3 AND LDISK.FreeSpace0 < @variable AND SYS.CollectionID = @CollID ORDER BY SYS.Name

  1. 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 SMS client's IP address in the second column of the query results.

Writing queries in the Report SQL Statement dialog box can be difficult and requires more experience. The tools that come with Microsoft SQL Server make writing SQL statements much easier. An example of using each of these tools is presented in the Clone a Predefined SMS 2003 Report and Create a New SMS 2003 Report scenarios.