Tag Archives: Teams

Notifying a Teams Channel of a SQL Agent Job result

Posted on by

Following on from yesterdays post about creating an overview of SQL Agent Job Results and sending it to a Teams channel, I was given another challenge

Can you write a job step that I can add to SQL Agent jobs that can send the result of that job to a Teams Channel

A person with a need

The use case was for some migration projects that had steps that were scheduled via SQL Agent Jobs and instead of the DBA having to estimate when they would finish and keep checking so that they could let the next team know that it was time for their part to start, they wanted it to notify a Teams channel. This turned out especially useful as the job finished earlier than expected at 3am and the off-shore team could begin their work immediately.

Using SQL Agent Job tokens with PowerShell

You can use SQL Agent job tokens in Job step commands to reference the existing instance or job but I did not know if you could use that with PowerShell until I read Kendra Little’s blog post from 2009.

Thank you Kendra

Nothing is ever as easy as you think

So I thought, this is awesome, I can create a function and pass in the Instance and the JobId and all will be golden.

Nope

job_id <> $(JobID)

If we look in the sysjobs table at the Agent Job that we want to notify Teams about the result.

We can see that the job_id is

dc5937c3-766f-47b7-a5a5-48365708659a

If we look at the JobId property with PowerShell

We get

dc5937c3-766f-47b7-a5a5-48365708659a

Awesome, they are the same

But

If we look at the value of the $(JobID) SQL Agent Job Token,

we get

C33759DC6F76B747A5A548365708659A

which makes matching it to the JobId tricky

I tried all sorts of ways of casting and converting this value in SQL and PowerShell and in the end I just decided to manually convert the value

    $CharArray = $JobID.ToCharArray()

    $JobGUID = $CharArray[8] + $CharArray[9] + $CharArray[6] + $CharArray[7] + $CharArray[4] + $CharArray[5] + $CharArray[2] + $CharArray[3] + '-' + $CharArray[12] + $CharArray[13] + $CharArray[10] + $CharArray[11] + '-' + $CharArray[16] + $CharArray[17] + $CharArray[14] + $CharArray[15] + '-' + $CharArray[18] + $CharArray[19] + $CharArray[20] + $CharArray[21] + '-' + $CharArray[22] + $CharArray[23] + $CharArray[24] + $CharArray[25] + $CharArray[26] + $CharArray[27] + $CharArray[28] + $CharArray[29] + $CharArray[30] + $CharArray[31] + $CharArray[32] + $CharArray[33]

Send the information to Teams

Following the same pattern as yesterdays post, I created a function to send a message, depending on the outcome of the job and post it to the Teams function.

Again, I used Enter-Pssession to run the Teams notification from a machine that can send the message. (I have also included the code to do this without requiring that below so that you can send the message from the same machine that runs the job if required)

This code below is saved on a UNC share or the SQL Server as SingleNotifyTeams.ps1

Param(
    $SqlInstance,
    $JobID
)

$webhookurl = ""

