The System Center Operations Manager data warehouse contains a wealth of information that can be surfaced by running reports in the console. However, using reports isn’t the only way you can extract valuable information from SCOM; you can also write tiny snippets of SQL to return exactly what you need to see.
Recently, we had a customer approach us with a specific dashboarding request. They were using the HTML5 dashboards that come with our Live Maps Unity solution to show information about service level agreements. While our solution has a built in widget that shows whether you’re currently meeting your service level objectives or not, they also wanted to see how they had performed against their service level agreements in the past.
This was a perfect opportunity to use the new SQL Query widget that we’ve recently added to the product. The widget lets you run arbitrary SQL queries against any SQL Server or Oracle database and presents the results in a nice table format.
When you configure the widget, you’re asked for the connection information for the database you wish to query; however, if you’re querying the SCOM data warehouse database you don’t need to provide anything beyond the SQL you want to run.
Before we jump into the query we wrote for our customer, let’s get our feet wet with a few simple ones. Kevin Holman lists a large number of useful queries and while he wrote them for SCOM 2007 many are still valid for 2012 as well.
We’ll put together a simple dashboard that shows us some information about our operational database using some of the queries Kevin wrote about.
Read the full blog and learn more about the new SQL Query widget within Live Maps Unity.