KQL Search

Query Details

HomeAI ToolsDevice Query

Security Nested Recommendation System Updates Update Center Assessments

Query

let query_period = 120d;
SecurityNestedRecommendation
| where TimeGenerated > ago(query_period) and ParentRecommendationId in ("e1145ab1-eb4f-43d8-911b-36ddf771d13f")
| summarize hint.strategy=shuffle
    StartSubAssessment = min(SubAssessmentTimeGeneration),
    EndSubAssessment = arg_max(SubAssessmentTimeGeneration, *)
    by AssessedResourceId, VulnerabilityId
| join hint.remote=local kind=leftouter (
    arg("").ResourceContainers
    | where type == "microsoft.resources/subscriptions"
    | project RecommendationSubscriptionId = subscriptionId, RecommendationSubscriptionName = name
    ) on RecommendationSubscriptionId
| project
    StartSubAssessment,
    EndSubAssessment,
    Assessment_Age = bin(EndSubAssessment - StartSubAssessment, 1d)/1d,
    IsSnapshot,
    ParentRecommendationId,
    RecommendationState,
    Cause,
    RecommendationSeverity,
    Category,
    RecommendationName,
    Description,
    Impact,
    RecommendationSubscriptionId = coalesce(RecommendationSubscriptionName, RecommendationSubscriptionId),
    ResourceGroup,
    ResourceName = tostring(split(AssessedResourceId, "/")[-1]),
    AssessedResourceId,
    VulnerabilityId,
    OsType = tostring(AdditionalData["Data"]["OsType"]),
    OsVersion = tostring(AdditionalData["Data"]["Version"]),
    ResourceLocation = tostring(coalesce(ResourceDetails["Source"], ResourceDetails["source"]))

Explanation

This query retrieves security recommendations for a specific parent recommendation ID within a given time period. It summarizes the data by the assessed resource ID and vulnerability ID. It also joins the recommendation data with information about the recommendation's subscription. The final result includes various fields such as the start and end times of the sub-assessment, assessment age, recommendation state, severity, category, name, description, impact, subscription ID, resource group, resource name, assessed resource ID, vulnerability ID, operating system type, operating system version, and resource location.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: February 2, 2024

Tables

SecurityNestedRecommendationarg("")

Keywords

Devices,Intune,User

Operators

whereandinsummarizehint.strategyminarg_maxbyjoinhint.remotekindleftouterwhereprojectbinIsSnapshotcoalescetostring

Actions