Our KQL Cheat Sheet covered the operators you reach for every day — filtering, joins, watchlists, time windows. This post picks up where that one stopped: the operators that separate a query that works from one that's fast, correct, and doesn't fall over the first time a field is missing or a bag is malformed.
What you'll learn
Six shape problems, six operators
1
mv-expand — Flattening Alert Entities
Shape problem
One string column holds a JSON array of many entities
SecurityAlert.Entities is a string column holding a JSON array — every account, host, IP, and file involved in an alert, bundled into one field. Most analysts either ignore it or reach for a clumsy parse_json(...)[0] to grab a single entity. mv-expand is the correct tool: it turns each array element into its own row.
Flatten every entity in every alert from the last 24 hours
Copy
// Flatten every entity in every alert from the last 24 hours
SecurityAlert
| where TimeGenerated > ago(24h)
| extend EntitiesArray = parse_json(Entities)
| mv-expand EntitiesArray
| extend EntityType = tostring(EntitiesArray.Type)
| project TimeGenerated, AlertName, AlertSeverity, EntityType, EntitiesArray
| where EntityType in ("account", "host", "ip")
Filter down to a specific entity type and pull out its properties once you know what you're looking at:
Every account entity referenced by high-severity alerts
Copy
// Extract every account entity referenced by high-severity alerts
SecurityAlert
| where TimeGenerated > ago(7d)
| where AlertSeverity in ("High", "Medium")
| extend EntitiesArray = parse_json(Entities)
| mv-expand EntitiesArray
| where tostring(EntitiesArray.Type) == "account"
| extend AccountName = tostring(EntitiesArray.Name)
| summarize AlertCount = count(), AlertNames = make_set(AlertName) by AccountName
| order by AlertCount desc
Performance rule
mv-expand multiplies row count — an alert with 12 entities becomes 12 rows. Always filter (where TimeGenerated, where AlertSeverity) before the expansion, not after, so you're not exploding rows you're about to throw away.
Row limit
The default row limit per input row is effectively unbounded (2147483647), but the deprecated mvexpand syntax caps at 128. Always use the current mv-expand spelling.
2
parse — Extracting Fields from Free-Text Messages
Shape problem
Unstructured text where you need structured fields
Not every log source gives you clean columns. The Syslog table's SyslogMessage field is a good example — a single string column containing whatever the daemon on the source system wrote. When you need structured fields out of unstructured text, parse beats a pile of extract() calls chained together.
SSH failed-password events out of raw Syslog
Copy
// Parse SSH failed-password events out of raw Syslog messages
Syslog
| where TimeGenerated > ago(1d)
| where ProcessName == "sshd"
| where SyslogMessage has "Failed password"
| parse SyslogMessage with * "Failed password for " User " from " SourceIP " port " Port " " *
| project TimeGenerated, Computer, User, SourceIP, Port
| summarize FailedAttempts = count() by User, SourceIP
| where FailedAttempts > 5
| order by FailedAttempts desc
The default simple mode requires every delimiter to appear or the row returns null for all extended columns — good for uniform log formats. When the input is inconsistent, switch to relaxed mode so partial matches still populate the columns that did parse:
Relaxed mode tolerates missing fields
Copy
// Relaxed mode tolerates messages where not every field is present
Syslog
| where TimeGenerated > ago(1d)
| where ProcessName == "sshd"
| parse kind=relaxed SyslogMessage with * "Failed password for " User " from " SourceIP " port " Port: int * "ssh2" *
| where isnotempty(User)
| project TimeGenerated, Computer, User, SourceIP, Port
Common mistake
Using contains inside a loop of extract() calls to pull five fields out of one string runs five separate regex passes over the same data. One parse statement does it in a single pass and is far easier to read six months later.
For a deeper dive on getting Syslog data into Sentinel in the first place — DCR configuration, facility filtering, rsyslog internals — see our Syslog forwarding deep-dive.
3
bag_unpack — Unpacking JSON Property Bags at Scale
Shape problem
A key–value bag, not an array — one column per key
SecurityAlert.ExtendedProperties is another string-typed JSON field, but this one is a property bag (key-value pairs), not an array. mv-expand isn't the right tool here — bag_unpack is, because it turns each key into its own column.
Unpack ExtendedProperties into real columns
Copy
// Unpack ExtendedProperties into real columns
SecurityAlert
| where TimeGenerated > ago(1d)
| extend PropsBag = parse_json(ExtendedProperties)
| evaluate bag_unpack(PropsBag)
Running bag_unpack without a defined output schema forces the engine to inspect every row's bag to infer the schema — expensive at scale. If you know which keys you care about, declare them and get a documented multi-x performance improvement:
Same unpack with an explicit output schema — much faster
Copy
// Same unpack, but with an explicit output schema
SecurityAlert
| where TimeGenerated > ago(1d)
| extend PropsBag = parse_json(ExtendedProperties)
| evaluate bag_unpack(PropsBag) : (*, ["Resource Type"]: string, ["Query Language"]: string)
Pro tip
Add an OutputColumnPrefix when unpacking a bag whose keys might collide with existing columns: evaluate bag_unpack(PropsBag, "Prop_"). Combine it with columnsConflict='keep_source' if you want to protect an existing column from being silently overwritten.
4
arg_max / arg_min — The Correct Way to Deduplicate
Shape problem
Many records per entity — you want the latest, whole row
A recurring SOC problem: you have multiple alert or sign-in records for the same entity and you only want the most recent one, with all its columns intact. Reaching for summarize count() doesn't help here — arg_max() does, because it returns an entire row, not just the maximized value.
Most recent alert per compromised entity
Copy
// Get the most recent alert per compromised entity, all columns intact
SecurityAlert
| where TimeGenerated > ago(7d)
| summarize arg_max(TimeGenerated, *) by CompromisedEntity
| project TimeGenerated, AlertName, AlertSeverity, CompromisedEntity, Status
arg_max() vs max() — pick the right one
max(TimeGenerated)
Returns only the maximum timestamp — a single scalar. You lose the record it came from.
arg_max(TimeGenerated, *)
Returns the entire row where that timestamp occurs — which alert, which host, which status.
This also solves the "first seen vs last seen" pattern cleanly — swap in arg_min() for first occurrence:
First and last sign-in per user, side by side (30 days)
Copy
// First and last sign-in per user in the last 30 days, side by side
let FirstSeen = SigninLogs
| where TimeGenerated > ago(30d)
| summarize arg_min(TimeGenerated, IPAddress) by UserPrincipalName;
let LastSeen = SigninLogs
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, IPAddress) by UserPrincipalName;
FirstSeen
| join kind=inner (LastSeen) on UserPrincipalName
| project UserPrincipalName,
FirstSeenTime = TimeGenerated,
FirstSeenIP = IPAddress,
LastSeenTime = TimeGenerated1,
LastSeenIP = IPAddress1
5
materialize() — Stop Recomputing the Same Subquery
Shape problem
One base expression reused across several query branches
When a query branches into a union or multiple summarize legs that all filter the same base table, KQL recomputes that base expression for every branch unless you tell it not to. materialize() evaluates a tabular expression once and caches it for the rest of the query.
Without materialize — the base filter runs twice
let baseAlerts = SecurityAlert | where TimeGenerated > ago(1d);
union
(baseAlerts | where AlertSeverity == "High" | summarize HighCount = count()),
(baseAlerts | where AlertSeverity == "Medium" | summarize MediumCount = count())
With materialize — the base filter runs once and is reused
let baseAlerts = materialize(SecurityAlert | where TimeGenerated > ago(1d));
union
(baseAlerts | where AlertSeverity == "High" | summarize HighCount = count()),
(baseAlerts | where AlertSeverity == "Medium" | summarize MediumCount = count())
Cache limit
materialize() has a 5 GB cache limit per cluster node, shared across every concurrently running query. If your base expression is huge, push filters and project statements into the materialized expression itself to shrink what gets cached — don't materialize the whole table and filter afterward.
When it doesn't help
materialize() adds overhead for simple queries with no repeated subquery. Benchmark before and after — Microsoft's own guidance is explicit that it can increase memory usage without a performance win in some cases.
6
make-series + series_decompose_anomalies() — Time-Series Anomaly Detection
Shape problem
Behaviour drifts slowly — a fixed threshold never fires
Threshold-based detections (FailCount > 10) miss slow, creeping changes. make-series builds an evenly-spaced time series per entity, and series_decompose_anomalies() scores each point against a seasonal baseline — the same technique behind Sentinel's built-in anomaly rules.
Abnormal spikes in failed sign-ins per user — 14 days, hourly bins
Copy
// Detect abnormal spikes in failed sign-ins per user over 14 days
let StartTime = ago(14d);
let EndTime = now();
SigninLogs
| where TimeGenerated between (StartTime .. EndTime)
| where ResultType != 0
| make-series FailedCount = count() default = 0
on TimeGenerated from StartTime to EndTime step 1h
by UserPrincipalName
| extend (Anomalies, AnomalyScore, Baseline) = series_decompose_anomalies(FailedCount, 1.5, -1, 'linefit')
| mv-expand TimeGenerated, FailedCount, Anomalies, AnomalyScore to typeof(double)
| where Anomalies > 0
| project TimeGenerated, UserPrincipalName, FailedCount, AnomalyScore
| order by AnomalyScore desc
The function returns three series, and knowing which is which saves a lot of guessing:
ad_flag
Renamed Anomalies above — +1 spike, −1 dip, 0 normal
ad_score
Magnitude — sort on this to triage the strongest signals first
baseline
The expected value at that point in the series
Raise the threshold parameter (the 1.5 above) toward 2.5–3.0 to suppress noisy, low-magnitude flags and surface only strong anomalies.
Pro tip
Seasonality = -1 autodetects periodicity (daily/weekly login patterns). If your data doesn't have a clean seasonal shape — a brand-new data source, for instance — set Seasonality = 0 to skip that component entirely rather than let it fit noise.
7
Putting It Together
Shape problem
All three at once — nested, duplicated, and reused
A realistic hunting query rarely uses one operator in isolation. Here's brute-force detection with entity enrichment and deduplication combined — mv-expand to unpack alert entities, arg_max to keep only the latest state per account, materialize to avoid recomputing the base filter:
Composite hunting query — three operators, one pipeline
Copy
let RecentAlerts = materialize(
SecurityAlert
| where TimeGenerated > ago(1d)
| where AlertSeverity in ("High", "Medium")
);
let EntityAccounts =
RecentAlerts
| extend EntitiesArray = parse_json(Entities)
| mv-expand EntitiesArray
| where tostring(EntitiesArray.Type) == "account"
| extend AccountName = tostring(EntitiesArray.Name);
EntityAccounts
| summarize arg_max(TimeGenerated, AlertName, AlertSeverity) by AccountName
| order by TimeGenerated desc
Each operator here solves one specific, real shape problem: entities are nested (mv-expand), state needs to be current-only (arg_max), and the base alert set is reused across the pipeline (materialize). That's the pattern worth internalizing — not the individual operators in isolation, but recognizing which shape problem you're looking at.
Quick recap
Reach for this when you see that
mv-expand
JSON array in a string column → one row per element. Filter first.
parse
Free-text message → typed columns in a single pass. Use relaxed for messy sources.
bag_unpack
Property bag → real columns. Declare the output schema at scale.
arg_max / arg_min
Latest (or first) whole row per entity — not just a scalar timestamp.
materialize()
Reused subquery → evaluated once. Watch the 5 GB per-node cache.
make-series
Seasonal baseline instead of a fixed threshold. Tune 1.5 → 3.0 for noise.
References
Verified vs Microsoft Learn
good