$NotifyServer = 'BeardNUC2'
function Notify-TeamsSQlAgentJob {
    Param(
        $SQLInstance,
        $JobID,
        $webhookurl
    )

    $SQLInstance = $SQLInstance 
    # Import-Module 'C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.107\dbatools.psd1'
    [System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

    $CharArray = $JobID.ToCharArray()

    $JobGUID = $CharArray[8] + $CharArray[9] + $CharArray[6] + $CharArray[7] + $CharArray[4] + $CharArray[5] + $CharArray[2] + $CharArray[3] + '-' + $CharArray[12] + $CharArray[13] + $CharArray[10] + $CharArray[11] + '-' + $CharArray[16] + $CharArray[17] + $CharArray[14] + $CharArray[15] + '-' + $CharArray[18] + $CharArray[19] + $CharArray[20] + $CharArray[21] + '-' + $CharArray[22] + $CharArray[23] + $CharArray[24] + $CharArray[25] + $CharArray[26] + $CharArray[27] + $CharArray[28] + $CharArray[29] + $CharArray[30] + $CharArray[31] + $CharArray[32] + $CharArray[33]

    $Job = Get-DbaAgentJob -SQlInstance $SQLInstance | Where jobid -eq $JobGuiD
    $JobName = $Job.Name
    $Jobsteps = Get-DbaAgentJobStep -SQlInstance $SQLInstance -Job $JobName

    $JobStepNames = $Jobsteps.Name -join ' , '
    $JobStartDate = $job.JobSteps[0].LastRunDate
    $JobStatus = $job.LastRunOutcome
    $lastjobstepid = $jobsteps[-1].id
    $Jobstepsmsg = $Jobsteps | Out-String
    $JobStepStatus = ($Jobsteps | Where-Object {$_.id -ne $lastjobstepid -and $_.LastRunDate -ge $JobStartDate} ).ForEach{
        "   $($_.Name)  - $($_.LastRunDate) **$($_.LastRunOutCome)**  
"
    } 
    
    $Text = @"
# **$SqlInstance**   
## **$JobName**  

$jobstepMsg

Started at $JobStartDate 
- The individual Job Steps status was  

$JobStepStatus  


"@

    if (( $jobsteps | Where id -ne $lastjobstepid).LastRunOutcome -contains 'Failed') {
        $JSONBody = [PSCustomObject][Ordered]@{
            "@type"      = "MessageCard"
            "@context"   = "http://schema.org/extensions"
            "summary"    = "There was a Job Failure"
            "themeColor" = '0078D7'
            "sections"   = @(
                @{
                    "activityTitle"    = "The Job Failed"
                    "activitySubtitle" = "Work to do - Please investigate the following job by following the steps in the plan at LINKTOPLAN"
                    "activityImage"    = "https://fit93a.db.files.1drv.com/y4mTOWSzX1AfIWx-VdUgY_Qp3wqebttT7FWSvtKK-zAbpTJuU560Qccv1_Z_Oxd4T4zUtd5oVZGJeS17fkgbl1dXUmvbldnGcoThL-bnQYxrTrMkrJS1Wz2ZRV5RVtZS9f4GleZQOMuWXP1HMYSjYxa6w09nEyGg1masI-wKIZfdnEF6L8r83Q9BB7yIjlp6OXEmccZt99gpb4Qti9sIFNxpg"
                    "text"             = $text
                    "markdown"         = $true
                }
            )
        }
    }
    else {
        $JSONBody = [PSCustomObject][Ordered]@{
            "@type"      = "MessageCard"
            "@context"   = "http://schema.org/extensions"
            "summary"    = "The Job Succeeded"
            "themeColor" = '0078D7'
            "sections"   = @(
                @{
                    "activityTitle"    = "The Job Succeeded"
                    "activitySubtitle" = "All is well - Please continue with the next step in the plan at LINKTOPLAN"
                    "activityImage"    = "https://6f0bzw.db.files.1drv.com/y4mvnTDG9bCgNWTZ-2_DFl4-ZsUwpD9QIHUArsGF66H69zBO8a--FlflXiF7lrL2H3vgya0ogXIDx59hn62wo2tt3HWMbqnnCSp8yPmM1IFNwZMzgvSZBEs_n9B0v4h4M5PfOY45GVSjeFh8md140gWHaFpZoL4Vwh-fD7Zi3djU_r0PduZwNBVGOcoB6SMJ1m4NmMmemWr2lzBn57LutDkxw"
                    "text"             = $text
                    "markdown"         = $true
                }
            )
        }
    }

    $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
 
    $NotifyCommand = {
    $parameters = @{
        "URI"         = $Using:webhookurl
        "Method"      = 'POST'
        "Body"        = $Using:TeamMessageBody
        "ContentType" = 'application/json'
    }
 
    Invoke-RestMethod @parameters
}
    $Session = New-PSSession -ComputerName $NotifyServer
    Invoke-Command -Session $Session -ScriptBlock $NotifyCommand
}

$msg = 'ServerName  = ' + $SQLInstance + 'JobId = ' + $JobID
Write-Host $msg
Notify-TeamsSQLAgentJob -SQlInstance $SqlInstance -JobID $JobID -webhookurl $webhookurl

Then it can be called in a SQL Agent job step, again following the guidelines at dbatools.io/agent

It is called slightly differently as you ned to pass in the SQL Agent tokens as parameters to the script

powershell.exe -File path to Notify-TeamsSQLAgentJob.ps1 -SQLInstance  $(ESCAPE_SQUOTE(SRVR)) -JobID  $(ESCAPE_NONE(JOBID))

SQL Agent Job Step Success and Failure

We need to take another step to ensure that this works as expected. We have to change the On Failure action for each job step to the “Go To Notify Teams” step

Making people smile

You can also add images (make sure the usage rights allow) so that the success notification can look like this

and the failure looks like this

Happy Automating !

Here is the code that does not require remoting to another server to send the message

Param(
    $SqlInstance,
    $JobID
)

$webhookurl = "https://outlook.office.com/webhook/5a8057cd-5e1a-4c84-9227-74a309f1c738@b122247e-1ebf-4b52-b309-c2aa7436fc6b/IncomingWebhook/affb85f05804438eb7ffb57665879248/f32fc7e6-a998-4670-8b33-635876559b80"

function Notify-TeamsSQlAgentJob {
    Param(
        $SQLInstance,
        $JobID,
        $webhookurl
    )

    $SQLInstance = $SQLInstance 
    # Import-Module 'C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.107\dbatools.psd1'
    [System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

    $CharArray = $JobID.ToCharArray()

    $JobGUID = $CharArray[8] + $CharArray[9] + $CharArray[6] + $CharArray[7] + $CharArray[4] + $CharArray[5] + $CharArray[2] + $CharArray[3] + '-' + $CharArray[12] + $CharArray[13] + $CharArray[10] + $CharArray[11] + '-' + $CharArray[16] + $CharArray[17] + $CharArray[14] + $CharArray[15] + '-' + $CharArray[18] + $CharArray[19] + $CharArray[20] + $CharArray[21] + '-' + $CharArray[22] + $CharArray[23] + $CharArray[24] + $CharArray[25] + $CharArray[26] + $CharArray[27] + $CharArray[28] + $CharArray[29] + $CharArray[30] + $CharArray[31] + $CharArray[32] + $CharArray[33]

    $Job = Get-DbaAgentJob -SQlInstance $SQLInstance | Where jobid -eq $JobGuiD
    $JobName = $Job.Name
    $Jobsteps = Get-DbaAgentJobStep -SQlInstance $SQLInstance -Job $JobName

    $JobStepNames = $Jobsteps.Name -join ' , '
    $JobStartDate = $job.JobSteps[0].LastRunDate
    $JobStatus = $job.LastRunOutcome
    $lastjobstepid = $jobsteps[-1].id
    $Jobstepsmsg = $Jobsteps | Out-String
    $JobStepStatus = ($Jobsteps | Where-Object {$_.id -ne $lastjobstepid -and $_.LastRunDate -ge $JobStartDate} ).ForEach{
        "   $($_.Name)  - $($_.LastRunDate) **$($_.LastRunOutCome)**  
"
    } 
    
    $Text = @"
# **$SqlInstance**   
## **$JobName**  

$jobstepMsg

Started at $JobStartDate 
- The individual Job Steps status was  

$JobStepStatus  


"@

    if (( $jobsteps | Where id -ne $lastjobstepid).LastRunOutcome -contains 'Failed') {
        $JSONBody = [PSCustomObject][Ordered]@{
            "@type"      = "MessageCard"
            "@context"   = "http://schema.org/extensions"
            "summary"    = "There was a Job Failure"
            "themeColor" = '0078D7'
            "sections"   = @(
                @{
                    "activityTitle"    = "The Job Failed"
                    "activitySubtitle" = "Work to do - Please investigate the following job by following the steps in the plan at LINKTOPLAN"
                    "activityImage"    = "https://fit93a.db.files.1drv.com/y4mTOWSzX1AfIWx-VdUgY_Qp3wqebttT7FWSvtKK-zAbpTJuU560Qccv1_Z_Oxd4T4zUtd5oVZGJeS17fkgbl1dXUmvbldnGcoThL-bnQYxrTrMkrJS1Wz2ZRV5RVtZS9f4GleZQOMuWXP1HMYSjYxa6w09nEyGg1masI-wKIZfdnEF6L8r83Q9BB7yIjlp6OXEmccZt99gpb4Qti9sIFNxpg"
                    "text"             = $text
                    "markdown"         = $true
                }
            )
        }
    }
    else {
        $JSONBody = [PSCustomObject][Ordered]@{
            "@type"      = "MessageCard"
            "@context"   = "http://schema.org/extensions"
            "summary"    = "The Job Succeeded"
            "themeColor" = '0078D7'
            "sections"   = @(
                @{
                    "activityTitle"    = "The Job Succeeded"
                    "activitySubtitle" = "All is well - Please continue with the next step in the plan at LINKTOPLAN"
                    "activityImage"    = "https://6f0bzw.db.files.1drv.com/y4mvnTDG9bCgNWTZ-2_DFl4-ZsUwpD9QIHUArsGF66H69zBO8a--FlflXiF7lrL2H3vgya0ogXIDx59hn62wo2tt3HWMbqnnCSp8yPmM1IFNwZMzgvSZBEs_n9B0v4h4M5PfOY45GVSjeFh8md140gWHaFpZoL4Vwh-fD7Zi3djU_r0PduZwNBVGOcoB6SMJ1m4NmMmemWr2lzBn57LutDkxw"
                    "text"             = $text
                    "markdown"         = $true
                }
            )
        }
    }

    $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
 
    $parameters = @{
        "URI"         = $webhookurl
        "Method"      = 'POST'
        "Body"        = $TeamMessageBody
        "ContentType" = 'application/json'
    }
 
    Invoke-RestMethod @parameters
}

$msg = 'ServerName  = ' + $SQLInstance + 'JobId = ' + $JobID
Write-Host $msg
Notify-TeamsSQLAgentJob -SQlInstance $SqlInstance -JobID $JobID -webhookurl $webhookurl

Sending a SQL Agent Job results overview to a Microsoft Teams Channel

Posted on by

Microsoft Teams is fantastic for collaboration. It enables groups of people, teams if you like to be able to communicate, collaborate on documents, hold meetings and much much more.

SQL Agent Job Overview

Using dbatools we can create a simple script to gather the results of Agent Jobs form a list of instances. Maybe it would be good to be able to get the job runs results every 12 hours so that at 6am in the morning the early-bird DBA can quickly identify if there are any failures that need immediate action and at 6pm , the team can check that everything was ok before they clock off.

Here is an example of such a script

$SqlInstances = (Get-Vm -ComputerName BEARDNUC,BEARDNUC2).Where{$_.State -eq 'Running' -and $_.Name -like '*SQL*'}.Name
$AllJobs = "
SqlInstance...|...Total...|...Successful...|...FailedJobs...|...FailedSteps...|...Canceled...     
---------------------------------------------  
"
foreach ($Instance in $SQLInstances) {
    Write-Host "Connecting to $instance"
    try{
        $smo = Connect-DbaInstance $Instance -ErrorAction Stop
        Write-Host "Connected successfully to $instance"
    }
    catch{
        Write-Host "Failed to connect to $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

    Write-Host "Getting Agent Jobs on $instance"
    try {
        $AgentJobs = Get-DbaAgentJobHistory -SqlInstance $smo -EnableException -StartDate $startdate 
        Write-Host "Successfully got Agent Jobs on $instance"
    }
    catch {
        Write-Host "Failed to get agent jobs on $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }
    

    $jobs = $agentJobs 
    $NumberOfJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0}).Count.ToString("00")
    $NumberOfFailedJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0}|  Where-Object {$PSItem.Status -eq 'Failed'}).StepName.Count.ToString("00")
    $NumberOfFailedJobSteps = ($Jobs |Where-Object {$PSitem.StepId -ne 0}|  Where-Object {$PSItem.Status -eq 'Failed'}).StepName.Count.ToString("00")
    $NumberOfSuccessfulJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0} | Where-Object {$PSItem.Status -eq 'Succeeded'}).StepName.Count.ToString("00")
    $NumberOfCanceledJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0} | Where-Object {$PSItem.Status -eq 'Canceled'}).StepName.Count.ToString("00")

     Write-Host "SqlInstance $Instance - Number of Jobs $NumberOfJobs - Number of Successful Jobs $NumberOfSuccessfulJobs  - Number of Failed Jobs $NumberOfFailedJobs"

    $AllJobs = $AllJobs + "$($Instance.Split('.')[0])..........<b>$NumberOfJobs</b>................<b>$NumberOfSuccessfulJobs</b>.........................<b>$NumberOfFailedJobs</b>............................<b>$NumberOfFailedJobSteps</b>..............................<b>$NumberOfCanceledJobs</b>........
