Send notifications to MS Teams

General
1

Hi,

I’m new with SQLWatch, and I already managed to install it on 3 different servers (Prod/test/dev) and also got the central repository working, looks really amazing the information I’m getting in the PowerBi report.

But now i’m trying to setup the notifications to MS Team, and I followed the guidelines in the Blog, every seems to be installed correctly. but i’m not receiving any messages in Team Channel.
The test message is working, and also in the [sqlwatch_logger_check_action] table are records that a message with the correct action_id should be send. what am I doing wrong or have I forgotten?

Thanks

2

Are you notifications working at all? You can check this thread if not: Notifications and Alerts

3

I only tried the MS Teams notifications and did the test as in your blog.
But I will try what you suggesting, to see if that will be working.

4

Please do, just to make sure the mechanism is working ok.

5

Hi Marcin,

I checked and the [dbo].[sqlwatch_meta_action_queue] if full of errors, so the “trigger” is working but no messages are send out…

6

What are the errors? They will be in the app_log table

7

these:

  • profile name is not valid (@queue_item_id: 2174)
  • The remote server returned an error: (400) Bad Request. (@queue_item_id: 2175)
  • profile name is not valid (@queue_item_id: 2176)
  • The remote server returned an error: (400) Bad Request. (@queue_item_id: 2177)
8

So this is the response from the Teams server. This tells me that the notifications work as such, you have just perhaps misconfigured something on the Teams side, or the notification action is passing wrong parameters.

the queue table holds the actual executable, you can copy it and run in PowerShell to debug.

9

If I run what is in the column “action_exec” it is just giving me a message in Teams!
so that is also working, it looks like the sction_id is not executed, while the check_action is configured correctly as far as I can see…

10

So when you run what’s in the [dbo].[sqlwatch_meta_action_queue] action_exec manually it works but it does not work when run via SQL Agent job?

Does your SQL Server or the agent account have access to the internet, to access the Teams endpoint?

What happens if you create a new agent job with PowerShell task and paste the bits from action_exec, as a one-off test?

11

I tired the things above, and what i found:
Wwen run in PowerShell or test sql agent job the [dbo].[sqlwatch_meta_action_queue] action_exec is does not work. Receiving an error: Unexpected token ‘Invoke-RestMethod’ in expression or statement.

When I run in same environment the [dbo].[sqlwatch_config_action] action_exec. evrything works and receiving test messages from and test sql agent and PowerShell

12

This will be due to quotes. Perhaps when you’re copying something goes wrong. Try make sure the quotes are single quotes ’ or double quotes "

Alternatively, can you paste your action exec here, as is, making sure there’s no confidential data, i.e. you can scramble the teams url etc.

13

Copy paste code from [dbo].[sqlwatch_config_action] action_exec

$webhookurl = “MS Teams Code”
Invoke-RestMethod -Uri $webhookurl
-Method Post -Body '{"text":"{BODY}", "title":"{SUBJECT}"}'
-ContentType ‘application/json’

Copy paste code from [dbo].[sqlwatch_meta_action_queue] action_exec

$webhookurl = “MS Teams Code” Invoke-RestMethod -Uri $webhookurl -Method Post -Body '{"text":"{ "@context": "https://schema.org/extensions", "@type": "MessageCard", "sections": [ { "facts": [ { "name": "Check Staus:", "value": "CRITICAL" }, { "name": "Check Name:", "value": "Failed Agent Job" }, { "name": "Host Name:", "value": "Remote System" } ], "text": "One or more SQL Server Agent Jobs have failed. If there is a report assosiated with this check, details of the failures should be inlcuded below." } ], "summary": "Check Summary", "title": "CRITICAL: Failed Agent Job on Remote System" } ", "title":""}' -ContentType ‘application/json’

I changed the MS Tems code and AWS system names

14

It looks like your template is incorrect. If you are trying to send card, instead of a simple notification, please use the template from this post: https://sqlwatch.io/blog/cards-in-microsoft-teams-notifications-with-action-templates/

For example, the difference is, yours starts with {"text": but the one in my post with "@context": When I run your piece of code I get Invoke-RestMethod : Bad payload received by generic incoming webhook. which is Teams server telling me it does not understand what the payload means

When I use the below template, everything works ok:

'{
    "@context": "https://schema.org/extensions",
    "@type": "MessageCard",
      "sections": [
        {
            "facts": [
                {
                    "name": "Check Staus:",
                    "value": "{CHECK_STATUS}"
                },
                {
                    "name": "Check Name:",
                    "value": "{CHECK_NAME}"
                },
                {
                    "name": "Host Name:",
                    "value": "{SQL_INSTANCE}"
                }
            ],
            "text": "{CHECK_DESCRIPTION}"
        }
    ],
    "summary": "Check Summary",
    "title": "{CHECK_STATUS}: {CHECK_NAME} on {SQL_INSTANCE}"
}
'

Here are more examples how to format JSON for cards:

15

Great it works now! I did install your card but did not change the {text, etc etc} to {body}.
last question, do I need to do this for each instance of my servers?

16

Great to hear it works.

Yeah you do but the easiest way is to use Central Management Server (CMS) or PowerShell “invoke-sqlcmd” to run the same query on a bunch of servers at the same time.