Category Archives: Azure

Calculating SQL Database DTU for Azure SQL DB Using PowerShell

Written by Tao Yang

over the last few weeks, I have been working on a project related to Azure SQL Database. One of the requirements was to be able to programmatically calculate the SQL Database DTU (Database Throughput Unit).

Since the DTU concept is Microsoft’s proprietary IP, the actual formula for the DTU calculation has not been released to the public. Luckily, Microsoft’s Justin Henriksen has developed an online Azure SQL DB DTU Calculator, you can also Justin’s blog here. I was able to use the web service Justin has developed for the online DTU Calculator, and I developed 2 PowerShell functions to perform the calculation by invoking the web service. The first function is called Get-AzureSQLDBDTU, which can be used to calculate DTU for individual databases, the second function is called Get-AzureSQLDBElasticPoolDTU, which can be used to calculate DTU for Azure SQL Elastic Pools.

Obviously, since we are invoking a web service, the computer where you are running the script from requires Internet connection. Here’s a sample script to invoke the Get-AzureSQLDBDTU function:

Note: you will need to change the variables in the ‘variables’ region, the $LogicalDriveLetter is the drive letter for the SQL DB data file drive.

The recommended Azure SQL DB service tier and coverage % can be retrieved in the ‘Recommendations’ property of the result:

image

the raw reading for each perf sample can be retrieved in the ‘SelectedServiceTiers’ property of the result:

image

Lastly, thanks Justin for developing the DTU calculator and the web service, and pointing me to the right direction.

HybridWorkerToolkit PowerShell Module Updated to Version 1.0.3

Written by Tao Yang

Few days ago, I published a PowerShell Module to be used on Azure Automation Hybrid Workers called HybridWorkerToolkit. You can find my blog article HERE.

Yesterday, my good friend and fellow CDM MVP Daniele Grandini (@DanieleGrandini) gave me some feedback, so I’ve updated the module again and incorporated Daniele’s suggestions.

This is the list of updates in this release:

  • A new array parameter for New-HybridWorkerEventEntry called “-AdditionalParameters”. This parameter allows users to insert an array of additional parameters to be added in the event data:

SNAGHTMLb6e7547

  • A new Boolean parameter for New-HybridWorkerEventEntry called “-LogMinimum”. This is an optional parameter with the default value of $false. When this parameter is set to true, other than the user specified messages and additional parameters, only the Azure Automation Job Id will be logged as event data:

image

As we all know, we pay for the amount of data gets injected into our OMS workspace, this parameter allows you to minimise the size of your events (thus saves money on your OMS spending).

I have published this new release to both GitHub and PowerShell Gallery.

New PowerShell Module HybridWorkerToolkit

Written by Tao Yang

HybridWorkerToolkit23/04/2016 Update: released version 1.0.3 to GitHub and PowerShell gallery. New additions documented in this blog post.

21/04/2016 Update: updated GitHub and PowerShell gallery and released version 1.0.2 with minor bug fix and updated help file.

Introduction

Over the last few days, I have been working on a PowerShell module for Azure Automation Hybrid Workers. I named this module HybridWorkerToolkit.

This module is designed to run within either a PowerShell runbook or a PowerShell workflow runbook on Azure Automation Hybrid Workers. It provides few functions that can be called within the runbook. These activities can assist gathering information about Hybrid Workers and the runbook runtime environment. It also provides a function to log structured events to the Hybrid Workers Windows Event Logs.

My good friend and fellow MVP Pete Zerger posted a method he developed to use Windows event logs and OMS as a centralised logging solution for Azure Automation runbooks when executed on Hybrid Workers. Pete was using the PowerShell cmdlet Write-EventLog to log runbook related activities to Windows event log and then these events will be picked up by OMS. Log Analytics. This is a very innovative way of using Windows event logs and OMS. However, the event log entries written by Write-EventLog are not structured are lacking basic information about your environment and the job runtime.  Couple of weeks ago, another friend of mine, Mr. Kevin Holman from Microsoft also published a PS script that he used to write to Windows event logs with additional parameters.

So I combined Pete’s idea with Kevin’s script, as well as some code I’ve written in the past for Hybrid Workers, and developed this module.

Why do we want to use Windows Event logs combined with OMS for logging runbook activities on Hybrid workers? As Pete explained on this post, it provides a centralised solution where you can query and retrieve these activity logs for all your runbooks from a single location. Additionally, based on my experience (and also confirmed with few other friends), is that when you use Write-Verbose or Write-Output in your runbook and enabled verbose logging, the runbook execution time can increase significantly, especially when loading a module with a lot of activities. Based on my own experience, I’ve seen a runbook that would normally takes a minute or two to run with verbose logging turned off ended up ran over half an hour after I enabled verbose logging. This is another reason I’ve developed this module so it gives you an alternative option to log verbose, error, process and output messages.

Functions

This module provides the following 3 functions:

  • Get-HybridWorkerConfiguration
  • Get-HybridWorkerJobRuntimeInfo
  • New-HybridWorkerRunbookLogEntry

