Okay, so I was deep in some messy CloudTrail logs today—you know the kind, where every vendor decides to pack all their custom context into a single dynamic field like `properties` or `additionalData`—and my usual `parse_json()` / `mv-expand` dance was making my queries crawl. I was about to resign myself to another coffee break while it ran, when I remembered something I saw buried in a KQL doc.
Turns out, combining `bag_unpack` with `extend` is a game-changer for performance when you need to flatten dynamic columns. I always used `bag_unpack` on its own before, which creates a *new column for every key* in the dynamic bag. That’s fine for small, predictable bags, but when you have hundreds of unique keys across millions of rows? It’s a schema explosion, and it murders performance.
Here’s the trick: instead of letting `bag_unpack` run wild, you use `extend` to project *only the specific keys you need* from the bag. This way, you avoid creating a massive, sparse table in memory.
My old, slow pattern looked like this:
```
SecurityEvent
| where EventID == 4688
| extend parsedData = parse_json(CommandLine)
| bag_unpack parsedData
```
This would generate columns for every single key found in *any* CommandLine JSON across my result set. Nightmare.
The new, speedy way:
```
SecurityEvent
| where EventID == 4688
| extend parsedData = parse_json(CommandLine)
| extend ProcessName = parsedData.ProcessName, CommandLineArgs = parsedData.Arguments
```
You’re explicitly telling Sentinel which fields you want to pull out. The query engine doesn't have to inventory all possible keys, create columns for them, and fill them with nulls. It just grabs the two you asked for.
I ran a comparison on a week of data:
* **Old `bag_unpack` method:** ~45 seconds, high memory usage
* **New `extend` with direct field access:** ~7 seconds, low memory
That’s a massive difference! It seems so obvious now. I think my old habit came from working with tools that require an explicit "flatten" step. Sentinel’s dynamic field access is way more efficient when you know the schema you’re after.
Has anyone else stumbled onto this? I’m now going back through all my "optimized" queries to see where I can replace lazy `bag_unpack` calls with targeted `extend` statements. The performance boost in my dashboards and alert rules is going to be significant.
I’m also wondering—are there any *gotchas*? Like, does this break down if the field name has a space or special character? (I think you need to use `["Field Name"]` syntax for those.) Or if the bag is nested? Would love to hear if anyone has pushed this pattern further.
🔥
Try everything, keep what works.
You're right that `extend` with `bag_unpack` is better, but you're missing the critical caveat: you still need to know your keys in advance. If you don't, you're back to square one.
A more reliable pattern for truly unknown schemas is to use `toscalar` to build a static list of the most common keys first, then unpack only those. It adds a scan but prevents the explosion.
```
let commonKeys = toscalar(
MyTable
| summarize make_set(keys(bag))
);
MyTable
| extend parsed = parse_json(bag)
| bag_unpack parsed to typeof(string) columns (commonKeys)
```
Otherwise you're just trading one type of guesswork for another.
Your fancy demo doesn't scale.