Security Nested Recommendation Container Registries MDVM Vulnerability Assessments

Query

let query_period = 120d;
SecurityNestedRecommendation
| where TimeGenerated > ago(query_period) and ParentRecommendationId in ("c0b7cfc6-3172-465a-b378-53c7ff2cc0d5", "c27441ae-775c-45be-8ffa-655de37362ce")
| summarize hint.strategy=shuffle
    StartSubAssessmentTimeGeneration = min(SubAssessmentTimeGeneration),
    EndSubAssessmentTimeGeneration = arg_max(SubAssessmentTimeGeneration, *)
    by Id
| extend
    RegistryLocation = case(
        ParentRecommendationId == "c0b7cfc6-3172-465a-b378-53c7ff2cc0d5", "Azure",
        ParentRecommendationId == "c27441ae-775c-45be-8ffa-655de37362ce", "AWS",
        ""),
    Scanner = "MDVM - Microsoft Defender Vulnerability Management" // "c0b7cfc6-3172-465a-b378-53c7ff2cc0d5" "MDVM" "AzureContainerRegistryVulnerability"
// "dbd0cb49-b563-45e7-9724-889e799fa648" "Qualys"|"Trivy" "ContainerRegistryVulnerability"
// "03587042-5d4b-44ff-af42-ae99e3c71c87" "Trivy" "ContainerRegistryVulnerability"
// "c27441ae-775c-45be-8ffa-655de37362ce" "MVDM" ""
| extend UpperCamelCase = isnotempty(AdditionalData["AssessedResourceType"])
| extend Keys = iff(UpperCamelCase, dynamic(null), set_union(extract_all(@'(\"[^\"]+\"\:)', tostring(AdditionalData)), dynamic(null)))
| extend Initials = iff(UpperCamelCase, dynamic(null), extract_all(@'(\".)[^\"]*\"\:', tostring(Keys)))
| extend AdditionalData = iff(UpperCamelCase,
    AdditionalData,
    todynamic(replace_strings(
        tostring(AdditionalData),
        Keys,
        todynamic(replace_strings(
            tostring(Keys),
            Initials,
            todynamic(toupper(Initials))
            ))
        ))
    )
| join hint.remote=local kind=leftouter (
    arg("").ResourceContainers
    | where type == "microsoft.resources/subscriptions"
    | project RecommendationSubscriptionId = subscriptionId, RecommendationSubscriptionName = name
    ) on RecommendationSubscriptionId
