SCOM: Reporting queries for performance counters

When you want to start making reports in your SCOM environment, you'd first have to learn how the data is stored in the SCOM DataWarehouse and which tables, views or stored procedure you can use.

I use SQL Server Business Intelligence Development Studio. You can use Report Builder, but it is less featured.
After you create a Shared DataSource and a new report, you can create your query for your Data Set.

Below is a query to get performance data from the aggregated view 'PerfDaily' for a computer from counter name 'Working Set'.

-- BEGIN QUERY
SELECT Perf.vPerfDaily.DateTime, Perf.vPerfDaily.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfDaily ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = 'Working Set') AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfDaily.DateTime

--END QUERY

@ComputerName is a parameter which can be entered by a user. Another option is to fill this value with server names based on another query. Like "give me all servers from a specific group".

Such a query could be:

-- BEGIN QUERY
SELECT dbo.vManagedEntity.Name
FROM dbo.vManagedEntity INNER JOIN
dbo.vRelationship On dbo.vManagedEntity.ManagedEntityRowId = dbo.vRelationship.TargetManagedEntityRowId INNER JOIN
dbo.vManagedEntity As CompGroup On dbo.vRelationship.SourcemanagedEntityRowId = CompGroup.ManagedEntityRowId
WHERE CompGroup.DisplayName = 'MyServers'
-- END QUERY

SCOM: Maintenance Mode with PowerShell

There are situations where you want to set maintenance windows on certain machines within your SCOM infrastructure. This can be accomplised with the Operations Console or Command Shell.
The great advantage for SCOM (in comparison with MOM) is that maintance mode can be set on all monitored classes. So it's possible to set maintance mode for a webapplication, without setting your complete IIS webserver to maintenance mode.

This post is about the Command Shell. When you install the SCOM Command Shell (Powershell is a prequirement), you'll get access to numerous SCOM cmdlets for different managing tasks.

To get all cmdlets concerning 'MaintenanceWindow', type:
>get-operationsmanagercommand where-object { $_.Name -match "MaintenanceWindow"}

Below are some examples...

Create a new maintenance window for a computer

# Ask user for input
$strComputerName = Read-Host "Enter computer name"

$objComputer = Get-Agent | Where-Object {$_.Name -match $strComputerName}
$objComputer.HostComputer | New-MaintenanceWindow -StartTime:"3/25/2009 22:00" -EndTime:"3/25/2009 23:30" -Comment: "Server maintenance"

Create a new maintenance window for a group

# Ask user for input
$strGroupName = Read-Host "Enter group name"

$objGroup = get-monitoringobject | Where-Object {$_.DisplayName -eq $strGroupName}
$objGroupAgents = $objGroup.getrelatedmonitoringobjects()

# Looping throug group object
foreach ($objAgent in $objGroupAgents)
{
New-MaintenanceWindow -startTime::"3/25/2009 22:00" -EndTime:"3/25/2009 23:30"
-monitoringObject:$objComputer -comment:"Server group maintenance"
}

Create a new maintenance window on a Web Application
Below is a script that puts a Web Application in maintenance mode. Using the extra get-monitoringclass cmdlet resulted in a faster script, then only using the cmdlet get-monitoringobject with a where clause.

# Ask user for input
$strWebApp = Read-Host "Enter the Web Application name"

# Get class object
$objMonClass = get-monitoringclass where-object { $_.Name -eq "Microsoft.SystemCenter.WebApplication.Perspective"}

# Connect object and add Maintenance Window
get-monitoringobject -MonitoringClass $objMonClass | Where-Object { $_.DisplayName -match $strWebApp } | New-MaintenanceWindow -StartTime:"3/25/2009 22:00" -EndTime:"3/25/2009 23:30" -Comment: "Server maintenance"

.... more examples to come.

See http://www.systemcenterforum.org/downloads/scom-maintenance-mode-script-20/ for ready made scripts to add computer, groups to maintance mode including the related health service.

Start of a System Center blog

Today i started my blog about System Center. A product suite of Microsoft for managing and maintaining your complete client/server IT-Infrastructure.

Because of my experience with various products from System Center, i wanted to share my knowledge with others.

Have a greate time!