Data Warehouse Queries for Noisy Rules

Here are a few SQL queries that you can run on the Operations Manager Data warehouse to determine what are the Noisy Event and Performance rules in your enviroment.

Noisy Event Rules

select count(*) as cnt, RuleDefaultName
from Event.vEvent as ev
Left Join Event.vEventRule as evi
On ev.eventOriginID = evi.eventOriginID
Left Join vRule as r
On r.RuleRowId = evi.RuleRowId
group by RuleDefaultName
order by cnt desc

Noisy Performance Rules

select count(*) as cnt, RuleDefaultName
from Perf.vPerfRaw as pr
Left Join vPerformanceRuleInstance as pri
On pr.PerformanceRuleInstanceRowId = pri.PerformanceRuleInstanceRowId
Left Join vRule as r
On r.RuleRowId = pri.RuleRowId
group by RuleDefaultName
order by cnt desc


As expected, in a few enviroments I tested, some of the busiest performance rules were the Processor and Memory collection rules.

However there were several unexpectedly busy Event rules that accounted for a significant portion of all the event data and some of these were subsequently disabled.

Thanks to Reut for pointing me in the right direction for the initial query.

Useful methods for searching MP's

Usually when i am looking for a way to do something in a management pack I refer to the Microsoft MP's as it has likely been done in them. Here is my normal process for searching these MP's.

In my development environment I dump all the management packs out to their XML. This is done using Command Shell.

mkdir c:\unsealed
get-managementpack | export-managementpack -path C:\unsealed

Then I open Notepad++ with the XML Tools plugin installed. A fantastic function of Notepad++ is Find in Files...

So say I am looking for the syntax to use in a recovery to start a service. I can Open up Notepad++ and do a Find in Files and look for "net start"




A few comments I read on the OpsMgr newsgroups about the Agent Maintenance Mode solution that I posted pointed out that the %MOMROOT% variable was not on the path and could not be used from the command line. If you do a search across all the MP's using the methid above it is defined in several different ones ( Sharepoint 2003, System Center Core Management Pack). Looks like this is something defined only within the context of OpsMgr.

Unsealed MP Backup with Retention Period

A small expansion of Pete's MP Export for Disaster Recovery. Here is an Management Pack that exports all the unsealed MP's once a day to a folder. The folder name is the date of the backup. Backups older than 7 days will be deleted.

Download the Management Pack

---Update---

Link to the MP has been fixed

Event description in Monitor or Rule

When creating an alert based on the Event description, this property is missing from both the Authoring console and the Ops console.

To be able to use the Event description you must add this property.



This example will capture Event with ID zero and the description containing the substring "Error 11" or "Error 17"

Embed a Powershell Script in a Management Pack

- Updated -

OpsMgr R2 allows you to embed powershell scripts in a MP - See this MP for an example - http://derekhar.blogspot.com/2009/11/new-agent-maintenance-mode.html

----------

If you want to run powershell, perl or any other scripts not run by cscript.exe from a management pack then you invariably had to create the script and distribute it across all the servers where it needed to run. This could easily become quite complex maintaining all these scripts in multiple different locations. If you are in a situation where you have multiple management groups and none of them are quite the same then you could have multiple different versions of the script and the rule that would call the script.

To avoid this problem it is now possible to embed other scripts in a management pack, not just vbscript and jscript.

To explain how this is done we need to break in to the XML. Open the image below to view the full command. This would be executed using on a recurring basis using a System.Scheduler datasource.


The Write Action aboves enables proxying for all management servers. This check is quite easy to do using the command shell alias.

<ApplicationName>%systemroot%\System32\windowspowershell\v1.0\powershell.exe</ApplicationName>

This line launches the Powershell exe. Remember that Powershell must be installed on any server you are targeting the rule at. The same goes for any other script language that you wish to use.


<WorkingDirectory>%MOMROOT%</WorkingDirectory>

The %MOMROOT% variable point to the Installation path of OpsMgr. This is useful when targeting Powershell scripts at the RMS or Management servers as it allows you to easily load the Command Shell Snap-in. This then gives access to all the Command Shell aliases.


<CommandLine>-Command "&amp; '$File/EnableProxy.ps1$'"</CommandLine>

The arguments being passed specifies to run the script $File/EnableProxy.ps1$. After this the file must be entered.


<Files>
<File>
<Name>EnableProxy.ps1</Name>
<Contents>

The name of the script must match the command line name. While I have not tested this there appears to be the ability to embed multiple files here.

Now we drop into the script itself. This rule is targeted to run only on the RMS so $rootMS is set as localhost. The next 3 lines load the Command Shell snapin, and opens the connection to the local management group.

$rootMS="localhost"
add-pssnapin "Microsoft.EnterpriseManagement.OperationsManager.Client";
set-location "OperationsManagerMonitoring::";
new-managementGroupConnection -ConnectionString:$rootMS;


Here is an example Management Pack

Data Warehouse Query

If you have multiple management groups rolling up to a single data warehouse here is a query that will allow you to return a computer name and its associated management group. Not great or anything but the first attempt at digging in to the db.