| project
    //TimeGenerated,
    LastPushedToRegistry = todatetime(AdditionalData["ArtifactDetails"]["LastPushedToRegistryUTC"]),
    StartSubAssessmentTimeGeneration,
    EndSubAssessmentTimeGeneration,
    Assessment_Age = bin(EndSubAssessmentTimeGeneration - StartSubAssessmentTimeGeneration, 1d)/1d,
    //IsSnapshot,
    //ResourceProviderType,
    Scanner,
    //ParentRecommendationId,
    //NestedRecommendationId,
    RecommendationState,
    Cause,
    RecommendationSeverity,
    RecommendationSubscriptionId = coalesce(RecommendationSubscriptionName, RecommendationSubscriptionId),
    ResourceGroup,
    AssessedResourceId,
    RecommendationName,
    VulnerabilityId,
    //Category, // Qualys
    Description,
    //Impact, // Qualys
    RemediationDescription,
    //ResourceDetails,
    //Id,
    //AdditionalData,
    //AdditionalDataKeys = array_sort_asc(bag_keys(AdditionalData)),
    CveId = AdditionalData["VulnerabilityDetails"]["CveId"],
    Vulnerability_Severity = AdditionalData["VulnerabilityDetails"]["Severity"],
    Vulnerability_PublishedDate = todatetime(AdditionalData["VulnerabilityDetails"]["PublishedDate"]),
    Vulnerability_Age = bin(now() - todatetime(AdditionalData["VulnerabilityDetails"]["PublishedDate"]), 1d)/1d,
    Vulnerability_Cvss = case(
        isnotempty(AdditionalData["VulnerabilityDetails"]["Cvss"]["3.0"]), strcat(AdditionalData["VulnerabilityDetails"]["Cvss"]["3.0"]["Base"], " - ", AdditionalData["VulnerabilityDetails"]["Cvss"]["3.0"]["CvssVectorString"]),
        isnotempty(AdditionalData["VulnerabilityDetails"]["Cvss"]["2.0"]), strcat(AdditionalData["VulnerabilityDetails"]["Cvss"]["2.0"]["Base"], " - ", AdditionalData["VulnerabilityDetails"]["Cvss"]["2.0"]["CvssVectorString"]),
        ""),
    Exploit_IsInExploitKit = tobool(AdditionalData["VulnerabilityDetails"]["ExploitabilityAssessment"]["IsInExploitKit"]),
    Exploit_IsPubliclyDisclosed = tobool(AdditionalData["VulnerabilityDetails"]["ExploitabilityAssessment"]["ExploitStepsPublished"]),
    Exploit_IsVerified = tobool(AdditionalData["VulnerabilityDetails"]["ExploitabilityAssessment"]["ExploitStepsVerified"]),
    Exploit_Types = AdditionalData["VulnerabilityDetails"]["ExploitabilityAssessment"]["Types"],
    Exploit_Uris = AdditionalData["VulnerabilityDetails"]["ExploitabilityAssessment"]["ExploitUris"],
    SoftwareName = AdditionalData["SoftwareDetails"]["PackageName"],
    SoftwareCurrentVersion = AdditionalData["SoftwareDetails"]["Version"],
    SoftwareFixedVersion = AdditionalData["SoftwareDetails"]["FixedVersion"],
    //AssessedResourceType = AdditionalData["AssessedResourceType"],
    //ArtifactType = AdditionalData["ArtifactDetails"]["ArtifactType"], // == "ContainerImage"
    RegistryHost = AdditionalData["ArtifactDetails"]["RegistryHost"],
    RepositoryName = AdditionalData["ArtifactDetails"]["RepositoryName"],
    ImageDigest = AdditionalData["ArtifactDetails"]["Digest"],
    ImageTags = array_sort_asc(AdditionalData["ArtifactDetails"]["Tags"]),
    RegistryLocation,
    MediaType = AdditionalData["ArtifactDetails"]["MediaType"]

Explanation

This KQL (Kusto Query Language) query is designed to analyze security recommendations related to vulnerabilities in Azure and AWS environments over the past 120 days. Here's a simplified breakdown of what the query does:

Define Time Period: It sets a time period of 120 days to look back from the current date.
Filter Data: It filters the SecurityNestedRecommendation table to include only records generated within the last 120 days and with specific ParentRecommendationId values corresponding to Azure and AWS.
Summarize Data: It summarizes the data by calculating the earliest and latest sub-assessment times for each unique Id.
Extend Data: It adds new columns to the data:
- RegistryLocation: Determines if the recommendation is related to Azure or AWS based on ParentRecommendationId.
- Scanner: Sets the scanner type to "MDVM - Microsoft Defender Vulnerability Management".
- UpperCamelCase, Keys, Initials, and AdditionalData: These are used to manipulate and format additional data fields.
Join with Subscription Data: It performs a left outer join with another dataset to include subscription details like RecommendationSubscriptionId and RecommendationSubscriptionName.
Project Relevant Columns: It selects and formats specific columns for the final output, including:
- Vulnerability details such as CveId, Severity, PublishedDate, and Cvss scores.
- Exploitability assessments like whether the vulnerability is in an exploit kit or publicly disclosed.
- Software details such as SoftwareName, CurrentVersion, and FixedVersion.
- Artifact details like RegistryHost, RepositoryName, ImageDigest, and ImageTags.
- Other metadata like RegistryLocation, MediaType, and Assessment_Age.

The query essentially provides a detailed report on vulnerabilities, their assessments, and related metadata for specific Azure and AWS security recommendations over the past 120 days.