8 October 2015 blogs Steven Dwyer 4 min read
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 product to show information about service level agreements. While our product 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.
First, we’ll show the sizes of the files the database is composed of by running the query:
from dbo.sysfiles a
Then we’ll show which tables are taking up the most room in the database by running:
SELECT top 25 so.name,
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE ‘U’ = so.type GROUP BY so.name ORDER BY data_kb DESC
(Note that we’ve amended Kevin’s original query to only show the top 25 tables).
And finally, we’ll show which rules generate the most performance data by running the following query (note, we had to write this one ourself):
SELECT top 25 *
SELECT vManagementPack.ManagementPackDefaultName As [Management Pack Name], vRule.RuleDefaultName As [Rule Name], SUM(vPerfDaily.SampleCount) as [Data Points]
INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN vRule ON vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN vManagementPack ON vManagementPack.ManagementPackRowId = vRule.ManagementPackRowId
GROUP BY vManagementPack.ManagementPackDefaultName, vRule.RuleDefaultName
) AS T
ORDER BY [Data Points] DESC
This query looks at data generated for all time, so if you’re just interested in the last while, you can add date constraints to the Perf.vPerfHourly portion.
And the final result appears as:
SCOM Data Warehouse Status
Some of you may recognize these tables from System Center Central Health Check Reports MP that Marnix Wolf has blogged about.
With those basic queries out of the way, what did we end up writing for our customer? We confess it was slightly more involved – the way the database schema is set up for service level objectives is “complicated”. We’ll show you the results first and then present the query as an attachment for the daring – which you can find here.
Core SLA Dashboard