System Center Configuration Manager Reporting Unleashed / Edition 1

by Garth Jones, Dan Toll, Kerrie Meyler

Overview

Microsoft System Center Configuration Manager’s SQL Server database contains valuable information about your users, computers, hardware, operating systems, applications, compliance status, and much more. Microsoft has provided excellent tools for extracting this information in meaningful ways, including SQL Server Reporting Services (SSRS) and SQL Server Data Tools Business Intelligence (SSDT-BI). System Center Configuration Manager Reporting Unleashed shows you how to make the most of these tools.

World-renowned System Center reporting guru Garth Jones and his expert coauthors guide you through all facets of custom reporting with System Center. You’ll walk through installing and configuring SSRS, using SQL views to find the data you need, writing SQL queries, creating basic and advanced reports, and using role-based administration to securely deliver those reports to the correct individuals.

Jones brings together reliable, comprehensive, and up-to-date System Center reporting techniques you’ll find in no other book or website. Using this guide, you can consistently retrieve the right information to solve immediate problems and quickly respond to management concerns.

Detailed information on how to…
• Install and configure SQL SSRS for optimal System Center reporting and easier troubleshooting
• Understand the data stored in the ConfigMgr site database
• Efficiently retrieve ConfigMgr data by writing SQL queries in SQL Server Management Studio
• Learn best practices for developing and designing System Center reports
• Create report templates, customize content with report parameters, and embed charts
• Customize logos, color palettes, and other report elements for your own organization
• Construct advanced drillthroughs to provide deeper understanding
• Strengthen report security by integrating ConfigMgr role-based administration into SQL queries
• Leverage reporting to measure KPIs and gain a better understanding of your environment
• Tailor your reports to the needs of end-users or management
• Foreword by Wally Mead, Principal Program Manager, Cireson

The only book entirely dedicated to Configuration Manager reporting, this guide complements Meyler's System Center 2012 Configuration Manager Unleashed, offering far more in-depth coverage of reporting than the single chapter in that book. Most of the content in this new guide will be equally valuable in both System Center 2016 and 2012 environments.

Product Details

ISBN-13:	9780672337789
Publisher:	Sams
Publication date:	05/09/2016
Series:	Unleashed Series
Pages:	416
Product dimensions:	7.00(w) x 9.00(h) x 1.20(d)

About the Author

Garth Jones, System Center Enterprise Client Manager MVP, is chief architect at Enhansoft, an Ottawa-based company that develops products and services to extend the value of System Center Configuration Manager. Garth started working with the product in 1996, when it was known as SMS. He is the founder of the Ottawa Windows Server User Group and its associated study group. In addition to being an active participant in Microsoft’s forums, Garth also participates on OWSUG.ca, SMSug.ca, FAQshop.com, and myITForum.com. He has presented at OWSUG, MMS, EnergizeIT, ITProTeach, and Techdays. Garth also creates webcasts for SMSUG.ca and myITForum.com.

Dan Toll, a System Center Configuration Manager administrator, has worked with different versions of the product starting with SMS 2003. Dan specializes in OS deployment for workstations and servers using Microsoft Deployment Toolkit (MDT) with ConfigMgr, as well as Configuration Manager reporting. In his current job, Dan is the SME for Configuration Manager as well as the client computing environment, which includes deployment, functionality, and maintenance of systems; their operating systems; applications; patching compliancy; and reporting.

Kerrie Meyler, System Center Cloud and Datacenter Management MVP, is the lead author of numerous System Center books in the Unleashed series, including System Center 2012 Configuration Manager Unleashed (2012), System Center 2012 R2 Configuration Manager Unleashed Supplement (2014), System Center 2012 Service Manager Unleashed (2014), System Center 2012 Operations Manager Unleashed (2013), and System Center 2012 Orchestrator Unleashed (2013). She is an independent consultant with more than 17 years of information technology experience. Kerrie was responsible for evangelizing SMS while a senior technology specialist at Microsoft and has presented on System Center technologies at TechEd and MMS.

Table of Contents

Foreword xiii
Introduction 1

