AZ-500 Manage identity and access Practice Question
A KQL hunting query joins SecurityIncident with SecurityAlert but returns duplicate rows for incidents with multiple alerts. What KQL approach best preserves one row per incident while summarizing alert details?
⚠ Common exam trap
Watch out — candidates often confuse sorting or limiting rows (options A and C) with deduplication, or incorrectly think a union can replace a join, missing the fundamental need to aggregate after a one-to-many relationship.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use summarize make_set() or arg_max() grouped by IncidentNumber
`summarize make_set()` or `arg_max()` grouped by `IncidentNumber` collapses multiple alert rows into a single incident row while preserving alert details in an array or the most recent alert. This directly addresses the duplicate rows caused by a one-to-many join between SecurityIncident and SecurityAlert, ensuring one row per incident without data loss.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use order by TimeGenerated desc only
Why it's wrong here
ORDER BY TimeGenerated DESC only sorts the result set by the alert/incident timestamp; it does not affect row cardinality. Every matching row produced by the join remains, so an incident with multiple security alerts will still appear multiple times. Sorting alone cannot collapse the fan-out from the join into one row per IncidentNumber.
- ✗
Replace join with union
Why it's wrong here
UNION concatenates rows from SecurityIncident and SecurityAlert, so it does not evaluate the relationship between incident and alert at all. The output is a single list containing all incidents plus all alerts, not one row per incident with associated alert details. UNION also requires compatible column schemas and would not deduplicate the incident-alert pairs created when one incident has many alerts.
- ✗
Use take 1 before the join
Why it's wrong here
TAKE 1 before the join restricts the input to a single row from SecurityIncident before any correlation happens. This is an arbitrary global limit, not a per-incident grouping operation, so the query would return data only for one randomly selected incident and ignore all others. It changes the scope of the query rather than solving the one-row-per-incident fan-out problem.
- ✓
Use summarize make_set() or arg_max() grouped by IncidentNumber
Why this is correct
Summarize make_set() collects the distinct values of a chosen column (for example AlertId or AlertName) into an array for each IncidentNumber, guaranteeing exactly one output row per incident while preserving all associated alert details. If only the most recent alert per incident is needed, arg_max(TimeGenerated, *) returns the latest alert row for each incident. Both operators perform the aggregation after the join and directly address the duplicate-row issue.
Go deeper
Related to this question
About these practice questions
Courseiva writes every AZ-500 question from scratch — 617 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This AZ-500 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the AZ-500 exam.