Security Nested Recommendation SQL Databases Assessments
Query
let query_period = 120d;
SecurityNestedRecommendation
| where TimeGenerated > ago(query_period) and ParentRecommendationId in ("82e20e14-edc5-4373-bfc4-f13121257c37", "f97aa83c-9b63-4f9a-99f6-b22c4398f936")
| summarize hint.strategy=shuffle
StartSubAssessment = min(SubAssessmentTimeGeneration),
EndSubAssessment = arg_max(SubAssessmentTimeGeneration, *)
by AssessedResourceId, VulnerabilityId
| 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))
))
))
)
| 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, "/")[-3]),
DatabaseName = tostring(split(AssessedResourceId, "/")[-1]),
AssessedResourceId,
VulnerabilityId,
Benchmarks = todynamic(replace_string(tostring(AdditionalData["Benchmarks"]), '"$type":"additionalData/benchmark",', "")),
Query = tostring(AdditionalData["Query"]),
ResourceType = tostring(AdditionalData["Type"]),
ResourceLocation = tostring(coalesce(ResourceDetails["Source"], ResourceDetails["source"]))Explanation
The query retrieves data from the SecurityNestedRecommendation table within a specified time period. It filters the data based on specific ParentRecommendationIds. It then summarizes the data by grouping it based on AssessedResourceId and VulnerabilityId. It also performs some data manipulation and joins the data with another table. Finally, it selects specific columns from the data and renames some columns for better readability.