Exactly-Once Log Summaries with Sentinel data lake and Fabric

Firewall, DNS and proxy logs produce millions of events a day. Keeping them all in Sentinel analytics tier is expensive; keeping them only in the data lake means no analytics rules on them. Summarization is the middle ground: the raw events stay in the lake, and an aggregated version goes back to the expensive tier for detection and reporting.

Microsoft’s built-in options - summary rules and KQL jobs - make this easy, but neither can guarantee that every event is counted exactly once, or even at least once. This post explains why, and walks through a Fabric notebook that counts every event exactly once, however late it arrives - no Fabric experience needed.

Why summaries break

Logs are never queryable the moment they are generated, so an event’s TimeGenerated field and the time it was ingested can differ. Usually the gap is small, but slow sources or collection outages can stretch it to hours.

This is a known problem for analytics rules. Microsoft’s recommendation is to let rule runs overlap on TimeGenerated - the time field Sentinel filters on by default - so late events stay in scope, and then filter on ingestion_time() - the time the record was ingested - so the overlap doesn’t produce duplicates. This still does not solve really big delays.

The data lake doesn’t have this option. KQL jobs support “All KQL operators and functions” except a short list, and ingestion_time() is on it. Microsoft’s guidance is again to overlap the queries - but without ingestion_time(), there is nothing to resolve the duplicates with. And the overlap only catches events that are less late than the overlap itself: with a 15-minute overlap, an event that arrives 20 minutes late is still missed. And the more delay you want to handle, the more data every run reads again, and “Data lake query charges apply per GB of uncompressed data scanned”.

With a 30-minute lookback, on-time event A is counted twice - and very late event B is still missed

To sum up, with KQL jobs you get:

• No at-least-once processing: an event that arrives later than the overlap covers is never counted.
• No at-most-once processing: an event that arrives on time is counted by every run that overlaps it.
• Paying for the same data more than once: the more delay you want to handle, the more data each run scans again.

Fabric’s solution: process by arrival, not by timestamp

Instead of asking “which TimeGenerated range should this run cover?”, ask “what arrived since the last run?”. Sentinel can’t answer that in the data lake - a Delta table in Fabric can.

The Mirrored Azure Monitor (preview) lets Fabric read Sentinel’s tables in place, without moving or copying them. It “doesn’t introduce extra storage or pipeline costs” - you only pay for the Fabric compute.

Log Analytics already stores its data in Delta format, and every write to a Delta table is a new, numbered commit - in arrival order, not by TimeGenerated event time. A firewall log generated 45 minutes ago that landed 5 minutes ago sits in one of the newest commits. In Fabric, Spark can read a Delta table commit by commit and remember the last commit it processed in a checkpoint, so every run picks up exactly what arrived since the previous one. My sample notebook runs every 30 minutes, and however late a log arrives, it is never skipped.

These are exactly the two signals data lake and KQL jobs lack: the commit order replaces ingestion_time(), and the checkpoint remembers what the last run processed.

Bins, not ranges

Each summary row records a time range, so an analyst can find the raw events behind it. A run’s input is mostly recent events plus the occasional delayed event, so a min/max TimeGenerated range would let one late event stretch it to hours. So, instead of this, events are grouped into fixed 15-minute bins, and a late event gets its own row in its real bin:

Every row has a tight lookup range, and the late event isn’t lost

Each row’s TimeGenerated is the processing time; the event time is kept in BinStart and BinEnd. Since Analytics Rules are based on the TimeGenerated timestamp, so this ensures a late 13:30 row falls into the next analytics rule run’s window, not behind one that already ran.

The trade-off: a late event adds a second row for an old bin. So one 15-minutes bin will have multiple entries with different TimeGenerated fields. Your parser/rule has to be prepared to handle this.

Making it exactly-once

Spark processes the new commits in numbered batches. Every batch goes through two steps:

1. Summarize the batch and send it to Sentinel through the Logs Ingestion API
2. Spark marks the batch as done in the checkpoint - automatically, once step 1 finishes without an error

If a run dies before step 2, the checkpoint still points to the previous batch, so the next run replays the failed one - with the same batch ID and the same records. Nothing is lost. But the Fabric docs warn: “If you use custom sinks or foreachBatch, make the write logic idempotent because Spark might rerun a batch after a failure.” The Logs Ingestion API isn’t idempotent: if the run died after the summary was (partly) sent, the replay sends those rows again. So, it can create dups.

That only happens when a run dies at exactly the wrong moment, and the duplicates are easy to spot: replayed rows have the same BatchId as the originals, while a genuine late row always comes from a different batch. This function handles the duplicates:

FwFlowSummary_CL
| summarize arg_min(TimeGenerated, *)
    by PipelineId, BatchId, BinStart, SourceIP, DestinationIP, DestinationPort, Computer, Protocol

So how does this compare to KQL jobs?

• At-least-once processing: yes. Every log that reaches the data lake is processed, regardless of how late it is.
• At-most-once processing: not on its own. A crash during or right after sending can send a batch twice - rare, and filtered out by the deduplication function above. The pipeline itself could prevent it too, for example by checking the summary table in Sentinel before sending or by adding additional states to the pipeline, but that adds complexity and comes with its own trade-offs, so I’m leaving it out here.
• Paying for the same data twice: no. Reading through the mirror doesn’t incur data lake query charges, so there is no overlap to pay for. Fabric compute isn’t free, though: if you already have a Fabric capacity, the extra cost is small, and in my experience, for mid-sized and larger environments it’s still cheaper than querying the data lake via KQL Jobs.

So, compared to KQL jobs, Fabric doesn’t miss late events, makes the rare duplicate easy to remove, and for many mid-sized and larger environments it’s even cheaper.

The notebook

The core is a few lines - a function that summarizes and sends each batch, and the stream that feeds it only what arrived:

def process_batch(batch_df, batch_id): ##batch_df is the new records, _id is the sequence number
    send_to_sentinel(summarize(batch_df, batch_id))   # step 1 - raises on failure, so the batch is replayed; sends the data to Sentinel

query = (spark.readStream.format("delta") # read the new commits
    .load(SOURCE_TABLE_PATH)  # shortcut to the mirrored table
    .writeStream # from this point describe the actions
    .foreachBatch(process_batch)                     # step 1 for every batch hand the records to the function
    .option("checkpointLocation", CHECKPOINT_PATH)   # step 2: define the location of the checkpoint tracker
    .trigger(availableNow=True)  # process everything new available, then stop
    .start())

The whole code is more complex, but this small bit resolves all the issues we had with KQL Jobs. Handles the tracking of the new entries. The full notebook - with Key Vault authentication, chunked sending and a lock against overlapping runs - is here, with a step-by-step setup guide.

The setup step that catches almost everyone: the notebook sends data with a service principal, but its secret is read from Key Vault with the notebook user’s identity, and a scheduled notebook runs as “the user who created or last updated the schedule.” So the schedule owner needs read access to the secret.

Closing thoughts

Summary rules and KQL jobs only see TimeGenerated, so they have to guess how late the data is. Fabric processes by arrival, and with a checkpoint and a small ledger, every event is counted exactly once. If you are building summaries on top of the data lake, this is the pattern I’d start with.

Notebooks aren’t new to Sentinel, but most SIEM engineers have happily avoided them. Now some things can only be done in Fabric notebooks. It is a forced move, but a useful one: data engineering is exactly what security teams will need going forward.