Wednesday, July 9, 2008

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%\

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">

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
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%')
FROM CurrentDiskFree
where RowNumber = 1

# -- UnsealMP.ps1 -- #

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

# Usage:
#  . .\UnsealMP.ps1 -f c:\temp\
#  . .\UnsealMP.ps1 -f c:\temp\ -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)
} else {
  write-host "No management pack specified"


Operations Manager Report Authoring Guide

Microsoft Operations Manager 2007 Management Pack Authoring Guide

Introduction to the Operations Manager 2007 Design Guide

Operator Element (RDL)

FilterExpression Element (RDL)

Reports in Management Packs.

ManagementPackDataWarehouseScript.UpgradeUnsupported Property

Enabling the EnterpriseManagementChartControl

Example XML:

Defining Report Datasets for a SQL Server Relational Database

Select the Data Source (Report Wizard)

SSRS examples

Expression Examples in Reporting Services

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

No comments:

All Posts

printQueue AD objects for 2003 ClusterVirtualCenter Physical to VirtualVirtual 2003 MSCS Cluster in ESX VI3
Finding duplicate DNS recordsCommand-line automation – Echo and macrosCommand-line automation – set
Command-line automation - errorlevels and ifCommand-line automation - find and findstrBuilding blocks of command-line automation - FOR
Useful PowerShell command-line operationsMSCS 2003 Cluster Virtual Server ComponentsServer-side process for simple file access
OpsMgr 2007 performance script - VMware datastores...Enumerating URLs in Internet ExplorerNTLM Trusts between 2003 and NT4
2003 Servers with Hibernation enabledReading Shortcuts with PowerShell and VBSModifying DLL Resources
Automatically mapping printersSimple string encryption with PowerShellUseful NTFS and security command-line operations
Useful Windows Printer command-line operationsUseful Windows MSCS Cluster command-line operation...Useful VMware ESX and VC command-line operations
Useful general command-line operationsUseful DNS, DHCP and WINS command-line operationsUseful Active Directory command-line operations
Useful command-linesCreating secedit templates with PowerShellFixing Permissions with NTFS intra-volume moves
Converting filetime with vbs and PowerShellDifference between bat and cmdReplica Domain for Authentication
Troubleshooting Windows PrintingRenaming a user account in ADOpsMgr 2007 Reports - Sorting, Filtering, Charting...
WMIC XSL CSV output formattingEnumerating File Server ResourcesWMIC Custom Alias and Format
AD site discoveryPassing Parameters between OpsMgr and SSRSAnalyzing Windows Kernel Dumps
Process list with command-line argumentsOpsMgr 2007 Customized Reporting - SQL QueriesPreventing accidental NTFS data moves
FSRM and NTFS Quotas in 2003 R2PowerShell Deleting NTFS Alternate Data StreamsNTFS links - reparse, symbolic, hard, junction
IE Warnings when files are executedPowerShell Low-level keyboard hookCross-forest authentication and GP processing
Deleting Invalid SMS 2003 Distribution PointsCross-forest authentication and site synchronizati...Determining AD attribute replication
AD Security vs Distribution GroupsTroubleshooting cross-forest trust secure channels...RIS cross-domain access
Large SMS Web Reports return Error 500Troubleshooting SMS 2003 MP and SLPRemotely determine physical memory
VMware SDK with PowershellSpinning Excel Pie ChartPoke-Info PowerShell script
Reading web content with PowerShellAutomated Cluster File Security and PurgingManaging printers at the command-line
File System Filters and minifiltersOpsMgr 2007 SSRS Reports using SQL 2005 XMLAccess Based Enumeration in 2003 and MSCS
Find VM snapshots in ESX/VCComparing MSCS/VMware/DFS File & PrintModifying Exchange mailbox permissions
Nested 'for /f' catch-allPowerShell FindFirstFileW bypassing MAX_PATHRunning PowerSell Scripts from ASP.Net
Binary <-> Hex String files with PowershellOpsMgr 2007 Current Performance InstancesImpersonating a user without passwords
Running a process in the secure winlogon desktopShadow an XP Terminal Services sessionFind where a user is logged on from
Active Directory _msdcs DNS zonesUnlocking XP/2003 without passwords2003 Cluster-enabled scheduled tasks
Purging aged files from the filesystemFinding customised ADM templates in ADDomain local security groups for cross-forest secu...
Account Management eventlog auditingVMware cluster/Virtual Center StatisticsRunning scheduled tasks as a non-administrator
Audit Windows 2003 print server usageActive Directory DiagnosticsViewing NTFS information with nfi and diskedit
Performance Tuning for 2003 File ServersChecking ESX/VC VMs for snapshotsShowing non-persistent devices in device manager
Implementing an MSCS 2003 server clusterFinding users on a subnetWMI filter for subnet filtered Group Policy
Testing DNS records for scavengingRefreshing Computer Account AD Group MembershipTesting Network Ports from Windows
Using Recovery Console with RISPAE Boot.ini Switch for DEP or 4GB+ memoryUsing 32-bit COM objects on x64 platforms
Active Directory Organizational Unit (OU) DesignTroubleshooting computer accounts in an Active Dir...260+ character MAX_PATH limitations in filenames
Create or modify a security template for NTFS perm...Find where a user is connecting from through WMISDDL syntax in secedit security templates

About Me

I’ve worked in IT for over 20 years, and I know just about enough to realise that I don’t know very much.