Security Nested Recommendation Virtual Machines MDVM Vulnerability Assessments

Query

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 AssessedResourceId, VulnerabilityId
| 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))
            ))
        ))
    )
| 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"]

Explanation

This KQL query is designed to analyze security recommendations related to vulnerability management over a specified period (120 days). Here's a simplified breakdown of what the query does:

Data Filtering: It starts by filtering records from the SecurityNestedRecommendation table where the TimeGenerated is within the last 120 days and the ParentRecommendationId matches specific IDs related to Microsoft Defender vulnerability management.
Data Summarization: It summarizes the data by calculating the earliest and latest times (StartSubAssessmentTimeGeneration and EndSubAssessmentTimeGeneration) for each assessed resource and vulnerability ID.
Data Transformation: The query checks if the AssessedResourceType is in UpperCamelCase format and adjusts the AdditionalData field accordingly. It also identifies the type of scanner used based on the AssessedResourceType.
Further Filtering: It filters the results to include only those with a specific AssessedResourceType or a VulnerabilityId that matches a certain pattern.
Joining with Subscription Data: The query performs a left outer join with subscription data to include subscription names and IDs.
Data Projection: Finally, it selects and renames various fields for the output, such as assessment age, scanner type, source, recommendation details, and software information. It also includes cloud-specific enrichment data like resource location and identifiers.

Overall, this query is used to generate a detailed report on vulnerability management recommendations, including metadata about the resources and vulnerabilities assessed, over a specified time frame.

Details

Jose Sebastián Canós

Released: January 26, 2024

Tables

SecurityNestedRecommendation

Keywords

SecurityNestedRecommendationAzureAwsResourceVulnerabilityManagementServerSubscriptionGroupSoftwareVersionCategoryDescription

Operators

let|where>agoandinsummarizehint.strategy=shuffleminarg_maxbyextendisnotemptyiffset_unionextract_alltostringtodynamicreplace_stringstouppercase==orstrlenmatches regexjoinhint.remote=localkind=leftouteronprojectbin/coalescearray_sort_ascbag_keys

Actions

GitHub

KQL Search