4.3.2. KQL Jobs and Summary Rules
💡 First Principle: Data in the lake is cheap to keep but not built for interactive, real-time querying. Two background mechanisms bridge the gap. KQL jobs run a query against lake data, once or on a schedule, and promote the results to a table you can work with. Summary rules continuously condense high-volume data into compact aggregates. Both let you keep raw data cheap while working with small, fast result tables.
KQL jobs run KQL against data lake tables (and federated tables such as Entra ID and Microsoft 365 data) across selected workspaces. You create them from the KQL query editor (Create job) or under Data lake exploration > Jobs, as one-time jobs (for example, a historical investigation) or scheduled jobs (by minute, hourly, daily, weekly, or monthly). Results write to one destination table: in the analytics tier (new tables get the _KQL_CL suffix, or append to an existing table with a matching schema) or, in preview, back to the lake (_KQL suffix). Limits to recognize: one output table per job, a one-hour query timeout, five concurrent job runs per tenant (extra runs are rejected, not queued), 100 enabled jobs per tenant, and query ranges up to 12 years. Because lake data can arrive about 15 minutes late, scheduled jobs should offset their time window with a delay. Creating jobs requires data lake onboarding and Entra roles with access across workspaces (such as Security Operator or Security Administrator); writing new analytics-tier tables also requires the data lake managed identity to hold Log Analytics Contributor on the workspace.
Summary rules aggregate data on a schedule and write the results to a custom _CL table on the Analytics plan. You define a KQL aggregation, a frequency (the bin size in the API), a delay to absorb ingestion latency, and a start time. When an analytics rule reads the summary table, keep the frequency at 5 minutes or more. Their sweet spot is verbose, high-volume sources kept in a low-cost tier, such as firewall, DNS, or proxy logs. For example, an hourly summary of distinct source and destination IPs with first-seen and last-seen times lets you search for an IP across a year in seconds. A threat-intelligence match on 1 TB per day of firewall data can write only the matches to a small table that an analytics rule then alerts on. Summary rules need Microsoft Sentinel Contributor, and with the Defender portal the workspace must be onboarded there. Enabling SummaryLogs diagnostics shows run history and failures.
| KQL jobs | Summary rules | |
|---|---|---|
| Core purpose | Run a query over lake data and promote results | Continuously aggregate data into a compact table |
| Run pattern | One-time or scheduled | Recurring at a fixed bin size |
| Typical source | Data lake and federated tables, across workspaces | High-volume tables in low-cost tiers |
| Output | One table: analytics tier (_KQL_CL) or lake (_KQL) | Custom Analytics table (_CL) |
| Best for | Historical investigations, retro-hunts, IoC sweeps over years, enrichment | Long-range reporting, cost-efficient detection on aggregates, IP and domain lookups |
| Permissions | Data lake onboarding plus Entra roles (Security Operator or Security Administrator) | Microsoft Sentinel Contributor |
⚠️ Exam Trap: "Search two years of archived proxy logs in the data lake for a newly published IoC and make the hits available to analytics rules" is a one-time KQL job writing to an analytics-tier table. "Reduce the cost of querying 500 GB per day of DNS logs for daily reporting" is a summary rule. Swapping them is the classic distractor.
Reflection Question: Why do both KQL jobs and summary rules offset their query windows with a delay, and what would go wrong without one?
