MS Teams - posting results of a SQL query as a table via Powershell.

(update 2019/12/10 13:33 - fixing the tables; the formatting I included got screwed up because Blogger decided to render it)

TL;DR - below is code that will post the results of a SQL query, as a table, in a channel within MS Teams.

Special thanks to http://mikeconjoice.com/2018/05/25/send-a-notification-to-microsoft-teams-via-powershell/, who had the original post/code I used.

First of all - MS, you should be embarrassed. All the documentation says to use Office 365 Connector Cards/Adaptive Cards, but it doesn't work for teams as of December 2019 ("Supported in Teams, not in Bot Framework."), and there's no sense that it'll ever happen, given there's a Connect/User Voice that's been open a year with no updates.

A) Here's some documentation that doesn't actually work for what we're doing.
https://docs.microsoft.com/en-us/microsoftteams/platform/webhooks-and-connectors/how-to/connectors-using
https://techcommunity.microsoft.com/t5/Microsoft-Teams/Microsoft-Teams-Incoming-Webhook-Message-Formatting/m-p/31984
https://docs.microsoft.com/en-us/outlook/actionable-messages/send-via-connectors
https://messagecardplayground.azurewebsites.net/
https://stackoverflow.com/questions/58598592/microsoft-teams-escaping-underscores-in-text-posting-to-webhook

* Note there's a max of 20711 characters in the JSON, and 10 sections in the JSON. (Somewhere else on StackOverflow it says 28k for an image is the max size of that)
* Note there's also a limit how often you can post, and it's a "back-off" algorithm, IIRC.

So.... you want to post to a Teams channel automagically.  Should be simple, and it is!  Alas, it means you have to ignore most of the documentation.  Let's do this!

1) Here's how to hook it up with your channel.  Note that when I created a brand new "Team", it took about 5-10 minutes before I was able to add the webhook connector - prior to that, I got a "channel does not exist or has been deleted".
https://docs.microsoft.com/en-us/outlook/actionable-messages/send-via-connectors
(scroll down)

2) Getting a basic post working - this works and will post text.  Note that you can split lines by using "/r/n" in the text, because it sort of supports formatting (see #3)

$webhook_url = "https://outlook.office.com/webhook/  ......" #URL when you added the connector

$json = @"
{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "text": "Hi Mom",
  "version": "1.0",
  "type": "AdaptiveCard"
}
"@

Invoke-RestMethod -Method post -ContentType 'Application/Json' -Body $json -Uri $webhook_url

3) Adding a table:

Basic HTML is supported.  And I mean BASIC. It supports less formatting than Outlook (I had a border and had to remove it).

So the JSON would look like:

4) Putting it all together, we can do something like this, which will query SQL Server using invoke-sqlcmd2, create the proper JSON, and post it to our channel (note the below SQL is incomplete):

5) And here's what the powershell script returns, if successful:

1

and in Teams...

If you don't do it right, you'll get errors like:

Invoke-RestMethod : Summary or Text is required.

or (the typical "you didn't do it perfectly") of:

Invoke-RestMethod : Bad payload received by generic incoming webhook.