December 3rd – SQL Stored Procedure in a SCOM Widget

Download Stored Procedure:

Download “Download Stored Procedure” SP2Widget-SP1.zip – Downloaded 60 times – 685 B

Download Web Site: 

Download “Web Site Download” SP2Widget-web1.zip – Downloaded 63 times – 130 KB

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

By | 2015-12-03T12:00:00+00:00 December 3rd, 2015|Operations Manager (SCOM), Visualization|0 Comments

About the Author:

Kåre Rude Andersen
Microsoft Certified Trainer since 1996, MCSE, TS and ITP in Microsoft Windows, SQL (also Microsoft), Exchange (MS) and Microsoft Operations Manager. Senior Consultant and have recently worked as a consultant at CSC, SAS, ISS, Nokia and Scandlines.

Leave A Comment