• Article

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

 

Updated: January 1, 2014

Applies To: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager

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

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.