December 3rd – SQL Stored Procedure in a SCOM Widget

Download Stored Procedure: zip Download Stored Procedure 685 bytes
Downloaded 28 times

Download Web Site: zip Web Site Download 130.12 kB
Downloaded 30 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):

SNAGHTML5ea9f33c

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.

Elf Oms


Leave a Reply