Update!: Fixed the run issue on SCOM 2012 R2 installations in MP version V22.214.171.124 . Thanks community for pointing out to this R2 issue.
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….
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.
Suggestion to fix, is to use a like match. See yellow part.
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.
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.
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).
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: