The Computer Information for a specific computer report is one of the predefined reports in Configuration Manager 2007 and is a good example of a report that combines multiple SQL views to obtain the desired data. To open the report properties, use the following procedure:

To open the Computer Information for a Specific Computer report
  1. In the display pane, right-click the Computer information for a specific computer report, and then click Properties to open the Computer information for a specific computer Report Properties dialog box.

  2. Click Edit SQL Statement to open the Report SQL Statement dialog box. The SQL statement is listed in the SQL statement pane.

  3. Click Prompts to open the Prompts dialog box. The report prompts associated with the query will be listed.

  4. Click Close to close the Prompts dialog box, and then click Close to close the Report SQL Statement dialog box.

  5. Click the Links tab for information about the linked target report.

The SQL statement, report prompt, and report link properties of the Computer information for a specific computer report are listed below:

SQL statement

SELECT SYS.Netbios_Name0, SYS.User_Name0, SYS.User_Domain0,

  SYS.Resource_Domain_OR_Workgr0, OPSYS.Caption0 as C054,

  OPSYS.Version0, MEM.TotalPhysicalMemory0, IPAddr.IP_Addresses0,

  Processor.Manufacturer0, CSYS.Model0, Processor.Name0,


FROM v_R_System SYS LEFT JOIN v_RA_System_IPAddresses IPAddr

ON SYS.ResourceID = IPAddr.ResourceID



LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID =




WHERE SYS.Netbios_Name0 = @variable

ORDER BY SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0

Prompt properties

Name: variable

Prompt text: Computer Name

Allow an empty value: cleared

Provide a SQL statement: checked

Prompt SQL statement:


 if (@__filterwildcard = '')

  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0


  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS

  WHERE SYS.Netbios_Name0 like @__filterwildcard

  ORDER By SYS.Netbios_Name0


Link properties

Link type: Link to Computer Details

Computer name column: 1

This report contains a more complex SQL statement that combines multiple SQL views to obtain the desired data. The query results will list the NetBIOS name, user name, operating system, memory, and so forth with the NetBIOS name used as the variable in the report prompt (WHERE SYS.Netbios_Name0 = @variable). The query retrieves information from six different SQL Server views (v_R_System, v_RA_System_IPAddresses, v_GS_X86_PC_MEMORY, v_GS_COMPUTER_SYSTEM, v_GS_PROCESSOR, and v_GS_OPERATING_SYSTEM) that are joined together by using the ResourceID column from the v_R_System view and where the NetBIOS name in the v_R_System view is equal to the one provided in the report prompt. Finally, the results are ordered first by the Netbios Name column and then the User Domain column.

The report prompt will display Computer Name as the prompt text and has a variable named variable that will be populated by the user. In this report, the variable cannot be an empty value and uses a SQL statement to filter the list of available values that will be displayed when the user clicks the Values button to select the prompt value when running the report. The report prompt SQL statement's purpose is to display all possible results if there is no text in the Computer Name text box and filter the displayed values on any text that is entered in the text box when the Values button is clicked.

This report also has a link type of Link to Computer Details. The NetBIOS name needs to be passed to this link type, which is column 1 in this report. The Link to Computer Details Web page will provide a single report with links to all the reports directly related to a specific computer.