TH2.15 KQL Anti-Patterns That Produce False Negatives

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// WRONG: Case-sensitive comparison misses variations
SigninLogs
| where AppDisplayName == "Microsoft Azure Portal"
// Misses: "microsoft azure portal", "MICROSOFT AZURE PORTAL"
// M365 logs are inconsistent in capitalization across tenants

// CORRECT: Case-insensitive comparison
SigninLogs
| where AppDisplayName =~ "Microsoft Azure Portal"
// =~ is case-insensitive equals
// has and has_any are also case-insensitive by default
// contains is case-insensitive
// == is case-SENSITIVE — use =~ for string matching in hunting

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// WRONG: Raw results hit the 10,000-row limit silently
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType != 0  // Failed sign-ins
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType
// If there are 50,000 failed sign-ins, you see 10,000
// You do not know you are missing 40,000
// Your analysis of "all failed sign-ins" is based on 20% of the data

// CORRECT: Aggregate before the row limit
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType != 0
| summarize FailedCount = count(),
    UniqueIPs = dcount(IPAddress)
    by UserPrincipalName
// Aggregation produces one row per user — well within 10,000 limit
// If you need raw events, narrow the time window or add filters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

// WRONG: inner join drops users with no matching activity
let suspects = SigninLogs
| where TimeGenerated > ago(7d)
| where RiskLevelDuringSignIn == "high"
| distinct UserPrincipalName;
suspects
| join kind=inner (
    CloudAppEvents | where TimeGenerated > ago(7d)
) on $left.UserPrincipalName == $right.AccountId
// Users with risky sign-ins but NO CloudAppEvents activity
//   are silently dropped from results
// A compromised account that has not yet performed cloud actions
//   disappears from the hunt — false negative

// CORRECT: leftouter preserves all suspects
suspects
| join kind=leftouter (
    CloudAppEvents | where TimeGenerated > ago(7d)
    | summarize CloudActions = count() by AccountId
) on $left.UserPrincipalName == $right.AccountId
// Users with no CloudAppEvents activity show CloudActions = null
// The ABSENCE of activity may itself be an indicator
//   (account compromised but attacker using a different access path)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

// WRONG: Pre-filtering the enrichment table too aggressively
let suspects = ...; // suspect user list
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Consent to application"  // only consent
| join kind=inner suspects on ...
// Misses: MFA registration, role assignment, CA policy change
// The filter restricts to one operation when the hunt needs all

// CORRECT: Join first, then examine all activity
AuditLogs
| where TimeGenerated > ago(7d)
| join kind=inner suspects on ...
| summarize Operations = make_set(OperationName, 20)
    by tostring(InitiatedBy.user.userPrincipalName)
// See ALL audit operations by suspect users
// Then filter to the interesting ones based on what you find

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

// WRONG: Baseline overlaps with detection window
let baseline = SigninLogs
| where TimeGenerated > ago(30d)  // Includes the last 7 days!
| summarize KnownIPs = make_set(IPAddress) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(7d)
| join baseline on UserPrincipalName
| where not(IPAddress in (KnownIPs))
// The attacker's IP from the last 7 days is IN the baseline
// because the baseline includes the detection window
// The attacker's IP is classified as "known" — false negative

// CORRECT: Gap window separates baseline from detection
let baseline = SigninLogs
| where TimeGenerated between (ago(37d) .. ago(7d))  // Ends 7 days ago
| summarize KnownIPs = make_set(IPAddress) by UserPrincipalName;
// TH1.10 covers this in detail — the gap prevents contamination

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// WRONG: Using == on a field that contains a JSON array
SecurityAlert
| where ExtendedProperties == "anomalousToken"
// ExtendedProperties is a JSON string, not "anomalousToken"
// This matches nothing — ever

// ALSO WRONG: Using has on a comma-separated value
| where RiskEventTypes has "anomalousToken"
// has requires a full term boundary — works for whole words
// But if the value is "anomalousToken,unfamiliarFeatures"
//   without spaces, has may not match correctly

// CORRECT: Parse first, then filter
| extend RiskTypes = parse_json(RiskEventTypes)
| where RiskTypes has_any ("anomalousToken", "tokenIssuerAnomaly")
// Or for ExtendedProperties:
| extend Props = parse_json(ExtendedProperties)
| where tostring(Props.["Techniques"]) has "T1557"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

// WRONG: Filtering by UTC hour when your users are in GMT+0
SigninLogs
| where hourofday(TimeGenerated) < 6 or hourofday(TimeGenerated) > 22
// TimeGenerated is UTC. For UK users, this is correct in winter (GMT=UTC)
// but wrong in summer (BST = UTC+1): 22:00 UTC = 23:00 BST
// A sign-in at 23:30 BST (22:30 UTC) is correctly flagged in summer
//   but a sign-in at 05:30 BST (04:30 UTC) is incorrectly flagged

// CORRECT: Adjust for the user's time zone
SigninLogs
| extend LocalHour = hourofday(
    datetime_utc_to_local(TimeGenerated, "Europe/London"))
| where LocalHour < 6 or LocalHour > 22
// datetime_utc_to_local handles DST automatically
// For multi-timezone organizations, join with a user→timezone lookup