SC-200 Inner Join Practice Question
Exhibit
{
"QueryText": "DeviceNetworkEvents | where RemoteIPType == 'Public' and Timestamp > ago(30d) | summarize ConnectionCount = count() by DeviceName, RemoteIP | where ConnectionCount > 100 | join kind=inner (ThreatIntelligenceIndicator | where Active == true) on $left.RemoteIP == $right.NetworkIP",
"QueryDescription": "Hunt for devices making high-volume outbound connections to known threat intelligence IPs"
}Refer to the exhibit. You are reviewing a custom hunting query in Microsoft Defender XDR. The query aims to identify devices with more than 100 outbound connections in the last 30 days to IPs that appear in active threat intelligence indicators. However, the query returns no results. What is the most likely cause?
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
✓
The ThreatIntelligenceIndicator table does not contain any indicators with an Active status that match the remote IPs.
The most likely cause is that the ThreatIntelligenceIndicator table does not contain any active indicators that match the remote IPs from the DeviceNetworkEvents. The query uses an inner join between DeviceNetworkEvents and ThreatIntelligenceIndicator on RemoteIP and NetworkIP. If there are no matching indicators, the join produces zero results. Option A is incorrect because filtering for public IPs is correct for outbound connections to the internet. Option B is incorrect because IP version mismatch would cause a join failure, but the query would still return results if the version matched. Option D is incorrect because the threshold of 100 connections may be high, but if there were matching indicators, some devices would return results.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The RemoteIPType filter for 'Public' excludes all internal IPs, but devices connect to internal IPs mostly.
Why it's wrong here
While the RemoteIPType filter does restrict the dataset to public IPs in KQL, this condition alone would not inevitably produce zero results unless every remote IP in the environment is private. The query is designed to examine public network connections, so excluding internal IPs is intentional. The absence of results is more likely due to the inner join failing to find any active threat indicators matching those public IPs, making the filter a contributing factor rather than the root cause.
- ✗
The join on RemoteIP and NetworkIP is mismatched because one is IPv4 and the other IPv6.
Why it's wrong here
In Microsoft Sentinel's KQL, a join between IP columns is performed on compatible data types; if one column holds IPv4 strings and the other IPv6 strings, they would simply not match, but the query would still execute without error. A true type mismatch, such as comparing a string to an integer, would raise a conversion error, not silently return an empty set. Therefore, the mismatch could reduce matches solely due to different IP format families, but it cannot explain zero results across all traffic unless the environment never uses the same family.
- ✓
The ThreatIntelligenceIndicator table does not contain any indicators with an Active status that match the remote IPs.
Why this is correct
This is the most plausible reason: in a KQL inner join, only rows with matching keys are returned. If the ThreatIntelligenceIndicator table contains no indicators with an Active status that equal any of the observed RemoteIP values, the joined result set will be empty by design. Indicators that are expired or have another status are filtered out, and the TI lookup data may simply not include the IPs the devices are connecting to. This aligns with the classic behavior where an inner join produces zero rows when the right side lacks matches.
- ✗
The ConnectionCount threshold of 100 is too high; most devices do not exceed this.
Why it's wrong here
The ConnectionCount threshold is applied after aggregation and the threat intelligence join, so it narrows results by volume rather than by indicator existence. A threshold of 100 would not cause an empty result set unless every grouped IP individually fails the condition, which is unlikely in a large environment with varying connection volumes. More importantly, if the underlying join already returned no rows because no active indicators matched, the threshold would have nothing to filter, making it an unlikely root cause. The actual issue is almost certainly the absence of matching active threat intelligence entries.
Go deeper
Related to this question
About these practice questions
This SC-200 question is part of Courseiva's 1,303-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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 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.