Data preparation

Vulnerability Data schema

The vulnerability data is prepared by Microsoft Defender and can be viewed on the Microsoft Defender Advanced Hunting Portal

Table Name	Description	Fields
DeviceTvmSoftwareVulnerabilities	Contains vulnerability data of the clients	DeviceID, DeviceName, OSPlatform, OSVersion, OSArchitecture, Software-Vendor, SoftwareName, SoftwareVersion, CveId, VulnerabilitySeverityLevel, RecommendedSecurityUpdate, RecommendedSecurityUpdateId, CveTags, CveMitigationStatus, MachineGroup
DeviceTvmSoftwareVulnerabilitiesKB	Contains data of a specific vulnerability	CveId, CvssScore, CvssVector, CveSupportability, IsExploitAvailable, Vulner-abilitySeverityLevel, LastModifiedTime, PublishedDate, VulnerabilityDescrip-tion, AffectedSoftware, TenantId, Type, SourceSystem
DeviceTvmSoftwareEvidenceBeta	Contains registry and disk paths of a software	DeviceId, SoftwareVendor, SoftwareName, SoftwareVersion, RegistryPaths, DiskPaths, LastSeenTime, TenantId, Type, SourceSystem, MachineGroup

Table joins overview

Final result and source for each field

Field ⬇ Source Table ➡	DeviceTvmSoftwareVulnerabilities	Asset	DeviceTvmSoftwareVulnerabilitiesKB	DeviceTvmSoftwareEvidenceBeta
Host	(x)	x
VulnerabilitySeverityLevel			x
DiskPath				x
RegistryPath				x
CveID			x
CvssScore			x
RecommendedSecurityUpdate	x
contact		x
SoftwareVendor	(x)			x
SoftwareName	(x)			x
SoftwareVersion	(x)			x
VulnerabilityDescription			x

“x” indicates the main source for the field and “(x)” is where the field also exists but is only used for joining!

Full Query

$kqlQuery = @"
let date_to_ignore = ago(30d);
let severitylevels_to_ignore = dynamic(["Low", "Medium", "High"]);
let arcjoined =
    AzureArcServer_CL
    | where TimeGenerated > ago(1d)
    | extend hostLookup = tolower(tostring(split(HostName, ".")[0]))
    | summarize arg_max(TimeGenerated, PatchGroup) by hostLookup
    | project hostLookup, PatchGroup = tolower(PatchGroup), isArcJoined = 1;
let assetbase =
    ServerAsset_CL
    | where TimeGenerated > ago(1d)
    | where noSvn == false
    | extend hostLookup = tolower(tostring(split(hostLookup, ".")[0]))
    | summarize arg_max(TimeGenerated, contact, oe) by hostLookup
    | lookup kind=leftouter arcjoined on hostLookup
    | project hostLookup, contact, oe, PatchGroup, isArcJoined;
let deviceinfo =
    DeviceInfo
    | extend hostLookup = tolower(tostring(split(DeviceName, ".")[0]))
    | summarize arg_max(Timestamp, DeviceId, OSPlatform) by hostLookup
    | project hostLookup, DeviceId, OSPlatform;
let lastfullscan =
    DeviceEvents
    | where ActionType contains "AntivirusScanCompleted"
    | where AdditionalFields has "Full"
    | extend hostLookup = tolower(tostring(split(DeviceName, ".")[0]))
    | summarize latestFullScanTime = max(Timestamp) by hostLookup;
let asset =
    assetbase
    | lookup kind=leftouter deviceinfo on hostLookup
    | lookup kind=leftouter lastfullscan on hostLookup
    | where latestFullScanTime > ago(7d)
    | project DeviceId, hostLookup, contact, oe, PatchGroup, isArcJoined, OSPlatform;
let tasks =
    SVNClosedTasks_CL
    | where isnotempty(taskCompletedDate)
    | summarize arg_max(TimeGenerated, taskCompletedDate) by taskid
    | project TaskId = taskid, taskCompletedDate;
let svnOpenNotifications =
    SVNmaster2026_CL
    | where TimeGenerated > ago(360d)
    | lookup kind=leftouter tasks on TaskId
    | where isempty(taskCompletedDate)
    | project TaskId, HostName, CveId, SoftwareVendor, SoftwareName, SoftwareVersion;
