Performance Reporting with Power View

Hello, my name is Mark Drewfs.  I am a Premier Field Engineer for Microsoft with a focus on Systems Center Operations Manager.  I started building data models of the SCOM Data Warehouse to use for interactive reports, and I would like to share some techniques.  Tim McFadden graciously offered to edit the work and host this blog post.  Thank you, Tim!

The data SCOM collects and Business Intelligence tools fit well together, but there can be a significant barrier to combining them.  Power View lowers that barrier a lot.  Users can move quickly between performance counters and go from an enterprise view down to a single server without much effort. 

Here is an example where I filtered the view to look at four Windows servers to see general data for the past week.  Each chart is filtered to a specific object and counter:

 

 pic001

 

I can analyze the data, too.  For instance, I popped out the Memory chart and highlighted a server in the legend.  Then I hovered over a low point to get the time and value:

pic002

 

The SQL 2012 reporting services feature, Power View (http://technet.microsoft.com/en-us/library/hh213579.aspx), is included with Excel 2013.  It provides quick and dynamic access to data from the SCOM Data Warehouse.  This post describes how to setup Excel 2013 with a PowerPivot model and Power View reports using SCOM performance data. 

 

Overview

A.      Setup Excel 2013

B.      Create a data source for the SCOM Data Warehouse

C.      Add datasets

D.      Create relationships

E.       Make a view

 

Step by step

A.      Setup Excel 2013

1.       Install Excel 2013.  I use the 64-bit version, because it can make use of more physical memory.  Select File à Account à About Excel to check the bitness.

2.       Load the add-ins for PowerPivot and Power View.  Select File à Options à Add-Ins

3.       Select COM Add-ins from the Manage: pulldown and click Go…

4.       Check the boxes for Microsoft Office PowerPivot for Excel 2013 and Power View then click OK

      pic003

5.       Accept the prompts to install Silverlight if necessary.

 

 

B.      Create a data source for the SCOM Data Warehouse

1.       Click the POWERPIVOT tab

2.       Then click Manage

     pic004

3.       Select From Database  then select From SQL Server from the pulldown

    pic005

4.       Enter a name like SCOM_DW in the Friendly connection name: field

5.       Enter the SCOM Data Warehouse server name (with instance and port, if necessary) in the Server name: field

6.       Select the name of the SCOM DW (OperationsManagerDW by default) from the Database name: pulldown

7.       Click Next

8.       On the How to Import the Data dialog select Write a query that will specify the data to import

9.       Click Next

10.   In the Friendly Query Name field type Perf Data

11.   Copy and Paste in the following query (Simple Perf Queries):

SELECT     PERF.ManagedEntityRowId

,PR.RuleRowId

,PERF.DateTime

,PERF.AverageValue

,PERF.MinValue

,PERF.MaxValue

,PERF.SampleCount

,PR.ObjectName

,PR.CounterName

,PRI.InstanceName

FROM Perf.vPerfHourly PERF

INNER JOIN vPerformanceRuleInstance PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId

INNER JOIN vPerformanceRule PR ON PR.RuleRowId = PRI.RuleRowId

WHERE PERF.DateTime > (GETUTCDATE() – 7)

/*WHERE PERF.DateTime > (getutcdate() + 1) — Dummy condition to return no rows*/

pic006

12.   Click Finish

 

C.      Add Additional datasets

1.       Click Existing Connections in the Ribbon

        pic007

2.       Select SCOM_DW from the PowerPivot Data Connections

 pic008

 

3.       Click Open

4.       Select Write a query that will specify the data to import and click Next >

5.       In the Friendly Query Name type Perf Entities and Types

6.       Copy and Paste in the following query:

select distinct

ME.ManagedEntityRowId

,ME.Name

,ME.DisplayName

,ME.Path

,MET.ManagedEntityTypeDefaultName

from Perf.vPerfHourly PERF

inner join vManagedEntity ME on ME.ManagedEntityRowId = PERF.ManagedEntityRowId

inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId

inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId

where PERF.DateTime > (GETUTCDATE() – 7)

order by MET.ManagedEntityTypeDefaultName

7.       Click Finish

8.       Repeat steps 1-7 to create another dataset

9.       Name it MPs and Rules

10.   Use the following SQL Query:

SELECT distinct RU.RuleRowId

,MP.ManagementPackDefaultName

,MP.ManagementPackSystemName

,RU.RuleDefaultName

FROM vManagementPack MP

inner join vRule RU on RU.ManagementPackRowId = MP.ManagementPackRowId

inner join vPerformanceRule PR on PR.RuleRowId = RU.RuleRowId

where PR.LastReceivedDateTime > (GETUTCDATE() – 7)

 

D.      Create relationships

1.       Click Diagram View in the Ribbon

pic009 

2.       Click ManagedEntityRowID in the Perf Data dataset and drag a line to ManagedEntityRowID in the Perf Entities and Types dataset

        pic010

3.       Click RuleRowID in the Perf Data dataset and drag a line to RuleRowID in the MPs and Rules dataset

        pic011

4.       Close the PowerPivot window

        pic012

 

E.       Make a view

1.       Click the INSERT menu

2.       Click the Power View icon

pic013 

3.       Rename the tab to SCOM Perf

pic014 

4.       Expand the Perf Entities and Types dataset in the Power View Fields pane

        pic015

5.       Drag the ManagedEntityTypeDefaultName to the Filters area

        pic016 

6.       Check the boxes for managed entity types of interest

        pic017 

7.       Expand the Perf Data dataset in the Power View Fields pane

8.       Check the box for ObjectName

        pic019 

9.       Click Slicer in the ribbon

        pic020 

10.   Click in the empty space of the view to deselect the slicer

11.   Check the boxes for AverageValue and DateTime in the Perf Data dataset

      pic021 

 

12.   Select Other Chart à Line
pic022

 

13.   Change the AverageValue field to be averaged instead of summed

        pic023

 

14.   Drag CounterName into the TILE BY field

        pic024

 

Note: The managed entity will determine the values for DisplayName, Name and Path.  For top level entities, the path is null.

 

15.   Select the chart

16.   Drag the Path to the Legend

pic025

 

17.   Save the workbook

  

Here is my example:

  

 pic026

Sample Files: https://www.scom2k7.com/downloads/PowerPivotExample.zip

Continue Reading

Uninstalling AVIcode 5.7 Agent

 

I was recently working with a customer that was trying to install SCOM 2012 and leverage Application Performance Monitoring.  After pushing the SCOM 2012 agent the customer told they had previously installed AVIcode 5.7 on the server.  To install APM, it requires the Avicode 5.7 agent to be removed.  http://technet.microsoft.com/en-us/library/hh543998.aspx

Using Add/Remove programs, we tried to uninstall AVIcode 5.7 and we were presented with the following error.  Intercept Agent is using files that need to be updated by this setup.  Please stop AVIcode Intercept Studio monitoring.

Locked Files:

PerfMon64.dll
StubProfiler64.dll

clip_image002

We removed all monitored applications using the Intercept Management Console, but we still couldn’t uninstall Avicode 5.7 as we kept getting the same error. 

clip_image002[5]

We checked the processes using sysinternals process explorer but it didn’t show the files were in use.

So the simple solution. 

Rename PerfMon64.dll and StubProfiler64.dll to a different name. Now were were able to run the uninstall of AVIcode 5.7.   After that we did a Repair of the Operations Manager 2012 agent which the successfully installed the APM service.

Continue Reading

SQL Server 2012 installation fails in a VMware environment that is running Windows Server 2012 if the default installation directory is changed

If you are installing SQL 2012, on Server 2012 running on a VMware VM.  This is something to watch out for.

http://support.microsoft.com/kb/2799534

 

Assume that you try to install Microsoft SQL Server 2012 in a VMware environment that is running Windows Server 2012. You change the shared features and the instance root directory to be located on a drive other than the default drive C. In this situation, the installation fails, and you receive an error message that resembles the following:

Attempted to perform an unauthorized operation
To work around this issue, use one of the following methods:

 

 

 

 

Continue Reading

Last day for Early-bird pricing to The Microsoft Management Summit (MMS 2013)

Today 2-13  is the last day for Early-bird pricing to MMS 2013.   If you haven’t attended MMS in the past I highly recommend it.

http://www.2013mms.com

 

Here are a few of the many benefits.  http://www.2013mms.com/why

  • 300-400 level training on the System Center Products Suite
  • A chance to get some of your toughest questions answers by the people who created System Center 2012.
  • Network with some of the top System Center experts like Kevin Holman, Marnix Wolf, Pete Zerger,  Cameron Fuller and many more.
  • It’s in Vegas!

 

I just found out that I will be going as well, so hope to see you all there.

 

Continue Reading

Adding SCOM 2012 Visio Dashboards into SharePoint 2010

 

In my last blog post, I showed how to create a SCOM Dashboard using Visio.  https://www.scom2k7.com/creating-scom-2012-dashboards-using-visio-2010-or-visio-2013/

 

Now I am going to show how to add this Dashboard into SharePoint 2010.

 

To start I need the Microsoft Visio 2010 and SharePoint 2010 Extensions for System Center 2012 found here.
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=29268

 

Part 1 – Installing SharePoint 2010 Integration Components

 

Here are the pre-requisites:

  • System Center 2012 – Operations Manager console or Operations Manager 2007 R2 Authoring Console.
  • SharePoint 2010 Enterprise with Visio Services enabled.
  • The Microsoft .NET Framework 3.5 SP1, available from http://go.microsoft.com/fwlink/?LinkID=131605

 

First I install the Operations Manager 2012 SP1 console on my SharePoint Server
image

 

I also verify that the SharePoint 2010 Enterprise with Visio Services are enabled.

 

On my SharePoint server I go to the SharePoint Central Administration Page.

 

I click Configuration Wizards.

image

 

I click Launch Farm Configuration Wizard

image

I click Start the Wizard

 

I scroll down and verify that the Visio Graphics Service is Enabled.

image

It appears to be already enabled so I click Cancel.

 

I verify that Microsoft .NET Framework 3.5 SP1 installed so I continue on.

image

 

Ok now I am ready.

 

First I need to install the Visio Services Data Provider.

I log on to my SharePoint server as the Farm Administrator so I have all the permissions I need.

Now I copy the Server folder over to my SharePoint 2010 server in the C:\temp\ directory from the Extensions I downloaded earlier.

image

 

Then I run setup.exe.  Click Next, Next, Close.

 

On my SharePoint server, I open the SharePoint 2010 Management Shell as an Administrator

image

 

In the Command shell I change directories to

C:\Program Files\Visio Services Data Provider for System Center 2012 – Operations Manager

image

 

Now I type in

.\InstallOpsMgrDataModule.ps1

image

 

I click enter and it installs

image

 

I run

get-spsolution

image

I see that it says that is is Deployed so I am good.

 

I also see that another one listed called microsoft.enterprisemanagem…  This is from when I installed the SCOM Dashboard Integration
https://www.scom2k7.com/how-to-view-scom-2012-dashboards-in-sharepoint-2010/  and is not required to make the Visio stuff work.

 

Part 2 – Configuring SharePoint 2010 Integration Components

Ok now I go back to the SharePoint Central Administration Console.  I click Configure service accounts from the Security Section

image

 

From the dropdown I pick Service Application Pool – SharePoint Web Services

image

 

image

 

I need to grant the scom2k7.com\spsvc access to read SCOM.  I double click on the Operations Manager Read-Only Operators and add my SharePoint service account to the Operations Manager Read-Only Operations user role.

 

image

image

 

Ok SharePoint is ready.

Part 3 – Adding your Visio Dashboard to SharePoint 2010

 

I get my SCOM Dashboard Visio document from my previous post.  https://www.scom2k7.com/creating-scom-2012-dashboards-using-visio-2010-or-visio-2013/

 

I open up the diagram document in Visio 2013

image

 

I click Configure to make sure my connection is correct.

image

 

I click File, Save As.  Then I click SharePoint and click Browse.

image

 

I type in the address of my SharePoint Web page in the address bar.

I choose Save as Type: Visio 2010 Web Drawing

Then I click Double Click on Shared Documents.

Now I hit Save.

 

image

 

I get a warning about some Compatibility issue.  I just click Continue.

image

 

I now go to my SharePoint page and click Shared Documents

image

 

I click on my Visio Document and it comes up in a web console.

image

I click Enable (always)

OK now that is done.

Part 4 – Configuring SharePoint 2010 to show your Visio Dashboard as a web part.

 

I want to add my Visio Dashboard as a Web part to my Original SCOM dashboard.

I browse to the actual SharePoint site where I want to display my dashboard.

 

Under Site Actions I click Edit Page

image

 

I Check Out the page

image

 

I put my cursor just below Shared Documents but above my previous dashboard.

image

 

I select Business Data.  Then I select Visio Web Access

 

image

Then I click Add on the middle right of the page.

 

I select Click here to open tool pane.

image

On the right hand side I pick the the … Next to Web Drawing URL

image

 

I then select Shared Documents and select my SCOM-Visio.vdw file.

I changed the Automatic Refresh Interval to 1 minute.

I also de-select Show Open in Visio

image

 

I click OK at the bottom of the screen

Next I click the Format Text tab at the top and Check In the changes and click continue.
image

 

I am finished.  I check out the finished product.

image

Now I just need to create some really cool looking SCOM Visio Dashboards.

Continue Reading

Creating SCOM 2012 Dashboards using Visio 2010 or Visio 2013

 

First I download the Microsoft Visio 2010 and SharePoint 2010 Extensions for System Center 2012 from here
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=29268

 

The install doc says I need these pre-requisites:

  • System Center 2012 – Operations Manager console.
    • As an alternative, you can also install the Authoring Console from Operations Manager 2007 R2.
  • Microsoft Visio 2010 Professional or Premium edition
  • Visual Studio Tools for Office 2010
  • The Microsoft .NET Framework 4.0

I am running Windows 8 with Visio 2013.

 

After the install, I launch Visio 2013 and get this message. I click Install.

image

 

Now I create Blank Drawing.
image

 

At the top I see a new Tab called Operations Manager

image

 

I click Configure.

I give it a Name and my SCOM 2012 Web Console address.  I also select Automatic data refresh

image

 

Now I just click Insert Shape and add the SCOM object I want in my diagram.

It would be nice if I could multi-select here.

image

 

Also I add the Dashboard status so I  know if the link to SCOM is working or not.

image

 

Here is my first diagram/dashboard.  It was easier that I thought.   I see tons of possibilities using this.

image

Continue Reading

Advanced Troubleshooting of the SharePoint 2010 MP

 

The SharePoint 2010 MP is one of the most difficult MPs to troubleshoot.  From the surface it appears it have some magical configuration file and configuration tasks.  But if you drill down into the nuts and bolts of the MP it’s really not magical at all.

First make sure you have followed all the steps in the MP Guides http://www.microsoft.com/en-us/download/details.aspx?id=4419

Also review the following KB article http://support.microsoft.com/kb/2690744

If you are running SCOM 2012 make sure you imported the helper MP http://www.microsoft.com/en-us/download/details.aspx?id=34697

 

Ok so I have done all that, but my SharePoint server still shows up as “Not monitored”

clip_image001

First I enable the DebugTrace by going to the Administration View.  In the Tasks pane, I run “Set DebugTrace for SharePoint Management Pack (SCOM 2012)”

image

image

After a while I notice that I am getting the following error in the Operations Manager event log on my SharePoint server.

Log Name:      Operations Manager
Source:        Operations Manager
Event ID:      0
Level:         Error
Computer:      SP01.scom2k7.com.com
Description:
Cannot identify which SharePoint farm this server is associated with. Check the management pack guide for troubleshooting information.

So what does this mean? 

I triple checked my configuration using the MP guide and KB2690744

I tried tweaking the SharePointMP.config file a few dozen times. But it didn’t fix the problem.

I start thinking this MP can’t be all that magical and I start to dissect the MP.

 

Frist I export all the MPs to XML using my MP2XML Pro tool

Next I open up the Configure SharePoint Management Pack task and try to understand what it is actually doing.  I found the script that is run by the “Configure SharePoint Management” Task in the SCOM 2012 Helper MP

image

After I review the PowerShell Script I conclude it only does three basic things.

-  First it creates a Overrides MP called Microsoft.SharePoint.Foundation.2010.Override.xml.

I open up the Override MP and find out all it does is enable discovery and sets the interval and sync time for the discoveries.  I know that this is not my issue so I move on.

– Second it Enables Agent Proxy on the the servers I listed in the SharePointMP.config file

Not so complex.  I could have did this manually.

-Third it sets the Run-As profiles for the SharePoint Discovery/Monitoring Account

image

Ok so this one seems to be a little more magical!  I can seem to create these from the console, as the Association is System.  But I probably could have just created the associations the normal way.  Not worth spending anymore time on this as I know its correct.

 

So now what?  I have everything setup correctly but my SharePoint server still shows up as Unidentified!

I go back to the event log that I seen earlier. 

Event ID:      0
Level:         Error
Computer:      SP01.scom2k7.com.com
Description:
Cannot identify which SharePoint farm this server is associated with. Check the management pack guide for troubleshooting information

What is causing this?  After a few searches I don’t find anything useful.  I know the alert is generated by the MP so lets search my exported MPs to see why it’s getting generated.

I search the exported MPs for “Cannot identify which SharePoint farm this server is associated with. Check the management pack guide for troubleshooting information” using Agent Ransack (My favorite file search tool)

image

I discover this text is coming from the Microsoft.SharePoint.Foundation.2010.xml management pack

I open up the text file and search for the “Cannot identify which SharePoint farm this server is associated with. Check the management pack guide for troubleshooting information”.

I discovery the text is thrown by a PowerShell script when the variable $global:EventLog_Fail_Identify_Farm I called.

 image

I search for $global:EventLog_Fail_Identify_Farm

I discover that the error will be thrown if variable $global:Farm is equal to null.

image

Lets see what Get-Farm does.

I log into my SharePoint Server.

I launch the the SharePoint 2012 Management Shell and I get this error.

SharePoint Issue

SharePoint Scripts are not working at all on my SharePoint server!  The problem has nothing to do with SCOM.  It’s a SharePoint issue.

I run a regular PowerShell Prompt and try to load the PowerShell extensions and I get this error

“Microsoft SharePoint is not supported with version 4.0.30319.296 of the Microsoft .Net Runtime.”

So how do I fix this?  I search for this error and it turns out it is related to “Windows Management Framework 3.0” being installed.  http://blog.supermega.pro/tag/microsoft-sharepoint-is-not-supported-with-version-4-0-30319-296-of-the-microsoft-net-runtime/

 

So I remove the Windows Management Framework 3.0

Reboot the SharePoint Server.

Just like that all my servers get discovered and everything starts to work!

image

 

If I find any of theses other errors in the future, now I know the process to troubleshoot it.  

EventLog_Fail_Identify_Farm = "Cannot identify which SharePoint farm this server is associated with. Check the management pack guide for troubleshooting information.";
EventLog_Fail_Retrieve_Farm_And_Local = "Failed on retrieving information for SharePoint farm and Sharepoint local machine. Check the management pack guide for troubleshooting information.";
EventLog_Fail_Retrieve_OM_Value = "Failed on retrieving values from SharePoint object model.";
EventLog_Fail_Retrieve_Service = "Failed on retrieving {0} service from SharePoint farm {1}.";
EventLog_Fail_Retrieve_Object = "Failed to find {0} in SharePoint object model.";

Continue Reading

Cumulative Update 7 for SCOM 2007 R2 Released

     

Download Link

KB Link

Issues that are fixed in Cumulative Update 7

  • Console performs poorly when User Roles are used to scope object access.
  • Availability data is not shown for the current day when daily aggregation is used.
  • Behavior is inconsistent between some views on web console and console.
  • Log files do not roll over when the Unicode log file is monitored.
  • Several security issues are fixed.

 

Cross-platform issues that are fixed in Cumulative Update 7

  • Logical disk performance statistics are not collected for some volume types on Solaris computers.
  • Some Network Adapters on HP-UX computers may not be discovered.
  • Network adapter performance statistics are not collected for HP-UX network adapters.
  • The Solaris 8 and 9 agent may not restart after an ungraceful shutdown.
Continue Reading