[FIX] Fixing the Top n by Performance Widget

27 May

Update!: Fixed the run issue on SCOM 2012 R2 installations in MP version V1.0.0.6 . Thanks community for pointing out to this R2 issue.

Challenge:

First, I really LOVE the dashboard widgets included in SCOM. When making MPs I always deliver dashboards that gives the operator a one shot overview of the monitored targets. The most used and valuable widget for this is the “Objects by performance Widget”

This works perfect EXECPT when you have more instances of a performance value. Let’s say the table space free of table spaces, or disk C: D: ect from windows servers.

The problem is that most of the time you will get the situation below “Empty Widgets”

 

The problem is the Stored Procedure “Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet“. I don’t go into details because this issue is a known fact and already reported several times on the community. For example by Cameron Fuller http://blogs.catapultsystems.com/cfuller/archive/2013/06/05/issue-with-the-objects-by-performance-widget-with-and-all-performance-instances-scom-sysctr.aspx

But a fix for this in a SCOM CU was till now never released…. Till now….

 

Analyze

As I mentioned before it’s in the Stored Procedure “Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet“. We have a code part that does an exact match on the instance name. If we want to show all instances it will not return any matches. See the yellow parts below.

  •    INSERT
    INTO
    #ResolvedSeriesTable(ManagedEntityRowId, PerformanceRuleInstanceRowId)

                  SELECT
    CET.ContainedEntityRowId, PRI.PerformanceRuleInstanceRowId

                  FROM
    PerformanceRule
    PR

                  JOIN
    PerformanceRuleInstance
    PRI
    ON (PR.RuleRowId = PRI.RuleRowId)

                  JOIN
    #ContainedEntitiesTable
    CET
    ON (1=1)

            WHERE (
    (PR.ObjectName
    =
    @ObjectNamePattern)
    AND

                    (PR.CounterName
    =
    @CounterNamePattern)
    AND

                    (PRI.InstanceName
    =
    @InstanceNamePattern))

Suggestion to fix, is to use a like match. See yellow part.

       INSERT
INTO
#ResolvedSeriesTable(ManagedEntityRowId, PerformanceRuleInstanceRowId)

              SELECT
CET.ContainedEntityRowId, PRI.PerformanceRuleInstanceRowId

              FROM
PerformanceRule
PR

              JOIN
PerformanceRuleInstance
PRI
ON (PR.RuleRowId = PRI.RuleRowId)

              JOIN
#ContainedEntitiesTable
CET
ON (1=1)

        WHERE (
(PR.ObjectName
like
@ObjectNamePattern)
AND

                (PR.CounterName
like
@CounterNamePattern)
AND

                (PRI.InstanceName
like
@InstanceNamePattern))

 

To change this you will need SQL Developer knowledge. And I realize that most of the operators know a lot of backend/frontend products but aren’t developers. So it could be a bit of a challenge to change this stored procedure yourself.

 

Solution

To solve this issue I have created a Management Pack that changes this stored procedure for you. It doesn’t do this automatically, because I want you to choose to do it. So I implemented it as a SCOM task. When you import the MP and go the ManagementServer target that has the property “Is Root Health Service Emulator = True” (you can find it in the view Operations Manager -> Management Server -> Management Servers State) you will see a Task “Task Fix TopNQuery Widget“. Now you execute the task and you will see a Task output below:


And you go to the Widget dashboard you created and what do you see ????

 


Yes a working TopN Widget page.

 

NOTICE!!!

Using this task is totally unsupported. But in my opinion the negative impact is very low compared to the positive impact because this stored procedure is only used for reading data and not changing it so it wouldn’t impact the DB with incorrect data (except for some SQL performance penalty for the use of the like statement).

NOTICE!!!

When you reload the MP Microsoft.SystemCenter.Visualization.Library the stored procedure will be overwritten to the original version. This could happen if you implement an upcoming CU release. If the issue isn’t fixed in this release you must rerun the TASK again.

 

You can download this MP on my personal download site:

https://onedrive.live.com/redir?resid=A6ECD6E173E79D82!6314&authkey=!AE0rJkhRPXOcblI&ithint=file%2c.zip

Happy Scomming

Michel Kamp

Advertisements

10 Responses to “[FIX] Fixing the Top n by Performance Widget”

  1. peter May 28, 2014 at 18:02 #

    Hi, thank you for posting a fix that is easy to implement. However, when I ran this task in my environment (scom 2012 r2 ur2), I get the error below. Any idea? Thank you!

    Task Output:

    1
    You cannot call a method on a null-valued expression.
    At line:55 char:34
    + $STP_TEXT = $STP_TEXT.replace <<<< ($searchtext,$replacetext)
    + CategoryInfo : InvalidOperation: (replace:String) [], RuntimeEx
    ception
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At line:61 char:34
    + $STP_TEXT = $STP_TEXT.replace <<<< ('CREATE PROCEDURE','ALTER PROCEDURE')
    + CategoryInfo : InvalidOperation: (replace:String) [], RuntimeEx
    ception
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: Com
    mandText property has not been initialized"
    At line:63 char:43
    + $rows_affected=$SqlCmd.ExecuteNonQuery <<<< ()
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    Error: Could not change Stored procudure [Microsoft_SystemCenter_Visualization_
    Library_TopNEntitiesByPerfGet]

    • Michel Kamp May 28, 2014 at 18:52 #

      Hi,

      Did You downloaded the latest version ? I only tested it on 2012 sp1.

      Michel

      Verzonden met mijn Windows Phone ________________________________

      • peter May 28, 2014 at 19:13 #

        Hi, I downloaded (today) the only version available at the OneDrive link on this page.

        It’s actually not too difficult to manually update the stored procedure with SQL Server Management Studio. I ended up manually updating it as described in Cameron Fuller post here:

        http://blogs.catapultsystems.com/cfuller/archive/2013/12/09/fixing-the-objects-by-performance-widget-when-using-the-all-performance-instances-scom-sysctr.aspx

        I noticed that he only changed the PRI.InstanceName which didn’t work. So I also changed the PR.ObjectName and PR.CounterName as described in your post and all is working.

        Thank you!

      • Michel Kamp May 28, 2014 at 19:53 #

        Hi, I did a update of the download file 3 h ago. So I think you used the incorrect one. It should be v1.0.0.4

        Verzonden met mijn Windows Phone ________________________________

  2. peter May 28, 2014 at 20:21 #

    Michel, the one that didn’t threw the error was 1.0.0.4. Maybe it won’t work in scom 2012 r2?

    • peter May 28, 2014 at 20:22 #

      correction. version 1.0.0.4 did not work and threw the error.

    • Michel Kamp June 2, 2014 at 14:25 #

      Peter,

      Fixed the run issue on SCOM 2012 R2 installations in MP version V1.0.0.6 .

      Thanks.

      Michel

Trackbacks/Pingbacks

  1. Dashboards | - June 2, 2014

    […] [FIX] Fixing the Top n by Performance Widget […]

  2. [FIX] Part 2. Fixing the Top n by Performance Widget now the supported way | Touching SCOM - September 5, 2014

    […] In May this year I posted an article on how to fix the top-n widgets. https://michelkamp.wordpress.com/2014/05/27/fix-fixing-the-top-n-by-performance-widget/ […]

  3. SCOM link - IT Consult - September 1, 2016

    […] Fixing the Top n by Performance Widget […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: