OpsMgr 2007 Customized Reporting - SQL Queries

This post provides information on SQL queries relating to Microsoft Operations Manager 2007. I do not have much knowledge of OpsMgr 2007 or SQL, but the following includes information on becoming familiar with the database, using OpsMgr groups and querying XML with SQL - all integral parts of authoring Operations Manager 2007 reports.

Familiarity with the OpsMgr Data warehouse database

The default reports provide many specific and generic report types, but apart from modifying the layout of an existing result set, the only way to report on extra information is to run additional queries against the data warehouse.

There are several different ways to become familiar with the Operations Manager 2007 Data Warehouse database, including:

1. The Management Pack authoring guide, the report authoring guide and the design guide. See the References section.
2. There are dozens of example queries in the sealed management packs provided with Operations Manager 2007. See below for information on unsealing a management pack
3. There are many web pages providing example SQL queries and custom management packs.

Unsealing a Management Pack

Management packs usually exist in:
c:\Program Files\System Center Operations Manager 2007\

To unseal a management pack:

1. Copy the management pack from the server to your workstation
2. Change directory to the folder containing the script, Start PowerShell, and then run
3. . .\UnsealMP.ps1 -f %path%\%file%.mp

The powershell script can be found at the end of this post.

This provides the XML for the management pack, typically containing any SQL queries used in the operation of that management pack.

Parsing Managed Entity IDs

One of the most useful smart parameter controls available in Operations Manager 2007 is the ParameterPrompt.ObjectList control, providing the ability to select one or more groups and/or objects to use as the managed entity filter for the report.

Creating Groups

Depending on the data being queried, it seems that the direct objects returned in the search are not always relevant. For example, when choosing instances of performance-based rules, selecting specific objects - such as a specific ‘Windows Computer’ instance - does not return any results. This is because the performance rules are created against a different ManangedEntityRowID - the logical disk free counter for each instance of a logical disk against that windows computer instance.

It is more practical and less management overhead to create a structured series of dynamic groups, used throughout all functions of Operations Manager. For example, groups containing types of servers, such as clusters, IIS servers, SMS servers etc usually makes sense when reporting.

This OpsMgr smart control creates XML and passes it as a string parameter to SSRS (SQL Server Reporting Services), which is in turn passed to the stored procedure used for the main dataset.

The following SQL query creates an example XML string containing the object ID of a group which essentialy contain a group of servers. This would return multiple managed entities relating to each server in the group, including the servers themselves, cluster servers, logical drives, network connections, group policy and license objects.

DECLARE @ExecError int
DECLARE @StartDate datetime
DECLARE @EndDate datetime
Set @EndDate = GetDate()
Set @StartDate = DateAdd(day, -1, @EndDate)

DECLARE @ManagedEntity table (ManagedEntityID int)
DECLARE @TESTGROUP nvarchar(256)

Select @TESTGROUP = '<data><objects><object use="Containment">
' + Cast(ManagedEntityRowID as varchar) + '</object></objects></data>'
from vmanagedentity where managedentitydefaultname = 'Test Group'

INSERT INTO @ManagedEntity
EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
@StartDate = @StartDate,
@EndDate = @EndDate,
@ObjectList = @TESTGROUP

select * from vmanagedentity
inner join @ManagedEntity MET on vmanagedentity.ManagedEntityRowID = MET.ManagedEntityID

Querying XML using TSQL

Passing multi-valued parameters from the Operations Manager Smart Controls through the SSRS report to SQL can be difficult, and the method employed most by the default Microsoft reports seems to be XML.

SQL 2005 includes the XML data type and associated methods, including XQuery, a subset of the XPath query standard. In addition, SQL supports OPENXML – a rowset provider to construct a relational rowset view of an XML document.

The second method below is used in the ‘Managed Entity Current Instance’ report to pass a multi-valued string parameter as XML to the SQL query. After the XML has been transformed to a rowset, it is then used in the where clause to filter the instance names of the performance counters being returned.

Included below are two examples of processing an XML string, and querying an element value from the XML elements inside a SQL insert/select clause.

declare @execerror int
declare @xmldoc xml
declare @ixmldoc int
set @xmldoc = '<data><objects><object use="Containment">
123</object><object use="Containment">
234</object></objects></data>'
'

DECLARE @tblTest table (test int)

/* Parse the XML document and insert the converted int object element value into temporary table */
element value into temporary table */
EXEC @ExecError = sp_xml_preparedocument @ixmldoc OUTPUT, @xmldoc
INSERT INTO @tblTest
SELECT * FROM
OPENXML (@ixmldoc, '/Data/Objects/Object')
WITH (InstanceFilter int '.')

