Hunt for public remotly exploitable devices (with high EPSS)

// Flag remotly exploitable, no user interaction, CVE's with a EPSS score above a certain threshold (likelyhood of exploitation)
// For devices with incomming public connections
// See efficiency research on https://www.first.org/epss/model
let epss_threshold = 0.1;
let exploit_statusses = dynamic(["ExploitIsPublic","ExploitIsInKit","ExploitIsVerified"]);
// Xspm base query we materialize since we need these results multiple times
let xspm_base = materialize (
    ExposureGraphNodes
    // Get device nodes with their inventory ID
    | mv-expand EntityIds
    | where EntityIds.type == "DeviceInventoryId"
    // Get first important properties
    | extend DeviceId = tostring(parse_json(EntityIds)["id"]),
        ExposureScore = tostring(parse_json(NodeProperties)["rawData"]["exposureScore"]),
        HasHighOrCriticalCve = tostring(parse_json(NodeProperties)["rawData"]["highRiskVulnerabilityInsights"]["hasHighOrCritical"])
    // Focus on devices with high exposure
    | where ExposureScore == "High"
    // Get vulnerability exploit information
    | extend RceExploitLevels = parse_json(NodeProperties)["rawData"]["highRiskVulnerabilityInsights"]["vulnerableToRemoteCodeExecution"]["explotabilityLevels"]
    | extend PrivEscExploitLevels = parse_json(NodeProperties)["rawData"]["highRiskVulnerabilityInsights"]["vulnerableToPrivilegeEscalation"]["explotabilityLevels"]
    // Focus on devices where cve has known epxloits
    | where RceExploitLevels has_any (exploit_statusses) or PrivEscExploitLevels has_any (exploit_statusses)
    // Focus on devices that are public exposed
    | join kind=inner (
        DeviceNetworkEvents
        | where TimeGenerated > ago(7d)
        | where ActionType contains "InboundConnection"
        | where RemoteIPType == "Public"
        // Exclude MacOS Rapportd and ControlCenter
        | where InitiatingProcessFileName != "rapportd" and InitiatingProcessFileName != "controlcenter"
        | distinct DeviceName, DeviceId, LocalPort, InitiatingProcessFolderPath, InitiatingProcessVersionInfoProductName, InitiatingProcessFileName
    ) on $left.DeviceId == $right.DeviceId
    // Save all the open ports and their process in a JSON
    | extend OpenPortJson = bag_pack_columns(LocalPort, InitiatingProcessFolderPath, InitiatingProcessFileName)
    // Save open ports by Device ID
    | summarize PublicOpenPortList = make_set(OpenPortJson) by DeviceId
);
// Save flagged device IDs in list to limit results of CVE's we need to search later
let flagged_devices = toscalar(
    xspm_base
    | summarize make_set(DeviceId)
);
// CVE base query we materialize since we need these results multiple times
let cve_base = materialize (
    DeviceTvmSoftwareVulnerabilities
    | where VulnerabilitySeverityLevel in ("High", "Critical")
    | where DeviceId in ( flagged_devices )
);
// Save flagged CVE IDs in list to limit results of CVE database we need to search later
let flagged_cves = toscalar(
    cve_base
    | summarize make_set(CveId)
);
// Query the CVE's of the flagged devices
cve_base
// Enrich the CVE data with their EPSS and CVSS Score
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    // Focus on flagged CVE's
    | where CveId in ( flagged_cves )
    // Focus on CVE's tagged with Attack Vector being over the Network
    // 'Vulnerabilities with this rating are remotely exploitable, from one or more hops away, up to and including remote exploitation over the Internet.'
    // 'Does not require user interaction'
    | where CvssVector contains "/AV:N" and CvssVector contains "/UI:N"
    // Focus on CVE's where an exploit is available
    | where IsExploitAvailable != 0
    | distinct CveId, EpssScore, CvssScore, CvssVector, IsExploitAvailable, AffectedSoftwareList=tostring(AffectedSoftware)
) on CveId
// Continue with only relevant data
| project DeviceId, DeviceName, OSPlatform, OSVersion, OSArchitecture, SoftwareName, SoftwareVendor, SoftwareVersion, CveId, VulnerabilitySeverityLevel, EpssScore, CvssScore, CvssVector, IsExploitAvailable, AffectedSoftwareList
// Now flag CVE's with a EPSS score above a certain threshold
// See efficiency research on https://www.first.org/epss/model
| where EpssScore >= epss_threshold
// Save all the CVE data in a JSON column
| extend CveJson = bag_pack_columns(SoftwareName, SoftwareVendor, SoftwareVersion, CveId, EpssScore, CvssScore, CvssVector, IsExploitAvailable, AffectedSoftwareList)
// Group the CVE data for each device per device
| summarize CveList = make_list(CveJson) by DeviceId, DeviceName
// Add xspm data again
| join kind=inner xspm_base on DeviceId
| project-away DeviceId1
// Sort by CVE amount
| extend CveCount = array_length(CveList)
| sort by CveCount desc