Evaluation of the Computer Information for a Specific Computer Report in Configuration Manager

To examine the Computer Information for a Specific Computer report

1. In the Configuration Manager console, click Monitoring.

2. In the Monitoring workspace, click Reporting, and then click Reports.

3. From the list of displayed reports, click Computer information for a specific computer and then, in the Home tab, in the Report Group group, click Edit.

4. After Report Builder opens, in the Report Data pane, expand Datasets and then double-click DataSet0 to examine the SQL statement for the report which appears as follows:

     SELECT distinct SYS.Netbios_Name0, SYS.User_Name0, SYS.User_Domain0,  SYS.Resource_Domain_OR_Workgr0,
                 OPSYS.Caption0 as C054, OPSYS.Version0,
                 MEM.TotalPhysicalMemory0,
                 STUFF((SELECT (N','+IPAddr.IP_Addresses0) AS [text()]
                 FROM fn_rbac_RA_System_IPAddresses(@UserSIDs)  IPAddr
                 WHERE SYS.ResourceID = IPAddr.ResourceID for xml path(N''))
                 ,1,1,N'') as IP_Addresses0, -- if there are multiple IP address then combine them together
                 Processor.Manufacturer0,
                 CSYS.Model0, Processor.Name0, Processor.MaxClockSpeed0, SYS.Is_AOAC_Capable0
                 FROM fn_rbac_R_System(@UserSIDs)  SYS
                 LEFT JOIN  fn_rbac_GS_X86_PC_MEMORY(@UserSIDs)  MEM on SYS.ResourceID = MEM.ResourceID
                 LEFT JOIN  fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs)  CSYS on SYS.ResourceID = CSYS.ResourceID
                 LEFT JOIN  fn_rbac_GS_PROCESSOR(@UserSIDs)  Processor  on Processor.ResourceID = SYS.ResourceID
                 LEFT JOIN fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs)  OPSYS on SYS.ResourceID=OPSYS.ResourceID
                 WHERE SYS.Netbios_Name0 = @variable
                 ORDER BY SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0
   

5. Close the Dataset Properties dialog box and then double-click DataSetAdminID to examine the SQL statement that presents a list of possible computers for the user to choose. This appears as follows:

     SELECT dbo.fn_rbac_GetAdminIDsfromUserSIDs(@UserTokenSIDs) as userSIDs
   

6. 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 more 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.

7. The report prompt will display Computer Name as the prompt text and has a variable named variable that will be populated by the user. You can examine details about the variables and parameters used by the report in the Parameters node of the Report Data pane.

8. Close Report Builder.

See Also

Evaluation of Existing Configuration Manager Reports