SQL Monitor Alerts in Microsoft Teams

A customer of mine uses Redgate SQL Monitor to monitor their production SQL estate. They make use of the alerts via email and Slack. Recently they started looking in to moving away from Slack as their corporate communications tool, and use Microsoft Teams instead.

SQL Monitor does not (yet – see below) have any native hooks into Teams. So I started to think about how I could achieve something similar.

My first thoughts turned to If This Then That as a way of processing these. I could pick up the email alerts from my Office 365 Outlook inbox and post them in Teams. This lead me to a blog post about Posting messages to Microsoft Teams from IFTTT from Tom Morgan (@tomorgan) and the linked post about Post notifications to Microsoft Teams from Michael Greene.

There was enough here to start, but unfortunately the IFTTT applet was unreliable, sometimes just not firing whatsoever, with absolutely no logging. Every time I reviewed the applet, saved it and tested it, it worked fine. But leave it alone for a few hours and it simply did not deliver. I have no idea why.

Then it hit me – why am I trying to rely on a 3rd party solution to connect Microsoft Office 365 Outlook to Microsoft Teams, when there is a Microsoft offering – Microsoft Flow

So I took the basic ideas from my failed IFTTT experiment and implemented them into Flow, the rough outline being:

  • setup the channel in Teams with an Incoming Webhook connector
  • monitor my inbox for emails from the customer’s SQL Monitor email address
  • post a message into the customer’s Team channel using the connector

Setup Teams

First we need to setup Teams. In the Channel where we want the messages to appear, click the ellipses, and choose Connectors.

Add a connector of type Incoming Webhook. Click the Configure button give it a name and an image (if you want), and click Create

And make sure to grab the URL, and click Done

You’ll get a confirmation message in the Channel to show that the connector has been added

Create a workflow

In Flow, go to My Flows and choose New -> Automated – from blank

Give it a name, and you can also choose the trigger here, which is going to be When a new email arrives (Office 365 Outlook), click Create

For my setup I’m looking in my inbox for items with a specific email address

Add a new step, choose the action HTTP

Set Method to POST. Paste the URL you got from Teams connector into the URI field. Use the body field to specify the message in JSON

For elements of content that Flow knows about, these can be added as dynamic content and are shown in the body as coloured labels, such as Subject and Body Preview.

The actual text of the body is

{
  "title": " @{triggerBody()?['Subject']}",
  "text": "@{triggerBody()?['BodyPreview']}"
}

Note: There are pre-existing templates that will allow you to easily set up a workflow from Outlook to Teams, however I could not use these as my inbox is in my domain, and the Team I’m trying to post in is another domain. In other words different Office 365 tenants, so I had to use the HTTP action.

Ship it!

Save the flow, sit back and wait for an alert….or force one!

When the alert email has arrived, then the Flow is triggered and a message appears in Teams.

Tada!

This message is not as neatly formatted as the alerts we get in Slack, but at least we are getting some kind of alert in Teams. Slack and email alerts have a clickable link that will open SQL Monitor up on the alert in question. This was something that I really wanted to get. I wanted less of a barrier between getting the alert in Teams and viewing it properly in SQL Monitor. All of the information is there in the email alert (including the unique alert id). We could conceivably build a string that represents the URL for that alert. I’m just not sure yet how to extract that information using Flow.

What I could do though was include a link to the Alerts Inbox – this is a fixed URL. So this is what I did!

Which now gave me a clickable link in the message text.

Eurgh

vNext

But I wasn’t completely satisfied with this. A little more reading about the specification for message cards and I could now add an Action button to the cards.

A word of warning – if you copy the code as you see it above, you are likely to get an error message

The template validation failed: ‘The template action ‘HTTP’ at line ‘1’ and column ‘1627’ is not valid: “Unable to parse template language expression ‘type’: expected token ‘LeftParenthesis’ and actual ‘EndOfData’.”.’.

which means that you need to escape the @ symbol by typing it twice – so the code should look like

{
   "title": " @{triggerBody()?['Subject']}",
   "text": "@{triggerBody()?['BodyPreview']}",
   "potentialAction": [
     {
       "@@type": "OpenUri",
       "name": "View Alerts in SQL Monitor",
       "targets": [
         {
           "os": "default",
           "uri": "http://XXX.XXX.XXX.XXX:8080/Alerts/Inbox"
         }
       ]
     }
   ]
 }

Unfortunately once you’ve saved the flow action and then re-edit it, the double @ is lost and you need to re-type it

The format that is being used here is the Workflow Definition Language that is also used in Azure logic apps. (Hmm I wonder what the technology behind Flow is then?)

This now gives us a card that looks like

A clearer call-to-action.

Future

I’d like to better format the message card. All the format options are laid out very well in the message card reference, I just don’t know how to extract the text from the email in the first place. More reading is needed there.

I might be beaten to it though. Redgate has a roadmap for SQL Monitor that lists webhook support as ‘in development‘, so I’m sure this will be along soon. In the meantime, if you need to get your alerts in Teams, then at least you can use this method.

One downside to note is that all your alerts must be configured for email. The customer in this case had some alerts for email, some for Slack – and now it’s all or nothing.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.