Listen to this Post
Introduction:
The “Plan” column has finally arrived in Microsoft Sentinel’s Usage table, unlocking granular cost insights that were previously impossible. By leveraging the power of Kusto Query Language (KQL) to analyze data ingestion patterns, security teams can now pinpoint budget-draining tables and estimate massive savings by shifting secondary data to the Data Lake tier, all without compromising detection coverage. This article dissects three new open-source KQL queries that empower you to take control of your SIEM budget, moving beyond guesswork to precise, actionable cost optimization.
Learning Objectives:
- Compute daily average ingestion volumes per table and plan tier to establish cost baselines.
- Model hypothetical cost reductions by re-routing high-volume tables from Analytics to Data Lake.
- Visualize hourly ingestion spikes for proactive capacity planning and anomaly detection.
You Should Know:
- Pinpoint Your Biggest Spenders: Daily Ingestion Per Table & Plan
Before cutting costs, you need a precise map of your spending. The `Usage` table in Microsoft Sentinel acts as your central ledger, recording every gigabyte ingested. By grouping data by `DataType` and the new `Plan` column, you can see exactly which tables are driving up your bill.
What this does: Calculates the average daily ingestion (in GB) for each table and plan tier (Analytics, Basic, or Data Lake) over a custom time window.
How to use it: Run this query in your Sentinel Log Analytics workspace’s Log Management environment.
KQL Query:
let Timeframe = 30d; // Define the required Timeframe let decfra = 2; // Determine the size of fractional part to fit your need Usage | where TimeGenerated >= startofday(ago(Timeframe)) | where IsBillable == true | summarize DailyGB = sum(Quantity) / 1000.0 by bin(TimeGenerated, 1d), DataType, Plan | summarize AvgDailyGB = round(avg(DailyGB), decfra) by DataType, Plan | order by AvgDailyGB desc
Step‑by‑step guide:
- Set the time window: Modify `Timeframe` (e.g.,
7d,90d) to suit your analysis. - Precision control: Adjust `decfra` to change decimal rounding.
- Interpret output: Tables with high `AvgDailyGB` under the “Analytics” plan are your primary cost drivers.
- Prioritize: Focus on the top 3-5 tables for further investigation or re-tiering.
This query is essential for establishing a baseline “normal” usage pattern, making anomalous spikes or unexpected high-volume tables immediately visible.
- Unlock Hidden Savings: Estimate Data Lake Migration Benefits
The new Data Lake tier offers drastically lower ingestion and storage costs for secondary security data (e.g., NetFlow, cloud logs, or verbose audit trails). This query simulates the financial impact of moving a table from the Analytics tier to the Data Lake tier, providing a concrete dollar figure for potential savings.
What this does: Takes high-volume Analytics-tier tables and recalculates their monthly cost using Data Lake pricing, then computes the difference.
How to use it: Run this after identifying cost drivers with the first query. Note: This is a planning tool; always verify current pricing from the official Microsoft Azure pricing page.
KQL Query:
let AnalyticsPricePerGB = 2.48; // Example price per GB for Analytics let DataLakePricePerGB = 0.05; // Example price per GB for Data Lake Usage | where TimeGenerated >= startofday(ago(30d)) | where IsBillable == true | summarize IngestedGB = round(sum(Quantity) / 1000.0, 2) by DataType, Plan | where Plan == "Analytics" | extend CurrentCost = round(IngestedGB AnalyticsPricePerGB, 2) | extend IfDataLakeCost = round(IngestedGB DataLakePricePerGB, 2) | extend PotentialSavings = round(CurrentCost - IfDataLakeCost, 2) | where PotentialSavings > 0 | project DataType, IngestedGB, CurrentCost, IfDataLakeCost, PotentialSavings | order by PotentialSavings desc
Step‑by‑step guide:
- Confirm pricing: Update `AnalyticsPricePerGB` and `DataLakePricePerGB` with your region’s exact rates.
- Scope the analysis: The 30-day window gives a stable monthly estimate.
- Review the `PotentialSavings` column: This is your projected monthly saving for each table.
- Prioritize by savings: Focus on tables with the highest savings potential first.
- Validate operational impact: Before moving any table, test that required detections still work, as Data Lake has query performance characteristics.
Crucial Caveat: This query models ingestion savings only. According to Microsoft, Data Lake pricing includes ingestion ($0.05/GB), processing ($0.10/GB), storage ($0.026/GB/month), and query costs ($0.005/GB scanned). A complete cost model must account for these additional charges, especially for high-query-volume tables.
3. Flatten Cost Spikes: Hourly Ingestion Trend Analysis
Cost overruns often come from unexpected, short-lived ingestion spikes. This query reveals exactly when these spikes occur, broken down by plan tier, enabling targeted remediation.
What this does: Aggregates billable ingestion by hour and plan, presenting the data in an easy-to-read column chart to identify peak windows.
How to use it: Run this to visualize your workspace’s ingestion “heartbeat.” A sudden, sustained spike in the Analytics tier warrants immediate investigation.
KQL Query:
let Timeframe = 7d; // Define the required Timeframe
Usage
| where TimeGenerated >= ago(Timeframe)
| where IsBillable == true
| extend Hour = datetime_part("hour", TimeGenerated)
| summarize IngestedGB = sum(Quantity) / 1000.0 by Hour, Plan
| evaluate pivot(Plan, sum(IngestedGB))
| order by Hour asc
| render columnchart
Step‑by‑step guide:
- Analyze the chart: Look for hours where Analytics-tier ingestion dramatically exceeds the baseline.
- Correlate with data sources: Use the `Hour` value to pinpoint which connector or data source (
DataType) drove the spike. - Apply mitigations: For recurring spikes, implement ingestion time transformations to filter out low-value logs, or switch the offending data source to the Basic or Data Lake tier.
- Proactive alerting: Create a scheduled alert rule in Sentinel using a variant of this query to notify you when hourly ingestion exceeds a defined threshold.
What Undercode Say:
- Cost visibility is now proactive, not reactive.
- The Data Lake tier is a game-changer, but not a panacea.
The introduction of the `Plan` column and these KQL queries marks a shift from opaque billing to transparent, queryable telemetry. Many organizations have struggled to justify Sentinel’s cost because they couldn’t easily attribute spending. Now, with these three queries, any security analyst can produce a detailed cost breakdown in minutes, enabling data-driven decisions.
However, the analysis must go beyond simple queries. Effective cost management requires a layered strategy: implement summary rules to reduce high-volume data by aggregating it, leverage Azure Policy to prevent unauthorized high-cost data sources from being onboarded, and consistently audit the `Usage` table. The Data Lake tier is ideal for secondary security data, but critical security signals needed for real-time correlation must remain in the Analytics tier. This nuanced approach is the key to unlocking Sentinel’s full potential without budget overruns.
Expected Output:
A comprehensive, query-driven cost optimization dashboard that reduces monthly SIEM costs by 40-60% by shifting appropriate tables to Data Lake, eliminating anomalous data ingestion, and providing continuous visibility into spending per security signal.
Prediction:
As cloud-native SIEM costs become a primary driver of security budgets, capabilities like the `Plan` column and advanced KQL for cost will evolve from “nice-to-have” to mandatory features. We predict that within 12-18 months, Microsoft will release native AI-driven optimization recommendations directly within Sentinel, using patterns from these types of queries to automatically suggest and even automate tier migrations. The future SOC leader will be as proficient in KQL-driven cost analytics as in threat hunting.
▶️ Related Video (80% Match):
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Mmihalos Kql – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