"
    try{
        $smo.ConnectionContext.Disconnect()
        Write-Host "Disconnecting $instance"
    }
    catch{
        Write-Host "Failed disconnect from  $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

}

Write-Host "Since $startdate"
Write-Host "$AllJobs"

and an example of running it.

Create a Teams Channel

If you have permissions, you can create a new Teams channel by clicking on the 3 ellipses and add channel

Then fill in the blanks

Create a Webhook Connector for the channel

Next, you need to have a connector for the channel, click on the 3 ellipses for the channel and click on connectors

Then you can choose the Incoming Webhook connector and click configure

Give the connector a name and upload an image if you wish and click create

The resulting screen will give you a URL that you can copy. If you need to find it again, then use the 3 ellipses again, click connectors and look at configured. You can then choose the webhook that you have created and click manage and you will find the URL.

Send to Teams using PowerShell

Now you can send a message to that Teams channel using PowerShell. You will need to add the webhook URL from your Teams connector

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$webhookurl = ""

    $Text =  @"
# Here is a Title

and a message

Image is from

https://www.flickr.com/photos/157270154@N05/38494483572

Photo by CreditDebitPro
"@

    $JSONBody = [PSCustomObject][Ordered]@{
        "@type"      = "MessageCard"
        "@context"   = "http://schema.org/extensions"
        "summary"    = "This is my summary"
        "themeColor" = '0078D7'
        "sections"   = @(
            @{
                "activityTitle"    = "Something Important "
                "activitySubtitle" = "I have something to say"
                "activityImage"    = "https://live.staticflickr.com/4568/38494483572_a98d623854_k.jpg"
                "text"             = $text
                "markdown"         = $true
            }
        )
    }
 
    $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
 
    $parameters = @{
        "URI"         = $webhookurl
        "Method"      = 'POST'
        "Body"        = $TeamMessageBody
        "ContentType" = 'application/json'
    }
 
    Invoke-RestMethod @parameters

