Microsoft System Center Reporting Cookbook

Microsoft System Center Reporting Cookbook

By : Samuel Erskine, Dieter Gasser, Kurt Van Hoecke, Nasira Ismail

Buy this Book

Microsoft System Center Reporting Cookbook

By: Samuel Erskine, Dieter Gasser, Kurt Van Hoecke, Nasira Ismail

Buy this Book

Overview of this book

Microsoft System Center 2012 is an integrated management platform that helps you to easily and efficiently manage your data centers, client devices, and hybrid cloud IT environments. This hands-on cookbook will guide you through how to create ready-to-use reports for all the components of System Center. The book starts by showing you how to plan business valued reports, while also discussing the building blocks of your reporting framework. Moving on from the basics, the later recipes demonstrate how you can create System Center Configuration Manager reports using the report builder tool and System Center Operations and Virtual Machine Manager reports with data available from the Operations Manager databases. The book will then teach you how to build on and enhance the reports previously created by delving into advanced reporting techniques such as creating database reports, based on combined data sources. Finally, you will use Power BI to analyze and visualize System Center data, while also looking into the seamless integration between cloud services and System Center.

Microsoft System Center Reporting Cookbook

Credits

Foreword

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Understanding the Goals of Reporting

Introduction

Understanding the goals of reporting

Planning and optimizing dependent data inputs

Planning report outputs

Understanding dashboards and their dependencies

Targeting reports at decision stakeholders

Documenting report designs

Planning System Center Report Design

Introduction

Understanding the reporting schemas of System Center components

Understanding how SQL Reporting Services interfaces with System Center

Building SQL queries the easy way

Unpacking System Center Report Building Tools

Introduction

Planning the Report Builder installation

Installing Report Builder

Navigating through the Report Builder interface

Organizing the reporting environment and delegating access to reports

Creating data sources

Creating datasets and a basic report in Report Builder

Preparing for report subscriptions

Creating Reports for System Center Configuration Manager

Introduction

Exploring the Configuration Manager database schema

Delegating access to out-of-the-box Configuration Manager reports

Preparing the SCCM reporting environment for reporting

Creating Configuration Manager custom reports

Applying role-based security to custom reports

Creating Reports for SCOM and SCVMM

Introduction

Using out-of-the-box Operations Manager reports

Understanding the OperationsManagerDW schema

Preparing your environment to author reports

Creating alert reports

Creating event reports

Creating performance reports

Creating availability reports

Using Virtual Machine Manager reports

Creating Reports for System Center Data Protection Manager

Introduction

Preparing the DPM reporting environment

Preparing a dataset query for a DPM agent status report

Creating a DPM agent version and a disk space report

Creating a backup status report with a community template

Creating Reports for System Center Service Manager and Orchestrator

Introduction

Using out-of-the-box Service Manager reports and cubes

Understanding the Service Manager Data Warehouse data mart

Creating Work Item and Configuration Item reports

Creating reports using OLAP cubes

Creating reports using the operational database

Accessing data using Microsoft Excel

Extending the Service Manager Data Warehouse

Creating Orchestrator runbook reports

Creating System Center Advanced Reports

Introduction

Creating dashboards from basic reports

Working with drillthrough reports

Working with shared datasets and parameters

Analyzing Chargeback reports using System Center 2012 R2 data

Using Power BI to Analyze and Visualize System Center Data

Introduction

Configuring Microsoft Excel for System Center data analysis

Connecting to System Center data sources with Power Query

Creating the data model with PowerPivot

Visualizing the analysis with Power View and Power Maps

Using Microsoft Cloud services to share and visualize System Center data

Useful Websites, Chapter Code, and Community Resources

Authors' community blogs

Useful community blogs

Websites for SQL and reporting

Online wikis and curations

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Online wikis and curations

Microsoft TechNet Wiki page on SQL Database Engine: http://social.technet.microsoft.com/wiki/contents/articles/22759.sql-server-general-database-engine-resources-on-the-technet-wiki.aspx

Microsoft TechNet Wiki page on Opalis Survival Guide: http://social.technet.microsoft.com/wiki/contents/articles/768.opalis-survival-guide.aspx

SQL Server on Curah!: http://curah.microsoft.com/44214/sql-server

SSRS Expressio n Examples: https://curah.microsoft.com/23335/ssrs-expression-examples

Social network resources

Microsoft SQL Server on Facebook: https://www.facebook.com/sqlserver

SSRS on Twitter: https://twitter.com/SSRS

Chapter code

This section lists the code referenced in the relevant chapters that you must type to complete the recipe steps. You can also download the code files from the Packt Publishing website using the link provided in the introduction section of this appendix.

Custom.IncidentDashboard.View.xml code

The following is the code:

<ManagementPack ContentReadable="true" SchemaVersion="1.1" OriginalSchemaVersion="1.1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Manifest>
  <Identity>
    <ID>Custom.IncidentDashboard.View</ID>
    <Version>1.0.0.0</Version>
  </Identity>
  <Name>Custom.IncidentDashboard.View</Name>
  <References>
    <Reference Alias="System">
      <ID>System.Library</ID>
      <Version>7.5.8501.0</Version>
      <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
    <Reference Alias="Console">
      <ID>Microsoft.EnterpriseManagement.ServiceManager.UI.Console</ID>
      <Version>7.5.1561.0</Version>
      <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
    <Reference Alias="WorkItem">
      <ID>ServiceManager.WorkItem.Library</ID>
      <Version>7.5.1561.0</Version>
      <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
  </References>
</Manifest>
<PresentationTypes>
  <ViewTypes>
    <ViewType ID="ServiceManager.DashboardViewType" Accessibility="Public">
      <Configuration>
        <xsd:any minOccurs="0" maxOccurs="unbounded" processContents="skip" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
      </Configuration>
      <ViewImplementation>
        <Assembly>Console!WpfViewsAssembly</Assembly>
        <Type>Microsoft.EnterpriseManagement.UI.WpfViews.Overview</Type>
      </ViewImplementation>
    </ViewType>
  </ViewTypes>
</PresentationTypes>
<Presentation>
  <Views>
    <View ID="CustomIncidentDashboardView" Accessibility="Public" Enabled="true" Target="System!System.Entity" TypeID="ServiceManager.DashboardViewType" Visible="true">
      <Category>Overview</Category>
      <Configuration>
        <Definitions />
          <Presentation>
          <Header />
          <Content>
            <WebBrowser Name="wb1" Source="http://[SSRSServerName]/Reports/Pages/Report.aspx?ItemPath=%2fSystemCenter%2fServiceManager%2fCustom%2fIncident+Dashboard" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" />
            </Content>
          </Presentation>
        </Configuration>
      </View>
  </Views>
  <FolderItems>
    <FolderItem ElementID="CustomIncidentDashboardView" ID="CustomIncidentDashboardView.FolderItem" Folder="WorkItem!ServiceManager.Console.WorkItem.Root" />
</FolderItems>
</Presentation>
<LanguagePacks>
  <LanguagePack ID="ENU" IsDefault="true">
    <DisplayStrings>
      <DisplayString ElementID="Custom.IncidentDashboard.View">
        <Name>Custom Incident Dashboard Views</Name>
      </DisplayString>
      <DisplayString ElementID="CustomIncidentDashboardView">
      <Name>Incident Dashboard</Name>
    </DisplayString>
    </DisplayStrings>
  </LanguagePack>
</LanguagePacks>
</ManagementPack>

Accessing data using Microsoft Excel query code

Use DWDataMart:

GO

CREATE VIEW v_Custom_r_AllIncidents AS