let excludes =
    svnOpenNotifications
    | extend JoinKey = tolower(strcat(HostName, "-", CveId, "-", SoftwareVendor, "-", SoftwareName, "-", SoftwareVersion))
    | distinct JoinKey;
let excludesWithPaths =
    svnOpenNotifications
    | join kind=inner (
        SVNPaths_CL
        | project TaskId, SoftwareVendor, SoftwareName, SoftwareVersion, DiskPaths
    ) on TaskId, SoftwareVendor, SoftwareName, SoftwareVersion
    | mv-expand DiskPath = DiskPaths to typeof(string)
    | extend DiskPath = tolower(DiskPath)
    | where isnotempty(DiskPath)
    | extend JoinKey = tolower(strcat(HostName, "-", CveId, "-", SoftwareVendor, "-", SoftwareName, "-", SoftwareVersion))
    | distinct JoinKey, DiskPath;
let excludesWithoutPaths =
    excludes
    | join kind=leftanti (
        excludesWithPaths
        | distinct JoinKey
    ) on JoinKey;
let static =
    SVNstatic_CL
    | where TimeGenerated > ago(180d)
    | extend hostLookup = tolower(tostring(split(HostName, ".")[0]))
    | distinct hostLookup, SoftwareVendor, SoftwareName, SoftwareVersion;
let staticV3 =
    SVNstaticV3_CL
    | where TimeGenerated > ago(360d)
    | extend hostLookup = tolower(tostring(split(hostLookup, ".")[0]))
    | extend SoftwareVendorKey = tolower(SoftwareVendor)
    | extend SoftwareNameKey = tolower(SoftwareName)
    | extend SoftwareVersionKey = tolower(SoftwareVersion)
    | extend StaticCveIdKey = tolower(tostring(CveId))
    | extend StaticPaths = pack_array(tolower(Level3Path), tolower(Level4Path))
    | mv-expand StaticPath = StaticPaths to typeof(string)
    | where isnotempty(StaticPath)
    | distinct
        hostLookup,
        SoftwareVendorKey,
        SoftwareNameKey,
        SoftwareVersionKey,
        StaticCveIdKey,
        StaticPath;
let cve =
    DeviceTvmSoftwareVulnerabilitiesKB
    | project CveId, CvssScore, PublishedDate;
let vulns = materialize(
    DeviceTvmSoftwareVulnerabilities
    | extend hostLookup = tolower(tostring(split(DeviceName, ".")[0]))
    | extend SoftwareVendorKey = tolower(SoftwareVendor)
    | extend SoftwareNameKey = tolower(SoftwareName)
    | extend SoftwareVersionKey = tolower(SoftwareVersion)
    | project
        DeviceId,
        hostLookup,
        SoftwareVendor,
        SoftwareName,
        SoftwareVersion,
        SoftwareVendorKey,
        SoftwareNameKey,
        SoftwareVersionKey,
        CveId,
        VulnerabilitySeverityLevel,
        RecommendedSecurityUpdate
    | join kind=inner hint.strategy=broadcast asset on hostLookup
    | project-away hostLookup1, DeviceId1
    | where not(OSPlatform == "Linux" and PatchGroup contains "all_auto_reboot")
    | where not(PatchGroup contains "all_auto_reboot" and RecommendedSecurityUpdate contains "Security Updates")
    | lookup kind=leftouter cve on CveId
    | where not(VulnerabilitySeverityLevel in (severitylevels_to_ignore) and PublishedDate > date_to_ignore)
    | extend CveIdKey = tolower(tostring(CveId))    
    | extend JoinKey = tolower(strcat(hostLookup, "-", CveId, "-", SoftwareVendor, "-", SoftwareName, "-", SoftwareVersion))
    | join kind=leftanti static on hostLookup, SoftwareVendor, SoftwareName, SoftwareVersion
    | where not(SoftwareName contains "openssl" and CvssScore < 8.5)
);
let vulnerableSoftware =
    vulns
    | distinct DeviceId, SoftwareVendor, SoftwareName, SoftwareVersion;
