System Center Configuration Manager Reporting Unleashed

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.

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