The code above will send a message that looks like this

Running as a SQL Agent Job

Now we can run this code as a SQL Agent Job and schedule it. Now, you may not be able to run that code on your SQL Server. It cannot connect to the internet, so how can we contact the Teams webhook?

There are probably a number of ways to do this but the solution that I took, was to allow a proxy account the ability to use PSRemoting and run the part of the script that connects to Teams on a different machine, that does have connectivity.

The script I used was as follows. You will need to add in the SQL Instances or better still dynamically gather them from your source of truth. You will need the webhook URL and the name of the server that can connect to Teams

$SQLInstances = 'SQL2005Ser2003','SQL2008Ser12R2','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3','SQL2017N5','SQL2019N20','SQL2019N21','SQL2019N22','SQL2019N5'

$startdate = (Get-Date).AddHours(-12)
$webhookurl = ""
$NotifyServer = 'BeardNUC2'

$AllJobs = "
SqlInstance...|...Total...|...Successful...|...FailedJobs...|...FailedSteps...|...Canceled...     
---------------------------------------------  
"
foreach ($Instance in $SQLInstances) {
    Write-Host "Connecting to $instance"
    try{
        $smo = Connect-DbaInstance $Instance -ErrorAction Stop
        Write-Host "Connected successfully to $instance"
    }
    catch{
        Write-Host "Failed to connect to $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

    Write-Host "Getting Agent Jobs on $instance"
    try {
        $AgentJobs = Get-DbaAgentJobHistory -SqlInstance $smo -EnableException -StartDate $startdate 
        Write-Host "Successfully got Agent Jobs on $instance"
    }
    catch {
        Write-Host "Failed to get agent jobs on $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }
    

    $jobs = $agentJobs 
    $NumberOfJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0}).Count.ToString("00")
    $NumberOfFailedJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0}|  Where-Object {$PSItem.Status -eq 'Failed'}).StepName.Count.ToString("00")
    $NumberOfFailedJobSteps = ($Jobs |Where-Object {$PSitem.StepId -ne 0}|  Where-Object {$PSItem.Status -eq 'Failed'}).StepName.Count.ToString("00")
    $NumberOfSuccessfulJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0} | Where-Object {$PSItem.Status -eq 'Succeeded'}).StepName.Count.ToString("00")
    $NumberOfCanceledJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0} | Where-Object {$PSItem.Status -eq 'Canceled'}).StepName.Count.ToString("00")

     Write-Host "SqlInstance $Instance - Number of Jobs $NumberOfJobs - Number of Successful Jobs $NumberOfSuccessfulJobs  - Number of Failed Jobs $NumberOfFailedJobs"

    $AllJobs = $AllJobs + "$($Instance.Split('.')[0])..........<b>$NumberOfJobs</b>................<b>$NumberOfSuccessfulJobs</b>.........................<b>$NumberOfFailedJobs</b>............................<b>$NumberOfFailedJobSteps</b>..............................<b>$NumberOfCanceledJobs</b>........
