System Center Configuration Manager Reporting Unleashed

Description

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'sSystem 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.

下载

Sample Files

在线内容available to provide added value to readers ofSystem Center Configuration Manager Reporting Unleashed. Some of the files available include XML export files, sample SQL queries, and sample reports. Refer to Appendix C in your book for specific information about what files are available.

Download Sample Files(275 KB .zip)

Sample Content

Sample Pages

Download the sample pages(includes Chapter 3 and Index)

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
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
使用ResourceID列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
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
设置the Size of a Report 173
Accessing Report Items 175
添加a Table to a Report 177
Previewing a Report 180
Customizing a Report 181
添加Background Color to Rows 181
Alternating Data Row Colors 183
添加Interactive Sorting 184
设置the Table Header Row to Repeat on All Pages 187
添加a Page Header to a Report 188
添加a Page Footer to a Report 191
添加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
添加a Simple Prompt 210
添加a Dropdown Prompt 213
添加a Multi-Value Parameter 217
添加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
添加a Chart to a Report 221
Customizing Chart Items 225
添加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
添加a Chart to the Patch Compliance Progression Report 238
添加a Chart to the Computer Hardware Information Report 239
Summary 240
Chapter 8 SSRS Reporting Features 241
Using Report Drillthroughs 242
添加an Action on a Table Cell’s Value 242
添加Hyperlinks to a Report Item 246
添加a Subreport Item 247
Creating Custom Color Palettes 258
Available Out-of-the-Box Color Palettes 259
设置Custom Color Codes 259
设置Custom Color Codes by Values 263
添加Reports to SSRS 268
Manually Adding a Report to SSRS 268
出版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
出版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
添加the DLL to a Report 296
添加an RBA Dataset 298
添加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
添加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
添加a Chart to the Patch Compliance Progression Report 341
添加a Chart to the Computer Hardware Information Report 343
Chapter 8 Demonstration Outcomes 343
添加a Patch Compliance Progression Drillthrough Series 343
添加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
出版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
353年transact - sql底漆
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

Updates

Submit Errata