How to send notifications to Slack

Some time ago we discussed how to send notifications to Microsoft Teams:

How to send notification to Microsoft Teams

In this post, we are going to look at doing the same with Slack. The only difference between these two platforms is in the payload they expect. Beyond configuration of the correct action, the action association process is exactly the same in both scenarios.

Add a new Slack Application

In Slack application, go to Apps and search for Web-hook and click Add:

This will take you the Slack website where you can Add and configure the new Webhook. Click Add to Slack:

Next, set the channel you want the notifications to go to:

When you click “Add Incoming WebHooks integration” the next screen will show you the WebHook URL and the WebHook configuration. Copy the WebHook URL as we are going to use it in the SQLWATCH config:

Further down the config screen, you set the name and the icon that will be displayed next to the incoming message:

That’s it. We are going to test it now. In the PowerShell window, run the below command. Remember to replace URL with your WebHook URL:

$webhookurl = "https://hooks.slack.com/services/..."
$body = ConvertTo-Json @{
    pretext = "Hey SQLWATCHERS!"
    text = "This is the body text"
}

Invoke-RestMethod `
    -Uri $webhookurl `
    -Method Post `
    -Body $body `
    -ContentType 'application/json'

Should result in the following:

Configure SQLWATCH

Once we have confirmed the WebHook has been created and works as expected, we can move on to configuring SQLWATCH. This step is going to be exactly the same as in Teams with the only difference being the action:

  INSERT INTO [dbo].[sqlwatch_config_action] (
	action_description, action_exec_type, action_exec, action_enabled
	)
  VALUES (
	'Send to Slack via Webhook',
	'PowerShell',
	'$webhookurl = "https://hooks.slack.com/services/..."
$body = ConvertTo-Json @{
    pretext = "{SUBJECT}"
    text = "{BODY}"
}

Invoke-RestMethod `
    -Uri $webhookurl `
    -Method Post `
    -Body $body `
    -ContentType ''application/json''',
	1
	)

The last step is to associate the new action with checks. You can be selective and only direct few critical checks to Slack or all of them. I am going to associate all of them:

INSERT INTO [dbo].[sqlwatch_config_check_action] (
		[check_id]
	  ,	[action_id]
      ,	[action_every_failure]
      ,	[action_recovery]
      ,	[action_repeat_period_minutes]
      ,	[action_hourly_limit]
      ,	[action_template_id]
	  )
SELECT 	[check_id]
	  , [action_id] = 1 --action_id of the new action we just created in the step above
      ,	[action_every_failure]
      ,	[action_recovery]
      ,	[action_repeat_period_minutes]
      ,	[action_hourly_limit]
      ,	[action_template_id] = -4 --plain text template
FROM [dbo].[sqlwatch_config_check_action]
WHERE action_id = -1 --existing action that we want to use to replicate assosciations
or action_id in (
	--also include actions that normally produce a report
	select action_id from [dbo].[sqlwatch_config_action]
	where [action_report_id] is not null
	)

That’s it! Enjoy your Slack integration!

Slack SSL Error

If you are not receiving notifications but find the following error messages in the app_log: The request was aborted: Could not create SSL/TLS secure channel. (@queue_item_id: ****) :

You will have to make sure the PowerShell Invoke-RestMethod is over TLS1.2. You can add the following like to the command:

  INSERT INTO [dbo].[sqlwatch_config_action] (
	action_description, action_exec_type, action_exec, action_enabled
	)
  VALUES (
	'Send to Slack via Webhook',
	'PowerShell',
	'[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$webhookurl = "https://hooks.slack.com/services/..."
$body = ConvertTo-Json @{
    pretext = "{SUBJECT}"
    text = "{BODY}"
}

Invoke-RestMethod `
    -Uri $webhookurl `
    -Method Post `
    -Body $body `
    -ContentType ''application/json''',
	1
	)

This post was originally published on 2nd June 2020.