"
    try{
        $smo.ConnectionContext.Disconnect()
        Write-Host "Disconnecting $instance"
    }
    catch{
        Write-Host "Failed disconnect from  $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

}

Write-Host "Since $startdate"
Write-Host "$AllJobs"

$NotifyCommand = {
[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$webhookurl = $Using:TeamsWebhook
 
$allJobsMessage = $Using:AllJobs 
    $Text =  @"
# Overview of SQL Agent Jobs in Production since $($Using:startdate)  

$allJobsMessage
"@

    $JSONBody = [PSCustomObject][Ordered]@{
        "@type"      = "MessageCard"
        "@context"   = "http://schema.org/extensions"
        "summary"    = "Overview for the last 12 hours"
        "themeColor" = '0078D7'
        "sections"   = @(
            @{
                "activityTitle"    = "Job Failures "
                "activitySubtitle" = "Overview for the last 12 hours since $($Using:startdate)"
                "activityImage"    = "https://live.staticflickr.com/4568/38494483572_a98d623854_k.jpg"
                "text"             = $allJobsMessage
                "markdown"         = $true
            }
        )
    }
 
    $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
 
    $parameters = @{
        "URI"         = $webhookurl
        "Method"      = 'POST'
        "Body"        = $TeamMessageBody
        "ContentType" = 'application/json'
    }
 
    Invoke-RestMethod @parameters
}

$Session = New-PSSession -ComputerName $NotifyServer
Invoke-Command -Session $Session -ScriptBlock $NotifyCommand

Then, follow the steps at dbatools.io/agent to create an agent job to run the script above on an instance with the dbatools module available to the SQL Service account. Use or create a proxy with permissions on the notify server and create an Agent Job.

USE [msdb]
GO

/****** Object:  Job [I am a Job that notifies Teams]    Script Date: 27/07/2020 20:27:27 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 27/07/2020 20:27:28 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'12 Hour Teams Notify', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job will notify Teams every 12 hours', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'THEBEARD\SQL_SVC', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Notify Teams]    Script Date: 27/07/2020 20:27:28 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Notify Teams', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'powershell.exe -File C:\temp\AgentJobs\NotifyTeams.ps1', 
		@flags=0, 
		@proxy_name=N'TheBeardIsMighty'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

When the job runs

The results are posted to the Teams Channel

If you can run the Agent Job on a machine that can connect to Teams and your SQL Instances then you can remove the need to use a remote session by using this code

$SQLInstances = 'SQL2005Ser2003','SQL2008Ser12R2','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3','SQL2017N5','SQL2019N20','SQL2019N21','SQL2019N22','SQL2019N5'

$startdate = (Get-Date).AddHours(-12)
$webhookurl = ""


# Import-Module 'C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.107\dbatools.psd1'
$AllJobs = "
SqlInstance...|...Total...|...Successful...|...FailedJobs...|...FailedSteps...|...Canceled...     
---------------------------------------------  
"
foreach ($Instance in $SQLInstances) {
    Write-Host "Connecting to $instance"
    try{
        $smo = Connect-DbaInstance $Instance -ErrorAction Stop
        Write-Host "Connected successfully to $instance"
    }
    catch{
        Write-Host "Failed to connect to $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

    Write-Host "Getting Agent Jobs on $instance"
    try {
        $AgentJobs = Get-DbaAgentJobHistory -SqlInstance $smo -EnableException -StartDate $startdate 
        Write-Host "Successfully got Agent Jobs on $instance"
    }
    catch {
        Write-Host "Failed to get agent jobs on $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }
    

    $jobs = $agentJobs 
    $NumberOfJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0}).Count.ToString("00")
    $NumberOfFailedJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0}|  Where-Object {$PSItem.Status -eq 'Failed'}).StepName.Count.ToString("00")
    $NumberOfFailedJobSteps = ($Jobs |Where-Object {$PSitem.StepId -ne 0}|  Where-Object {$PSItem.Status -eq 'Failed'}).StepName.Count.ToString("00")
    $NumberOfSuccessfulJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0} | Where-Object {$PSItem.Status -eq 'Succeeded'}).StepName.Count.ToString("00")
    $NumberOfCanceledJobs = ($Jobs |Where-Object {$PSitem.StepId -eq 0} | Where-Object {$PSItem.Status -eq 'Canceled'}).StepName.Count.ToString("00")

     Write-Host "SqlInstance $Instance - Number of Jobs $NumberOfJobs - Number of Successful Jobs $NumberOfSuccessfulJobs  - Number of Failed Jobs $NumberOfFailedJobs"

    $AllJobs = $AllJobs + "$($Instance.Split('.')[0])..........<b>$NumberOfJobs</b>................<b>$NumberOfSuccessfulJobs</b>.........................<b>$NumberOfFailedJobs</b>............................<b>$NumberOfFailedJobSteps</b>..............................<b>$NumberOfCanceledJobs</b>........
"
    try{
        $smo.ConnectionContext.Disconnect()
        Write-Host "Disconnecting $instance"
    }
    catch{
        Write-Host "Failed disconnect from  $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

}

Write-Host "Since $startdate"
Write-Host "$AllJobs"

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
 
$allJobsMessage = $AllJobs 
    $Text =  @"
# Overview of SQL Agent Jobs in Production since $($startdate)  

$allJobsMessage
"@

    $JSONBody = [PSCustomObject][Ordered]@{
        "@type"      = "MessageCard"
        "@context"   = "http://schema.org/extensions"
        "summary"    = "Overview for the last 12 hours"
        "themeColor" = '0078D7'
        "sections"   = @(
            @{
                "activityTitle"    = "Job Results "
                "activitySubtitle" = "Overview for the last 12 hours since $($startdate)"
                "activityImage"    = "https://live.staticflickr.com/4568/38494483572_a98d623854_k.jpg"
                "text"             = $allJobsMessage
                "markdown"         = $true
            }
        )
    }
 
    $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
 
    $parameters = @{
        "URI"         = $webhookurl
        "Method"      = 'POST'
        "Body"        = $TeamMessageBody
        "ContentType" = 'application/json'
    }
 
    Invoke-RestMethod @parameters

Happy automating!