Note: Although the job runtime are different between PowerShell runbooks and PowerShell Workflow runbooks, I have spent a lot of time together with Pete making sure we can use these activities exactly the same ways between PowerShell and PowerShell workflow runbooks.

Get-HybridWorkerConfiguration

This function can be used to get the Hybrid Worker and Microsoft Monitoring Agent configuration. A hash table is returned the following configuration properties retrieved from Hybrid Worker and MMA agent:

  • Hybrid Worker Group name
  • Automation Account Id
  • Machine Id
  • Computer Name
  • MMA install root
  • PowerShell version
  • Hybrid Worker version
  • System-wide Proxy server address
  • MMA version
  • MMA Proxy URL
  • MMA Proxy user name
  • MMA connected OMS workspace Id

Get-HybridWorkerJobRuntimeInfo

This function retrieves the following information about the Azure Automation runbook and the job run time. They are returned in a hashtable:

  • Runbook job ID
  • Sandbox Id
  • Process Id
  • Automation Asset End Point
  • PSModulePath environment variable
  • Current User name
  • Log Activity Trace
  • Current Working Directory
  • Runbook type
  • Runbook name
  • Azure Automation account name
  • Azure Resource Group name
  • Azure subscription Id
  • Time taken to start runbook in seconds

New-HybridWorkerRunbookLogEntry

