Introduction to SCCM Reporting
SCCM comes with so many great components. Today we are going to cover a little bit of System Center Reporting. The SCCM software itself comes with hundreds of default reports that are extremely helpful. They cover all the various features and functions of the product like asset management, software metering, compliance reporting, site information, user data, vulnerability assessments and many more.
SCCM stores its information about users, hardware, software, inventory, applications etc. in a relational database. All we need to do is to figure out how to get that information out of the SQL database and into an attractive, useful, and reusable report.
The default reports are displayed in the Configuration Manager console and further organized in subfolders based on the report category. Like most other Configuration Manager objects, you must have the appropriate permissions to run or modify reports. Configuration Manager reports are now fully enabled for role-based administration. The data for all reports included with Configuration Manager is filtered based on the permissions of the administrative user who runs the report. Administrative users with specific roles can only view information defined for their roles.
There are 3 basic types of reports you should be familiar with. They are The Summarized Data Report, The List Report, and The Detailed Report. These reports are rather self-explanatory by the names. These 3 reports are referred to as series reports. There are some design configurations that should be considered when putting together reports. Consistency is important. Page layouts should be logical and consistent between the screen and printed versions. Consider creating a template for each type of report. By using a template, it alleviates the necessity of recreating the entire page layout each time you create a new report and keeps the reports looking professional. When using a table style report it’s good to have alternating rows of color, normally gray and white, as it makes it easier to read. Reports should look the same in print version as they do on screen. Exporting your report to .PDF format is good for checking to make sure that the report displays properly in print. Exporting to Excel is useful for reports where you may need to manipulate the data. Another valuable feature of SQL Server Reporting services (SSRS) is that it provides subscriptions to reports so that administrators or managers can automatically receive an emailed report each day that perhaps details the status of an update roll out, or an application push. These reports can be exported in a variety of popular formats including Excel, CSV, and .PDF. We can also present our finished reports directly from the Reporting Services website called Report Manager, or users can view them directly within their web or Windows based applications. Reports can also be run from SharePoint.
There are tools used for building SSRS reports. We’ll look at two for now. One tool is SSDT-BI (SQL Server Data Tools-Business Intelligence) for Visual Studio. This software works best if you’re creating many reports and need to keep them organized and easily accessible. There is also Report Builder which is used to quickly create several basic feature reports. It may be more familiar to most people as it is used like most Microsoft Office products. We’ll compare the two below:
- Can only work on one report at a time
- Copy and paste is not available
- Creating “drill-through” links is very cumbersome, although it can be done
- There are some missing features and some features cannot be modified or controlled
- Can open and work on multiple reports at the same time
- Cut/copy and paste between reports
- “Drill-through” links can easily be created in reports
- Can control every feature and setting from within the report.
There are multiple versions of report authoring tools available and it’s important to create reports using a tool that generates files supporting the installed version of SQL Server Reporting Services version you are using.
An important part of writing useful reports is the ability to add parameters. This allows the report to prompt viewers for details. There are several different styles of parameters that can be used, including simple, dropdown, and multivalue. Parameters allow the report to adapt and tailor the data to specific information requested by the viewer. For instance, if you want to see a compliance report for different types of systems, you can just create one single report with a prompt to have the user specify whether they want to see a compliance report for servers, or just for desktops. This eliminates the need for two separate reports.
Configuration Manager uses WMI extensively for both client and server operations. The Configuration Manager client uses WMI for internal control of its own operations and for gathering hardware inventory. Configuration Manager also uses WMI as an interface to the site database. WMI provides its own query language that allows you to query managed objects as data providers. WMI Query Language (WQL) is essentially a subset of SQL (Structured Query Language) with minor semantic changes. WQL does have extensions that support WMI events and other features specific to WMI. WQL is the basis for Configuration Manager queries, whereas SQL is used for Configuration Manager reports.
System Center 2012 Configuration Manager queries SQL Server views in the Configuration Manager site database to retrieve the information that is displayed in reports. A Microsoft SQL Server view is a virtual table whose contents are based on the result from a SQL query. A view consists of a set of named columns and rows of data. The rows and columns of data come from tables or other SQL views referenced in the query that defines the view and are produced dynamically when the query is run. To create effective reports, accurate SQL statements based on the appropriate Configuration Manager views need to be used to retrieve the required data and to display the expected output. Knowing the Configuration Manager database view schema is an important first step in learning how to create these reports.
There are many Configuration Manager SQL view categories. I’ve listed a few below:
- Application Management views
- Client Deployment views
- Client Status views
- Collection Views
- Discovery views
- Endpoint Protection views
- Mobile Device Management views
- Operating System Deployment views
I hope you enjoyed this introduction to SCCM Reporting. I strongly suggest spending time to learn the basics of report design. Many new SCCM admins don’t spend enough time on the basics such as templates, styles, and functionality before diving into advanced reporting. It’s important to understand what you want to create, how you will create it and how you want your viewers to interact with it. As you start to get comfortable with reporting, your reports will soon have some great features like drill-through menus, interactive sorting, charts and more.