Chapter 18. Reporting in ConfigMgr
Everything you do in Configuration Manager generates data. Collections, deployments, inventories, and everything else generates data, which is written into the ConfigMgr database, ready for use.
At some point, you’re going to want to query that data for business reasons: How many of the managed systems received the latest updates? Have there been any antimalware outbreaks in the last week? You can use the console to access this data, but what about nontechnical users who need report data, or setting up processes for automatically generating relevant reports on a schedule? This is where Configuration Manager reporting comes into play.
As shown in figure 18.1, this chapter is all about enabling reporting within ConfigMgr, working with reports, scheduling them, and creating custom reports.
Figure 18.1. Reports, reports, and more reports!
By the end of this chapter, you’ll have configured your lab environment for reporting and you’ll have a solid understanding of how ConfigMgr reporting works and can be customized for your own purposes.
18.1. Enabling Reporting Services
To be able to use reports in your ConfigMgr environment, like the one shown in figure 18.2, you first have to enable the reporting services point.
Figure 18.2. Reports give you easy access to ConfigMgr data.
This is a ConfigMgr server role that uses SQL Server Reporting Services (SSRS), which is already installed on CM01 (this happened during the initial virtual machine build).
You can use the installation of SQL Server Management Studio on CM01 to have a dig around in SSRS, although at this stage there’s not much to see.
On CM01, open SQL Server Management Studio, and when the Connect to Server dialog box opens, change the server type from “Database Engine” to “Reporting Services.” Leave all other options the same.
The only node of reporting services on CM01 that has anything in it is Security; notice that the roles are different from those found in a SQL database server.
For more information on SSRS, spend a bit of time browsing through this MSDN article: https://msdn.microsoft.com/en-us/library/ms159106.aspx.
To install the reporting services point(RSP), do the following:
1. In the ConfigMgr console, navigate to Administration > Site Configuration > Servers and Site System Roles.
2. Right-click “CM01” and select “Add Site System Roles.”
3. Click through to the “System Role Selection” page, and then tick “Reporting services point.”
4. On the “Reporting services point configuration” page, click “Verify” to check the connection to the SSRS instance on CM01.
5. Leave the folder name and reporting services server instance name the same. For the username, select Set > New Account > MOLCM_SR. The configuration should look like figure 18.3.
Figure 18.3. The configuration for the reporting services point installation
Important Note
In a lab environment, you could use an existing account, such as the network access account, as the reporting services account. Best practice is to have a dedicated account for each discrete ConfigMgr role, so that there’s no overlap of security boundaries and you minimize the risk of too few accounts having too many privileges.
6. Click through to the end of the wizard, and the RSP installation will commence in the background.
Which Log?
The installation of the RSP gets logged to srsrpMSI.log and srsrpsetup.log, respectively. After the RSP is up and running, ConfigMgr populates the SSRS instance with all of the included reports; this activity gets logged to srsrp.log, and the process takes a few minutes to complete.
After the reports have been copied to the reporting point, you can check out the new reports in the console by navigating to Monitoring > Reporting > Reports, as shown in figure 18.4.
Figure 18.4. You’re now ready to run reports.
Expand the Reports node and you’ll see a load of new organizational folders. Select the topmost node (Reports) and you’ll get a full list of the 468 built-in reports that are now available in the RSP. Now you can start using reports.
18.2. Executing reports
Executing a report from the console is a simple process: find the report you’re interested in, right-click the report, and select “Run.” Easy, right?
Let’s take a report that most admins and managers are interested in as an example: software update compliance (indicating the number of clients that have installed the updates deployed to them). To run this report, do the following:
1. Navigate to the report folder Software Updates – A Compliance.
2. Right-click the report “Compliance 1 – Overall compliance” and select “Run.”
3. As shown in figure 18.5, the report requires parameters in order to run. Select “MoL - Windows 10 Updates” for the “Update Group,” and “PS100014 - All Windows 10 Clients” for the “Collection.”
Figure 18.5. Select the input parameters in order to run the compliance report.
4. Select “View Report” to execute the report with the selected parameters.
5. As shown in figure 18.6, the report renders with a breakdown of the compliant and noncompliant systems in the specified collection. You have only a single system in your lab, and at this stage it doesn’t matter whether it’s compliant.
Figure 18.6. A report showing the state of compliance in your lab environment
6. Note that the entries under “State” are underlined. These are hyperlinks, which are links to existing, predefined reports. Click either “Compliant” or “Noncompliant” (depending on your report).
7. This causes a new report to be rendered: Compliance 7 – Computers in a specific compliance state for an update group. The report has been automatically populated with the parameters you selected for the first report, with the addition of the compliance state as an input.
8. The device name (CLIENT01) is also a hyperlink to another report. Click the name, and a new report is rendered: Compliance 5 – Specific computer. This report gives a full rundown of every update that has been deployed against the system and the corresponding compliance state. Note that you can narrow the results further by specifying the Vendor and/or Classification.
This shows the power of ConfigMgr’s reporting model. The built-in reports are linked in such a way that you can start your report at a high level and then drill down as necessary. You could have run either of the linked compliance reports directly if you knew exactly what information you were after. This is handy for users who need to view ConfigMgr data but who don’t have in-depth knowledge of the state of the current environment.
On that note, how would nonadministrative users access ConfigMgr reports? You don’t want to have to hand out remote logon access to the server, or deploy the console to every person who might want to run a report.
The good news is that you don’t have to. Because ConfigMgr reporting is built on top of SSRS, all of the reports are automatically available via a browser. To see this in action, do the following:
1. Log on to CLIENT01 as MOLAdministrator and open Internet Explorer.
2. Navigate to http://cm01/Reports, and when the SQL Server Reporting Services page loads, select the ConfigMgr_PS1 folder.
3. Select “Details View” (top-right corner) to change the view away from the tiled layout (easier to read).
4. In the “Search” field, enter Compliance 1. There will be two results: the report you want, and Compliance 7 (which refers to Compliance 1 in its description). Click “Compliance 1 – Overall compliance” to run the report.
5. Drill down through the reports as you did in the console.
Tip
When running web reports, it’s unclear where to change the parameter values. At the top of the report, directly underneath the report path is an arrow that triggers Show/Hide Parameters, as shown in figure 18.7. Select that and you’ll see the report’s parameter fields.
Figure 18.7. Use this drop-down to show or hide report parameters.
So now that you have your reports and know how to access them, what do you do with them?
18.3. Subscribing to reports
Reports give you the ability to quickly access comprehensive data about your ConfigMgr environment. You won’t need to frequently access some of these reports, whereas you should run others regularly so that you have a constant view of what’s happening day to day. This is where subscriptions are incredibly valuable.
Subscriptions allow you to automatically run a report on a schedule, and deliver it via various mechanisms so that you always have access to the latest report data without having to run each report manually. A good example of this is anti-malware definitions—a report that runs daily. Viewing the compliance state of your anti-malware agents will alert you to any issues before they become real problems.
To create a new report subscription, do the following:
1. In the console, navigate to Monitoring > Reporting > Reports > Software Updates – A Compliance.
2. Right-click the report “Compliance 1 – Overall compliance” and select “Create Subscription.”
3. For the Subscription Delivery options, use the information in table 18.1 to populate the subscription parameters.
Table 18.1. Subscription delivery options
Delivery method option
Option value
Report delivered by Windows File Share File name Windows10UpdateCompliance Add file extension when created Ticked Path \CM01Reports Render format Xml file with report data User Name MOLAdministrator Password Overwrite option Increment file names as newer files are added Note
Creating a new report subscription requires you to create a new folder on CM01 called E:Reports. Share the folder as reports so that the UNC path \CM01Reports is valid.
4. Click “Next” and create a Subscription schedule with the following parameters:
- Weekly
- Repeat after 1 week
- On day: Monday
- Start time: 8:30 a.m.
5. Click “Next” and specify the following report parameters:
- Update Group: MoL - Windows 10 Updates
- Collection: PS100014 - All Windows 10 Clients
6. Click through to the end, and you’ve created a new subscription.
7. Navigate to Reports > Subscriptions, and you’ll see the newly created subscription.
Based on the schedule you created, an XML file is created in \CM01Reports each week—which is great, but what if you wanted something a little more convenient?
Subscriptions can be delivered to your mail inbox, but before this is available as a delivery option, you have to give ConfigMgr the ability to talk to a mail server. To enable this functionality, do the following:
1. On CM01, go to Start > Reporting Services Configuration Manager.
2. Connect to CM01MSSQLSERVER and navigate to Email Settings.
3. Enter a sender address and the name of a valid Simple Mail Transfer Protocol (SMTP) server. Select “Apply” and close the RS Configuration Manager.
4. Back in the ConfigMgr console, go to Monitoring > Reporting > Subscriptions.
5. Right-click the subscription and select “Properties.” Change the “Report delivered by” option from “Windows File Share” to “Email.”
You can now send out regular reports by email, to yourself or to whoever else in the organization needs a regular update on what’s happening in ConfigMgr.
The email settings options in SSRS don’t give you many options to work with. It assumes that the SMTP server is an on-premises mail server that you can configure to allow mail relay from the SSRS server. In many organizations, this may indeed be the case, but not always and almost certainly not in a lab environment.
You can configure Internet Information Services (IIS) on Windows Server to act as a simple mail relay for SMTP. You can configure your application to talk directly to the local SMTP service, and then the service handles the communication with the SMTP server that you want to use.
For example, Office 365 and Google Mail both provide SMTP services, but you would need to configure your own mail relay locally in order to use them with SSRS. Read this TechNet article for a more detailed walk-through: https://technet.microsoft.com/en-us/library/dn592151(v=exchg.150).aspx
You’ve seen how to work with the built-in ConfigMgr reports, but what if you need something a bit more customized?
18.4. Building custom reports
The built-in reports in ConfigMgr are designed to cover a wide range of reporting scenarios, but there will always be a requirement for custom reporting. Different users within different companies are interested in lots of different types of data, presented in a variety of ways, and the built-in reports can’t possibly cater to every scenario.
Because ConfigMgr reporting is built upon SSRS, with a bit of knowledge you can easily create your own custom reports to meet any internal business need.
For example, let’s look at the endpoint report “Antimalware overall status and history.” This gives you the state of your SCEP clients over a given period of time, usually one week. This is a useful report to schedule because it can provide a running commentary on the health of your anti-malware deployment, making you quickly aware of potential problems. The issue with this report is that if you go to schedule it, you’ll find that the input parameters for the report include the start and end dates. This is fine for a one-off report, but if you schedule the report, you would want to specify a dynamic time frame (for example, “Last 7 days”). Unfortunately, the built-in report doesn’t give you that option. This is where creating a custom report will deliver the result you need.
To write a custom report, do the following:
1. Choose Monitoring > Reporting > Reports > Endpoint Protection.
2. Right-click “Antimalware overall status and history” and select “Edit.”
3. This causes SQL Server Report Builder to download (from SSRS) and run, as shown in figure 18.8.
Figure 18.8. Using SQL Server Report Builder to create a custom ConfigMgr report
4. On the left side under “Report Data,” expand “Parameters” and select “StartDate.”
5. Right-click “StartDate” and select “Parameter Properties.”
6. On the General tab, change the “Select parameter visibility” option from “Visible” to “Hidden.”
7. On the Available Values tab, select “Get values from a query”:
- Dataset: DateRange
- Value field: StartDate
- Label field: StartDate
8. Click “OK” to save the changes, and then repeat the process with the EndDate parameter:
- Dataset: DateRange
- Value field: EndDate
- Label field: EndDate
9. Select the SQL Server image in the top-left corner and choose “Save As.”
10. Save the report in the original folder as Antimalware overall status and history - Last 7 days and close Report Builder.
11. Go back into the console and refresh the reports in the Endpoint Protection folder; you’ll see the newly created report.
12. Run the report. The only parameter you need to input is the Collection. The start and end date fields are hidden and automatically populated from the DateRange dataset. You can create a subscription to this report, and the start and end dates will be calculated based on the day on which the report runs.
Tip
Whenever you’re working with a built-in resource such as a report (or any resource in any application), it’s best practice to make a copy and save all your changes to the copy. That way, you’ll always have an untouched original, in case you ever need to roll back custom changes.
Congratulations! You’ve successfully created a custom SQL report, and you’re finished with reporting.
18.5. Labs
As you saw earlier, SSRS doesn’t have a complex system for sending email. For this lab, try configuring the installation of IIS on CM01 to support SMTP relay. Then configure that SMTP instance to talk to an external SMTP service, such as Office 365, Google Mail, or your own ISP. Finally, configure both SSRS and Configuration Manager (Administration > Site Configuration > Sites > PS1 > Configure Site Components > Email Notification) to send email via the SMTP service on CM01.