SQL Server Reporting Services
and System Center Configuration Manager
Create a Robust, Integrated Reporting Solution
Reporting is a crucial ability for most organizations—and the ability to provide robust reporting of various Microsoft System Center Configuration Manager (SCCM) functions is no exception. Reporting is a feature that hasn’t changed much over several versions of Systems Management Server and SCCM. With the release of SCCM R2, reporting has undergone one significant change—the ability to interface and take advantage of SQL Server Reporting Services (SSRS). This one change brings with it the ability to make use of the many robust features SSRS provides. Like any new technology, there is a learning curve associated with SSRS—but the benefits are well worth it. This article will discuss the integration between SCCM and SSRS and walk through a very simple example of building a report in SSRS and publishing for use in SCCM.
SCCM and SSRS—Better Together
Prior to R2, the SCCM reporting engine was little changed. The standard reporting feature was sufficient for most functions but did have limitations, particularly when reporting needs called for robust and flexible methods of presenting data graphically. SSRS opens up almost limitless possibilities for crafting reports according to your specific requirements.
SCCM integration with SSRS requires SCCM R2 to be installed and that the SSRS component of SQL be installed on the target SQL server. In addition, the Reporting Services point site system role will need to be installed on your SSRS server. This article doesn’t address installing and configuring SSRS to interface with SCCM in detail, but the process isn’t difficult.
Once R2 is installed and SSRS integration is configured we have the choice of which reporting engine to use. Figure 1 shows the reporting node after an R2 installation.
Figure 1 System Center Configuration Manager Reporting Node After R2 Installation
The Reports node will access reports using the traditional SCCM reporting engine. The Reporting Services node is our access point for SSRS deployed reports. Expanding the Reporting Services node, also shown in Figure 1, will display the server configured as the Reporting Services point along with a folder to contain all reports deployed to SSRS. If the All Reports node is selected right after R2 is installed and SSRS configuration is complete there will be no reports displayed. Adding reports to SSRS can be done by either converting standard SCCM reports into SSRS format or authoring your own reports.
Converting standard reports for use with SSRS is straightforward—simply right-click on the server name hosting the Reporting Services point role (directly under the Reporting Services node shown in Figure 1) and select Copy Reports to Reporting Services as shown in Figure 2.
Figure 2 Straightforward conversion of standard reports for use with SSRS requires a simple right-click.
This will launch a wizard that will guide the process of converting either all or selected standard SCCM reports for use with SSRS. The wizard will collect information needed for building an SSRS data source, determining how SSRS security should be handled—Windows Integrated is the common choice—and which reports should be converted and deployed to SSRS. Once the wizard is complete, the conversion and deployment of reports takes place. When complete, the All Reports view should contain data similar to what you are accustomed to seeing in standard SCCM reporting. Figure 3 is an example of what you might see after conversion.
Figure 3 All Reports window showing data after converting selected System Center Configuration Manger Reports for use with SQL Server Reporting Services.
The naming format of the reports is the same as standard SCCM reporting. Displaying a report is as simple as right-clicking on the report and selecting to Run. On the right-click menu, in addition to Run, you might also notice an option to create a New Subscription. This option taps into another welcome feature of SSRS: the ability to create subscriptions for reports. Think of an SSRS subscription as similar to a newspaper subscription. A newspaper “subscriber” expects to have the newspaper available to them according to their selected schedule. The same is true for an SCCM/SSRS subscription. Selecting the New Subscription option launches a wizard to configure this feature. The initial wizard screen is shown in Figure 4. Subscriptions allow reports to be executed and the resulting report delivered in an automated fashion. With a subscription, reports are delivered to a file share and can be rendered in a variety of formats, including XML, Excel, PDF, TIFF and Web page formats.
Figure 4 The System Center Configuration Manager Create Subscription Wizard
Discussion so far has centered on interacting with SSRS through the SCCM console itself. Using the SCCM console is useful for many interactions with SSRS, such as executing reports and creating subscriptions. In some cases, however, accessing the reporting engine directly is helpful—particularly for testing or troubleshooting. Discussion of the SSRS interface itself is discussed in detail in various forums and is beyond the scope of this article. If you want to access the SSRS interface directly, log on to the server hosting SSRS, open a Web browser and navigate to https://localhost/reports. You can also access this interface remotely by substituting the server name (NetBIOS or FQDN) in place of localhost in the listed URL.
Authoring SSRS Reports for SCCM
So far we’ve discussed how to interact with SSRS using standard SCCM reports. With SSRS it’s possible to author custom reports—again, it’s not that difficult. There are a few ways to author custom reports. The first is by creating a report based on standard SQL queries, much like what is seen in the standard SCCM reports. In addition, reports can be built using models. The option for creating standard SQL or model-based reports is available through the SCCM console. These models are accessed by selecting Create Report from the right-click menu shown in Figure 2. Selecting Create Report brings up a wizard as shown in Figure 5.
Figure 5 System Center Configuration Manager Create Report Wizard
It’s useful to create standard SQL or even model-based reports, but options are limited compared to the authoring that can be done using Visual Studio. More information on using model-based reporting is available in the “Creating CM2007 Reports Using Report Builder” white paper. Other resources to help with models are also available. Another option is to author custom reports directly in Visual Studio, which provides a rich and flexible environment for building reports. In addition to all of this there’s a fourth option, which will be the focus of this article: using Report Builder 2.0. Report Builder 2.0, designed for SQL Server 2008, is a robust environment for designing reports and can even be used to design report models that can be used with SCCM. Report Builder 2.0 is available for download at microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en. If authoring reports for SQL Server 2005, using Visual Studio is the most robust option. If authoring reports for SQL Server 2008, Report Builder 2.0 or Visual Studio can be used.
Regardless of the authoring method being used, the report author will need to have a good understanding of the SCCM database to be successful. A discussion of the SCCM database is beyond the scope of this article.
The remainder of this article will walk through creating a basic report using Report Builder 2.0, deploying the resulting report to SSRS and executing the report through SCCM.
To get started, launch Report Builder 2.0 and an introduction screen similar to Figure 6 will be displayed.
Figure 6 Report Builder 2.0 Introduction Screen
The environment is immediately ready to begin designing reports and is wizard-based to make the process easy to follow. In the design area of the report, click in the title area and replace the default text with the title of this report. The title for this sample report will be “Basic ConfigMgr Report.” Just like with any test, full formatting options are available from the right-click menu. With the title entered, it’s time to start designing the report. Again in the designer there are two options: create a table- or matrix-based report, or create a chart-based report. Choose to create a chart-based report. This will open the New Chart wizard as shown in Figure 7.
Figure 7 Report Builder 2.0 New Chart Wizard
The first requirement is to create a data source. The data source configures the report to link to the proper SQL server and proper database on the SQL server that hosts the data to be used by the report. Click New and the data source screen will appear. On this screen, title the data source, select that the connection will be to a Microsoft SQL Server and then enter the connection string either manually or using the option to build the connection string. A completed Data Source Properties window will appear similar to that shown in Figure 8.
Figure 8 A Completed Data Source Properties Window in Report Builder 2.0
The Credentials window is used to configure how the report should expect credentials to be provided. Options are to use the credentials of the current Windows user; use a specific username, password and prompt for credentials; or that no credentials should be used. These options are only stored when the report is saved to the report server.
When configuration is complete, select OK to return to the wizard. Once in the wizard, click Next, which will display the window to input the query to be used with the report. There are a couple of options here to either use the query designer by selecting specific tables of interest on the left-hand database view, selecting to import an existing query, or selecting to edit as text and input the query manually. Having the ability to build a query graphically with the designer is useful, but for the example report the following query will be used:
select (select count(*) from v_r_system vrs inner join v_agentdiscoveries vad on vrs.resourceid=vad.resourceid where vad.agentname = 'Heartbeat Discovery' and (datediff(day, vad.agenttime, getdate())< 7)) as 'Number of agents with Heartbeat less than 7 days', (select count(*) from v_r_system vrs inner join v_agentdiscoveries vad on vrs.resourceid=vad.resourceid where vad.agentname = 'Heartbeat Discovery' and (datediff(day, vad.agenttime, getdate()) >7)) as 'Number of agents with Heartbeat greater than 7 days'
Notice that when in the text editor an option is available to select whether the query will be text-based or based on a stored procedure. For this example, the query will be text-based. There are definite advantages to using stored procedures, including query optimization. Using stored procedures is beyond the scope of this article. Regardless of the method chosen to build the query, sufficient understanding of the SCCM database is required. Further, when building a query against the SCCM database, be sure to use the views instead of the tables. When finished with editing, the screen should appear similar to Figure 9.
Figure 9 A Text-Based Query in Report Builder 2.0
Select Next to continue the wizard and then choose which type of chart should be used for this report. Options include column, line, pie, bar and area. For this example a bar chart will be used. Select it and click Next.
The next screen allows for fields of data to be arranged on the chart for display. There are two available fields that can be placed in the categories, values or series sections. For this simple example, place both data fields into the values section and click Next.
You’ll see that the wizard allows a choice for report style. Choose Ocean and complete the wizard. When complete the result should appear similar to what is seen in Figure 10.
Fig 10 A Completed Report in Report Builder 2.0
From here, the chart elements themselves can be modified. Just click on the chart title or the chart legend to make any desired changes including resizing the chart, changing the report title, changing the style of the chart and so on. After making the desired changes, select Run in the upper left-hand corner of the designer to test execute the report. After a couple of modifications, the sample report renders as shown in Figure 11.
Figure 11 Final Rendering of a Sample Report Built in Report Builder 2.0
Once the report is complete it’s time to deploy it to SSRS. With Report Builder 2.0 there are two choices: save the report to an RDL file or deploy it directly to SSRS. For this example Report Builder 2.0 will be used to deploy directly. Deploying directly from Report Builder 2.0 to SSRS requires that Report Builder be configured with the location of the SSRS Web pages. A quick look in the bottom left-hand corner of the Report Builder window will indicate whether an SSRS instance is known by Report Builder. If not, click on the Report Builder button in the top left-hand corner of the Report Builder window and select Options. The screen displayed in Figure 12 will allow an SSRS instance to be specified.
Figure 12 The Report Builder Options window will allow you to specify a SQL Server Reporting Services instance.
Once associated, saving the sample report is a matter of selecting the Report Builder button and choosing to save. If the SSRS association is correct, the save window will display the folders in place on SSRS. Choose a folder to save the report to and select Save. When saving a report for display in the SCCM console, make sure to place it either in the root SCCM folder or a child folder within. For the example report, it will be saved in the SampleSCCMReportProject folder as shown in Figure 13.
Figure 13 Saving a report to a child folder within the root System Center Configuration Manager folder.
The report is now deployed to SSRS. This can be validated by opening the SSRS Web page https://localhost/reports and verifying the test folder has been created under ConfigMgr_<sitecode>. All of the pieces have come together and the report is now ready to execute in SSRS. The tendency here might be to open the SCCM console to execute this report. With the work done so far the report will show up in the SCCM console, but if it’s executed, the console will crash. The SCCM console only supports the SQL Server 2005-based reporting schema but the report and all of the options needed are accessible directly in the SSRS console, including subscriptions, security and so on. Executing the sample report in the SSRS console will present a screen similar to that shown in Figure 14.
Figure 14 SSRS console.
This article has detailed the integration between SCCM and SSRS and the steps needed to deploy a simple report to SSRS. The sample report required no user input and relied on a simple SQL query. Reports can be, and often are, much more complex, including the use of variables to prompt users for information, the use of stored procedures and so on. In addition, there are many design options to help present data in the perfect form.
While most reports written will be more complex than what was demonstrated in this simple sample, understanding the examples in this article is fundamental to being successful with SCCM/SSRS integration.
Steve Rachui is a manageability support escalation engineer in the Product Support Services group at Microsoft. He has supported SMS since version 1.2. Rachui can be reached at firstname.lastname@example.org.