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