KQL Search

let query_period = 120d;
SecurityNestedRecommendation
| where TimeGenerated > ago(query_period) and ParentRecommendationId in ("1195afff-c881-495e-9bc5-1486211ae03f", "77a4a140-e051-481a-84cc-d4bf2109bd65") // 4ab6e3c5-74dd-8b35-9ab9-f61b30875b27 // e1145ab1-eb4f-43d8-911b-36ddf771d13f
// 1195afff-c881-495e-9bc5-1486211ae03f "Agentless Microsoft Defender vulnerability management"|"Microsoft Defender vulnerability management" Source =="Azure" CloudResourceLocation in ("", "AwsMultiCloud", ...)
// 77a4a140-e051-481a-84cc-d4bf2109bd65 "Agentless Microsoft Defender vulnerability management" Source =="Aws"
| summarize hint.strategy=shuffle
    StartSubAssessmentTimeGeneration = min(SubAssessmentTimeGeneration),
    EndSubAssessmentTimeGeneration = arg_max(SubAssessmentTimeGeneration, *)
    by Id
| extend UpperCamelCase = isnotempty(AdditionalData["AssessedResourceType"])
| extend
    Keys = iff(UpperCamelCase, dynamic(null), extract_all(@'(\"[^\"]+\"\:)', tostring(AdditionalData))),
    Initials = iff(UpperCamelCase, dynamic(null), extract_all(@'(\".)[^\"]*\"\:', tostring(AdditionalData)))
| extend AdditionalData = iff(UpperCamelCase,
    AdditionalData,
    todynamic(replace_strings(
        tostring(AdditionalData),
        Keys,
        todynamic(replace_strings(
            tostring(Keys),
            Initials,
            todynamic(toupper(Initials))
            ))
        ))
    )
| extend AssessedResourceType = tostring(AdditionalData["AssessedResourceType"])
| extend Scanner = case(
    AssessedResourceType == "ServerVulnerabilityTvm", "MDVM - Microsoft Defender Vulnerability Management",
    //AssessedResourceType == "ServerVulnerability", "Qualys",
    ""
    )
| where AssessedResourceType == "ServerVulnerabilityTvm" or (strlen(VulnerabilityId) == 6 and VulnerabilityId matches regex @"[A-Z0-9]*[A-Z][A-Z0-9]*")
| 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,
    Source = tostring(AdditionalData["Source"]),
    ParentRecommendationId,
    //NestedRecommendationId,
    RecommendationState,
    Cause,
    RecommendationSeverity,
    RecommendationSubscriptionId = coalesce(RecommendationSubscriptionName, RecommendationSubscriptionId),
    ResourceGroup,
    AssessedResourceId,
    RecommendationName,
    VulnerabilityId,
    Category,
    Description,
    //Impact, // Qualys
    //RemediationDescription, //Qualys
    //ResourceDetails,
    //Id,
    //AdditionalData,
    //AdditionalDataKeys = array_sort_asc(bag_keys(AdditionalData)),
    Cve = AdditionalData["Cve"],
    CurrentSoftwareName = AdditionalData["SoftwareName"],
    CurrentSoftwareVersion = AdditionalData["SoftwareVersion"],
    RecommendedSoftwareName = AdditionalData["RecommendedProgram"],
    RecommendedSoftwareVersion = AdditionalData["RecommendedVersion"],
    //AssessedResourceType,
    ResourceLocation = coalesce(AdditionalData["CloudSpecificEnrichmentData"]["AssessedResourceType"], ResourceDetails["Source"], ResourceDetails["source"]),
    ResourceHierarchyId = AdditionalData["CloudSpecificEnrichmentData"]["HierarchyId"],
    NativeCloudUniqueIdentifier = AdditionalData["CloudSpecificEnrichmentData"]["NativeCloudUniqueIdentifier"],
    ResourceType = AdditionalData["CloudSpecificEnrichmentData"]["ResourceType"]