Cards in Microsoft Teams Notifications with action templates

In the previous post, I have explained how to configure SQLWATCH To send simple, plain text notifications to Microsoft Teams.

Today, we are going to look at how to send formatted cards:

Microsoft Teams Cards

Action templates

Formatted cards require slightly more complex JSON structure and for that reason, we are going to create a new message template. SQLWATCH has always been designed with flexibility in mind. Adding a new message template is a very simple task:

USE [SQLWATCH]
GO

declare @template varchar(max) = '{
    "@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}"
}
'
INSERT INTO [dbo].[sqlwatch_config_check_action_template]
           ([action_template_description]
           ,[action_template_fail_subject]
           ,[action_template_fail_body]
           ,[action_template_repeat_subject]
           ,[action_template_repeat_body]
           ,[action_template_recover_subject]
           ,[action_template_recover_body]
           ,[action_template_type])
     VALUES
           ('Microsoft Teams Card'
           ,''
           ,@template
           ,''
           ,@template
           ,''
           ,@template
           ,'TEXT' )
GO

SELECT action_id = SCOPE_IDENTITY()

The next step is to re-assign existing action to use the new template. In our case this is action_id=1 (as in the previous post) and action_template_id=1:

update [dbo].[sqlwatch_config_check_action]
set action_template_id = 1
where action_id = 1

The last step is to update the existing action_exec in [dbo].[sqlwatch_config_action] and only pass {BODY} as the entire payload formatting is now done in the template:

$webhookurl = "https://outlook.office.com/webhook/..." 

Invoke-RestMethod `
    -Uri $webhookurl `
    -Method Post `
    -Body '{BODY}' `
    -ContentType 'application/json'

If you are using the original action from the previous post, you can simply update the action:

  UPDATE [dbo].[sqlwatch_config_action]
  SET action_exec = REPLACE(action_exec,'{"text":"{BODY}", "title":"{SUBJECT}"}','{BODY}')
  WHERE action_id = 1

Conclusion

That’s it. You are now going to receive formatted notifications and you are free to modify the card template to fit your purpose. If you are new to SQLWATCH please check our documentation and the getting started guide.

Table of Contents
Marcin Gminski

Marcin Gminski

Following his degree in Telecommunications Engineering and Information Technology, Marcin has gained over 20 years’ experience and knowledge of the SQL Server administration, Data Migrations, E2E performance testing and tuning, database development life cycle covering technical architecture, data analysis, logical and physical design including disaster recovery, specification, coding, implementation, and support. Marcin has delivered data migration projects in banking, retail, automotive and medical industries.
This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.