PowerShell Script: Create Performance Rules from CSV file

IMPORTANT! This script was tested in a LAB environment and is to be considered as Beta. I will not take responsibility for any damage it might cause.

It’s been tested with SCOM R2, can’t say if it works with SP1 or below as I don’t use those environments.

If you find any errors, please contact me: andreas.zuckerhut(at)gmail.com

Index:

- Preface

- Functionality

- Supported Format of the CSV

- Supported Values in the CSV

- Step-by-Step Sample Import

- About those unanswered Questions

Preface:

Creating Performance Rules can be a time consuming process and not only that, it’s a mind numbing one because you have to go through the wizard for each performance counter which is just boring, boring, boring... eyestrain!

Since Performance Rules always have the same schema it’s not too hard to build them through the SDK and thus I wrote this little script which simply takes a csv file with the right format and for each line it creates a new performance rule.

Functionality:

You basically run the script with 4 parameters which are as follows:

Nr. Description Sample Value

1 Root Management Server Name “localhost”

2 Path to File “C:\temp\sample.csv”

3 Separator used by the CSV “;”

4 Target Management Pack name “My.Custom.ManagementPack”

Sample:

C:\temp\CreatePerformanceRulesFromCSV.ps1 “localhost” “C:\temp\sample.csv” “;” “My.Custom.ManagementPack”

Supported Format of the CSV:

At the top of the CSV there have to be column names indicating which values are stored in it.

There have to be at least the following column names, and they have to have exactly these names:

Colum Name Description Sample Value

Object Performance Object Processor

Counter Performance Counter % Processor Time

Instance Performance Instance _Total

AllInstances Boolean if true or false false

Interval Interval in seconds 300

TargetClass Name of the Target Class Microsoft.Windows.Computer

Optionally you can define an EnabledFor column containing classnames.

The idea is to target a rule to Microsoft.Windows.Computer and only enable it for a certain group of computers.

If EnabledFor is present and a value is in the line, the rule will be disabled per default and enabled for the class provided.

Colum Name Description Sample Value

EnabledFor ClassName for Override Microsoft.SQLServer.ComputerGroup

Supported values in the CSV:

Depending on your csv editor, it might do something to the format of the csv that is unhealthy. I took in account that Excel turns ‘”’ into ‘””’ and that it also encloses values with ”value”.

I am aware of that and remove these in case they are there.

Besides that, we also needed to be able to add $Target references, for the SQL Server Performance Counters for example.

SQL Performance Counters are always like this: %InstanceName%:ObjectName

I won’t explain this now as it’s already been explained by others.

But if you need to add any $Target references, just add them without the MP Alias. Example:

$Target/Property[Type="Microsoft.SQLServer.DBEngine"]/PerformanceCounterObject$:SQL Statistics

In usual case you’d say: Type=”SQLMPAlias!Microsoft.SQLServer.DBEngine”

However, the Alias is a configurable value, therefore what I do in the script is: I get the Class you provide there, get the Management Pack, add it to the References in case it’s not referenced yet, get the Alias and replace the Type=”ClassName” with Type=”MPAlias!ClassName”

Step-by-Step Sample Import:

Create a new Management Pack:

In the Operations Console, open the Administration pane, right click Management Packs and select “Create Management Pack”

Enter a name and note the ID as we need it later. In my case: Performance.Rules

Next, Create. That’s it. We created a new Management Pack and can now drop rules in it.

Fill the CSV file:

Open the Sample.csv file I attached in Excel and add some rules. I left 2 samples in there for usage reference:

If you don’t know the Name of a class you can run this from the Operations Manager Shell:

get-monitoringclass | where {$_.DisplayName -eq 'Windows Computer'} | select Name

Determine the separator:

Once the CSV file is saved, open it with notepad. In my case Excel used the semicolon

Import:

We need to provide the following parameters:

RMS Name: In my case localhost as I run it directly from the RMS

Path to File: In my case C:\SampleImport\Sample.csv

Separator: In my case ;

Management Pack Name In my case Performance.Rules

Open Powershell, and start the script with your parameters, in my case:

C:\SampleImport\CreatePerformanceRulesFromCSV.ps1 “localhost” “C:\SampleImport\Sample.csv” “;” “Performance.Rules”

And I get this output:

About those unanswered Questions:

What if the csv is in an “unsupported” format?

It checks if all 6 mandatory parameters are there, if it matches more than 6 times (because you had 2 Object columns for example), or less than 6 times because you missed the Instance column, the script will just abort.

Same goes for a wrong separator, you’ll get an error like this: