Using the SQL Sentry PowerShell Action

As I explained previously about new features in version 11.2 of SentryOne, we have introduced a new Condition Action that can execute PowerShell. I will now demonstrate a potential use-case for getting the most of this Action in order to integrate SentryOne with Slack and Teams.

High Level SentryOne Architecture

Before we start looking at making use of the PowerShell Condition Action we need to understand the SentryOne monitoring architecture. This will allow us to understand how we can best utilize the PowerShell Condition Action.

First and foremost it is important to understand that SentryOne uses a scale-out design pattern at the monitoring service layer. As such it is possible for SentryOne to scale with your Data Platform Estate. When you add more servers and services, you simply need to add more SentryOne services. These will then distribute the load evenly over the services, as well as rebalancing if one of the servers goes offline. So there is both load balancing and high availability built-in to the SentryOne architecture.

Once the services are in place it is a simple task of adding Targets and Connections. These can range from on-premises SQL Servers through to Azure SQL Database or Azure SQL DW. With regards to the PowerShell Action, it is important to understand which Targets you are going to be monitoring as this will have a bearing on where the PowerShell can be executed.

PowerShell Execution Locations

Once we have SentryOne in place we can think about where it is possible to execute the PowerShell scripts that we will be placing into the Condition Action. Building on the architechture view that we have just looked at, we can identify potential execution locations. It is important to remember that the PowerShell will be executed by the monitoring service, where it is executed is defined when we create the Condition Action.

Execute on Monitoring Server

The first option, and one that I will be using for the demonstration part of this post, is to execute the script on the same server as the monitoring service. Now I am sure you are asking yourself, "which monitoring service will execute the script?" Well, it will be the monitoring service that is currently monitoring the Target and, in turn, detects the Condition associated with the Action. As such it can, in theory, be executed on any of the monitoring servers. This means that we need to ensure that any PowerShell modules referenced in the scripts are installed on all of the monitoring service servers. More on this later.

Execute on Target

Another option is to execute the script on the target that generated the Conditon that was detected. This has a number of additional constraints around making sure that PowerShell remoting is enabled. Again - it is important to ensure that the appropriate modules are in place before the script is executed, as well as ensuring that PowerShell remoting is enabled.

Execute on Specified Target

Building on the execution of the PowerShell on the target, there is the option to specify a dedicated target on which to execute scripts. This can be useful if you need to access external resources that are not accessible from many of the targets that are being monitored.

Overall the execution locations are where a PowerShell runtime is located. This means that any of the PaaS targets cannot be selected as a target upon which to execute the PowerShell. The diagram below highlights where the PowerShell can be executed and where it cannot.

Getting ready to use PowerShell Condition Actions

Now that we understand the execution model and locations we can look at the options around making use of the Action. Here I am going to explore the options to pass SentryOne Condition Messages to external channels, specifically Slack and Teams channels. This is possible because as of PowerShell 3.0 the Invoke-RestMethod cmdlet was introduced. This allows interaction with REST web services for performing different options such as POST, GET etc. This, combined with the ability to create and configure inbound webhooks for both Slack and Teams, is what allows me to make this integration.

Setting up Slack

Prior to being able to send SentryOne notifications to Slack I need to configure the inbound WebHooks. This is done using the following steps:

1. Login to your Slack Workspace via the web browser
2. From the admin menu, select the "Manage apps" option
3. In the App Directory, select the "Browse the App Directory" link to see all of the options
4. Search for "Incoming WebHooks" and select the app when it is visible
5. Select the "Add Configuration" option to progress to setting up the Incoming WebHook

Now that we have selected the WebHook app we need to finalize the configuration starting with selecting the channel that we want the WebHook to send messages to. Here I have the option to select an existing channel or to create one if I have not already done so. Having used Slack for a while with a number of other application integrations I am of the opinion that it is best to send messages to a dedicated channel. This will mean that the work channels that you use to communicate in the business will not get interupted if notifications arrive. Additionally, this also gives a focus point that everyone can look to for notifications. Now all we need to do is click on the "Add Incoming WebHooks integration".

This now gives us the ability to see the WebHook URL as well as giving us the opportunity to modify the configuration. By default the name and image used for the WebHook is going to be "incoming-WebHook" and the little fidget-spinner type icon. I would suggest altering this to reflect where the notification is coming from, as such I will set the following configuration parameters for the WebHook;

1. Descriptive label
2. Customize Name
3. Customize Icon

As you can see, this will make it a lot easier to see where the message is coming from.

Now all we need to do is take the URL from this configuration page and we are ready to start using the PowerShell Action.

Setting up Teams

At Ignite 2017, Microsoft made the announcement that Teams and Skype for Business would be merging into one collaboration application. Teams offers very similar capabilities to that of Slack for channel based team communications but with a number of other benefits. These include logically grouping channels into defined teams as well as deep integration with many office and third party apps. Because this is a SaaS offering one of the native integration methods is again WebHooks. Here we will walk through the setup required to allow SentryOne to send notifications to Teams.

The first step is to login to Teams and make sure that you have a team for which you can create a channel. I have already created a team called "Data Platform Monitoring" within my workspace and will use this. By default there is a "General" channel however, as with the Slack integration, I would recommend creating a dedicated notifications channel. This will allow for greater versatility in dealing with any notifications that might come in. The steps to configure the Teams channel to receive notifications are as follows;

1. Click on the properties of the channel and select "Connectors"
2. Search for "Incoming WebHook" and click "Configure"

Once this is in place we can go through the same configuration process for the WebHook as we did for Slack. Specifically, setting the name and icon that will be displayed when messages are posted to the channel in Teams. After configuring the WebHook we can now get the URL which we will use later in our PowerShell scripts.

Using PowerShell Condition Actions

Now that we have everything in place with which to receive the notifications, it is time to setup SentryOne PowerShell Actions. For the purposes of this post I will be using the Availability Group Failover failsafe conditon as the trigger for my PowerShell action.

The first step is to select the level at which we want this condition to apply in the logical hierarchy of our SentryOne Targets. Once you have established this, select the conditon you need and then select the "Execute PowerShell" Action for it. This will then present you with the "Conditions" pane where we can now configure the Action. The first step is to set the "Server" field. Given that I am using the AG Failover Condition, the Target option is not appropriate. As such I will be using the Monitoring Server.

The next stage is to understand the execution context that will be used when the Condition equates to true and the Action is fired. There are three options available, all of which can be set on in the Action Settings tab. The first, and default, option is the "PowerShell Execution Account". This is a global account that can be configured in the "PowerShell" section of the "Alerting" tab in "Global Settings". Another option is to use a domain account that is specified in that Conditon Action. The final option is to use an account local to the execution location. I will be using the Global Execution Account for this Action.

Now we get to the bit that makes it all work, the PowerShell.

For both Slack and Teams we can make use of the native Invoke-RestMethod cmdlet available in PowerShell 3.0 and above.

Let's take a moment and look through this script and some of the key elements within it.

Use of SentryOne Execute Actions Parameter

You will have noticed on the first line of the script that there is a message placeholder with the format of <%Message%>. This is an Execute Action Parameter in SentryOne and allows us to pass content into these execution actions. We've discussed this before in Use Execute Action Parameters for Feeding Events to a Ticketing System .

WebHook URL

The next section of the script is where we specify the URL for the WebHook that we setup earlier. This will then be passed into the Invoke-RestMethod call later in the script.

Convert Message Body to JSON and Send

The main body of the message that we are sending needs to be in JSON format for both Slack and Teams. Here we are setting a parameter that specifies the content that is being sent in the message, as well as converting the raw message to JSON. Once all this is completed the Invoke-RestMethod cmdlet can be called and the message sent.

Enter PSSlack

As you saw previously it is possible to use the native Invoke-RestMethod cmdlet in PowerShell. If you know that you only want to send messages to Slack then there is a module for that: Warren Frame (Blog|Twitter) aka RamblingCookieMonster, has created a module called PSSlack which he has published on GitHub for all to make use of. You can find the module on GitHub here along with documentation and other useful information.

Now, a key thing when using modules that are not shipped with PowerShell is making sure they are everywhere you need them. As I am using the monitoring service server, I need to ensure that the PSSlack module is installed on all servers hosting the monitoring service. Additionally, the installation has to be done so that the module is available to all profiles on the server. When installing manually this means ensuring that it is placed into the appropriate profile for All Users. Additionally, if using the Install-Module cmdlet then make sure that the Scope paramter is used and set to "AllUsers".

By using PSSlack we can eliminate the need to specify the content type for the message as that is handled. Additionally, there are a number of other options around being able to send messages, attachments, and the ability to specify channels. This provides a lot more capability as well as minimizes our need to write additional scripts compared to using Invoke-RestMethod.

The Condition and Action

Once we have the scripts written and tested in our editor of choice (I prefer VSCode) we can paste them into the Condition Action. This gives us the fully configured action that is ready to send our notifications to Slack or Teams as needed.

Now all we need is for one of our Availability Groups to failover.

The Payoff

Once we have all of this in place, then we should receive messaged into the specified channel in either Teams or Slack.

Teams Notification

Slack Notification

Now we have SentryOne sending messages to Slack and Teams. Each platform has a number of formatting options if you want to add extra icons and empahsis to the messages that are sent. In the case of Slack this is a big benefit of using the PSSlack module as it has a lot of capability built in for sending attachments and message formatting. There is also the possibility to use the SentryOne message editor to modify the messages that SentryOne sends in response to a condition. That, however, is a whole other blog post,...

Summary

So here I have walked through, at a high level, the mechanisms that you can use in order to send notifications to an external platform via the new PowerShell action that we shipped in v11.2 of SentryOne. While I have focused on Teams and Slack in this post, the mechanism for sending data to any service that has a REST API is fundamentally the same.

Is this a replacement for the long established email notifications that we all know and love? In my view, no. This is a complimentary option that can aid in reducing alert fatigue. This allows for a model to sent high priority notifications via one channel, and have another for the less urgent ones that you still want to know about.