Download Stored Procedure: Download Stored Procedure 685 bytes
Downloaded 43 times
Download Web Site: Web Site Download 130.12 kB
Downloaded 48 times
If you already knows about the Widgets in SCOM you have perhaps thought about how to execute a stored procedure against a SQL server directly from your console. However – I have used the following use-cases a couple of times before and used it for presenting different information.
In this Use-case its about creating a widget which is able to show the current performance on the SQL Server database disk for your SCOM implementation (Web Browser Widget):
First of all – why produce and present this information. Often when I do a Review of existing SCOM installations two things you don’t ignore are the Page Life Expectancy and the Disk Performance on the SQL Server. So why not present it directly in the SCOM Console?
A big thanks to Brent Ozar (http://www.brentozar.com/) which properly knows everything about performance in regards to a SQL server. He have a Stored Procedure (SP_Blitz) which is both great and free to download.
But lets begin by creating the foundation for the SP:
1) Install an IIS on a server and create a directory: x:\inetpub\wwwroot\SP2Widget, download and paste the following zip file:
2) In the Web.Config file change the last 4 variables according to your environment:
|IntervalSeconds||Update Interval – default 15 secs|
|DBServer||Database Server FQDN|
|DBQuery||Name of Stored Procedure, Default MP_GetSCOMPerformance|
|DB||Name of Database for the SP|
3) If you want the same Stored Procedure as in this sample please run the following SQL code to create the Stored Procedure
4) Create a new Dashboard in SCOM, and choose a Grid Widget – One of the Widgets in the Grid should be a Web Browser widget. and paste the URL for your IIS and Web2Data web site.
May you have a great Christmas with a lot of expensive but unhappy children.