Samuel Erskine Dieter Gasser Kurt Van Hoecke Nasira Ismail
BIRMINGHAM - MUMBAI
Table of Contents
Preface v
Introduction 1
Understanding the goals of reporting 2
Planning and optimizing dependent data inputs 4
Planning report outputs 7
Understanding dashboards and their dependencies 9
Targeting reports at decision stakeholders 11
Documenting report designs 14
Introduction 17
Understanding the reporting schemas of System Center components 18
Understanding how SQL Reporting Services interfaces with System Center 27
Building SQL queries the easy way 31
Introduction 37
Planning the Report Builder installation 38
Installing Report Builder 40
Navigating through the Report Builder interface 45
Organizing the reporting environment and delegating access to reports 52
Creating data sources 60
Creating datasets and a basic report in Report Builder 66
Preparing for report subscriptions 73
Introduction 75
Exploring the Configuration Manager database schema 76
Delegating access to out-of-the-box Configuration Manager reports 83
Preparing the SCCM reporting environment for reporting 88
Creating Configuration Manager custom reports 92
Applying role-based security to custom reports 108
Introduction 117
Using out-of-the-box Operations Manager reports 118
Understanding the OperationsManagerDW schema 123
Preparing your environment to author reports 129
Creating alert reports 131
Creating event reports 138
Creating performance reports 144
Creating availability reports 150
Using Virtual Machine Manager reports 158
Protection Manager 163
Introduction 163
Preparing the DPM reporting environment 164
Preparing a dataset query for a DPM agent status report 168
Creating a DPM agent version and a disk space report 176
Creating a backup status report with a community template 186
Manager and Orchestrator 191
Introduction 192
Using out-of-the-box Service Manager reports and cubes 193
Understanding the Service Manager Data Warehouse data mart 199
Creating Work Item and Configuration Item reports 204
Creating reports using OLAP cubes 211
Creating reports using the operational database 215
Accessing data using Microsoft Excel 224
Extending the Service Manager Data Warehouse 227
Creating Orchestrator runbook reports 232
Introduction 239
Creating dashboards from basic reports 240
Working with drillthrough reports 252
Working with shared datasets and parameters 258
Analyzing Chargeback reports using System Center 2012 R2 data 265
Center Data 277
Introduction 277
Configuring Microsoft Excel for System Center data analysis 278
Connecting to System Center data sources with Power Query 283
Creating the data model with PowerPivot 298
Visualizing the analysis with Power View and Power Maps 305
Using Microsoft Cloud services to share and visualize System Center data 309
Appendix: Useful Websites, Chapter Code, and Community Resources 313
Authors' community blogs 313
Useful community blogs 314
Websites for SQL and reporting 314
Online wikis and curations 315
Index 325
Preface
The System Center product provides you with the best IT systems management and reporting capabilities. The various components of this product continue to provide great value in all areas of management and monitoring. Each of the components has one or more data storing epositories. These data repositories hold valuable strategic information that can assist an organization in transforming its IT business unit into a strategic enabler. The database (repository) technology of the System Center product is Microsoft SQL Server. There is a vast array of information on the Internet on how to query and report on Microsoft SQL databases. The skill set required for the level of querying and reporting on these databases is normally aligned to Database Administrators (DBAs) and Business Intelligence (BI) specialists.
The DBAs and BI specialists use various tools to create business-valued reports based on the data stored in these Microsoft SQL databases. The challenge DBAs and BI experts face with System Center reporting is a lack of insight into how the data is structured, and more importantly, a lack of deeper understanding of the products responsible for the input data. Conversely, in the majority of cases, the System Center product's Subject Matter Experts (SMEs) do not have the same level of competency as the DBAs and BI experts in creating and enhancing reports. The aim of this book is to enhance the competency of System Center SMEs in the areas of reporting. There are a number of books on querying Microsoft SQL databases and generating business-value reports. However, those books do not focus on System Center. Also, all the books on System Center's components dedicate a reporting chapter to that specific component of the book's focus.
Microsoft System Center Reporting Cookbook, provides a one-stop shop for how to plan, create, and manage reports for all the components of the System Center product. This book is written in the Packt Publishing style, which provides you with independent, task-oriented steps to achieve specific reporting objectives. We recommend that you read the first three chapters as primers for subsequent chapters. This book may be read in the order of your interest, but wherever relevant, we've referred to the dependent recipes in other chapters.
What this book covers
Chapter 1, Understanding the Goals of Reporting, discusses the building blocks of your reporting framework and how to plan for business-valued reports.
Chapter 2, Planning System Center Report Design, covers the internals of System Center reporting database structures, which you must understand in order to create credible and powerful reports. This chapter also includes an introductory recipe to SQL queries.
Chapter 3, Unpacking System Center Report Building Tools, discusses the common report-creating tools available for System Center administrators and Business Intelligence power users.
Chapter 4, Creating Reports for System Center Configuration Manager, provides tasks for System Center administrators, who can create System Center Configuration Manager reports using the Report Builder tool.
Chapter 5, Creating Reports for SCOM and SCVMM, demonstrates how to create System Center Operations and Data Protection Manager reports, with data available from the Operations Manager databases.
Chapter 6, Creating Reports for System Center Data Protection Manager, shows you how to create data-driven reports for System Center Data Protection Manager.
Chapter 7, Creating Reports for System Center Service Manager and Orchestrator, illustrates how to create service management and automation activity reports using data from System Center Service Manager and Orchestrator.
Chapter 8, Creating System Center Advanced Reports, shows you recipes that build on and enhance the reports from previous recipes in this book. These recipes delve into advanced reporting techniques and combined data source reporting.
Chapter 9, Using Power BI to Analyze and Visualize System Center Data, introduces the Power Business Intelligence (Power BI) options available for you from Microsoft. These recipes provide steps for analyzing and visualizing the System Center data using the Microsoft Excel Power BI add-ons, and introduce the cloud Power BI versions available for you.
Appendix, Useful Websites, Chapter Code, and Community Contributions, lists some of the sites that provide ready-made solutions and extensive, real-world, and dynamic content on reporting. Microsoft SQL Server-based reporting, similar to most Microsoft product areas, has an extended solutions partner community. There is an extensive and active support base on the Web. This appendix also provides you with the SQL and XML code referred to in the relevant chapters.
What you need for this book
In order to complete all the recipes in this book, you will need access to environments configured with the relevant System Center component (or components). Here is a list of technologies the recipes depend on and their relevant versions used for this book:
- Microsoft Active Directory (Windows Server 2008 R2 and above)
- System Center 2012 Configuration Manager SP1/R2
- System Center 2012 Operations Manager SP1/R2
- System Center 2012 Virtual Machine Manager SP1/R2
- System Center 2012 Service Manager SP1/R2
- System Center 2012 Orchestrator SP1/R2
- System Center 2012 Data Protection Manager SP1/R2
- Microsoft Report Builder 3.0
We recommend using the online Microsoft resource due to the frequency of updates to the products' supported requirements. Also note that the dynamic nature of the Internet may require you to search for updated links listed in this book.
Who this book is for
This book is for IT professionals who are responsible for producing reports using the data from System Center components. Basic knowledge of Microsoft System Center technologies is assumed.
