You are previewing System Center Configuration Manager Reporting Unleashed.
O'Reilly logo
System Center Configuration Manager Reporting Unleashed

Book 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'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

  1. About This E-Book
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Table of Contents
  6. About the Authors
  7. Dedication
  8. Acknowledgments
  9. Foreword
  10. We Want to Hear from You!
  11. Reader Services
  12. Introduction
    1. Part I: Installing and Configuring SSRS for Configuration Manager
    2. Part II: About Data and Retrieval
    3. Part III: Using SSRS for Configuration Manager Reporting
    4. Part IV: Appendixes
    5. Disclaimers and Fine Print
  13. Part I: Installing and Configuring SSRS for Configuration Manager
    1. Chapter 1. Installing SQL Server Reporting Services
      1. Installing the SQL SSRS Component
      2. Configuring SSRS
        1. Configuring the SSRS Installation
        2. Configuring Email
        3. Enabling Remote Errors
      3. Installing Client Tools for SSRS Reporting
        1. Installing Client Tools
        2. Installing SQL Server Data Tools Business Intelligence
      4. Summary
    2. Chapter 2. Installing and Configuring Configuration Manager Reporting
      1. Creating the Reporting Services Point
      2. Using ConfigMgr Security Reporting Roles
        1. Creating a Security Reporting Role
        2. Restricting Access to Reports
        3. Removing a Security Role
        4. Exporting and Importing Security Roles
      3. Working with Security Roles
        1. Applying a Security Role to a Security Group
        2. Removing Permissions
      4. Accessing ConfigMgr Reports
      5. Changing the ConfigMgr Report Logo
      6. Demonstrating Creating and Assigning ConfigMgr Security Roles
        1. Working with the Report Reader Security Role
        2. Working with the Software Updates Report Reader Security Role
        3. Working with the Inventory Report Reader Security Role
      7. Summary
  14. Part II: About Data and Retrieval
    1. Chapter 3. Understanding Configuration Manager Data
      1. Using Data Classes and SQL Views
        1. Using Discovery Classes
        2. Using Hardware Inventory Classes
        3. Using Software Inventory Classes
        4. Using Software Update Inventory Classes
        5. Using Software Metering Inventory Classes
        6. Using Status Message Classes
        7. Using State Messages
        8. Using Collection Data Classes
      2. Using Tools for Creating Reports
        1. Introducing SQL Server Management Studio
        2. Connecting to the ConfigMgr Database Server
        3. Executing a Query in SQL Server Management Studio
        4. Using the Object Explorer
      3. Demonstrating SQL Server Management Studio
        1. Creating and Executing a Query
        2. Looking at Views
      4. Summary
    2. Chapter 4. Transact-SQL Primer
      1. Understanding the Basic SQL Sections in a Query
        1. Using the SELECT Statement
        2. Using the FROM Statement
        3. Defining Criteria with the WHERE Statement
        4. Using ORDER BY
      2. Using Secondary Operators
        1. Using the DISTINCT Keyword
        2. The GROUP BY Operator
        3. Using SQL View Aliases
        4. Using Operators
        5. Column Naming in Query Results
      3. Understanding Aggregate Functions
        1. Using the COUNT Function
        2. Using the MIN (Minimum) Function
        3. Using the AVG (Average) Function
        4. Using the MAX (Maximum) Function
        5. Using the SUM Function
      4. Understanding Date and Time Functions
        1. Understanding Date Parts
        2. Using Common Date and Time Functions
      5. Transforming Data Stored in the ConfigMgr Database
        1. Using the CASE Function
        2. Converting Data with the CONVERT and CAST Functions
        3. Using the ISNULL Function
      6. Understanding the SQL JOIN Statement
        1. What Is a JOIN?
        2. Using the ResourceID Column
        3. Using the v_R_System_Valid View
        4. Explaining the JOIN Statements
      7. Demonstrating SQL Operators
        1. Running the Sample SQL File
        2. Editing the Sample Query
        3. Creating a Query with Aliases
      8. Demonstrating Aggregate Functions
        1. Creating a New Query
        2. Using the COUNT Function
      9. Demonstrating Date and Time Functions
      10. Demonstrating Data Transforms
        1. Applying the CASE Function
        2. Applying the CAST and CONVERT Functions
        3. Applying the ISNULL Function
      11. Demonstrating JOIN Statements
        1. Executing an Inefficient Query
        2. Writing an Efficient Query
        3. Working with the Different JOIN Statements
      12. Summary
  15. Part III: Using SSRS for Configuration Manager Reporting
    1. Chapter 5. Basic Report Design
      1. Understanding the Purpose of a Report Series
        1. Using a Summarized Data Report
        2. Using a List Report
        3. Using a Detailed PC Report
      2. Understanding Report Design Considerations
        1. Providing a Consistent Look and Feel
        2. Printed Versus Interactive Reports
        3. The Benefits of Using Report Templates
        4. The Ability to Export Reports
      3. Using Custom Report Requests
        1. Gathering Report Information
        2. Questioning Report Requirements
        3. Report Verification and Delivery
        4. Report Life Cycle and Maintenance
      4. Summary
    2. Chapter 6. Building a Basic Report
      1. Creating a Project
      2. Creating a Basic Report
        1. Creating a Data Source
        2. Creating a Dataset
        3. Setting the Size of a Report
        4. Accessing Report Items
        5. Adding a Table to a Report
        6. Previewing a Report
      3. Customizing a Report
        1. Adding Background Color to Rows
        2. Alternating Data Row Colors
        3. Adding Interactive Sorting
        4. Setting the Table Header Row to Repeat on All Pages
        5. Adding a Page Header to a Report
        6. Adding a Page Footer to a Report
        7. Adding an Image to a Report
      4. Demonstrating Basic Report Creation
        1. Creating a New Project
        2. Creating a Patch Compliance Progression Report
        3. Creating a Computer Hardware Information Report
      5. Summary
    3. Chapter 7. Intermediate Reporting Concepts
      1. Creating Report Templates
        1. Creating a Report Template (8.5×11in)
        2. Creating a New Report from a Template
        3. Exporting Reports from SSDT-BI
        4. Modifying the Page Size of an Existing Report
      2. Using Report Parameters
        1. Understanding the Value and Label Fields
        2. Using Default Values
        3. Understanding Cascade Prompts
        4. Using Multi-Value Parameters
        5. Adding a Simple Prompt
        6. Adding a Dropdown Prompt
        7. Adding a Multi-Value Parameter
      3. Adding a Chart Item to a Report
        1. Using Column Charts
        2. Using Bar Charts
        3. Using Line Charts
        4. Using Pie Charts
        5. Using Doughnut Charts
        6. Using Other Chart Types
        7. Adding a Chart to a Report
        8. Customizing Chart Items
        9. Adding an Action to a Chart
      4. Demonstrating Template Creation
        1. Creating Template Reports
        2. Modifying Page Sizes for Existing Reports
      5. Demonstrating Report Parameters
        1. Updating the Patch Compliance Progression Report
        2. Updating the Computer Hardware Information Report
      6. Demonstrating Adding Chart Items
        1. Adding a Chart to the Patch Compliance Progression Report
        2. Adding a Chart to the Computer Hardware Information Report
      7. Summary
    4. Chapter 8. SSRS Reporting Features
      1. Using Report Drillthroughs
        1. Adding an Action on a Table Cell’s Value
        2. Adding Hyperlinks to a Report Item
        3. Adding a Subreport Item
      2. Creating Custom Color Palettes
        1. Available Out-of-the-Box Color Palettes
        2. Setting Custom Color Codes
        3. Setting Custom Color Codes by Values
      3. Adding Reports to SSRS
        1. Manually Adding a Report to SSRS
        2. Publishing Reports from SSDT-BI to the SSRS Website
        3. Creating Report Subscriptions
      4. Demonstrating Report Drillthroughs
        1. Patch Compliance Progression Drillthrough Series
        2. Computer Hardware Information Drillthrough Series
      5. Demonstrating Custom Color Palettes
        1. Applying a Custom Color Palette to Cycle Through Color Codes
        2. Applying a Custom Color Palette Based on Values
      6. Demonstrating Adding Reports to SSRS
        1. Manually Adding Reports to the SSRS Website
        2. Publishing Reports to SSRS
        3. Creating a Subscription
      7. Summary
    5. Chapter 9. Role-Based Administration and Reporting
      1. How Role-Based Administration and Reporting Work
      2. Using SQL Queries with RBA
        1. Converting a SQL Query to a ConfigMgr RBA SQL Query
        2. Testing a ConfigMgr RBA SQL Query
      3. Using Reports and RBA
        1. Updating SSDT-BI for RBA
        2. Adding the DLL to a Report
        3. Adding an RBA Dataset
        4. Adding RBA Prompts to a Report
      4. RBA Tips and Troubleshooting
        1. RBA SQL Query Performance
        2. Troubleshooting and Errors
      5. Demonstrating Creating an RBA Report
        1. Converting the Patch Compliance Progression Query to an RBA Query and Testing It
        2. Converting the Patch Compliance Progression Report to an RBA Report
        3. Converting the Computer Hardware Information Query to an RBA Query and Testing It
        4. Adding a Chart to the Computer Hardware Information Report
      6. Summary
  16. Part IV: Appendixes
    1. Appendix A. Glossary
      1. Glossary of Terms
    2. Appendix B. Demonstration Outcomes
      1. Chapter 2 Demonstration Outcomes
        1. Working with the Report Reader Security Role
        2. Working with the Software Updates Report Reader Security Role
        3. Working with the Inventory Report Reader Security Role
      2. Chapter 3 Demonstration Outcomes
        1. Creating and Executing a Query
        2. Looking at Views
      3. Chapter 4 Demonstration Outcomes
        1. Demonstrating SQL Operators
        2. Working with Aggregate Functions
        3. Working with Date and Time Functions
        4. Working with Data Transforms
        5. Executing an Inefficient Query
        6. Writing an Efficient Query
      4. Chapter 6 Demonstration Outcomes
        1. Creating a New Project
        2. Creating a Patch Compliance Progression Report
        3. Creating a Computer Hardware Information Report
      5. Chapter 7 Demonstration Outcomes
        1. Creating Template Reports
        2. Modifying Page Sizes for Existing Reports
        3. Updating the Patch Compliance Progression Report
        4. Updating the Computer Hardware Information Report
        5. Adding a Chart to the Patch Compliance Progression Report
        6. Adding a Chart to the Computer Hardware Information Report
      6. Chapter 8 Demonstration Outcomes
        1. Adding a Patch Compliance Progression Drillthrough Series
        2. Adding a Computer Hardware Information Drillthrough Series
        3. Applying a Custom Color Palette to Cycle Through Color Codes
        4. Applying a Custom Color Palette Based on Values
        5. Manually Adding Reports to the SSRS Website
        6. Publishing Reports to SSRS
        7. Creating a Subscription
      7. Chapter 9 Demonstration Outcomes
    3. Appendix C. Available Online
      1. Creating and Assigning ConfigMgr Security Roles
      2. Understanding Configuration Manager Data
      3. Transact-SQL Primer
      4. Basic Report Design
      5. Building a Basic Report
      6. Intermediate Reporting Concepts
      7. SSRS Reporting Features
      8. Role-Based Administration and Reporting
  17. Index
  18. Code Snippets