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-
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.
-
Click Edit SQL Statement to open the Report SQL Statement dialog box. The SQL statement is listed in the SQL statement pane.
-
Click Prompts to open the Prompts dialog box. The report prompts associated with the query will be listed.
-
Click Close to close the Prompts dialog box, and then click Close to close the Report SQL Statement dialog box.
-
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,
Processor.MaxClockSpeed0
FROM v_R_System SYS LEFT JOIN v_RA_System_IPAddresses
IPAddr
ON SYS.ResourceID = IPAddr.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID =
MEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS ON SYS.ResourceID =
CSYS.ResourceID
LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID
=
SYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID
=
OPSYS.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:
begin
if (@__filterwildcard = '')
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
ORDER By SYS.Netbios_Name0
end
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.