select * from @tbltest

/* Translate the value of object nodes to an int from XML document into the test field of the table */ nodes to an int from XML document into the test field of the table */
INSERT INTO @tblTest (test)
select tblTest.test.value('.', 'int')
from @xmldoc.nodes('/Data/Objects/Object') AS tblTest(test)

select * from @tbltest

Example SQL

An example I was providing executes a generic version of the following SQL, selecting the most recent performance rule instance for each the specified rule and managed entity, in this case, servers with a name matching ‘%testserver%’ and the 'Logical Disk Free Megabytes’ performance rule.

This query generates a temporary named result set, partitioning that result set using the row-number() ranking windowing function, over managed entities by time and then selecting the first record of each partition.

WITH CurrentDiskFree AS
(SELECT PRI.Instancename, DateAdd(Hour, 10, PPR.DateTime) as DateTime, ME.Path, ME.ManagedEntityRowID, PPR.SampleValue,
ROW_NUMBER() OVER (partition by ME.ManagedEntityRowID order by PPR.DateTime DESC)as RowNumber
from vperformanceruleinstance PRI
inner join vPerformanceRule PR on PRI.RuleRowID = PR.RuleRowID
inner join perf.vPerfRaw PPR on PRI.PerformanceRuleInstanceRowID = PPR.PerformanceRuleInstanceRowID
inner join vManagedEntity ME on ME.ManagedEntityRowID = PPR.ManagedEntityRowID
inner join vRule RU ON RU.RuleRowID = PR.RuleRowID
WHERE RU.RuleDefaultName = 'Logical Disk Free Megabytes'
AND ME.Path like '%testserver%')
SELECT *
FROM CurrentDiskFree
where RowNumber = 1

# -- UnsealMP.ps1 -- #

param(
  $FileInput = "",
  $outputDirectory = $pwd)

# Usage:
#  . .\UnsealMP.ps1 -f c:\temp\test.mp
#  . .\UnsealMP.ps1 -f c:\temp\test.mp -o c:\ManagementPacks
#

$OMManagement = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.OperationsManager")

if ($FileInput -ne "") {
    $mp = new-object Microsoft.EnterpriseManagement.Configuration.ManagementPack($FileInput)
    $mpWriter = new-object Microsoft.EnterpriseManagement.Configuration.IO.ManagementPackXmlWriter($outputDirectory)
    $mpWriter.WriteManagementPack($mp)
} else {
  write-host "No management pack specified"
}

References:

Operations Manager Report Authoring Guide
http://blogs.technet.com/momteam/archive/2008/02/26/operations-manager-report-authoring-guide.aspx

Microsoft Operations Manager 2007 Management Pack Authoring Guide
http://download.microsoft.com/download/7/4/d/74deff5e-449f-4a6b-91dd-ffbc117869a2/OM2007_AuthGuide.doc

Introduction to the Operations Manager 2007 Design Guide
http://download.microsoft.com/download/7/4/d/74deff5e-449f-4a6b-91dd-ffbc117869a2/OpsMgr2007_DesignGuid.doc

Operator Element (RDL)
http://technet.microsoft.com/en-us/library/ms154634.aspx

FilterExpression Element (RDL)
http://technet.microsoft.com/en-us/library/ms154035.aspx

Reports in Management Packs.
http://blogs.msdn.com/eugenebykov/archive/2007/05/18/reports-in-management-packs.aspx

ManagementPackDataWarehouseScript.UpgradeUnsupported Property
http://msdn.microsoft.com/en-us/library/microsoft.enterprisemanagement.configuration.managementpackdatawarehousescript.upgradeunsupported.aspx

Enabling the EnterpriseManagementChartControl
http://go.microsoft.com/fwlink/?LinkId=111034

Example XML:
http://blogs.technet.com/momteam/archive/2008/02/26/operations-manager-report-authoring-guide.aspx
http://blogs.msdn.com/eugenebykov/archive/2007/05/18/reports-in-management-packs.aspx

Defining Report Datasets for a SQL Server Relational Database
http://msdn.microsoft.com/en-us/library/ms159260.aspx

Select the Data Source (Report Wizard)
http://technet.microsoft.com/en-us/library/ms189364.aspx?ref=Sawos.Org

SSRS examples
http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part-4/

Expression Examples in Reporting Services
http://msdn.microsoft.com/en-us/library/ms157328.aspx

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.