SELECT ManagedEntity.DisplayName, ManagementGroup.ManagementGroupDefaultName
from OperationsManagerDW.dbo.ManagedEntity
INNER JOIN OperationsManagerDW.dbo.ManagementGroup ON
ManagedEntity.ManagementGroupRowId
= ManagementGroup.ManagementGroupRowId
WHERE fullname like 'Microsoft.Windows.Computer:%'

Active Directory Agent Assignment using a database

The post over on System Center forum about Using Property Bags with Custom Scripting in Operations Manager 2007 has helped me to solve a problem I was having with doing Active Directory based Agent Assignment using a database. This is for integration with a CMDB type database to control agent assignment.

This will take the form of a rule that runs a script to query a database and returns multiple property bags with each property bag containing the dnshostname and distinguished name of each computer that is to be assigned to a management server.

The output fo this script can then be passed to the AD writer action that writes the computer objects to the relevant security groups in the management group OU under the Operations Manger OU.

The rule that is generated by the GUI using the LDAP provider to perform the LDAP query and returns a result for each object found and passes each result seperatley to the Ad Writer rule. This is where the multiple property bags comes in as the of the AD Writer action expects multiple inputs that can only be provided, as far as I can determine, by using multiple property bags.

The rule will run on each management server and the script will simply query the database for the agents belonging to that management server. All the load balancing will be done by the database itself.

I will be have more on this rule in future posts.

Passing an Event Description to an Alert description

Finding the correct Data Item Context Parameters to use in Operations manager can be a problem. Here is the one to pass an Event description to an Alert. Useful if you want the Alert description to contain the Event description.

$Data/Context/EventDescription$

Operation Manager Command Shell on any system

This is a basic guide to get a Operations Manager Command shell on any system without having to install the UI components. You will need to have Powershell and DotNet 3.0 installed on the system.

Copy the Files

You will need to copy some files from your Ops manager install to get the command shell working.

From the Ops Manager installation directory:

Microsoft.EnterpriseManagement.OperationsManager.ClientShell.dll Microsoft.EnterpriseManagement.OperationsManager.ClientShell.dll-help.xml Microsoft.EnterpriseManagement.OperationsManager.ClientShell.Format.ps1xml Microsoft.EnterpriseManagement.OperationsManager.ClientShell.Types.ps1xml

From the SDK Binaries Folder in the Ops Manager installation directory:

Microsoft.EnterpriseManagement.OperationsManager.dll
Microsoft.EnterpriseManagement.OperationsManager.Common.dll

Copy these files to a folder on the system you want the command shell on. For this example I will copy them to C:\OpsShell.

Register the Powershell Snap-in

For the Powershell snap-in to work we need to register it. As we are not installing the UI this will have to be done manually. Add the below registry settings replacing "C:\\OpsShell" in the following regsitry entries with the correct path to the files.

-----------

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\PowerShellSnapIns\Microsoft.EnterpriseManagement.OperationsManager.Client] "ApplicationBase"="C:\\OpsShell" "AssemblyName"="Microsoft.EnterpriseManagement.OperationsManager.ClientShell, Version=6.0.4900.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" "ModuleName"="C:\\OpsShell\\Microsoft.EnterpriseManagement.OperationsManager.ClientShell.dll" "PowerShellVersion"="1.0" "Vendor"="Microsoft Corporation" "Version"="6.0.4900.0" "Description"="Microsoft Operations Manager Shell Snapin" "Types"="C:\\OpsShell\\Microsoft.EnterpriseManagement.OperationsManager.ClientShell.Types.ps1xml" "Formats"="C:\\OpsShell\\Microsoft.EnterpriseManagement.OperationsManager.ClientShell.Format.ps1xml"

-----------


Create the Startup Script

Below is a Powershell script that you can use as a shortcut to start the Command Shell. Copy it to notepad and save it in C:\OpsShell as OpsShell.ps1. Some of the script was borrowed from here

-----------

param ($rootMS)

$rootMS.Length
if ($rootMS -eq $null)
{
$rootMS = Read-Host "Root manangement server"
}

$checksnappin = Get-PSSnapin | where {$_.Name -eq "Microsoft.EnterpriseManagement.OperationsManager.Client"}
if ($checksnappin -eq $null)
{
add-pssnapin "Microsoft.EnterpriseManagement.OperationsManager.Client" -ErrorVariable errSnapin ;
}
Set-Location "OperationsManagerMonitoring::" -ErrorVariable errSnapin ;
new-managementGroupConnection -ConnectionString:$rootMS -ErrorVariable errSnapin ;
set-location $rootMS -ErrorVariable errSnapin ;

-----------


Starting the Command Shell

To start the Command Shell open powershell and run C:\OpsShell\OpsShell.ps1.


If you get the error:

File C:\OpsShell\OpsShell.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.

Then run this command to change the execution policy:

Set-ExecutionPolicy Unrestricted

This works for me on XP SP2 and Server 2003 SP2

This could be expanded to offer the ability for our server admins to do things such as set Maintenance Mode on or off without having to do it in 3 different locations in the console as is currently needed.