Chapter 12
Reporting

Configuration Manager includes more than 450 reports that help you to gather, organize, and present information such as the following:

In Configuration Manager 2012, the Reporting feature changed significantly from earlier releases of the product. Configuration Manager no longer uses ASP web reports but instead utilizes SQL Server Reporting Services (SSRS) for reporting. Using SSRS gives you as an administrator several benefits compared to the classic web reports, such as these:

Another great feature of reports in Configuration Manager is that they utilize role-based access control and allow you to define what data users are able to view in the reports. With that in mind, in this chapter you will learn skills that will help you install and manage Reporting Services as well as modify and create reports.

In this chapter, you will learn to

Installing SQL Server Reporting Services

Prior to installing the Configuration Manager Reporting Services Point role, you need to ensure that you have installed SQL Server Reporting Services (SSRS). SSRS can be found on the SQL media you used to install SQL Server.

Before you install SSRS, you need to make sure that the server meets the minimum requirements and prerequisites. The following site provides the prerequisites for reporting in Configuration Manager: https://technet.microsoft.com/en-us/library/mt488903.aspx

Considerations for Installing SQL Server Reporting Services

There are several considerations prior to installing SQL Server Reporting Services, such as which extra tools to install, security, where to place the site system role, and SQL instance support. This section will try to answer what are considered to be the most common considerations.

With the installation of SSRS you not only install a reporting engine, but you also install the following:

  • The report server web service
  • Report Builder
  • Reporting Services configuration tool
  • Command-line utilities, such as rsconfig.exe, rs.exe, and rskeymgmt.exe

Besides the core tools and services, you should consider installing these features:

Business Intelligence Development Studio When you select to install Business Intelligence Development Studio (BIDS) using the SQL media, it will install Visual Studio and provide you with reporting and report model design tools. This tool can be useful in creating more advanced reports and report models.

SQL Server Management Studio The client tools feature on the SQL media gives you SQL Server Management Studio, which is another tool often used in the report-creation process. SQL Server Management Studio is also used to configure SQL maintenance jobs, configure SQL security, and provide a GUI for maintaining the databases.

You also choose modes and instances:

Installation Mode During setup you can choose between Native mode or SharePoint mode. Configuration Manager supports only Native mode.

Instance You can install SQL Server Reporting Services in either the default instance or a named instance. Additionally, the instance you use can be shared with other System Center products as long as the other System Center products do not have restrictions for sharing the instance of SQL Server.

WHERE TO INSTALL THE REPORTING SERVICES

The reporting site system can be installed on any primary site or the Central Administration Site. It is considered a best practice to install the site system on the Central Administration Site if one exists. At the Central Administration Site you will have access to all global data and all site data. Installing the site system on a primary site will allow you to display only global data and site data that are local to the corresponding site. Installing the site system on a secondary site is not supported.

You can install the site system on the site server or on a remote server. For performance reasons you may consider using a dedicated server acting as a reporting server. However, this all depends on several factors such as hardware and number of resources and expected usage. If you implement a dedicated reporting point server, note that the SQL version being used on the primary site must match the SQL version of the dedicated SQL Reporting Services server.

SECURITY

Configuration Manager will connect to the SQL server and set the necessary permissions in Reporting Services. This eliminates the need to go into Reporting Services Configuration Manager and manually configure security. The security settings will be Credentials Stored Securely In The Report Server and Use As Windows Credentials When Connecting To The Data Source.

Installation of the Reporting Services Site System

Prior to installing the site system, you should verify that SQL Server Reporting Services is installed correctly and works as expected.

For installation and configuration information for reporting in Configuration Manager you can refer to this TechNet article: https://technet.microsoft.com/en-us/library/mt488921.aspx.

The account that you use to install the reporting services point role must have Read access to the database. To retrieve information about named instances, the user must also have Read access to WMI on the site system. To install a Reporting Services point, take the following steps:

  1. In the Configuration Manager administrative console, select the Administration workspace and navigate to Overview ➢ Site Configuration ➢ Servers And Site System Roles.
  2. Select the server where you want to install the role:
    • From the ribbon click Add Site System Roles to start the Add Site System Roles Wizard.
    • If you want to install the role on a new server, click Create Site System Server.
  3. On the General page, click Next.
  4. On the Proxy page, enter the appropriate information, and then click Next.
  5. On the System Role Selection page, select Reporting Services Point from the list of available roles, and click Next.
  6. On the Reporting Services Point Settings page, you must create the data source. Note that the data source includes the data source type, connection information, and authentication settings.
  7. The wizard will discover the name of the site database server and the Configuration Manager database. To specify a named instance, type <Server Name>\<Instance Name>. To verify the settings in the Site Database and Database Name fields, click the Verify button.
  8. The folder name will default to ConfigMgr_<Sitecode>; you can change the folder name if you want.
  9. In the Authentication Settings area, click Set and choose an existing Configuration Manager account or select a new account. It is considered a best practice to have a specific low-rights user account for Authentication settings. The account that runs Reporting Services must belong to the domain local security group Windows Authorization Access Group, and have the Read tokenGroupsGlobalAndUniversal permission set to Allow. In addition, the account that you specify must have Log on Locally permissions on the computer hosting the Reporting Services database.
  10. Once all settings are configured as shown in Figure 12.1, click Next.
  11. On the Summary page, verify the settings and click Next.
  12. On the Completion page, click Close.
Screenshot of “Add Site System Roles” wizard shows creation of data source for specific reporting service setting.

FIGURE 12.1 Creating the data source

WHAT HAPPENS DURING THE INSTALLATION?

