Teams Notify Jobs - Success Failure last hour

gistfile1.txt

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

Import-Module 'C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.107\dbatools.psd1'

$AllFailedJobs = 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
    $FailedJobs = $jobs | Where-Object { $Psitem.Status -ne 'Succeeded' }
    $FailedJobs | Group-Object Job 
    try{
        $smo.ConnectionContext.Disconnect()
        Write-Host "Disconnecting $instance"
    }
    catch{
        Write-Host "Failed disconnect from  $Instance" 
        $errorMessage = $_ | Out-String
        Write-Host $errorMessage
        Continue
    }

}
Write-Host "We have  $($AllFailedJobs.Count) Failed Jobs"

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

foreach ($j in $AllFailedJobs) {
 
$Inst = $j.group[-1].SqlInstance
$jName = $j.name
$sname = $j.group[-1].StepName
$edate = $j.group[-1].EndDate
$errMessage = $j.group[-1].Message
    $Text =  @"
# **$Inst**   
## **$JName**  
- The Job step that failed is - **$sname**  
- It failed at - **$edate**  
- It failed because   - $errMessage   
"@

    $JSONBody = [PSCustomObject][Ordered]@{
        "@type"      = "MessageCard"
        "@context"   = "http://schema.org/extensions"
        "summary"    = "There was a Job Failure"
        "themeColor" = '0078D7'
        "sections"   = @(
            @{
                "activityTitle"    = "Job Failures "
                "activitySubtitle" = "in the Last 1 hour"
                "activityImage"    = "https://fromthegreennotebook.com/wp-content/uploads/2018/03/shutterstock_344519003-796x565.jpg"
                "text"             = $text
                "markdown"         = $true
            }
        )
    }
 
    $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
 
    $parameters = @{
        "URI"         = $webhookurl
        "Method"      = 'POST'
        "Body"        = $TeamMessageBody
        "ContentType" = 'application/json'
    }
 
    Invoke-RestMethod @parameters
}

if(-not $AllFailedJobs){
  
        $JSONBody = [PSCustomObject][Ordered]@{
            "@type"      = "MessageCard"
            "@context"   = "http://schema.org/extensions"
            "summary"    = "There were no job failures in the last hour at $(Get-Date)"
            "themeColor" = '0078D7'
            "sections"   = @(
                @{
                    "activityTitle"    = "There were no job failures at $(Get-Date)"
                    "activitySubtitle" = "in the Last hour"
                    "activityImage"    = "https://cdn2.vectorstock.com/i/1000x1000/59/81/green-tick-check-mark-icon-simple-style-vector-8375981.jpg"
                    "text"             = "All is well"
                    "markdown"         = $true
                }
            )
        }
     
        $TeamMessageBody = ConvertTo-Json $JSONBody -Depth 100
     
        $parameters = @{
            "URI"         = $webhookurl
            "Method"      = 'POST'
            "Body"        = $TeamMessageBody
            "ContentType" = 'application/json'
        }
     
        Invoke-RestMethod @parameters
}