SELECT
  I.Id AS [ID],
  CONVERT(smalldatetime, I.CreatedDate) AS [Created Date],
  IStatusDS.DisplayName AS [Status],
  AffectedUser.DisplayName AS [Affected User],
  I.Title AS [Title],
  I.Description AS [Description],
  IImpactDS.DisplayName AS [Impact],
  IUrgencyDS.DisplayName AS [Urgency],
  I.Priority AS [Priority],
  ISourceDS.DisplayName AS [Source],
  ITierQueueDS.DisplayName [Support Group],
  AssignedTo.DisplayName AS [Assigned To],
  IClassificationDS.DisplayName AS [Classification Category],
  CONVERT(smalldatetime, I.ResolvedDate) AS [Resolved Date],
  IResolutionCategoryDS.DisplayName AS [Resolution Category],
  I.ResolutionDescription AS [Resolution Description],
  CONVERT(smalldatetime, I.ClosedDate) AS [Closed Date],
  CreatedBy.DisplayName AS [Created By],
  ResolvedBy.DisplayName AS [Resolved By]
FROM
  IncidentDimvw I
  
  LEFT OUTER JOIN WorkItemDimvw WI ON
    I.EntityDimKey = WI.EntityDimKey
  
  LEFT OUTER JOIN   WorkItemAffectedUserFactvw ON
    WI.WorkItemDimKey = WorkItemAffectedUserFactvw.WorkItemDimKey
    AND WorkItemAffectedUserFactvw.DeletedDate IS NULL
  LEFT OUTER JOIN UserDimvw AS AffectedUser ON
    WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
  
    LEFT OUTER JOIN WorkItemAssignedToUserFactvw ON
      WI.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey
      AND WorkItemAssignedToUserFactvw.DeletedDate IS NULL
    LEFT OUTER JOIN UserDimvw AssignedTo ON
      WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
  
    LEFT OUTER JOIN WorkItemCreatedByUserFactvw ON
      WI.WorkItemDimKey = WorkItemCreatedByUserFactvw.WorkItemDimKey
        AND WorkItemCreatedByUserFactvw.DeletedDate IS NULL
  LEFT OUTER JOIN UserDimvw CreatedBy ON
    WorkItemCreatedByUserFactvw.WorkItemCreatedByUser_UserDimKey = CreatedBy.UserDimKey
  
    LEFT OUTER JOIN IncidentResolvedByUserFactvw ON
      I.IncidentDimKey = IncidentResolvedByUserFactvw.IncidentDimKey
        AND IncidentResolvedByUserFactvw.DeletedDate IS NULL
    LEFT OUTER JOIN UserDimvw ResolvedBy ON
      IncidentResolvedByUserFactvw.TroubleTicketResolvedByUser_UserDimKey = ResolvedBy.UserDimKey
  
    LEFT OUTER JOIN IncidentStatusvw IStatus ON
      I.Status_IncidentStatusId = IStatus.IncidentStatusId
    LEFT OUTER JOIN DisplayStringDimvw IStatusDS ON
      IStatus.EnumTypeId = IStatusDS.BaseManagedEntityId
      AND IStatusDS.LanguageCode = 'ENU'

    LEFT OUTER JOIN IncidentImpactvw IImpact ON
      I.Impact_IncidentImpactId = IImpact.IncidentImpactId
    LEFT OUTER JOIN DisplayStringDimvw IImpactDS ON
      IImpact.EnumTypeId = IImpactDS.BaseManagedEntityId
        AND IImpactDS.LanguageCode = 'ENU'

    LEFT OUTER JOIN IncidentUrgencyvw IUrgency ON
      I.Urgency_IncidentUrgencyId = IUrgency.IncidentUrgencyId
    LEFT OUTER JOIN DisplayStringDimvw IUrgencyDS ON
      IUrgency.EnumTypeId = IUrgencyDS.BaseManagedEntityId
      AND IUrgencyDS.LanguageCode = 'ENU'

    LEFT OUTER JOIN IncidentSourcevw ISource ON
      I.Source_IncidentSourceId = ISource.IncidentSourceId
    LEFT OUTER JOIN DisplayStringDimvw ISourceDS ON
      ISource.EnumTypeId = ISourceDS.BaseManagedEntityId
        AND ISourceDS.LanguageCode = 'ENU'

    LEFT OUTER JOIN IncidentTierQueuesvw ITierQueue ON
      I.TierQueue_IncidentTierQueuesId = ITierQueue.IncidentTierQueuesId
    LEFT OUTER JOIN DisplayStringDimvw ITierQueueDS ON
      ITierQueue.EnumTypeId = ITierQueueDS.BaseManagedEntityId
      AND ITierQueueDS.LanguageCode = 'ENU'

    LEFT OUTER JOIN IncidentClassificationvw IClassification ON
      I.Classification_IncidentClassificationId = IClassification.IncidentClassificationId
    LEFT OUTER JOIN DisplayStringDimvw IClassificationDS ON
      IClassification.EnumTypeId = IClassificationDS.BaseManagedEntityId
        AND IClassificationDS.LanguageCode = 'ENU'

    LEFT OUTER JOIN IncidentResolutionCategoryvw IResolutionCategory ON
      I.ResolutionCategory_IncidentResolutionCategoryId = IResolutionCategory.IncidentResolutionCategoryId
    LEFT OUTER JOIN DisplayStringDimvw IResolutionCategoryDS ON
      IResolutionCategory.EnumTypeId = IResolutionCategoryDS.BaseManagedEntityId
        AND IResolutionCategoryDS.LanguageCode = 'ENU'