When the installation begins, Configuration Manager will perform these steps:

  1. Create the data source with the specified data source authentication account.
  2. Create a root folder for all Configuration Manager reports.
  3. Add the Configuration Manager Report Users and Configuration Manager Report Administrators security roles in Reporting Services.
  4. Create subfolders and files in %programfiles%\SMS_SRSRP.
  5. Deploy all reports in the %programfiles%\SMS_SRSRP\Reports folder.
  6. Assign all user accounts in Configuration Manager the Configuration Manager Report Reader rights on root folders and the Configuration Manager folder.
  7. Assign all user accounts with Site Modify rights in Configuration Manager the Configuration Manager Report Administrator rights on root folders and the Configuration Manager folder.
  8. Read the current permissions in Configuration Manager, and map those to the newly created reporting folders.
  9. Assign users who have Run Report permission for any object Configuration Manager Report Reader rights to the associated report folder.
  10. Assign users who have Report Modify rights in Configuration Manager Report Administrator rights on the associated report folder.
  11. Retrieve the mapping between report folders and Configuration Manager secured object types (those maintained in the Configuration Manager site database).
  12. Configure the following rights for administrative users in Configuration Manager to specific report folders in Reporting Services:
    • Add users and assign the Configuration Manager Report Users role to the associated report folder for administrative users who have Run Report Permissions for the Configuration Manager object.
    • Add users and assign the Configuration Manager Report Administrators role to the associated report folder for administrative users who have Modify Report permissions for the Configuration Manager object.

Configuration Manager connects to Reporting Services and sets the permissions for users on the Configuration Manager and Reporting Services root folders and specific report folders. After the initial installation of the Reporting Services point, Configuration Manager connects to Reporting Services in a 10-minute interval to verify that the user rights configured on the report folders are the associated rights that are set for Configuration Manager users. When users are added or user rights are modified on the report folder by using Report Manager, Configuration Manager overwrites those changes by using the role-based assignments stored in the site database. Configuration Manager also removes users who do not have Reporting rights in Configuration Manager.

VERIFYING THE INSTALLATION

You can verify the Reporting Services installation by monitoring these log files:

sitecomp.log The sitecomp.log file will have an entry like this:

Starting service <span cssStyle="font-family:monospace">SMS_SERVER_BOOTSTRAP_Server</span> with command‐line arguments
"SiteCode e:\Program Files\Microsoft Configuration Manager /install
E:\Program Files\Microsoft Configuration Manager\bin\x64\rolesetup.exe
SMSSRSRP"

srsrpMSI.log srsrpMSI.log is the main log file for the installation. You can monitor this for detailed information. Most likely you will find this log file interesting only when troubleshooting a failed installation.

srsrp.log Once the site system is installed, you can monitor the srsrp.log file. It gives you detailed information about the folders and reports as they are published to the reporting site.

You can also monitor the folders created in %ProgramFiles%\SMS_SRSRP:

Reports Contains all the default RDL files. The reports are imported during the original installation process. You can use the RDL files to import one or multiple reports again.

Resources Contains various DLL files.

Style Contains the three graphical elements used in most reports. You can replace these files with a custom company logo, for example.

The final test is to connect to the reporting site and verify that you can view all of the reports. Open a browser and type http://reportingserver/reports. Figure 12.2 shows the Configuration Manager reports.

Figure of a window shows home page of SQL Server reporting Services in Configuration Manager.

FIGURE 12.2 Viewing the default reports

Default Reports

By default, over 470 native reports are added during the installation. The reports are organized in several different folders. You can move the reports between the folders as you like and also create new folders. The following folders are available:

Administrative Security Contains information about role-based security, scopes, and audit information.

Alerts Contains information about alerts, such as who closed the alerts.

Asset Intelligence Contains inventory and software license information.

Client Push Contains information about current and past client push installation attempts.

Client Status Contains information about the overall client health and status of the clients installed.

Company Resource Access Contains information about certificate issuance by the certificate registration point and devices/users with certificates about to expire.

Compliance and Settings Management Allows administrators and others to report on Settings Management assignment for a computer or collection. Contains information about the compliance states for computers and collections along with detailed information on configuration items.

Device Management Contains information about device information collected using the Exchange Server mobile connector and from natively managed mobile devices. Integration with Windows Intune provides asset and compliance reporting across cloud-managed PCs as well as mobile devices, including Windows RT, Windows Phone 8, iOS, and Android.

Driver Management Contains information about drivers imported into the drivers repository.

Endpoint Protection Provides detailed information about malware and virus activities. These reports will provide the Security department with up-to-date information about the overall security threat level in the organization.

Hardware - CD-ROM Contains information about CD-ROMs.

Hardware - Disk Contains physical and logical disk information.

Hardware - General Contains a very useful hardware report along with information about the various inventory classes assigned.

Hardware - Memory Contains memory information. You will find information for a specific computer and a count of each unique memory configuration.

Hardware - Modem Contains modem information.

Hardware - Network Adapter Contains network adapter information like IP address, MAC address, and adapter information.

Hardware - Processor Contains processor information.

Hardware - SCSI Contains SCSI information.

Hardware - Sound Card Contains sound card information.

Hardware - Video Card Contains video card information.

Migration Contains information about the migration process from one Configuration Manager site to another. Includes migration jobs, migration job status, and other migration reports.

Network Provides information about IP addresses and IP subnets. These reports will give you the number of IP subnets in the network and how many IP addresses you have in each subnet.

Network Access Protection Provides information about NAP rules and computers that have been affected by one or more NAP policies.

Operating System Provides information about operating systems and service packs in the organization.

Power Management Provides power management information like computer activity, energy cost by day, and energy consumption. The information provides the organization with valuable data about the overall power consumption and also how new client power settings have lowered the environmental impact.

