System Center Configuration Manager Reporting Unleashed: (Unleashed)

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.

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

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.