let swevidence =
    DeviceTvmSoftwareEvidenceBeta
    | join kind=inner hint.strategy=broadcast vulnerableSoftware on DeviceId, SoftwareVendor, SoftwareName, SoftwareVersion
    | mv-expand Path = DiskPaths to typeof(string)
    | where Path !contains "azureconnectedmachineagent"
    | summarize
        DiskPaths = make_set(tolower(Path), 256),
        arg_max(LastSeenTime, RegistryPaths)
        by DeviceId, SoftwareVendor, SoftwareName, SoftwareVersion;
let staticV3SoftwareExcludes =
    vulns
    | extend CveIdKey = tolower(tostring(CveId))
    | distinct
        DeviceId,
        hostLookup,
        SoftwareVendor,
        SoftwareName,
        SoftwareVersion,
        SoftwareVendorKey,
        SoftwareNameKey,
        SoftwareVersionKey,
        CveIdKey
    | join kind=inner hint.strategy=broadcast swevidence
        on DeviceId, SoftwareVendor, SoftwareName, SoftwareVersion
    | mv-expand DiskPath = DiskPaths to typeof(string)
    | extend DiskPath = tolower(DiskPath)
    | lookup kind=inner staticV3
        on hostLookup, SoftwareVendorKey, SoftwareNameKey, SoftwareVersionKey
    | where DiskPath startswith StaticPath
    | where isempty(StaticCveIdKey)
        or isempty(CveIdKey)
        or CveIdKey == StaticCveIdKey
    | distinct
        hostLookup,
        SoftwareVendorKey,
        SoftwareNameKey,
        SoftwareVersionKey,
        CveIdKey;
vulns
| lookup kind=leftouter swevidence on DeviceId, SoftwareVendor, SoftwareName, SoftwareVersion
| join kind=leftanti staticV3SoftwareExcludes
    on hostLookup, SoftwareVendorKey, SoftwareNameKey, SoftwareVersionKey, CveIdKey
| join kind=leftanti excludesWithoutPaths on JoinKey
| extend DiskPathsForExpand = iff(isnull(DiskPaths) or array_length(DiskPaths) == 0, dynamic([""]), DiskPaths)
| mv-expand DiskPath = DiskPathsForExpand to typeof(string)
| extend DiskPath = tolower(DiskPath)
| join kind=leftanti excludesWithPaths on JoinKey, DiskPath
| summarize
    DiskPaths = make_set_if(DiskPath, isnotempty(DiskPath), 256),
    RegistryPaths = any(RegistryPaths),
    RecommendedSecurityUpdate = any(RecommendedSecurityUpdate),
    oe = any(oe),
    CvssScore = any(CvssScore),
    SoftwareVendor = any(SoftwareVendor),
    SoftwareName = any(SoftwareName),
    SoftwareVersion = any(SoftwareVersion),
    VulnerabilitySeverityLevel = any(VulnerabilitySeverityLevel),
    contact = any(contact),
    PatchGroup = any(PatchGroup),
    isArcJoined = any(isArcJoined)
    by hostLookup, CveId, JoinKey
| project
    HostName = hostLookup,
    DiskPaths,
    RegistryPaths,
    RecommendedSecurityUpdate,
    oe,
    CvssScore,
    CveId,
    SoftwareVendor,
    SoftwareName,
    SoftwareVersion,
    VulnerabilitySeverityLevel,
    contact,
    PatchGroup,
    isArcJoined
| extend SeverityRank = case(
    VulnerabilitySeverityLevel == "Critical", 1,
    VulnerabilitySeverityLevel == "High",     3,
    VulnerabilitySeverityLevel == "Medium",   6,
    VulnerabilitySeverityLevel == "Low",      9,
    10
)
| extend CveYear = strcat("CVE-", split(toupper(CveId), "-")[1])
| summarize
    CveCount = dcount(CveId),
    Cves = make_set(pack("CveId", CveId, "CvssScore", CvssScore), 30),
    Severity = min(SeverityRank),
    DiskPaths = make_set(DiskPaths, 30),
    RegistryPaths = make_set(RegistryPaths, 30),
    OldestCveYear = min(CveYear),
    RecommendedSecurityUpdate = make_set(RecommendedSecurityUpdate, 5)
    by HostName, SoftwareVendor, SoftwareName, SoftwareVersion, contact, oe, PatchGroup, isArcJoined
| extend DiskPathsString = tostring(DiskPaths)
"@

$response = Invoke-DefenderATPQuery -Query $kqlQuery
$result = $response.Result