Replication Traffic Provides information about database replication throughout the hierarchy.

Site - Client Information Provides information about Configuration Manager client versions, deployments, assignments, communication settings, and out-of-band management configurations.

Site - Discovery and Inventory Information Provides information about client discovery and inventory information. These reports can be very useful when determining the overall client health of the sites.

Site - General Lists computers belonging to a specific Configuration Manager site, and shows when the site status was last updated.

Site - Server Information Lists all site servers and site system roles for a specific site.

Software - Companies and Products One of the primary folders when searching for installed software applications.

Software - Files Provides information based on the software inventory processes.

Software Distribution - Application Monitoring Provides basic and detailed information about application deployment. These reports will provide you with information about computers where requirements or dependencies have not been met, per asset deployment information, application usage, application infrastructure errors, and application compliance.

Software Distribution - Collections Contains information about collections, maintenance windows, and resources belonging to specific collections.

Software Distribution - Content Contains information about distribution points, distribution groups, content, and distribution point usage.

Software Distribution - Package and Program Deployment Contains information about package deployments.

Software Distribution - Package and Program Deployment Status Contains information about statuses for package deployments.

Software Metering When software is enabled, you will use the reports as the primary information source to find information about the applications you monitor, when they were last started and by whom, and where they are installed but not used. The reports will enable you to determine which applications you can uninstall and that way cut down on the license costs.

Software Updates - A Compliance Provides overall compliance information about the software updates released from Windows Updates and also third-party software updates authored and published using the System Center Update Publisher tool.

Software Updates - B Deployment Management Provides information about the software update deployments created in the organization and updates that are required but not yet deployed. You can use the reports in this category to assist you when troubleshooting software update deployments.

Software Updates - C Deployment States This category contains some of the most used reports whenever you work with software update deployment. The single most important report (in my opinion) is States 1 - Enforcement States for a Deployment. This report can be used to track down the deployment processes of any given software update deployment along with compliance information.

Software Updates - D Scan Reports in this category provide you with information about which clients are able to perform a scan against the WSUS server. The report Scan 1 - Last scan states by collection is the very first report you should run whenever you want to troubleshoot software update problems. If clients are unable to perform a scan, they will not be able to upload compliance information or install required software updates.

Software Updates - E Troubleshooting Contains a few reports that can assist you when troubleshooting client scans and update deployments.

State Migration Provides operating system state migration information for a specific site or state migration site system point.

Status Messages Allows you to see all status messages received by the site server. Useful to determine the health of the Configuration Manager hierarchy.

Status Messages - Audit Allows you to see audit status messages for a specific site or user if using a Configuration Manager agent to remotely control devices.

Task Sequence - Deployment Status Provides you with detailed information about the status of running and historical task sequence deployments.

Task Sequence - Deployments Provides you with detailed information about running and historical task sequence deployments.

Task Sequence - Progress Provides you with detailed information about the progress of task sequences.

Task Sequence - References Provides information about the objects referenced by a specific task sequence.

Upgrade Assessment Provides you with an overview of computers that meet the system requirements for Windows 7 and Windows 8 deployment and those computers that are not capable of being upgraded.

User - Device Affinity Provides you with UDA information, such as UDA associations per collection and UDA statistics.

User Data and Profiles Health Provides you with information about users in a specific domain, a count of users, and computers used by specific users.

Users Provides you with information about users in a specific domain, a count of users, and computers used by specific users.

Virtual Applications Provides information about virtual applications installed in the environment and information about virtual application packages.

Volume Purchase Programs - Apple Provides an inventory of all applications for iPhone, iPad, and iPod Touch licensed thru Apple’s Volume Purchase Program.

Wake On LAN Provides information about the Wake On LAN activity and configurations.

As you can see, Microsoft has done a good job of providing many key reports that are ready to use out of the box.

Running a Report

There are two ways to run a report. The first is to use the Configuration Manager administrative console and the second is to use the web-based Report Manager. A benefit of the web-based Report Manager is that the user does need to have the Configuration Manager administrative console installed in order to view reports.

Viewing Available Reports

You can view the list of available reports from within the Configuration Manager administrative console or through the web-based Report Manager created during the configuration of the Reporting Services site system role. Both Report Manager and Configuration Manager group reports in folders. If needed, you can always move a report from one folder to another folder or even create your own folders.

Running a Report from the Administrative Console

To run a report using the Configuration Manager administrative console, follow these simple steps:

  1. Open the Configuration Manager administrative console and select the Monitoring workspace.
  2. Under Overview, expand Reporting and select Reports. This will display the reports in the right pane. If you expand the Reports node, it lists out the folders, by category, that you can select and only displays the reports from that category.
  3. Select the report you want to run, and click Run on the ribbon.
  4. Many of the reports require that you select additional information, such as a collection, to narrow the results of the report data.
  5. Click View Report.

Figure 12.3 shows the Client Push Installation Status Summary report.

Screenshot of a dialog box shows Client push installation status summary in a pie chart.

FIGURE 12.3 The Client Push status report opened from the console

This will open the report in a new window. You can choose to print or export the report. If you choose to export the report, you can select a file format like Word, Excel, PDF, TIFF, MHTML, CSV, or XML.

Running a Report from Report Manager

