Listen to this Post
Introduction:
Microsoft Sentinel has long suffered from a frustrating visibility gap: the inability to distinguish between Analytics, Basic, and Auxiliary log table types directly within the Usage table. This forced security teams to rely on expensive API workarounds or ambiguous MeterId fields for cost estimation and usage analysis. The recent quiet update—populating the Plan field with the actual table type—finally brings native, KQL-queryable cost intelligence into Sentinel.
Learning Objectives:
- Query the new `Plan` field in the Usage table to break down ingestion costs by table type (Analytics, Basic, Auxiliary).
- Build automated cost alerts and workbooks using native KQL without external tooling.
- Implement fallback strategies for events where the Plan field is still missing, ensuring reliable cost governance.
You Should Know:
1. Querying the New Plan Field with KQL
This step‑by‑step guide shows how to use the newly populated `Plan` field to analyze Sentinel ingestion costs directly in the Log Analytics workspace.
Step 1 – Verify the Plan field exists
Run the following KQL query to check if your Usage table contains the new field:
Usage | getschema | where ColumnName == "Plan"
If the query returns a row, the field is available.
Step 2 – Total ingested data by table type
Usage | where TimeGenerated > ago(30d) | summarize TotalGB = sum(Quantity) by Plan | render barchart
Step 3 – Daily breakdown for a specific table type
Usage | where TimeGenerated > ago(7d) | where Plan == "Analytics" | summarize DailyGB = sum(Quantity) by bin(TimeGenerated, 1d) | order by TimeGenerated asc
Step 4 – Cost calculation (assuming $2.30 per GB for Analytics logs)
Usage | where TimeGenerated > ago(30d) | extend Cost = case( Plan == "Analytics", Quantity 2.30, Plan == "Basic", Quantity 0.50, Plan == "Auxiliary", Quantity 0.10, 0) | summarize TotalCost = sum(Cost) by Plan
Note: Adjust rates based on your Azure pricing agreement.
2. Automating Cost Analysis with Azure Monitor Workbooks
Create a reusable workbook that refreshes automatically, giving your SOC and FinOps teams a live dashboard.
Step 1 – Create a new workbook
In Azure Monitor → Workbooks → + New → Add query.
Step 2 – Insert KQL for table type breakdown
Usage | where TimeGenerated > ago(7d) | summarize TotalGB = sum(Quantity) by Plan, bin(TimeGenerated, 1d) | order by TimeGenerated asc | render timechart
Step 3 – Add a parameter for time range
Use the workbook’s built-in time picker and reference it as `{TimeRange}` in the query:
Usage
| where TimeGenerated {TimeRange}
| summarize sum(Quantity) by Plan
Step 4 – Publish and share
Save the workbook and assign RBAC roles (e.g., Cost Management Reader) to relevant teams.
- Setting Up Alerts for Anomalous Ingestion by Table Type
Proactively detect sudden spikes in Basic or Analytics logs that could indicate a misconfigured connector or an attack.
Step 1 – Create a scheduled KQL alert rule
In Azure Sentinel → Analytics → Create → Scheduled query rule.
Step 2 – Define the anomaly detection query
Usage | where TimeGenerated > ago(2h) | summarize HourlyGB = sum(Quantity) by Plan, bin(TimeGenerated, 1h) | where HourlyGB > 50 // adjust threshold | join kind=inner ( Usage | where TimeGenerated between (ago(3h) .. ago(2h)) | summarize BaselineGB = sum(Quantity) by Plan ) on Plan | where HourlyGB > BaselineGB 2
Step 3 – Set alert logic
- Frequency: 1 hour
- Period: 2 hours
- Threshold: Number of results > 0
Step 4 – Add automated response
Trigger a Logic App that sends a Teams message or creates a ServiceNow ticket when an anomaly is detected.
- Leveraging the Plan Field for FinOps and Chargeback
Use the new visibility to allocate costs to business units or projects that generate specific log types.
Step 1 – Extend the Usage table with custom tags
If you use resource tags on your Log Analytics workspace, you can join the Usage table with the `_ResourceId` field (requires workspace diagnostic settings).
Step 2 – KQL for chargeback by Plan and resource group
Usage | where TimeGenerated > startofmonth(now()) | summarize MonthlyGB = sum(Quantity) by Plan, _ResourceId | extend RG = tostring(split(_ResourceId, '/')[bash]) | project RG, Plan, MonthlyGB | order by MonthlyGB desc
Step 3 – Export to Power BI
Use the Azure Data Explorer connector in Power BI to pull this query and create executive dashboards.
5. Handling Missing Data and API Fallbacks
Microsoft noted the Plan field is “still randomly missing from some events.” Use this fallback method when the field is absent.
Step 1 – Detect missing Plan values
Usage | where isempty(Plan) or Plan == "" | count
Step 2 – Fallback to MeterId + metadata API
Retrieve table type from the Sentinel REST API using PowerShell:
$token = (Get-AzAccessToken).Token
$headers = @{Authorization = "Bearer $token"}
$workspaceId = "your-workspace-id"
$uri = "https://management.azure.com/subscriptions/{subId}/resourceGroups/{rg}/providers/Microsoft.OperationalInsights/workspaces/$workspaceId/tables?api-version=2021-12-01-preview"
$tables = Invoke-RestMethod -Uri $uri -Headers $headers -Method Get
$tables.value | select name, properties.plan
Step 3 – Create a KQL function that mimics the Plan field
Store the API output in a custom log table (e.g., TablePlanMapping_CL) and use:
Usage | join kind=leftouter TablePlanMapping_CL on $left.MeterId == $right.MeterId | extend Plan_fallback = coalesce(Plan, plan_from_mapping)
6. Detecting Unexpected Table Type Changes (Security Hardening)
Attackers or misconfigurations can change a table’s tier (e.g., from Basic to Analytics), inflating costs or hiding data. Monitor for changes.
Step 1 – Audit table plan modifications
Enable Azure Activity Logs for your Log Analytics workspace. Query for Microsoft.OperationalInsights/workspaces/tables/write.
Step 2 – KQL alert for plan changes
AzureActivity | where OperationNameValue == "MICROSOFT.OPERATIONALINSIGHTS/WORKSPACES/TABLES/WRITE" | where ActivityStatusValue == "Success" | where Properties contains "plan" | project TimeGenerated, Caller, Properties
Step 3 – Automate remediation
Use Azure Policy to deny changes that downgrade security‑relevant tables (e.g., SigninLogs) to Basic retention.
7. Cross‑Environment Comparison with Azure CLI
If you manage multiple Sentinel workspaces, script a cost comparison by Plan field using Azure CLI and jq.
Step 1 – List all workspaces in a subscription
az monitor log-analytics workspace list --query "[].customerId" -o tsv
Step 2 – Run KQL query via CLI for each workspace
WORKSPACE_ID="your-id" QUERY='Usage | where TimeGenerated > ago(30d) | summarize sum(Quantity) by Plan | order by Plan' az monitor log-analytics query --workspace $WORKSPACE_ID --analytics-query "$QUERY" --output table
Step 3 – Aggregate results across subscriptions (Linux/macOS)
for sub in $(az account list --query "[].id" -o tsv); do az account set --subscription $sub for ws in $(az monitor log-analytics workspace list --query "[].customerId" -o tsv); do echo "Workspace: $ws" az monitor log-analytics query --workspace $ws --analytics-query "$QUERY" -o json | jq '.tables[bash].rows' done done
What Undercode Say:
- Key Takeaway 1: Native KQL visibility into table types (Analytics/Basic/Auxiliary) eliminates the need for custom API tooling, democratizing cost analysis for every Sentinel user.
- Key Takeaway 2: The Plan field is not yet 100% reliable—missing from random events means you must implement fallback strategies (API calls, MeterId mapping) to avoid cost blind spots.
Analysis: Microsoft’s silent fix addresses a pain point that has frustrated SOC managers and cloud financial analysts for years. Previously, determining whether a table was Basic (cheap, 90‑day retention) versus Analytics (full, expensive) required parsing MeterId strings or calling the Tables API—both error‑prone and script‑heavy. By surfacing this as a simple Plan column, Microsoft aligns Sentinel with common log analytics platforms (e.g., Sumo Logic’s “source category”). However, the admission that “information is still randomly missing” suggests a phased rollout or eventual consistency issue. Until the field is present in 100% of events, mature teams will keep their API fallbacks. The real win is for automated FinOps: now you can write a single KQL alert that triggers when Auxiliary logs exceed 10% of budget, without maintaining external Python scripts.
Prediction:
Within six months, the Plan field will become the authoritative source for Microsoft Sentinel cost telemetry, and Azure Cost Management will natively ingest it to provide table‑type breakdowns alongside resource group tags. Expect third‑party observability tools (e.g., Cribl, Datadog) to add KQL queries that detect “Basic-to-Analytics” tier changes as a security control—turning a cost‑visibility fix into a detection mechanism for tampered logging policies. The next logical step from Microsoft: real‑time cost forecasting in Sentinel’s UI using machine learning on the Usage table’s Plan and Quantity fields.
▶️ Related Video (78% Match):
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Sandor Tokesi – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
