Courseiva
← SC-200
Full test
hardMultiple ChoiceObjective-mapped

SC-200 Practice Question: A security analyst in Microsoft Sentinel wants to…

A security analyst in Microsoft Sentinel wants to correlate Microsoft Entra ID sign-in logs with IP addresses known to be associated with a threat actor. The threat actor's IPs are stored in a custom table named 'ThreatIntelligence_IP' that is ingested daily. The analyst needs to create an analytics rule that triggers only when a sign-in occurs from one of these IPs AND when the user is not in a list of approved users (stored in another custom table 'ApprovedUsers'). Which KQL query pattern should the analyst use to achieve this correlation and filtering?

⚠ Common exam trap

Candidates often choose Option B thinking that `in` and `!in` with subqueries are the simplest way to filter, but they overlook that KQL requires proper join semantics for correlating two tables, and that `in` with a table expression may not work as expected in all contexts, especially when the subquery returns multiple rows or columns.

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

✓

SigninLogs | join kind=inner ThreatIntelligence_IP on IPAddress | join kind=leftanti (ApprovedUsers | project UserId) on $left.UserId == $right.UserId

It uses a `join kind=inner` to match sign-in logs with threat IPs, ensuring only sign-ins from known malicious IPs are considered. It then applies a `join kind=leftanti` to exclude any user who appears in the ApprovedUsers table, effectively filtering out approved users. This pattern guarantees that the rule triggers only when both conditions are met: the IP is in the threat list and the user is not approved.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

  • ✗

    SigninLogs | join ThreatIntelligence_IP on IPAddress | where UserId notin (ApprovedUsers | project UserId)

    Why it's wrong here

    This query uses the default join kind, which is innerunique, meaning Kusto deduplicates the left-side SigninLogs rows on the join key (IPAddress) before matching. That can silently discard valid sign-in events if multiple threat-intelligence records share the same IP, and the subsequent `where UserId notin (subquery)` is a scalar row-by-row filter that materializes the ApprovedUsers result set and forces a full comparison for every row. A `leftanti` join would preserve row fidelity and let the engine use a hash-based anti-join instead, making the logic both safer and faster.

  • ✗

    SigninLogs | where IPAddress in (ThreatIntelligence_IP | project IPAddress) and UserId !in (ApprovedUsers | project UserId)

    Why it's wrong here

    This query attempts to filter using `in` and `!in` with subqueries, but `!in` is not a valid KQL operator; the correct syntax is `notin`, so this query would fail to parse. Even after fixing that, the `where` clause materializes both the ThreatIntelligence_IP and ApprovedUsers subqueries and evaluates membership as a nested-loop-style scalar operation, which scales poorly against the large data volumes typical in Microsoft Sentinel. The join-based approach with `kind=inner` followed by `kind=leftanti` is much more efficient because Kusto can build hash tables and perform the inclusion/exclusion in a single pass.

  • ✓

    SigninLogs | join kind=inner ThreatIntelligence_IP on IPAddress | join kind=leftanti (ApprovedUsers | project UserId) on $left.UserId == $right.UserId

    Why this is correct

    This is the correct pattern. The first join uses `kind=inner`, which explicitly returns only matching rows from both sides without deduplicating the left-side SigninLogs rows—unlike the default `innerunique`—thereby preserving all sign-in events from threat-actor IPs. The second join uses `kind=leftanti` to keep every row from the left that has no match in the ApprovedUsers table on UserId, which is the exact semantic needed to exclude approved users. Both joins are optimized by the Kusto engine using hash-based algorithms, making this approach both logically clear and performant for large Sentinel tables.

  • ✗

    SigninLogs | join ThreatIntelligence_IP on IPAddress | where not(UserId in (ApprovedUsers | project UserId))

    Why it's wrong here

    This query uses the default join type, `innerunique`, which deduplicates rows in SigninLogs on IPAddress before the join, potentially losing legitimate sign-in events if the same IP appears in multiple threat-intelligence records. The `where not(UserId in (subquery))` is functionally equivalent to a scalar `notin`, but it is less readable and forces the query engine to evaluate the ApprovedUsers subquery for every surviving row rather than using an anti-join. While this approach might return the same result in some cases, it is neither as efficient nor as maintainable as a direct `leftanti` join, which better communicates the intended 'exclude users who are approved' logic.

About these practice questions

One of 209 original SC-200 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →

How Courseiva writes practice questions · Editorial policy

JA

Written by Johnson Ajibi, MSc IT Security

Senior Network & Security Engineer · founder of Courseiva

This SC-200 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 SC-200 exam.