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 asset = datatable(HostSrc: string, contact: string, oe: string)
[
    $assetString
];

let excludes = datatable(EntryUUID: string)
[
    $excludesString
];
DeviceTvmSoftwareVulnerabilities 
| extend HostSrc = replace_string(DeviceName, ".flo7000.ch", "")
| join kind=inner (asset) on HostSrc
| join kind=leftouter (DeviceTvmSoftwareVulnerabilitiesKB) on CveId
| join kind=leftouter (DeviceTvmSoftwareEvidenceBeta) on DeviceId, SoftwareName, SoftwareVendor
| extend EntryUUID = strcat(HostSrc, "-", CveId, "-",SoftwareVendor, "-",SoftwareName, "-",SoftwareVersion)
| join kind=leftanti (excludes) on EntryUUID
| extend FirstDiskPath = tostring(parse_json((DiskPaths))[0]) // Extract the first disk path
| extend FirstRegistryPath = tostring(parse_json((RegistryPaths))[0]) // Extract the first registry path
| sort by HostSrc, CveId, FirstRegistryPath, FirstDiskPath
// Summarize the query by taking the most recent entry for each HostSrc and CveId
| summarize arg_max(CveId, *) by DeviceName, CveId
| project HostName = HostSrc, FirstDiskPath, FirstRegistryPath, RecommendedSecurityUpdate, 
    VulnerabilityDescription, CvssScore, CveId, SoftwareVendor, SoftwareName, SoftwareVersion, VulnerabilitySeverityLevel, IsExploitAvailable, contact, OSPlatform
| order by HostName
| limit 200
"@

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