To run a report using the web-based SQL Server Reporting Services, follow these simple steps:

  1. Open your web browser to the URL (the default is http://ReportServer/Reports) for Report Manager.

    As described earlier in the chapter, this URL is the location you specified when setting up the reporting site system role.

  2. Open the report root folder and then select the folder for the category of the report you want to run.
  3. Select the report that you want to run from the list provided in the folder.
  4. Many reports require that you provide values that will be used to run the report. Specify the required criteria and click View Report.

Figure 12.4 shows the All application deployments (advanced) report

Screenshot of a window shows contents of all deployment application. It also shows advanced report in the form of pie charts at the bottom.

FIGURE 12.4 Contents of the All application deployments (advanced) report

Working with Reporting Security

Working with security for reports is not that different from working with security for other objects in Configuration Manager. Configuration Manager will automatically synchronize the security settings configured using the Configuration Manager administrative console to SSRS. In theory you can configure security in both the Configuration Manager administrative console and in Report Manager. The preferred method is using the Configuration Manager administrative console because all changes applied here will automatically be synchronized to SQL Server Reporting Services. If you configure security directly in Report Manager, you will find that all your custom settings will be overwritten by the security settings in Configuration Manager. By default Configuration Manager will connect to SSRS every 10 minutes and verify that security settings are correctly configured.

Permissions Required to Run Reports

Configuration Manager will create two new Reporting security groups in SQL Server Reporting Services:

Configuration Manager Report Administrators Offers full administrative permissions to all tasks related to working with reports.

Configuration Manager Report Users Allows users to read reports and create subscriptions.

When you assign the Read permission to a user or group in the Configuration Manager console, that group will automatically be granted the Configuration Manager Report Users role in SQL Server Reporting Services.

Most, but not all, objects will be assigned either Run Report or Modify Report or both, as shown in Figure 12.5, for the Asset Manager. To read reports not associated with a specific role, you must have Read permission to the site. When security is granted, Configuration Manager will automatically assign the appropriate group or user permissions to the reports.

Screenshot of a dialog box shows “Permissions” under “Asset Manager” properties, with selection on Inventory Reports.

FIGURE 12.5 Report options for built-in security roles

Managing Reports

One of the main benefits of using SQL Server Reporting Services is the ability to manage reports. Once a report is published, you will be able to use the Report Manager to modify some of the reporting properties. The properties control security, parameter values, the data source, when the report runs (scheduled or on demand), caching options, and more.

To manage the properties for a report, follow these simple steps:

  1. Open your web browser to the URL (the default is http://ReportServer/Reports) for the Report Viewer.
  2. Open the root report folder and then the folder for the category of the report you want to manage, for example, Software Distribution – Application.
  3. Click the drop-down arrow for the report you want to manage.
  4. From the list of options, click Manage.

The first properties you will see are the General properties for the report. You will find a list of report properties in Tables 12.1 through 12.9. Table 12.1 lists the General properties.

TABLE 12.1: General properties

PROPERTY DESCRIPTION
Delete Allows you to delete the report.
Move Allows you to move the report to another folder. This is especially useful if you want to create your own folder with a mix of canned reports from different folders. You should always consider creating custom folders like Servicedesk, Software, or License and move the reports you use the most to those folders.
Create Linked Report Allows you to create a link to another report. You will find more information on this property later in this chapter.
Download Allows you to save the report as an RDL file.
Replace Allows you to import a new version of the report.
Name Lets you specify the name of the report.
Description Lets you provide the report description.
Hide In Tile View Enable this to hide the report from the Report Manager tile view mode. Tile view mode is the default view when users browse for reports.

Using prompts (or parameters) in reports is very common. It is an easy way for the report user to specify what data to view without having to have deep knowledge of the underlying dataset or SQL reporting skills. You as the administrator can customize the parameters and configure settings such as the default parameter values described in Table 12.2.

TABLE 12.2: Parameter properties

PROPERTY DESCRIPTION
Parameter Name The name of the parameter.
Data Type Specification of the data type.
Has Default Allows you to specify a default value, thus saving time whenever you run the report. With this selected you can specify the value in Default Value and/or use the Null property.
Default Value A default value can originate from the report definition, can be query based, or can be a value you type in. The value you enter must adhere to the data type; the use of wildcards is also determined in the report itself.
Null Allows you to specify that the report will run even if the user does not select any prompted value. This does require that the report allows the use of Null values.
Hide Allows you to hide the parameters in the Report Manager from users when they run a report. The parameter value will still be visible if the user starts the subscription wizard.
Prompt User With this selected, users will be prompted for a parameter. Deselect the check box if you want to control the parameters to be used in the report.
Display Text Text that will be displayed with the parameter value.

As part of the SQL Server Reporting Services point installation, a shared data source is created. The data source is used to specify what data to access and which security credentials to use. Table 12.3 describes the values you can specify for the created data source. Notice that it is highly unlikely that you will need to change the data source from the Report Manager.

TABLE 12.3: Data Source properties

PROPERTY DESCRIPTION
A Shared Data Source Specifies the shared data source.
A Custom Data Source Allows you to specify a custom data source.
Connection String Specifies the connection string used to connect to the data source.
Connect Using Defines how you connect to the data source. For all the canned reports, the connection settings are already defined in the connection string.
Connect Using Credentials Supplied By The User Running The Report Defines that each user must provide a username and password.
Use As Windows Credentials When Connecting To The Data Source Configure this option if the credentials supplied by the user are Windows Authentication credentials.
Credentials Stored Securely In The Report Server Encrypts and stores the credentials in the report server. This will allow you to run a report unattended, which is a requirement for scheduled reports.
Impersonate The Authenticated User After A Connection Has Been Made To The Data Source Used only on rare occasions to allow delegation of credentials if supported by the data source.
Windows Integrated Security Uses the credentials of the logged-on user to access the data source.
Credentials Are Not Required Does not prompt for credentials when running the report. This works only if the data source does not require a user logon.
Test Connection Performs a test to the data source using the supplied credentials.

One of the advantages of using SQL Server Reporting Services is that you can configure automatic delivery of specified reports by either email or storing the report to a file share. You can create subscriptions in the Report Manager or by using the Create Subscription Wizard in the Configuration Manager administrative console. Table 12.4 explains the subscription options available when you are using the Report Manager.

TABLE 12.4: Subscription options

PROPERTY DESCRIPTION
New Subscription Allows you to create a new report subscription.
Delete Lets you delete the selected subscription.
Edit Allows you to edit the subscription properties.
Report Delivery Options (Email Selected) Can be delivered by email or can be file based. The following are the properties for an email-based subscription.
To Fill in the recipient email address; it can be a group or a list of individual email addresses separated by a semicolon (;). Note that the reporting server will not validate any of the email addresses.
Cc Fill in the email address of any recipients who will receive the email as Cc. Can be a group or a list of individual email addresses separated by a semicolon.
Bcc Fill in the email address of any recipients who will receive the email as Bcc. Can be a group or a list of individual email addresses separated by a semicolon.
Reply-To Fill in the Reply To email address.
Subject The email subject; you can use these variables combined with custom text:
@ExecutionTime—The runtime for the report
@ReportName—The name of the report
Include Report Includes the report in the email as an attachment.
Render Format Reports can be delivered in different formats:
XML
CSV (comma delimited)
Data Feed
PDF
HTML 4.0
MHTML (MIME HTML)
Excel
RPL Renderer (RPL)
TIFF File
Word
Include Link Includes a URL in the email body.
Priority Choose from these email priorities:
Low
Medium
High
Comment Text entered in the comment will be added to the email body.
Report Delivery Options (Windows File Share) The following are the properties for Windows file share subscription.
File Name Type in the filename of the report.
Add A File Extension When The File Is Created By default the file type will not be appended to the filename unless you enable this setting.
Path A UNC to an existing folder on the network. The specified user account must have Write permissions to the share.
Render Format Same as the email rendering formats.
Credentials Used To Access The File Share Specify the user account that will be used to save the file.
Overwrite Options Choose from these options:
Overwrite An Existing File With A Newer Version.
Do Not Overwrite An Existing File.
Increment File Names As Newer Versions Are Added. This will place a number at the end of the filename and increment the number as new reports are saved.

When reports are executed, they will be transformed from the reporting database into a viewable format. The defined query in the dataset will be executed and will return data to the reporting server, where the selected rendering extension will create the report. The performance impact of running a report depends very much on the amount of data retrieved and the rendering format selected. By default when users run a report, that report is generated on demand. Most of the data in Configuration Manager is either inventory data or state messages, which are very rarely real-time data. By knowing the processing options described in Table 12.5 and the report content, you will quickly learn how to speed up the processing for reports.

TABLE 12.5: Processing options

PROPERTY DESCRIPTION
Always Run This Report With The Most Recent Data Always shows the latest data in the report.
Cache A Temporary Copy Of The Report. Expire Copy Of Report After A Number Of Minutes. Specifies the number of minutes the intermediate format will be available in the cache.
Cache A Temporary Copy Of The Report. Expire Copy Of Report On The Following Schedule You can specify when the intermediate format will be removed from the cache based on a custom specific schedule or by using a shared schedule.
Render This Report From A Snapshot This option allows you to create the intermediate format prior to running the report the first time. The intermediate format can be created on a custom specific schedule or by using a shared schedule. Note that cached reports will be added as permanent storage to the ReportServer database, unlike cached reports, which will be removed. This feature is closely related to the values you specify on the Snapshot Options page.
Create A Report Snapshot When You Click The Apply Button On This Page Creates a snapshot of the report as soon as possible without using the specified schedule.
Report Timeout Controls the report processing timeout value. The default timeout value is specified in the Site Settings page.

When you cache a report, the first time the report is executed the process is similar to running an on-demand report. However, the intermediate format is stored in ReportServerTempDB (cache) for a configured period. If any other users request the same data, the server will take the intermediate format and render the report much more quickly.

You can preload the cache with temporary copies of the report by creating a refresh plan with the parameters described in Table 12.6. Creating a cache refresh plan requires that the cache options have been defined.

TABLE 12.6: Cache Refresh options

PROPERTY DESCRIPTION
New Cache Refresh Plan Lets you create a new plan.
Description You can provide a meaningful description for the plan.
Refresh The Cache According To The Following Schedule The cache can be refreshed on a custom specific schedule or by using a shared schedule.

As snapshots are generated, you will be able to view the reports in the Report History page. On this page you can also create a manual snapshot of the report. The options are shown in Table 12.7.

TABLE 12.7: Report History options

PROPERTY DESCRIPTION
Delete Deletes the selected report snapshot.
New Snapshot Creates a new snapshot. This option is available only if Allow Report History To Be Created Manually has been selected on the Snapshot Options page.

Report history is stored in the Report Server database. The Snapshot options, shown in Table 12.8, will assist you in controlling how many items are stored and when the snapshot is generated.

TABLE 12.8: Snapshot options

PROPERTY DESCRIPTION
Allow Report History To Be Created Manually Enables the New Snapshot button on the Report History page.
Store All Report Execution Snapshots In Report History With this feature, reports that are created based on the execution settings on the Processing Options page will be added to the Report History page.
Select The Number Of Snapshots To Keep Controls how many snapshots are added to the history. You can select from three different values:
Use Default
Unlimited Snapshots
Limit To A Specific Number

As described earlier in this chapter, Configuration Manager will apply default security settings when the Reporting Services point is installed and will check those security settings every 10 minutes. Table 12.9 describes the Security setting applied to a report.

TABLE 12.9: Security option

PROPERTY DESCRIPTION
Edit Item Security Allows you to customize the default Security settings. Notice that Configuration Manager will overwrite any custom settings you configure.

Working with Subscriptions

One of the many powerful features of using SQL Server Reporting Services is the ability to create subscriptions to reports. You can subscribe to reports and have them delivered via mail or as a file on a network share. A standard user can create and customize their own subscriptions with the options described in Table 12.4. You can create a subscription in Report Manager or use the Create Subscription Wizard in the Configuration Manager administrative console.

CREATING A FILE-BASED REPORT SUBSCRIPTION

Prior to creating a file-based subscription, you need to ensure that you have an account with write permissions to a predefined server share. In this example we’ll create a subscription for the Client Push Status Summary report.

To create a file-based report subscription using the Configuration Manager administrative console, follow these steps:

  1. Open the Configuration Manager console and select the Monitoring workspace.
  2. Inside Overview, click Reporting, and select the report from the Reports folder.
  3. With the report selected, click Create Subscription on the ribbon.
  4. In the Report Delivered By field, ensure that Windows File Share is selected.
  5. In File Name field, type in the name of the file without any extension—for example, ClientPushSummary.
  6. Enable the Add File Extension When Created check box.
  7. In the Path field, type in the UNC to an existing share.
  8. Select the rendering format; in the example we have selected Acrobat (PDF) File.
  9. In the User Name field, specify an account with Read access to the report and Write permissions to the specified UNC.
  10. Configure the needed Overwrite Option, and click Next.

    Figure 12.6 shows how the delivery options can be specified.

    Screenshot of “Create Subscription” wizard creation of windows file based report, with selection on “Delivery Method.”

    FIGURE 12.6 Creating a Windows file-based report subscription

  11. On the Subscription Schedule page, select a shared schedule or create a new schedule, and click Next.

    This report is a prompted report, and it requires that you select the prompted value. In this example you must click Values and select the number of days.

  12. Select All, click OK, and click Next.
  13. Read the summary information, and click Next.
  14. Click Close; the subscription is now created.

You can view information and edit the scheduled report by clicking Reporting ➢ Subscriptions in the Monitoring workspace. You can also open the Report Manager and open the properties for the report. Select Subscriptions; here you will find detailed information about when the subscription is about to run or when it was executed the last time.

CREATING AN EMAIL-BASED REPORT SUBSCRIPTION

You learned that you can create a report subscription that creates a document in a specified share on a defined schedule and that you can also create an email-based subscription that will deliver the report via email. Prior to creating an email-based subscription, you need to configure the email options in the Reporting Services Configuration Manager. In this example, you will configure the required mail options and use Report Manager to create a subscription for the Client Push Status Summary report.

To configure email support in the Reporting Services Configuration Manager and create an email-based subscription in Report Manager, follow these steps:

  1. From the Start menu, choose All Programs ➢ Microsoft SQL Server ➢ Configuration Tools ➢ Reporting Services Configuration Manager.
  2. You will be prompted to connect to a reporting server. Make sure the correct reporting server and instance are selected and click Connect.
  3. Click E-Mail Settings.
  4. In Sender Address, type the email address that will be used to send the mail.
  5. In Current SMTP Delivery Method, ensure that Use SMTP Server is selected.
  6. In SMTP Server, type the name of the SMTP server and click Apply to store the settings.
  7. Click Exit to close the Reporting Services Configuration Manager.
  8. Open the SQL Report Manager and connect to the Configuration Manager report by typing http://reportingserver/reports.
  9. Open the Configuration Manager folder and the Client Push folder.
  10. Hover the pointer over the report, and select Subscribe from the drop-down list.
  11. In Delivered By, ensure that E-Mail is selected.
  12. In To, type the recipient email addresses.

    Note that there will be no validation of the addresses; you need to make sure that what you type is correct. You can type multiple addresses by separating them with a semicolon (;).

  13. Fill in any other CC, BCC, and Reply-To email addresses.
  14. Type a subject using the variables explained in Table 12.4.
  15. Choose from among the following options:
    • Send A Link
    • Include The Report As Attachment
    • Embed The Report In The Mail
    If the recipients support HTML 4.0 and MHTML 5.0, you can select the Render format MHTML; this will embed the report in the mail.
  16. In the Comment field, type in any information that you want to include in the mail body.
  17. Configure the processing options, and click OK to create the subscription.

You can create the subscription in Report Manager as explained previously or in the Configuration Manager administrative console as explained for file-based subscription. The difference between the two methods is that subscriptions created in the administrative console will be listed in the administrative console as well as in Report Manager, unlike subscriptions created in Report Manager, which will be listed only in Report Manager.

Creating Reports

Configuration Manager includes over 470 default reports that meet many common reporting requirements. However, it’s possible that you may need to modify an existing report or create a new one to meet a specific reporting requirement. Creating reports requires at least a basic understanding of the Transact-SQL (T-SQL) query language, requires you to have some knowledge of the data and the views that are contained in the Configuration Manager database, and requires you to be familiar with the various tools available to create and customize reports.

You can create a new Configuration Manager report in the following ways:

Functions vs. Views

The native reports in Configuration Manager are based on the role-based access control (RBAC) model. As a result, users will see only the report data that they are allowed to see when they run the default Configuration Manager reports. These RBAC-enabled reports use table-based functions in the Configuration Manager database as well as identifiers for the user running the report to present only the report data that the user is allowed to view. An example of a table-based function for installed software inventory information is fn_rbac_GS_INSTALLED_SOFTWARE. Configuration Manager users can also create and run reports that do not follow the RBAC model; these reports are based on the SQL views in the database. An example of a view for installed software inventory information is v_GS_INSTALLED_SOFTWARE.

When you create reports for your organization, you will need to determine if RBAC is necessary for the new reports. There is no question that RBAC security is important when users interact with the Configuration Manager console because it limits what actions the user is able to perform on the site. However, it’s possible that, for your organization, security is not as important for Configuration Manager report data. If Configuration Manager report security is an essential requirement for your organization, then you will use the table-based functions when you create the queries that will be used in your custom reports. If report security is a not a critical concern for your organization, then you may find it easier to create reports that use SQL views in the queries for your custom reports. It is worth noting that many customers decide that RBAC in the reports is not critical and will build reports based on SQL views. Also, many of the sample reports you may download from blogs and Configuration Manager–related websites will be based on SQL views, not the RBAC-based functions. This chapter assumes that report security is not a critical concern for the organization and uses SQL views when creating queries that will be used in reports.

Basic SQL Commands

As previously discussed, Configuration Manager provides the ability to create reports based on queries that use SQL views to retrieve data from the database. The SQL views are essentially virtual tables that do not contain data but are based on tables that contain data. The data is dynamically compiled from source tables when the view is referenced. We do not recommend that you create reports that access the database tables directly because the table names may change in future versions of Configuration Manager and the reports will no longer work.

To create a Configuration Manager report, you need to create a SQL query based on views that retrieves the desired data from Configuration Manager.

NOTE Numerous tools are available for creating SQL queries. You can create the query directly in the Report Builder application, or you can create the query in another tool, like SQL Server Management Studio, and then copy the query into the Report Builder query design window. Feel free to use whichever approach is easier for you to use.

An in-depth study on how to create SQL queries is beyond the scope of this book, but some general knowledge may be helpful. As you examine the views in the Configuration Manager database, you may notice some standardized naming of the views. Some examples are:

  • V_GS_Name—Contains current inventory data
  • V_HS_Name—Contains historical inventory data
  • V_R_Name—Contains discovery data
  • V_RA_Name—Contains array (multivalue) discovery data

There are many commands and clauses in the T-SQL query language. Some of the more common ones you may use are SELECT, ORDER BY, and WHERE.

Consider the following query:

SELECT * FROM v_GS_COMPUTER_SYSTEM

This query would return all the rows from the Configuration Manager database for the view v_GS_COMPUTER_SYSTEM. If you wanted the query to return data for only computers that are virtual machines, you could add a WHERE statement:

SELECT * FROM v_GS_COMPUTER_SYSTEM
WHERE (MODEL0 = 'Virtual Machine')

If you wanted to sort the results by computer name, you could add an ORDER BY clause and then specify whether the list should be in ascending or descending order. The following query would sort the results by computer name in ascending order:

SELECT * FROM v_GS_COMPUTER_SYSTEM
WHERE (MODEL0 = 'Virtual Machine')
ORDER BY NAME0 ASC

It’s likely that the report you are creating should only include certain fields, not every possible field from the view. For example, if we only wanted to include the fields for computer name, computer manufacturer, and model, we could use the following query:

SELECT NAME0, MANUFACTURER0, MODEL0
FROM V_GS_COMPUTER_SYSTEM
WHERE (MODEL0 = 'Virtual Machine')
ORDER BY NAME0 ASC

The results for this query would be

NAME0  MANUFACTURER0             MODEL0
CM     Microsoft Corporation     Virtual Machine
CMSEC  Microsoft Corporation     Virtual Machine
DC     Microsoft Corporation     Virtual Machine
OM     Microsoft Corporation     Virtual Machine
SCORCH Microsoft Corporation     Virtual Machine
SQL    Microsoft Corporation     Virtual Machine
WIN101 Microsoft Corporation     Virtual Machine
WIN102 Microsoft Corporation     Virtual Machine
WIN103 Microsoft Corporation     Virtual Machine

And you will likely want to change the column headings to more user-friendly names. You can rename the headers when you create the report in Report Builder (or BIDS), or you can rename them via the AS command in the SQL query.

SELECT NAME0 AS 'Computer Name', MANUFACTURER0
AS 'Computer Manufacturer', MODEL0 AS 'Computer Model'
FROM V_GS_COMPUTER_SYSTEM
WHERE (MODEL0 = 'Virtual Machine')
ORDER BY NAME0 ASC

The results for this query would be

Computer Name Computer Manufacturer     Computer Model
CM            Microsoft Corporation     Virtual Machine
CMSEC         Microsoft Corporation     Virtual Machine
DC            Microsoft Corporation     Virtual Machine
OM            Microsoft Corporation     Virtual Machine
SCORCH        Microsoft Corporation     Virtual Machine
SQL           Microsoft Corporation     Virtual Machine
WIN101        Microsoft Corporation     Virtual Machine
WIN102        Microsoft Corporation     Virtual Machine
WIN103        Microsoft Corporation     Virtual Machine

Obviously these are very simple queries but they may be useful as you begin learning how to create powerful queries that you will implement in your custom Configuration Manager reports.

Report Models

A report model contains predefined views and fields logically grouped together. By using report models, you can assist users in building reports that expose only the needed views and fields. This shortens the learning curve for building reports and also speeds up the process because only the selected views and fields will be presented to Report Builder. In the next section you will learn how to build your own report model and create reports based on the included Configuration Manager report models. Some of the benefits of using report models are as follows:

  • You can assign reporting model logical names.
  • The underlying database structure can be hidden from the person creating the report.
  • The report model can contain multiple tables and views yet still list the model as a single object.
  • Time saved is money saved. When reporting models are created, not every person creating reports needs to have deep SQL knowledge.

Creating a Report Using Report Builder

Configuration Manager uses Microsoft SQL Server Reporting Services Report Builder 3.0 as the authoring and editing tool for both model and SQL-based reports. Report Builder will automatically be installed on your computer when you create or modify a report for the first time. With Report Builder, you are able to

  • Develop one report at a time in a Microsoft Office lookalike environment
  • Create charts and gauges
  • Create rich SQL
  • Use rich formatting capabilities
  • Export to reports in other formats

We will now go through the process of creating a new report using Report Builder. The report will be created in the report folder Hardware - General. Use the following steps to create a basic SQL report that will show and count all the different hardware models you have in the database.

  1. Open the Configuration Manager console and select the Monitoring workspace.
  2. In Overview, expand Reporting and right-click Reports.
  3. From the drop-down menu, select Create Report.
  4. Select SQL-Based Report.
  5. In the Name field, type Count Hardware Models.
  6. In the Description field, type Group and count all hardware models.
  7. In the Path field, click Browse, select the Hardware - General folder, and click OK.
  8. Click Next.
  9. On the Summary page, click Next (this will create the report).
  10. Click Close.

    Report Builder will launch automatically and allow you to finish creating the report. If this is the first time you launched the wizard, you will be prompted to install Report Builder.

  11. In Report Builder, click Table Or Matrix.

    This will launch the New Table Or Matrix Wizard.

  12. Click Create A Dataset and click Next.
  13. In Data Source Connections, verify that the Configuration Manager data source is selected, and click Next. If you want to verify that the connection is correct, use the Test Connection option at the bottom-right corner of the New Table Or Matrix window.
  14. Expand dbo ➢ Views ➢ v_GS_COMPUTER_SYSTEM.
  15. Select Manufacturer0 and Model0, as shown in Figure 12.7, and click Next.
    Screenshot of a dialog box shows creation of a query to specify the data from report Builder 3.0.

    FIGURE 12.7 Creating a report using Report Builder 3.0

  16. Drag Manufacturer0 and Model0 to Row Groups and Model0 to Values.
  17. In Values, right-click Model0 and select Count; click Next when finished.
  18. In Choose The Layout, select Stepped Subtotals Above, and click Next.
  19. Select a style and click Finish.

    The report is now open in Design view, allowing you to customize colors, fonts, and much more.

  20. To view the report, click Run in the ribbon.
  21. If you want to make additional changes to the format of the report, click Design in the ribbon.
  22. Once the report is formatted properly and ready to be published, click Save to save the report.
  23. Exit Report Builder.
  24. When prompted to publish the report, click Yes.

Linked Reports

A linked report is something you can create when you want to have several versions of the same report with different data. You can look at linked reports as you would cloning an existing report, with the exception that a linked report inherits the layout and data source properties of the original report. Properties like parameters, subscriptions, and schedules can be changed in the linked report.

Importing and Exporting Reports

One of the fastest and easiest ways to build reports in your Configuration Manager console is to import them from others who have already created the report you want. This technique allows administrators to share reports quickly and easily. Here is a list of some popular sites where you can download examples:

http://blog.coretech.dk/category/confmgr/config-mgr-inventory-and-reporting/ Contains ready-made reports to download and import. Offers step-by-step descriptions of how to create and modify reports.

http://myitforum.com/myitforumwp/ Large online System Center community where you will find several community leaders and contributors posting reporting solutions.

Please see the Microsoft TechNet Library article detailing how to create custom reports by using SQL Server views in System Center 2012 Configuration Manager at http://technet.microsoft.com/en-us/library/dn581954.aspx.

Note that there are many other community websites that contribute to the SQL reporting knowledge base, and the previous list provides just a base reference.

Importing Reports

Unlike in previous versions of Configuration Manager, you will not be able to import reports using the Configuration Manager console. Instead you will use Report Manager and upload a Report Definition Language (RDL) file as described here:

  1. Open a browser and connect to http://reportingserver/reports.
  2. Open the ConfigMgr_<SITECODE> folder.
  3. Click New Folder, and enter a name like Mastering Configuration Manager. Click OK when finished.
  4. Open the newly created folder and click Upload File.
  5. Click Browse, select the report file, and click Open.
  6. You can type a new name for the report; click OK when finished.

    It is very likely that the data source specified in the report is not valid and needs to be changed after the import.

  7. Hover the pointer over the right side of the report to access the drop-down arrow, and select Manage.
  8. Select Data Sources.
  9. Select A Shared Data Source and click Browse.
  10. Navigate to the Configuration Manager site folder, and select the data source.
  11. Click OK.
  12. Back in Data Sources, click Apply.

The newly imported report is now ready for use and will also be imported into the Configuration Manager administrative console.

Exporting Reports

Exporting reports also requires that you use Report Manager and download the report as described here:

  1. Open a browser and connect to http://reportingserver/reports.
  2. Open the Configuration Manager folder.
  3. Navigate to the report you want to export.
  4. Open the report properties as explained previously in this chapter.
  5. Select Properties and click Download.
  6. Click Save, and save the RDL file to a location of your choice.

The Bottom Line

Install the Reporting Services point. Installing a Reporting Services site system within Configuration Manager allows not only administrators but everyone to view reports in some fashion via either different file formats or a direct link within the Report Manager Website.

  • Master It What is the procedure to enable Reporting with Configuration Manager?

Manage reporting security. Reporting security is an integrated part of the built-in security. You provide users with access to reports by adding them to a predefined security role or by creating a custom role with permissions to run or modify reports.

  • Master It Add users to a security role that is able to view reports.

Create and manage report subscriptions. Creating subscriptions can be very helpful in many scenarios. You can configure subscriptions from Report Manager or in the Configuration Manager console.

  • Master It Create an email-based subscription.

Create custom reports. You may find some scenarios where the included reports in Configuration Manager may not meet your reporting needs and you need to create a custom report.

  • Master It Create a custom report. Determine whether the query in the report should use table functions or views.