How to send notification to Microsoft Teams

I have been meaning to write how to integrate SQLWATCH with different platforms for a long time. Recently a very kind person bought me a coffee and said they have implemented Microsoft Teams integration which motivated me to finalise it.

Hi Marcin,  love your work, just startet to rollout the current version of SQLWATCH and implemented the Teams Webhook integration for Alerting :) Very great work!!! Greating from Hamburg Germany

@SteffenEngel

This is really good news, in fact, two good news. It means that SQLWATCH is being used as intended – as a framework for the SQL Community to expand and build on and, I got my first coffee (thank you kindly) :-)

I always wanted actions to be easy to configure with minimum maintenance. Adding new actions or checks should not be more than adding a relevant config to the table.

Configure Teams

Add WebHook Integration

We are going to be using WebHooks to send messages to Microsoft Teams. WebHooks are essentially an API HTTP endpoints (URLs) that accept payloads via POST calls. In simple words, we are going to send some data to a URL.

To enable WebHooks in Teams, go to Apps and select WebHook integration, then select the team or channel you want this WebHook to feed into. In my case, this is SQLWATCH channel.

If you have just created new Team or channel you may have to wait few minutes whilst it is being setup otherwise you will get an error when trying to add WebHook

Once the WebHook is created, you will be given a URL. This is the URL we are going to send data to. Save it:

Test WebHook integration

Now we have setup WebHook we can test it to make sure everything works before we proceed to set up SQLWATCH. There are many ways to send data to HTTP endpoints. I am going to use PowerShell:

#Url from the step above
$webhookurl = "https://outlook.office.com/webhook/..." 

Invoke-RestMethod `
    -Uri $webhookurl `
    -Method Post `
    -Body '{"text":"This is text", "title":"This is subject"}' `
    -ContentType 'application/json' 

When successful it will return 1 and the message will appear in Teams. When not successful it will return the error:

Configure SQLWATCH

Add Action

Now we have WebHook integration we can now configure SQLWATCH to interface with it. Remember what I said at the beginning about SQLWATCH being very easy to configure? There are two things we need to do now. Add a new PowerShell action with our WebHook parameters.

  INSERT INTO [dbo].[sqlwatch_config_action] (
	action_description, action_exec_type, action_exec, action_enabled
	)
  VALUES (
	'Send to Teams via Webhook',
	'PowerShell',
	'$webhookurl = "https://outlook.office.com/webhook/..."
Invoke-RestMethod `
    -Uri $webhookurl `
    -Method Post `
    -Body ''{"text":"{BODY}", "title":"{SUBJECT}"}'' `
    -ContentType ''application/json''',
	1
	)

Remember to replace the $webhookurl with your actual URL. The variables {BODY} and {SUBJECT} will be automatically replaced by SQLWATCH during runtime.

Associate Action with Checks

The last thing to do now is to tell SQLWATCH which checks should be linked with the new action. You can cherry-pick specific checks to go to Teams but in this example, I am going to set all those that are currently being emailed out, to be also sent to Teams:

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
	)

And that’s it. Notifications will now appear in Teams:

Summary

This was an example of how to add simple, text-based notifications to Microsoft Teams. In the next post, we will explore how to send advanced cards that allow custom styling and layouts with custom SQLWATCH templates. Stay tuned and subscribe for updates.

If you are new to SQLWATCH check out the Getting Started Guide and our documentation portal

This post was originally published on 11th May 2020.