This function can be used to log event log entries. By default, other than the event message itself, the following information is also logged as part of the event (placed under the <EventData> XML tag:

  • Azure Automation Account Name
  • Hybrid Worker Group Name
  • Azure Automation Account Resource Group Name
  • Azure Subscription Id
  • Azure Automation Job Id
  • Sandbox Id
  • Process Id
  • Current Working Directory ($PWD)
  • Runbook Type
  • Runbook Name
  • Time Taken To Start Running in Seconds

This function also has an optional Boolean parameter called ‘-LogHybridWorkerConfig’ When this parameter is set to $true, the event created by this function will also contain the following information about the Hybrid Worker and MMA:

  • Hybrid Worker Version
  • Microsoft Monitoring Agent Version
  • Microsoft Monitoring Agent Install Path
  • Microsoft Monitoring Agent Proxy URL
  • Hybrid Worker server System-wide Proxy server address
  • Microsoft OMS Workspace ID

Sample Runbooks

Sample PowerShell Runbook:

Sample PowerShell Workflow Runbook

As you can see, the way to call these functions between PowerShell and PowerShell Workflow runbooks are exactly the same.

Hybrid Worker Configuration output:

SNAGHTML40e35ad

Hybrid Worker Job Runtime Info output:

SNAGHTML40f4d28

Event generated (with basic information / without setting –LogHybridWorkerConfig to $true):

SNAGHTML4159a60[4]

Event generated (whensetting –LogHybridWorkerConfig to $true):

SNAGHTML4150515

Consuming collected events in OMS

Once you have collected these events in OMS, you can use search queries to find them, and you can also create OMS alerts to notify you using your preferred methods.

Searching Events in OMS

i.e. I can use this query to get all events logged by a particular runbook:

Type=Event “RunbookName: Test-HybridWorkerOutput-PSW”

image

or use this query to get all events for a particular job:

Type=Event “JobId: 73A3827D-73F8-4ECC-9DE1-B9340FB90744”

image

OMS Alerts

i.e. if I want to create an OMS alert for any Error events logged by New-HybridWorkerRunbookLogEntry, I can use a query like this one:

Type=Event Source=AzureAutomation?Job* EventLevelName=Error

image

image

Download / Deploy this module

I have published this module on Github as well as PowerShell Gallery:

GitHub Repository: https://github.com/tyconsulting/HybridWorkerToolkit

PowerShell Gallery:  http://www.powershellgallery.com/packages/HybridWorkerToolkit/1.0.3

Credit

I’d like to thank Pete and Kevin for the ideas in the first place, also I’d like to thank Pete, Jakob Svendsen, Daniele Grandini and Kieran Jacobsen for the testing and feedback!

A Major Update for the SharePointSDK PS Module

Written by Tao Yang

Sharepoint-2013-LogoIntroduction

This blog has been a bit quiet over the last few weeks. This is because I have been really really busy. I have spent a lot of time working on an updated version of the SharePointSDK PS module. Just in case you have not played with this module, here’s some background info:

Just over a year ago, I posted a PowerShell / SMA / Azure Automation module on this blog called SharePointSDK. Few months ago, I have also published this module on Github and PowerShell Gallery. This module was designed to help automate operations around SharePoint lists (i.e. CRUD operations for SharePoint list items). Coupling SharePoint (both On-prem version or SharePoint Online) with Azure Automation (or even SMA) is becoming more and more common in the community when designing automation solutions. This module provides ways for your automation runbooks to interact with SharePoint list items.

However, I believe the original 1.0 release was really basic, and there are still a lot I’d like to cover in this module. Now I’m pleased to announce the new major release (version 2.0.1) is now available on both Github and PowerShell Gallery.

What’s New?

I’ve included the following updates in version 2.0.1:

  • 26 additional functions!
  • Updated the SharePoint CSOM (Client Component SDK) DLLs to the latest version in the module.
  • Created a separate help file for the module. Get-Help is now fully working
  • Various bug fixes

The table below lists all the functions that are shipped in the current release (version 2.0.1):

Function Description Released on Version
Import-SPClientSDK Load SharePoint Client SDK DLLs 1.0
New-SPCredential Create a SharePoint credential that can be used authenticating to a SharePoint (online or On-Premise) site. 1.0
Get-SPServerVersion Get SharePoint server version. 1.0
Get-SPListFields Get all fields from a list on a SharePoint site. 1.0
Add-SPListItem Add a list item to the SharePoint site. 1.0
Get-SPListItem Get all items from a list on a SharePoint site or a specific item by specifying the List Item ID. 1.0
Remove-SPListItem Delete a list item to the SharePoint site. 1.0
Update-SPListItem Update a list item to the SharePoint site. 1.0
Get-SPListItemAttachments Download all attachments from a SharePoint list item. 1.0
Add-SPListItemAttachment Upload a file as a SharePoint list item attachment. 1.0
Remove-SPListItemAttachment Remove a SharePoint list item attachment. 1.0
New-SPList Create a new list on the SharePoint site. 2.1
Remove-SPList Remove a list from the SharePoint site. 2.1
Get-SPList Get a list from the SharePoint site. 2.1
New-SPListLookupField Create a new lookup Field for a SharePoint list. 2.1
New-SPListCheckboxField Create a new checkbox Field for a SharePoint list. 2.1
New-SPListSingleLineTextField Create a new single line text Field for a SharePoint list. 2.1
New-SPListMultiLineTextField Create a new Multi-line text Field for a SharePoint list. 2.1
New-SPListNumberField Create a new number Field for a SharePoint list. 2.1
New-SPListChoiceField Create a new choice Field for a SharePoint list. 2.1
New-SPListDateTimeField Create a new date time Field for a SharePoint list. 2.1
New-SPListHyperLinkField Create a new Hyperlink or Picture Field for a SharePoint list. 2.1
New-SPListPersonField Create a new Person or Group Field for a SharePoint list. 2.1
Remove-SPListField Remove a Field from a SharePoint list. 2.1
Update-SPListField Update a SharePoint list field. 2.1
Set-SPListFieldVisibility Set the visibility of a SharePoint list field. 2.1
Get-SPGroup Get a single group or all groups from the SharePoint site. 2.1
New-SPGroup Create a new SharePoint group. 2.1
New-SPGroupMember Add an user to a SharePoint group. 2.1
Remove-SPGroupMember Remove an user from a SharePoint group. 2.1
Clear-SPSiteRecycleBin Empty the SharePoint site recycle bin. 2.1
Get-SPSiteTemplate Get avaialble Site Template(s) from the SharePoint site. 2.1
New-SPSubSite Create a new SharePoint sub site. 2.1
Get-SPSubSite Get all SharePoint sub sites from a root site. 2.1
Remove-SPSubSite Delete a SharePoint sub site. 2.1
Add-SPListFieldToDefaultView Add a SharePoint list field to the list default view. 2.1
Remove-SPListFieldFromDefaultView Remove a SharePoint list field to the list default view. 2.1

As you can see, the previous version has shipped 11 functions, and 26 additional functions have been added to the current release (2.0.1). With this release, other than the SharePoint list items, we are also able to manage SharePoint lists, list fields, groups, group members, and even subsites. I have included functions to create what I believe the most common list fields (as highlighted below):

image

Future Plans

At this stage, there are still few things I’d like to cover in this module but I simply do not have time. Since I think I have reached another milestone at this stage, I have decided to release this version now and roll other ideas into the future release.

In the second week of March, I will be presenting at SCU APAC (Kuala Lumpur, Malaysia) and Australia (Melbourne).  I am presenting 2 identical sessions at both locations:

  • Be a hero and save the day with OMS and Power BI (Co-present with CDM MVP Alex Verkinderen)
  • Automation for IT Ops with OMS and Azure Automation (Co-present with CDM MVP Pete Zerger)

As part of the demos I have prepared for the Azure Automation session with Pete, I will cover how I’m using this module as part of my automation solutions.

After SCU, I am planning to write another blog post for my Automating OpsMgr series which will cover one of the our SCU demos (I know, it has been a long time since my last post for that series). I will also cover this module in more details in this upcoming blog post.

Download the Module

So for now, if you’d like to give this module a try, you can find it from both GitHub and PowerShell Gallery. All functions are fully documented in the help file. You can access the help document as well as code examples using Get-Help with –Full switch.

Lastly, if you have any feedback, or suggestions for future releases, please feel free to drop me an email.

This is all I have to share for today, until next time, happy automating Smile.

Azure Automation Runbook: Test-OMSAlertRemediation

Written by Tao Yang

Couple of weeks ago, I published a post titled OMS Alerting Walkthrough. I mentioned in the post that I have written a test runbook called Test-OMSAlertRemediation that extracts information from the OMS alert JSON input sends to you via email.

Once you have created this rnbook in your Azure Automation account, you can use it as the remediation runbook for any OMS alerts.

Source code:

Requirements

This runbooks uses the SendEmail module for sending emails. You can install it to your automation account directly from PowerShell gallery(https://www.powershellgallery.com/packages/SendEmail/), or download the source code from GitHub(https://github.com/tyconsulting/SendEmail_PowerShellModule). Once the module is deployed to your Automation Account, you will then need to create a connection with type “SMTPServerConnection” with the name “SMTPNotification”:

SNAGHTML2d83c96

You will also need to place your email address in the last line of the runbook.

The email below is a sample of what this runbook produces:

SNAGHTML2e18888

Hopefully this runbook would help you when you are designing your OMS alerting solutions.

OMS Alerting Walkthrough

Written by Tao Yang

imageIntroduction

Earlier today, the OMS product team has announced the OMS Alerting feature has entered Public Preview. This is indeed an exciting news and it is another good example that Microsoft is working very hard to close the gaps between OMS and the existing On-Prem monitoring solution – System Center Operations Manager. Alex Frankel from the OMS product team has already given a brief introduction on this feature from the announcement blog post. In this post, I will demonstrate how I used this feature to alert and auto-remediate an issue detected in my lab environment.

Background

Few months ago, I have lost my lab OpsMgr management group completely due to hardware failures. After I replaced faulty hardware and built a brand new management group, I re-configured all the servers in my lab reported to the new management group. However, I then started getting many “Failed to enable Advisor Connector on the computer” alerts in my OpsMgr environment:

image

These alerts were raised because I did not unregister these agent computers from the previous management group (I couldn’t because it was dead), as explained in this forum thread. To fix this issue, on each OpsMgr agent, I must delete several registry keys, reset a regkey  values and then restart the health service.

Since My OpsMgr management group is connected to an OMS workspace, and I have enabled Alert Management solution (so all OpsMgr alerts are also uploaded into OMS), I have configured using the new OMS alerting feature to automatically remediate this error for me.

In order to configure the alerting and remediation for this OpsMgr alert, I need to following components:

  • OpsMgr management group connected to OMS
  • OMS Alert Management solution enabled
  • OMS Automation solution (Azure Automation) enabled
  • At least one Azure Automation Hybrid Worker is configured (because I need to target the remediation runbook to on-premises lab servers.
  • OMS Alerting and Alert remediation feature enabled

Creating Azure Automation Runbook

So first things first, I must create and publish the remediation runbook in the Azure Automation account before we can select it when we create the OMS alert. Although we cannot configure what parameters to pass into the runbook, the OMS alert passes the search result and some meta data into the runbook in JSON format (I will show it later). So based on my experience, in order to make the runbooks re-useable, we can some optional input parameters for the runbook, and inside the runbook, check if any of these optional parameters are null, then retrieve the value elsewhere (i.e. Azure Automation variable and credential assets).

In this case, I have created a PowerShell based runbook called Remove-SCAdvisorRegistration, the code is listed below:

Now, let’s fast forward a little bit and explain what does the input parameter from OMS alert look like. When we have configured Alert remediation during the OMS alert creation, a webhook for the runbook is automatically created. OMS uses this webhook to start the runbook. It passes a parameter called “WEBHOOKDATA”, which is in JSON format into the runook. You can see the actual input by clicking on the INPUT tile in the runbook job execution history:

image

If you copy and paste this input into a text editor such as Notepad++ and format it as a JSON document, it looks like this:

image

As you can see, the “SearchResults” contains 3 elements:

  • id
  • __metadata
  • Value

The Value property is where you can retrieve the search result, and it is defined as an array. When I was writing the remediation runbook, I was able to get the offending OpsMgr agent computer name from the “SourceDisplayName” field of each item in the “Value array”.

Now the runbook is created, make sure it is published before we heading back to the OMS portal start creating the alert. Please note that we will have to come back and revisit this runbook after the alert is created.

Creating OMS Alert

The search query that I’m using for this alert is:

Type=Alert AlertState=New AlertName=”Failed to enable Advisor Connector on the computer.”

image

I’m creating the alert with the following parameters:

  • Name: Alert – Failed to enable Advisor on computer
  • Schedule: every 15 minutes
  • Generate Alert when: Greater than 0
  • Over the time window: 15 minutes
  • Send Email Notification: Yes
  • Email Subject: Failed to enable Advisor on computer alert
  • Email Address: <Your email address>
  • Enable Remediation: Yes
  • Remediation Runbook: Remove-SCAdvisorRegistration

SNAGHTML1a29c296

After the alert is saved, you will be able to see it in the Settings/Alerts page:

image

Reconfiguring Runbook Webhook

In this example, because the runbook must be executed against a Hybrid Worker group (as we are targeting computers in on-prem network), I must reconfigure the webhook (created by OMS alert) to target a Hybrid Worker group (instead of the default config of targeting Azure workers). You can do so by going to the webhook parameters section, and choose Hybrid Worker group from the drop down list:

image

Note:

Please do not modify any other input parameters for the webhooks created by OMS alerts. If you do, the changes you’ve made won’t be saved in Azure Automation. Based on my experience, the only change you can modify for the webhook is the “Run on” parameter (Azure VS. Hybrid Worker).

From now on, this alert will be executed every 15 minutes, and search for the result (based on the search query) created within the last 15 minutes. If the number of records returned from the search is greater than 0 (as we configured), you will get an email similar to this one:

image

The OMS alert will also kick off the remediation runbook via the webhook. Because I have enabled verbose logging for this runbook, I was able to see some additional verbose messages:

image

Additional Resources

Test-OMSAlertRemediation Runbook

I have also written a test runbook called Test-OMSAlertRemediation that you can use for any OMS alerts. This extracts information from the JSON input and send to you via email. It should be very helpful for you when you are authoring real remediation runbooks (so you know what kind of input data you can play with). I will publish it in the next blog post as it’s getting closer to mid night now.

New OMS Ebook – Inside the MS Operations Management Suite

Over the last few months, I have been working with Pete Zerger, Stanislav Zhelyazkov and Anders Bengtsson on a free ebook for OMS. OMS Alerting is also explained in more details in this book. It will be released very soon, so stay tuned!

OMS_Book_Anncmt

Azure Automation Runbook: New-FakeVirus

Written by Tao Yang

Often when you are playing with security related products, you would need to create dummy/fake viruses on your computers. The most common way to do this is to create a EICAR test file (https://en.wikipedia.org/wiki/EICAR_test_file).

I have used this method in the past when testing the Microsoft Forefront Endpoint Protection management pack in OpsMgr. Today I needed to use it again when I was preparing a demo for the OMS Malware Assessment. I thought, why not make an Azure Automation runbook that automatically create the EICAR test file for me on remote computers, so I can trigger it manually or schedule it to run on a regular basis? So here’s what I came up with.

CAUTION: Use it at your own risk! And obviously, this runbook is designed to run on hybrid workers Smile.

Runbook: New-FakeVirus

You will need to specify 3 optional input parameters:

image

  • Credential: The name of the credential asset saved in your Azure Automation account – If you need to use an alternative credential to connect to the target computer (via WMI)
  • ComputerName: The target computer of where the fake virus is going to be created, if not specified, it will be created on the runbook worker itself.
  • Folder: the folder of where the file is going to be created on the target computer. If not specified, the runbook will use the System environment variable %TEMP%.

Runbook Output:

image

If your Windows Defender or System Center Endpoint Protection (SCEP) is working correctly, you will see this on your target computer straightaway:

image

If the target computer is monitored by OpsMgr and you have imported the Forefront Endpoint Protection (FEP) 2010 MP, you’ll get an alert:

image

And you will also see in the OMS Malware Assessment dashboard shortly:

image

image

Start A Child Runbook From Azure Automation Hybrid Worker on the Same Hybrid Worker Group

Written by Tao Yang

Today I was writing a PowerShell runbook (let’s call it Runbook A) that’s designed to run on on-prem hybrid workers. At the end of Runbook A, I needed to kick off another runbook (let’s call it Runbook B) that must run on the same Hybrid Worker group. Because I don’t want to hardcode the Hybrid Worker group name in the script (or using an Automation variable), I wrote a very simple function that returns the Hybrid Worker configuration (including the Hybrid Worker group name) from registry if runs on a Hybrid Worker.

To use it, simply place the function shown below in the parent runbook (Runbook A in this case), and call this function to retrieve the Hybrid Worker configuration.

Function:

SNAGHTML2f76e97b

Code Sample:

Note:

The Get-HybridWorkerConfig function would return $null value if the computer is not a Hybrid Worker.

Azure Automation Webhooks Now Support Hybrid Workers

Written by Tao Yang

My friend and fellow CDM MVP Pete Zerger just pinged me and told me he just spotted that Azure Automation webhooks now support targeting Hybrid Workers.

The webhook configuration used to look like this:

image

(Source image from David O’Brien’s blog: http://www.david-obrien.net/2015/05/azure-automation-webhooks/)

Currently, the webhook configuration looks like this:

image

Few days ago when Pete and I delivered the Azure Automation session at Microsoft Ignite Australia, in one of our demos, we used Webhook to kick off a process to create AD user accounts on On-Prem Active Directory using Hybrid Workers. Because Webhook did not support Hybrid Workers back then, we had to use an workaround of kicking off an intermediary runbook that runs on Azure worker then start AD user creation runbook on runbook worker groups within the intermediary runbook.

Now with the new capability, we can further simplify our demo scenario by removing the intermediary runbook as we are now able to kick off runbooks on Hybrid Workers directly from webhooks!

Automating OpsMgr Part 19: Creating Any Types of Generic Rules

Written by Tao Yang

OpsMgrExntededIntroduction

This is the 19th instalment of the Automating OpsMgr series. Previously on this series:

Although I have written number of functions in the current version of the OpsMgrExtended module that allows you to  create some popular types of rules in OpsMgr (i.e. perf collection rules and event collection rules). Sometimes, you still need to create other types of rules, such as WMI event collection rules, or rules based on module types written by yourself. In this post, I will demonstrate how to create any types of rules using the New-OMRule function.

Additionally, since the OpsMgrExtended module can be used on both your on-prem SMA infrastructure as well as on your Azure Automation account (with the help of Hybrid Workers), and pretty much all the previous runbooks and posts are based on SMA, I will use Azure Automation in this post (and maybe in the future posts too). I will demonstrate 2 sample runbooks in this post. Since Azure Automation now supports PowerShell runbooks on both Azure runbook workers as well as on hybrid workers, with the 2 sample runbooks I’m going to demonstrate, one is based on PowerShell workflow and the other one is a PowerShell runbook.

What Components are OpsMgr Rules Made Up?

Before we diving into the sample runbooks, please let me explain how are the OpsMgr rules made up. In OpsMgr, a rule is essentially a workflow that contains the following components:

  1. One or more Data Source modules
  2. Zero or one Condition Detection Modules
  3. One or more Write Action modules

To explain in plain English, a rule can have multiple data source modules and write action modules, but condition detection module is optional, and you can only use up to 1 condition detection module in your rule. The order of execution is Data Source Modules –> Condition Detection Module –> Write Action Modules. Additionally, some modules requires mandatory and/or optional configuration parameters (i.e. System.SimpleScheduler), some modules do not require any configuration parameters (i.e. Microsoft.SystemCenter.CollectPerformanceData).

OM-Rule Design Consideration

When I was writing the New-OMRule function, I have realised in order to capture all required information for each member module, the following information is required:

  • Module Type Name (Mandatory)
  • Configuration (Optional)
  • Member Module Name (Mandatory)
  • RunAs Profile Name (Optional)

i.e. in the example below, I have high lighted the information required in a member module of a rule.

image

Other than the information listed above, if we are writing an alert-generating rule, we would also need the following information for the alert configuration:

  • String Resource
  • Language Pack ID
  • Alert Name
  • Alert Description

I needed to figure out a way to enforce users to supply all required information listed above. In order to do that, I think the best way is to define a class for member module configurations and another class for alert configurations. However, since class definition is a new concept only been introduced in PowerShell version 5 (which still in preview other than Windows 10 machines at the time of this writing), I could not do this natively in PowerShell. In order to work around this limitation, I have defined these two classes in OpsMgrExtended.Types.dll using C#. This DLL is shipped as part of the OpsMgrExtended module.

image

The OM-Rule is expecting instances of these classes defined in OpsMgrExtended.Types.dll as input parameters. You will see how I used these classes in the sample runbooks.

Sample PowerShell Runbook: New-WMIPerfCollectionRule

OK, let’s start with a “simpler” one first. The New-WMIPerfCollectionRule runbook is a PowerShell runbook that can be used to create perf collection rules based on WMI queries. I think it’s simpler than the other one because it’s a PowerShell runbook (as opposed to PowerShell workflow) and we don’t have to worry about configuring alerts for the rules created by this runbook. The source code for this runbook is listed below:

As you can see, this runbook requires the following input parameters:

  • RuleName – the internal name of the rule
  • RuleDisplayName – the display name of the rule. this is what people will see in OpsMgr console
  • ClassName – The internal name of the target class (i.e. “Microsoft.Windows.OperatingSystem”)
  • WMINameSpace – the WMI name space of where the WMI class you are going to query resides. This is optional. if not specified, the default value of “Root\CIMV2” will be used
  • WMIQuery – the WMI query to retrieve the performance counter value
  • IntervalSeconds – the rule execution interval in seconds. this is optional, if not specified, the default value of 900 (15 minutes) will be used.
  • ObjectName – The object name for the performance data (i.e. Process, or LogicalDisk)
  • CounterName – the counter name for the performance data (i.e. ProcessCount, or FreeSpaceMB)
  • InstanceNameWMIProperty – the property returned from the WMI query which represent the performance data instance value (i.e. if you are collecting logical disk counters, the result of WMI query may contain a property that represent the drive letter, which can be used to identify the instance in perf data). This is optional, if not specified, the perf data instance name would be “_Total”.
  • ValueWMIProperty – the property returned from the WMI query that represent the perf value.

The first step of this runbook is to retrieve a connection object named “OpsMgrSDK_Home” from my Azure Automation account (or SMA). This connection object is pre-configured, which contains the computer name of one of my OpsMgr management servers, and the credential of a service account which has OpsMgr admin privilege in my management group.

SNAGHTMLddea7c

The runbook also needs to explicitly import the OpsMgrExtended module. During the test, I found for this PowerShell runbook, I must manually import the module using Import-Module cmdlet in order to load the assemblies defined in the OpsMgrExtended.Types.dll. Since we are going to be using hybrid workers, you must manually deploy this module to your hybrid workers because at the time of this writing, Azure Automation is not able to automatically deploy required modules to hybrid workers.

Next, we must define the module configurations for each member module used by the WMI perf collection rules that this runbook creates. I have determined this type of rules should be configured using the following modules

  • One Data Source Module
    • Microsoft.Windows.WmiProvider
  • One Condition Detection Module
    • System.Performance.DataGenericMapper
  • Three Write Action Modules
    • Microsoft.SystemCenter.CollectPerformanceData
    • Microsoft.SystemCenter.DataWarehouse.PUblishPerformanceData
    • Microsoft.SystemCenter.CollectCloudPerformanceData

To explain in English, the rules created by this runbook would periodically execute a WMI query (Data source module), then map the result from WMI query to OpsMgr performance data (Condition detection module), finally store the performance data in OpsMgr operational DB, data warehouse DB and also OMS workspace (3 write action modules).

So we will need to use the New-OMModuleConfiguration function from the OpsMgrExtended PS module to create an instance of the “OpsMgrExtended.ModuleConfiguration” class. As explained earlier, the “OpsMgrExtended.ModuleConfiguration” class is defined in the OpsMgrExtended.Types.dll. Take the data source member module as an example:

I have placed the module type name, module configuration and the member module name into separate variables and passed them to the New-OMModuleConfiguration function and created a module configuration object for the data source module.

Note:

You can use Get-Help New-OMModuleConfiguration to access the help file for this function. If you need to use an alternative OpsMgr RunAs profile to for any member modules, you can also specify the name of the RunAs profile you are going to use with the –RunAsMPName and the –RunAsName parameter. The RunAsMPName parameter is used to specify the internal name of the management pack that defined the RunAs profile, and the RunAsName parameter is used to specify the internal name of the RunAs profile (i.e. if you are creating rules for SQL related classes, you might need to use the default SQL RunAs Profile, in this case, the –RunAsMPName would be “Microsoft.SQLServer.Library” and –RunAsName would be “Microsoft.SQLServer.SQLDefaultAccount”.)

Since a rule can have multiple data source modules and multiple write action modules, the New-OMRule function is expecting the array type of input for data source modules and write action modules. This is why even there is only going to be one data source member module or write action member module, we still need to place them into separate arrays before passing into the OM-Rule function:

image

image

On the other hand, since the condition detection member module is optional, and you can only have maximum one condition detection member module, you do not need to place the module configuration object for the condition detection module into an array.

Lastly, I have hardcoded the management pack name to be “Test.OpsMgrExtended” in this sample runbook. this MP must be created prior to running this runbook otherwise it would fail. However, if you have a look at the sample runbooks in previous posts, you can easily figure out a way to firstly detect the existence of this MP and use this runbook to create the MP if it does not exist in your management group.

Now, it’s time to take this runbook for a test run. I’m using the following parameters during the test run:

  • RuleName: “Test.WMIPerfCollection.Process.Count.WMI.Performance.Collection.Rule”
  • RuleDisplayName: “Windows Server Process Count Performance Collection Rule”
  • ClassName: “Microsoft.Windows.OperatingSystem”
  • WMIQuery: “select Processes from Win32_PerfRawData_PerfOS_Objects”
  • ObjectName: “Process”
  • CounterName: “ProcessCount”
  • ValueWMIProperty: “Processes” –verbose

And I have specified to run on a hybrid worker group:

SNAGHTMLd3df42

The hybrid worker will pick up this job very soon, and during this test run, the job was completed around 2 minutes. after the job finishes, I’m able to find this rule in OpsMgr console:

image

image

Sample PowerShell Workflow Runbook: New-WindowsEventAlertRule

Now, let’s take a look at the second sample runbook. This runbook is designed to create rules that detects certain event log entries and generates alerts upon detection. I think it’s more complicated than the first sample because this is a traditional PowerShell workflow runbook (which works on both SMA and Azure Automation), and we also need to configure alert settings for this rule. The source code for this runbook is listed below:

This runbook takes the following input parameters:

  • RuleName – the internal name of the rule that you are creating
  • RuleDisplayName – the display name of the rule
  • ClassName – The internal name of the target class (i.e. “Microsoft.Windows.OperatingSystem”)
  • EventLog – the name of the event log (i.e. “System”)
  • EventID – the ID of the event that you are detecting
  • EventSource – is what you see as “source” in Windows event log
  • EventLevel – can be one of the following value:
    • Success
    • Error
    • Warning
    • Information
    • Audit Failure
    • Audit Success
  • AlertName – the alert name / title
  • AlertSeverity – can be one of the following value:
    • Critical
    • Warning
    • Information
  • AlertProirity – can be one of the following value:
    • Low
    • Medium
    • High

As you can see, firstly, other than the standard process of retrieving the connection object for my OpsMgr management group, I have used several “Switch” statements with inline scripts to translate the event level, alert priority and alert severity from English words (string) to numbers (integer), because when configuring member modules for OpsMgr rules, we must use the number (instead of names). Note I’ve also used “ValidateSet” to validate the input of these parameters, so only valid inputs are allowed.

I am not going to explain the member module configurations again, because I’ve already covered it in the first sample. But please note because the rules created by this runbook will be generating alerts, we must configure alert settings. In OpsMgr, when a workflow is configured to generate alerts (either rules or monitors), other than the rule / monitor itself, we must also define a String Resource for the alert message ID, as well as defining the alert name and description in a preferred language pack (by default, ENU). Therefore, we are going to use another class defined in OpsMgrExtended.Types.dll for alert configuration. The class for alert configuration is called OpsMgrExtended.AlertConfiguration, and you can use New-OMAlertConfiguration function to create an instance of this class. Same as all other functions in the OpsMgrExtended PS module, you can use Get-Help cmdlet to access the help file for New-OMAlertConfiguration. You will need to specify the following input parameters for New-OMAlertConfiguration:

  • AlertName – the name / title of the alert
  • AlertDescription – the alert description / detail
  • LanguagePackID – the 3-letter language pack code for the language pack that you wish to create the alert message under. this is an optional parameter, if not specified, the default value of “ENU’’ will be used.
  • StringResource – the ID for the alert string resource

As you can see, since the write action member module for the rules created by this runbook would be “System.Health.GenerateAlert”, and we are defining <AlertMessageId> and <AlertParameters> in the write action member module configuration:

image

The String Resource must match the AlertMessageId:

image

And if you have previously authored OpsMgr management packs, you’d probably already know how to define the <AlertParameters> section for the alert description. Basically, any variables you are using in the alert description must be defined in the <AlertParameters> section, then in the alert description, you’d reference them using “{}” and a number inside. <AlertParameter1> becomes {0}, <AlertParameter2> becomes {1}, and so on. You can up to define 10 alert parameters:

image

Since you can define multiple alert messages (for multiple language packs), when you are creating alert generating rules, the New-OMRule function would expect you to pass in an array that contains OpsMgrExtended.AlertConfiguration objects. So, even if you are only defining the alert in one language pack, please still place it into an array before passing to the New-OMRule function:

image

OK, now, let’s give this runbook a test run with the following parameters:

  • RuleName: “Test.Disk.Controller.Event.Alert.Rule”
  • RuleDisplayName: “Disk Controller Error Event Alert Rule”
  • ClassName: “Microsoft.Windows.OperatingSystem”
  • EventLog: “System”
  • EventID: 11
  • EventSource: “Disk”
  • EventLevel: Error
  • AlertName: “Windows Disk Controller Error”
  • AlertSeverity: Critical
  • AlertPriority: High

SNAGHTML1055790

After the hybrid worker in my lab executed the runbook, I am able to see the rule created in OpsMgr console:

image

image

and the raw XML (once I’ve exported the MP):

image

Hybrid Worker Configuration

Since the samples in this post are all based on Azure Automation and Hybrid worker, I just want to point out this article if you need help setting it up: https://azure.microsoft.com/en-us/documentation/articles/automation-hybrid-runbook-worker/

Also as I mentioned earlier, you will need to deploy OpsMgrExtended module manually on to the hybrid workers by yourself. When copying the OpsMgrExtended module to your hybrid workers, make sure you copy to a folder that’s listed in the PSModulePath environment variable. During my testing with a PowerShell runbook, I initially placed it under “C:\Program Files\WindowsPowerShell\Modules” folder, as it was listed in the PSModulePath environment variable when I checked in a PowerShell console on the hybrid worker. However, I got error messages telling me the runbook could not find commands defined in the OpsMgrExtended module. To troubleshoot, I wrote a simple PowerShell runbook:

image

and based on the output, the folder in “C:\Program Files” is not listed!

image

Therefore, I had to move the module to another location (C:\Windows\System32\WindowsPowerShell\v1.0\Modules). after the move, the runbook started working.

Summary

In this post, I have demonstrated how to use the New-OMRule function from OpsMgrExtended PS module to create any types of rules in OpsMgr. Although OpsMgrExtended module has already shipped two other functions to create event collection and performance collection rules, the New-OMRule fills the gap that allows users to specify each individual member module and its configurations. This is probably the most technically in-depth post in the Automating OpsMgr series. I have tried my best to explain and demonstrate how to use the New-OMRule function. But if you are still unclear, have questions or issues, please feel free to contact me.

I haven’t figured out what I will cover in the next post, but I still have a lot to cover in this series. Since I am attending few conferences in the coming weeks in November, I probably won’t have time to work on part 20 until end of November. Until next time, happy automating!