Notification history

To avoid the creation of multiple planner tasks for the same vulnerability, I’ve created the notification history with an unique ID for each vulnerability. The ID is created by adding the Hostname, CveID (from NIST), SoftwareVendor and SoftwareName.

An example; The server “server235” is a windows server and has the vulnerability “CVE-2025-12345”, then the ID would be “server235-microsoft-windows_server_2022-cve-2025-12345”.

That ID can be saved in a storage account table or a sql table and then be retrieved with the script for exluding these in the Defender API request. The new results can be added after a notification.

Getting the excludes

# Convert-TableToKQLString is from my custom PowerShell Module
$sqlServerName = "yourDB.database.windows.net"
$sqlDatabaseName = "db01"
$sqlSelectQuery4excludes = "SELECT uuid FROM [dbo].[excludes]" # This is an SQL View that joins all the fields
$sqlAccessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

$excludes = Invoke-Sqlcmd -ServerInstance $sqlServerName -Database $sqlDatabaseName -Query $sqlSelectQuery4excludes -AccessToken $sqlAccessToken
$excludesString = Convert-TableToKQLString -table $excludes -fields @('uuid')

$kqlQuery = @"
let excludes = datatable(HostSrc: string, contact: string, oe: string)
[
    $excludesString
];
DeviceTvmSoftwareVulnerabilities
| join kind=leftanti (excludes) on EntryUUID
...

Adding excludes

$sqlServerName = "yourDB.database.windows.net"
$sqlDatabaseName = "db01"
$sqlSelectQuery4excludes = "SELECT uuid FROM [dbo].[excludes]" # This is an SQL View that joins all the fields
$sqlAccessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

foreach ($entry in $batch) {
    $excludes = [PSCustomObject]@{
        CveId           = $entry.CveId
        SoftwareVendor  = $entry.SoftwareVendor
        SoftwareName    = $entry.SoftwareName
        SoftwareVersion = $entry.SoftwareVersion
        cvssScore       = $entry.cvssScore
    }
    $cveId = $excludes.CveId
    $softwareVendor = $excludes.SoftwareVendor
    $softwareName = $excludes.SoftwareName
    $softwareVersion = $excludes.SoftwareVersion
    $cvssScore = $excludes.cvssScore

    $sqlInsertExcludes = "INSERT INTO [dbo].[excludes] (hostname, CveId, SoftwareVendor, SoftwareName, SoftwareVersion, cvssScore) VALUES ('$hostName', '$cveId', '$softwareVendor', '$softwareName', '$softwareVersion' , '$cvssScore' )"
    Invoke-Sqlcmd -ServerInstance $sqlServerName -Database $sqlDatabaseName -Query $sqlInsertExcludes -AccessToken $sqlAccessToken
}

“…” means that there’s more code that is needed. Please check the GitHub Repo for the full code!