PART I: INSTALLING AND CONFIGURING SSRS FOR CONFIGURATION MANAGER
Chapter 1 Installing SQL Server Reporting Services 7
Installing the SQL SSRS Component 8
Configuring SSRS 16
Configuring the SSRS Installation 16
Configuring Email 24
Enabling Remote Errors 26
Installing Client Tools for SSRS Reporting 31
Installing Client Tools 31
Installing SQL Server Data Tools Business Intelligence 38
Summary 41
Chapter 2 Installing and Configuring Configuration Manager Reporting 43
Creating the Reporting Services Point 44
Using ConfigMgr Security Reporting Roles 52
Creating a Security Reporting Role 52
Restricting Access to Reports 57
Removing a Security Role 58
Exporting and Importing Security Roles 58
Working with Security Roles 62
Applying a Security Role to a Security Group 62
Removing Permissions 70
Accessing ConfigMgr Reports 71
Changing the ConfigMgr Report Logo 72
Demonstrating Creating and Assigning ConfigMgr Security Roles 75
Working with the Report Reader Security Role 75
Working with the Software Updates Report Reader Security Role 76
Working with the Inventory Report Reader Security Role 76
Summary 77
PART II: ABOUT DATA AND RETRIEVAL
Chapter 3 Understanding Configuration Manager Data 81
Using Data Classes and SQL Views 81
Using Discovery Classes 82
Using Hardware Inventory Classes 84
Using Software Inventory Classes 86
Using Software Update Inventory Classes 87
Using Software Metering Inventory Classes 89
Using Status Message Classes 90
Using State Messages 91
Using Collection Data Classes 92
Using Tools for Creating Reports 93
Introducing SQL Server Management Studio 94
Connecting to the ConfigMgr Database Server 94
Executing a Query in SQL Server Management Studio 95
Using the Object Explorer 95
Demonstrating SQL Server Management Studio 97
Creating and Executing a Query 98
Looking at Views 98
Summary 98
Chapter 4 Transact-SQL Primer 99
Understanding the Basic SQL Sections in a Query 100
Using the SELECT Statement 101
Using the FROM Statement 101
Defining Criteria with the WHERE Statement 102
Using ORDER BY 102
Using Secondary Operators 103
Using the DISTINCT Keyword 103
The GROUP BY Operator 104
Using SQL View Aliases 104
Using Operators 105
Column Naming in Query Results 106
Understanding Aggregate Functions 107
Using the COUNT Function 108
Using the MIN (Minimum) Function 108
Using the AVG (Average) Function 108
Using the MAX (Maximum) Function 108
Using the SUM Function 108
Understanding Date and Time Functions 109
Understanding Date Parts 109
Using Common Date and Time Functions 109
Transforming Data Stored in the ConfigMgr Database 111
Using the CASE Function 111
Converting Data with the CONVERT and CAST Functions 112
Using the ISNULL Function 114
Understanding the SQL JOIN Statement 115
What Is a JOIN? 116
Using the ResourceID Column 116
Using the v_R_System_Valid View 117
Explaining the JOIN Statements 118
Demonstrating SQL Operators 125
Running the Sample SQL File 125
Editing the Sample Query 126
Creating a Query with Aliases 127
Demonstrating Aggregate Functions 127
Creating a New Query 127
Using the COUNT Function 128
Demonstrating Date and Time Functions 128
Demonstrating Data Transforms 129
Applying the CASE Function 129
Applying the CAST and CONVERT Functions 130
Applying the ISNULL Function 130
Demonstrating JOIN Statements 130
Executing an Inefficient Query 131
Writing an Efficient Query 131
Working with the Different JOIN Statements 131
Summary 131
PART III: USING SSRS FOR CONFIGURATION MANAGER REPORTING
Chapter 5 Basic Report Design 135
Understanding the Purpose of a Report Series 136
Using a Summarized Data Report 136
Using a List Report 138
Using a Detailed PC Report 139
Understanding Report Design Considerations 140
Providing a Consistent Look and Feel 140
Printed Versus Interactive Reports 142
The Benefits of Using Report Templates 142
The Ability to Export Reports 144
Using Custom Report Requests 146
Gathering Report Information 146
Questioning Report Requirements 156
Report Verification and Delivery 157
Report Life Cycle and Maintenance 159
Summary 161
Chapter 6 Building a Basic Report 163
Creating a Project 165
Creating a Basic Report 168
Creating a Data Source 170
Creating a Dataset 171
Setting the Size of a Report 173
Accessing Report Items 175
Adding a Table to a Report 177
Previewing a Report 180
Customizing a Report 181
Adding Background Color to Rows 181
Alternating Data Row Colors 183
Adding Interactive Sorting 184
Setting the Table Header Row to Repeat on All Pages 187
Adding a Page Header to a Report 188
Adding a Page Footer to a Report 191
Adding an Image to a Report 193
Demonstrating Basic Report Creation 195
Creating a New Project 195
Creating a Patch Compliance Progression Report 196
Creating a Computer Hardware Information Report 196
Summary 197
Chapter 7 Intermediate Reporting Concepts 199
Creating Report Templates 200
Creating a Report Template (8.5×11in) 200
Creating a New Report from a Template 204
Exporting Reports from SSDT-BI 206
Modifying the Page Size of an Existing Report 207
Using Report Parameters 209
Understanding the Value and Label Fields 209
Using Default Values 209
Understanding Cascade Prompts 210
Using Multi-Value Parameters 210
Adding a Simple Prompt 210
Adding a Dropdown Prompt 213
Adding a Multi-Value Parameter 217
Adding a Chart Item to a Report 220
Using Column Charts 220
Using Bar Charts 220
Using Line Charts 220
Using Pie Charts 221
Using Doughnut Charts 221
Using Other Chart Types 221
Adding a Chart to a Report 221
Customizing Chart Items 225
Adding an Action to a Chart 233
Demonstrating Template Creation 236
Creating Template Reports 236
Modifying Page Sizes for Existing Reports 237
Demonstrating Report Parameters 237
Updating the Patch Compliance Progression Report 237
Updating the Computer Hardware Information Report 238
Demonstrating Adding Chart Items 238
Adding a Chart to the Patch Compliance Progression Report 238
Adding a Chart to the Computer Hardware Information Report 239
Summary 240
Chapter 8 SSRS Reporting Features 241
Using Report Drillthroughs 242
Adding an Action on a Table Cell’s Value 242
Adding Hyperlinks to a Report Item 246
Adding a Subreport Item 247
Creating Custom Color Palettes 258
Available Out-of-the-Box Color Palettes 259
Setting Custom Color Codes 259
Setting Custom Color Codes by Values 263
Adding Reports to SSRS 268
Manually Adding a Report to SSRS 268
Publishing Reports from SSDT-BI to the SSRS Website 273
Creating Report Subscriptions 277
Demonstrating Report Drillthroughs 282
Patch Compliance Progression Drillthrough Series 282
Computer Hardware Information Drillthrough Series 283
Demonstrating Custom Color Palettes 284
Applying a Custom Color Palette to Cycle Through Color Codes 284
Applying a Custom Color Palette Based on Values 284
Demonstrating Adding Reports to SSRS 285
Manually Adding Reports to the SSRS Website 285
Publishing Reports to SSRS 285
Creating a Subscription 286
Summary 286
Chapter 9 Role-Based Administration and Reporting 287
How Role-Based Administration and Reporting Work 288
Using SQL Queries with RBA 289
Converting a SQL Query to a ConfigMgr RBA SQL Query 289
Testing a ConfigMgr RBA SQL Query 292
Using Reports and RBA 295
Updating SSDT-BI for RBA 295
Adding the DLL to a Report 296
Adding an RBA Dataset 298
Adding RBA Prompts to a Report 300
RBA Tips and Troubleshooting 305
RBA SQL Query Performance 305
Troubleshooting and Errors 309
Demonstrating Creating an RBA Report 315
Converting the Patch Compliance Progression Query to an RBA Query and Testing It 315
Converting the Patch Compliance Progression Report to an RBA Report 315
Converting the Computer Hardware Information Query to an RBA Query and Testing It 316
Adding a Chart to the Computer Hardware Information Report 316
Summary 316
PART IV: APPENDIXES
Appendix A Glossary 319
Glossary of Terms 319
Appendix B Demonstration Outcomes 323
Chapter 2 Demonstration Outcomes 323
Working with the Report Reader Security Role 323
Working with the Software Updates Report Reader Security Role 324
Working with the Inventory Report Reader Security Role 325
Chapter 3 Demonstration Outcomes 326
Creating and Executing a Query 326
Looking at Views 327
Chapter 4 Demonstration Outcomes 328
Demonstrating SQL Operators 328
Working with Aggregate Functions 330
Working with Date and Time Functions 332
Working with Data Transforms 333
Executing an Inefficient Query 336
Writing an Efficient Query 336
Chapter 6 Demonstration Outcomes 338
Creating a New Project 338
Creating a Patch Compliance Progression Report 338
Creating a Computer Hardware Information Report 338
Chapter 7 Demonstration Outcomes 340
Creating Template Reports 340
Modifying Page Sizes for Existing Reports 340
Updating the Patch Compliance Progression Report 341
Updating the Computer Hardware Information Report 341
Adding a Chart to the Patch Compliance Progression Report 341
Adding a Chart to the Computer Hardware Information Report 343
Chapter 8 Demonstration Outcomes 343
Adding a Patch Compliance Progression Drillthrough Series 343
Adding a Computer Hardware Information Drillthrough Series 344
Applying a Custom Color Palette to Cycle Through Color Codes 345
Applying a Custom Color Palette Based on Values 345
Manually Adding Reports to the SSRS Website 345
Publishing Reports to SSRS 347
Creating a Subscription 348
Chapter 9 Demonstration Outcomes 348
Appendix C Available Online 351
Creating and Assigning ConfigMgr Security Roles 351
Understanding Configuration Manager Data 352
Transact-SQL Primer 353
Basic Report Design 355
Building a Basic Report 355
Intermediate Reporting Concepts 355
SSRS Reporting Features 357
Role-Based Administration and Reporting 358
Index 361