Converting a jobid agent token and notify teams

notify teams sql agent jobid convert.ps1

Param(
    $SqlInstance,
    $JobID
)

$webhookurl = ""
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.LastRunDate
    $JobStatus = $job.LastRunOutcome
    $lastjobstepid = $jobsteps[-1].id
    $JobStepStatus = ($Jobsteps | Where id -ne $lastjobstepid).ForEach{
        "   $($_.Name)  -  **$($_.LastRunOutCome)**  
"
    } 
    
    $Text = @"
# **$SqlInstance**   
## **$JobName**  

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"
                    "activityImage"    = "https://fromthegreennotebook.com/wp-content/uploads/2018/03/shutterstock_344519003-796x565.jpg"
                    "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"
                    "activityImage"    = "https://cdn2.vectorstock.com/i/1000x1000/59/81/green-tick-check-mark-icon-simple-style-vector-8375981.jpg"
                    "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

op step code.ps1

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