Custom SCCM reports

[Http://blog.sina.com.cn/s/blog_55a%e40100nwi2.html]

After SCCM2007 is deployed in the enterprise network environment, in addition to using SCCM to distribute software, patches, and network control, SCCM report functions can be further explored.

After SCCM2007 has opened the SP2 package, we have prepared 373 built-in reports with rich content. However, sometimes you may feel that it is not the report you want. Of course, Microsoft has already provided you with custom functions. If the company's computer account information is not properly maintained, you need to find the HostName and IP Address of the computers used by several users. At this time, IT is quite embarrassing. Of course, we can find out many ways, but today we will use SCCM's custom report function to Show a complete client account information report.

1. Create a report.
   For example, name: client information account
   Category: asset intelligence
   SQL statement:
   Select sys. Netbios_Name0, IPAddr. IP_Addresses0, SYS. User_Domain0, SYS. User_Name0, MEM. TotalPhysicalMemory0, ASSG. SMS_Installed_Sites0, SYS. Client_Version0
   FROM v_R_System as sys join v_RA_System_SMSInstalledSites as ASSG on SYS. ResourceID = ASSG. ResourceID
   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
   Where assg. SMS_Installed_Sites0 LIKE @ variable Order by SYS. Netbios_Name0
   Note: @ variable is a variable.
2. You need to click "prompt (P)" to create the prompt property. We can use this variable to select the "Site" range to be viewed.
   Name: variable
   Prompt text: site code
   SQL statement:
   Begin
   If (@__ filterwildcard = '')
   Select SiteCode, SiteName from v_Site order by SiteCode
   Else
   Select SiteCode, SiteName from v_Site
   WHERE SiteCode like @__ filterwildcard
   Order by SiteCode
   End
3. Click to query the information of each computer and link to the detailed report.
   On the "Link" tab, select "link type" as "link to other reports ".
   Report (R): hardware-General-computer information of a specific computer

   Now, we have created the "client information account" Report and accessed the SCCM Report Application website through IE, such as http: // SCCM-Server/smsreporting_001/
   It is not complicated to explain the SQL query statements here. You only need to refer to SCCM built-in reports to create more powerful reports.

Note:

1. The key to a custom report is to understand the information contained in the graph and combine the SQL query statements for your own purposes.

2. Check whether the information to be queried has been collected by the Agent. If not, modify the Configuration. mof file and the SMS_def.mof file to enable it. For example, SMS_def.mof: (True indicates that information collection is enabled. False indicates that information collection is not enabled)

• Part 1: SCCM report service Part 1.1-Report service Overview

  SCCM 2007 R2 provides two report roles: report point and reporting service Point. The report point is the built-in report service of SCCM, and SCCM 2007 provides 388 built-in reports. You can use SQL statements to create new reports. Note that to use a graph in a report, you must install the office web component. However, the 64-bit operating system does not support Office Web Components.

  In SCCM 2007 R2, SCCM provides a new role: Reporting Service Point. Reporting Service points give SCCM report services the following advantages:

  • Use the standard SQL Server Reporting Service of SQL Server to query the SCCM database.
  • The Model-Based Report creation method and the Microsoft Report Builder Report creation tool are provided, which makes it easier for users who are not familiar with SCCM database tables and views to customize reports.
  • You can use the reporting service of SQL Server to subscribe to reports.
  Part 1.2-Custom reports

  To create a new report based on your needs in SCCM, you must have a detailed understanding of the view of the SCCM database. Microsoft provides a program: Creating Custom Reports By Using Configuration Manager 2007 SQL Views, which provides detailed documents on the SCCM view.

  Part1.2.1-report Point (Reporting Point): Report Creation

  Here we use an example: for example, the IT administrator may ask to query all clients installed with specific software. You need to use three views:

  1. Dbo. v_R_System
  2. Dbo. v_GS_ADD_REMOVE_PROGRAMS
  3. Dbo. v_GS_ADD_REMOVE_PROGRAMS_64

  Create a report and enter the following in the SQL statement of the report:

  select v_R_System.ResourceID, v_R_System.Netbios_Name0 as Name from v_R_System where v_R_System.Client0 =1and(v_R_System.ResourceID in(select v_GS_ADD_REMOVE_PROGRAMS.ResourceID from v_GS_ADD_REMOVE_PROGRAMS                where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 like@ProgramName)or v_R_System.ResourceID in(select v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID from v_GS_ADD_REMOVE_PROGRAMS_64                where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like@ProgramName                ))

  The Filter in the report is used here, that is, the @ ProgramName in the preceding statement. The Filter is Prompt in SCCM. To define the @ ProgramName Prompt above, click the following button on the report creation page:

   

   

   

  Enter the SQL statement of Prompt in the area marked 5:

  Begin

  If (@__ filterwildcard = '')

  (Select distinct DisplayName0 As Name From v_GS_ADD_REMOVE_PROGRAMS) Union (Select distinct DisplayName0 As Name From v_GS_ADD_REMOVE_PROGRAMS_64)

  Else

  (Select distinct DisplayName0 As Name From v_GS_ADD_REMOVE_PROGRAMS where variables like @__ filterwildcard) Union (Select distinct DisplayName0 As Name From v_GS_ADD_REMOVE_PROGRAMS_64 where variables like @__ filterwildcard)

  End

  The following is the result of running the report:

   

• Part1.2.2-Reporting Service Point, report Creation

  Here we use the same example as above. The IT administrator may ask to query all clients installed with specific software.

  First download and install SQL Reporting Service Report Builder 3.0. Download link:

  Http://www.microsoft.com/downloads/details.aspx? FamilyID = 9f783224-9871-4eea-b1d5-f3140a253db6 & displaylang = en

  Note that you can specify the data source during installation. The data source format is:

  Http: // [SQL server machine name]/ReportServer

  If it is a named instance:

  Http: // [SQL Server machine name]/ReportServer _ [Instance Name]

  Use SQL Reporting Service Report Builder 3.0

   

  • Open Report Builder-> click the icon in the upper left corner-> Select Options. In the Report Builder Options window, enter your report server on the settings page. If your database is default instance: Http: // [SQL SERVER Reporting Service Machine name]/ReportServer. For named instance: Http: // [SQL SERVER Reporting Service Machine name]/ReportServer_InstanceName
  • Click Connect in the lower left corner. In this case, Report Builder connects to SQL Server Reporting Service and displays the connection status.
  • Click new> Data Source. A database connection to your database will be established here.

  Next we will create the Data Set required for your report.

  • We need to create a name that contains all the software in Windows Programs And Features. You can use SQL statements or Query Designer.

  The SQL statement is as follows:

  (Select distinct v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS Programs FROM v_GS_ADD_REMOVE_PROGRAMS) UNION (select distinct region AS Programs FROM v_GS_ADD_REMOVE_PROGRAMS_64) order by Programs

  1. Next, create a Data Set for your report Data. The following is an SQL statement, but pay attention to the @ Program parameter. report builder will generate a report parameter by default.

  SELECTDISTINCTv_R_System.ResourceID ,v_R_System.Netbios_Name0 AS Name ,v_R_System.Operating_System_Name_and0 AS OperatingSystem ,v_R_System.Client_Version0 AS ClientVersionFROM  v_R_System  LEFTOUTERJOIN v_GS_ADD_REMOVE_PROGRAMS    ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID  LEFTOUTERJOIN v_GS_ADD_REMOVE_PROGRAMS_64    ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceIDWHERE  v_R_System.Client0 =1AND(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 LIKE@Program  OR v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE@Program)

   

  • Open. The automatically generated Program parameter points this report parameter to the first data set on the Available Values page.

   

  Finally, insert the required Report on the Report Builder page. Select Insert> table. The new table window is displayed.

  • Select the dataset of our report, DataSet2.
  • Select the columns to be displayed on the report.
  • Complete other options for appearance and layout.

   

  This is the final result we can see on report builder.

   

   

  Here you can run the report through report builder, so that you can test the report before publishing it to the report server.

   

   

   

  If the report results meet the requirements, deploy the report to SQL Server Reporting Service. Click the icon in the upper left corner and save it to the file you want. The report has been saved to the report server.

   

  Finally, you can run the report through SQL server reporting service web report manager.