WHERE
    I.IsDeleted = 0

Extending the Service Manager Data Warehouse code

The following is the code:

<ManagementPack ContentReadable="true" SchemaVersion="2.0" OriginalSchemaVersion="1.1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Manifest>
    <Identity>
      <ID>Custom.MobilePhone.Library</ID>
      <Version>1.0.0.0</Version>
    </Identity>
    <Name>Custom.MobilePhone.Library</Name>
    <References>
      <Reference Alias="System">
        <ID>System.Library</ID>
        <Version>7.5.8501.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="Console">
        <ID>Microsoft.EnterpriseManagement.ServiceManager.UI.Console</ID>
        <Version>7.5.1561.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="Authoring">
        <ID>Microsoft.EnterpriseManagement.ServiceManager.UI.Authoring</ID>
        <Version>7.5.1561.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="DWBase">
        <ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>
        <Version>7.5.1561.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
    </References>
  </Manifest>
  <TypeDefinitions>
    <EntityTypes>
      <ClassTypes>
        <ClassType ID="Custom.MobilePhone" Accessibility="Public" Abstract="false" Base="System!System.ConfigItem" Hosted="false" Singleton="false" Extension="false">
          <Property ID="MobilePhoneID" Type="string" AutoIncrement="true" Key="true" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" DefaultValue="MP{0}" />
          <Property ID="SerialNumber" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" />
          <Property ID="Manufacturer" Type="enum" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" EnumType="MobilePhoneManufacturerEnum" />
        </ClassType>
      </ClassTypes>
      <RelationshipTypes>
        <RelationshipType ID="MobilePhoneOwnedByUser" Accessibility="Public" Abstract="false" Base="System!System.Reference">
          <Source ID="MobilePhoneOwnedByUserSource" MinCardinality="0" MaxCardinality="2147483647" Type="Custom.MobilePhone" />
          <Target ID="MobilePhoneOwnedByUserTarget" MinCardinality="0" MaxCardinality="1" Type="System!System.User" />
        </RelationshipType>
      </RelationshipTypes>
      <EnumerationTypes>
        <EnumerationValue ID="MobilePhoneManufacturerEnum" Accessibility="Public" />
      </EnumerationTypes>
    </EntityTypes>
  </TypeDefinitions>
  <Categories>
    <Category ID="Custom.MobilePhone.Library.Category" Value="Console!Microsoft.EnterpriseManagement.ServiceManager.ManagementPack">
      <ManagementPackName>Custom.MobilePhone.Library</ManagementPackName>
      <ManagementPackVersion>1.0.0.0</ManagementPackVersion>
    </Category>
    <Category ID="MobilePhoneManufacturerEnum.EnumerationViewTasks.Category" Target="MobilePhoneManufacturerEnum" Value="Authoring!Microsoft.EnterpriseManagement.ServiceManager.UI.Authoring.EnumerationViewTasks" />
    <Category ID="MobilePhoneManufacturerEnum.VisibleToUser.Category" Target="MobilePhoneManufacturerEnum" Value="System!VisibleToUser" />
  </Categories>
  <Warehouse>
    <Outriggers>
      <Outrigger ID="CustomMobilePhoneManufacturer" Accessibility="Public">
        <Attribute ID="MobilePhoneManufacturer" PropertyPath="$Context/Property[Type='Custom.MobilePhone']/Manufacturer$" />
      </Outrigger>
    </Outriggers>
    <Dimensions>
      <Dimension ID="CustomMobilePhoneDim" Accessibility="Public" Target="Custom.MobilePhone" InferredDimension="true" HierarchySupport="IncludeExtendedClassProperties" Reconcile="true" />
    </Dimensions>
    <Facts>
      <RelationshipFact ID="CustomMobilePhoneOwnedByUserFact" Accessibility="Public" Domain="DWBase!Domain.ConfigurationManagement" TimeGrain="Daily" SourceType="Custom.MobilePhone" SourceDimension="CustomMobilePhoneDim">
        <Relationships RelationshipType="MobilePhoneOwnedByUser" TargetDimension="DWBase!UserDim" />
      </RelationshipFact>
    </Facts>
  </Warehouse>
  <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
      <DisplayStrings>
        <DisplayString ElementID="Custom.MobilePhone.Library">
          <Name>Custom Mobile Phone Library</Name>
        </DisplayString>
        <DisplayString ElementID="Custom.MobilePhone">
          <Name>Mobile Phone</Name>
          <Description></Description>
        </DisplayString>
        <DisplayString ElementID="Custom.MobilePhone" SubElementID="Manufacturer">
          <Name>Manufacturer</Name>
        </DisplayString>
        <DisplayString ElementID="Custom.MobilePhone" SubElementID="MobilePhoneID">
          <Name>Mobile Phone ID</Name>
        </DisplayString>
        <DisplayString ElementID="Custom.MobilePhone" SubElementID="SerialNumber">
          <Name>Serial Number</Name>
        </DisplayString>
        <DisplayString ElementID="MobilePhoneManufacturerEnum">
          <Name>Mobile Phone Manufacturer</Name>
        </DisplayString>
        <DisplayString ElementID="MobilePhoneOwnedByUser">
          <Name>Mobile Phone Owned by User</Name>
        </DisplayString>
        <DisplayString ElementID="MobilePhoneOwnedByUser" SubElementID="MobilePhoneOwnedByUserSource">
          <Name>Mobile Phone</Name>
        </DisplayString>
        <DisplayString ElementID="MobilePhoneOwnedByUser" SubElementID="MobilePhoneOwnedByUserTarget">
          <Name>Owned by User</Name>
        </DisplayString>
      </DisplayStrings>
    </LanguagePack>
  </LanguagePacks>
</ManagementPack>

Microsoft System Center Reporting Cookbook

By : Samuel Erskine, Dieter Gasser, Kurt Van Hoecke, Nasira Ismail

Microsoft System Center Reporting Cookbook

By: Samuel Erskine, Dieter Gasser, Kurt Van Hoecke, Nasira Ismail

Overview of this book

Related Content you might be interested in

Current Title:

Microsoft System Center Reporting Cookbook

Online wikis and curations

Social network resources

Chapter code

Custom.IncidentDashboard.View.xml code

Accessing data using Microsoft Excel query code

Extending the Service Manager Data Warehouse code