KQL Search

Query Details

HomeAI ToolsDevice Query

Security Nested Recommendation Running Container Images MDVM Vulnerability Assessments

Query

let query_period = 120d;
SecurityNestedRecommendation
| where TimeGenerated > ago(query_period) and ParentRecommendationId in ("c609cf0f-71ab-41e9-a3c6-9a1f7fe1b8d5", "682b2595-d045-4cff-b5aa-46624eb2dd8f")
| summarize hint.strategy=shuffle
    StartSubAssessmentTimeGeneration = min(SubAssessmentTimeGeneration),
    EndSubAssessmentTimeGeneration = arg_max(SubAssessmentTimeGeneration, *)
    by Id
| extend
    ClusterLocation = case(
        ParentRecommendationId == "c609cf0f-71ab-41e9-a3c6-9a1f7fe1b8d5", "Azure",
        ParentRecommendationId == "682b2595-d045-4cff-b5aa-46624eb2dd8f", "AWS",
        ""),
    Scanner = "MDVM - Microsoft Defender Vulnerability Management"
// "c609cf0f-71ab-41e9-a3c6-9a1f7fe1b8d5" "MDVM" "AzureRuntimeImageVulnerability"
// "41503391-efa5-47ee-9282-4eff6131462c" "Qualys" "GeneralVulnerability"
// "682b2595-d045-4cff-b5aa-46624eb2dd8f" "MDVM" "AwsRuntimeImageVulnerability"|"AzureRuntimeImageVulnerability"
| 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,
    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"]["IsPubliclyDisclosed"]),
    Exploit_IsVerified = tobool(AdditionalData["VulnerabilityDetails"]["ExploitabilityAssessment"]["IsVerified"]),
    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"]),
    ClusterLocation,
    ClusterResourceId = AdditionalData["ClusterDetails"]["ClusterResourceId"],
    KubernetesContext = AdditionalData["KubernetesContext"]

Explanation

The query retrieves data from the SecurityNestedRecommendation table and filters it based on the time period and specific recommendation IDs. It then summarizes the data by grouping it by the "Id" field and calculates the minimum and maximum values of the "SubAssessmentTimeGeneration" field.

The query also extends the data by adding additional columns such as "ClusterLocation" and "Scanner" based on the values of the "ParentRecommendationId" field. It further extends the data by performing string manipulations and joins with another table to retrieve additional information.

Finally, the query selects specific columns from the data and renames them, and performs calculations on some columns such as calculating the age of an assessment and a vulnerability.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: February 2, 2024

Tables

SecurityNestedRecommendation

Keywords

Devices,Intune,User

Operators

whereandinsummarizebyextendcaseisnotemptyiffextract_allreplace_stringstodynamicjoinonprojectcoalescebintodatetimestrcattoboolarray_sort_asc

Actions