Listen to this Post
Introduction:
Security Information and Event Management (SIEM) platforms like Microsoft Sentinel ingest massive volumes of telemetry, but up to 80% of that data often adds little investigative value. Microsoft has now released two critical ingestion‑time features – filter and split transformations (currently in preview) – that let you discard useless noise and intelligently route high‑value logs to the analytics tier while offloading the rest to cheaper data lake storage. Mastering these KQL‑based rules is no longer optional; it’s a direct lever for controlling SIEM costs and sharpening threat detection.
Learning Objectives:
- Implement filter transformations to drop irrelevant log entries before they ever reach the analytics workspace, reducing noise and storage costs.
- Configure split transformations to dynamically route data between the Analytics tier and Data lake tier based on custom KQL logic.
- Validate and troubleshoot transformation rules using Azure CLI, PowerShell, and Sentinel’s built‑in testing tools.
You Should Know:
- Filter Transformations – Discard Useless Data at Ingestion
Filter transformations allow you to specify a KQL condition. Only logs that match the condition are sent to the Analytics tier; everything else is dropped permanently. This is ideal for eliminating repetitive heartbeat logs, debug traces, or known false positives from development environments.
Step‑by‑step guide to create a filter transformation:
- In Microsoft Sentinel, navigate to Settings → Workspace settings → Tables (or Data transformation under the Sentinel configuration blade).
- Select the target table (e.g.,
CommonSecurityLog,Syslog, or a custom table). - Click Create transformation and choose Filter as the type.
- Enter a KQL expression that returns `true` for logs you want to keep. For example, to drop all events with severity “informational” from a firewall:
| where Severity != "informational"
Or to keep only failed authentication attempts from a specific subnet:
| where EventID == 4625 and SourceIP startswith "192.168.1."
- Preview the transformation using a sample of recent data (Sentinel shows how many events would be kept vs. dropped).
- Deploy the rule. It will apply to all new ingested data – existing logs are unchanged.
Validation commands (Azure CLI & PowerShell):
After deploying, verify the transformation is active:
Azure CLI
az monitor log-analytics workspace table show --resource-group <RG> --workspace-name <Workspace> --name <TableName> --query "plan"
PowerShell (Az module)
Get-AzOperationalInsightsTable -ResourceGroupName <RG> -WorkspaceName <Workspace> -TableName <TableName> | Select-Object Name, Plan, RetentionInDays
If the `Plan` property shows `Analytics` with a transformation applied, the rule is active. Monitor the `_LogOperation` table for any transformation errors:
_LogOperation | where Operation == "Data Collection Rule" or Operation == "Ingestion Transformation" | project TimeGenerated, Detail, Status
- Split Transformations – Route High‑Value Data to Analytics, Low‑Value to Data Lake
Split transformations let you define a KQL expression that determines which data lands in the Analytics tier (fast querying, alerting, and hunting) and which goes only to the Data lake tier (cheaper storage, slower query, no active alerts). This is perfect for separating security‑critical events from compliance or verbose network flows.
Step‑by‑step guide to configure a split transformation:
- From the same Tables blade in Sentinel, select your target table and click Create transformation → Split.
- Write a KQL expression that returns `true` for logs that should go to Analytics. All others automatically go to the Data lake tier.
Example – send all Windows Event ID 4625 (failed logons) and 4688 (process creation) to Analytics; send everything else to the lake:| where EventID in (4625, 4688)
For a firewall log, send traffic to port 443 (HTTPS) plus all deny actions to Analytics; the rest to lake:
| where (DestinationPort == 443) or (Action == "deny")
- Use the preview feature to simulate the split – it will show percentages of data going to each tier.
- Deploy. Data will be split from the moment the rule is enabled. You can query the lake tier using Azure Data Explorer or Sentinel’s `_DataLake` table (preview).
Advanced example – split based on threat intelligence match:
Assume you enrich logs with TI data via a watchlist. This split sends only events with a known malicious indicator to Analytics:
let MaliciousIPs = (_GetWatchlist('TI_IPs') | project IP);
| where SourceIP in (MaliciousIPs) or DestinationIP in (MaliciousIPs)
How to verify split routing:
Query the `_DataLake` table to confirm low‑priority logs are landing correctly:
_DataLake | where TableName == "Syslog" | where TimeGenerated > ago(1h) | summarize Count = count() by SeverityLevel
Compare the same query on the main `Syslog` table – counts should differ based on your split logic.
- Combining Filter and Split – A Cost‑Optimization Blueprint
For maximum efficiency, you can apply both transformations to the same table by chaining them: first filter out absolute noise (e.g., heartbeats, debug), then split the remaining data between Analytics and lake.
Step‑by‑step layered transformation:
- Create a filter transformation that drops all events with `LogLevel == “DEBUG”` or
Message contains "heartbeat". - Create a split transformation on the same table that sends only `EventID == 4625` (failed logons) to Analytics, while `EventID == 4663` (file access) goes to the lake.
- Order of execution: filter runs first, then split on the surviving logs. This prevents lake tier from being polluted with already‑filtered data.
Testing both rules using Azure Resource Manager (ARM) template validation:
Export current transformation rules $rules = Get-AzSentinelDataCollectionRule -WorkspaceName <Workspace> -ResourceGroupName <RG> $rules.Properties.Transformations | ConvertTo-Json -Depth 10
Use the Sentinel Testing Tool (preview CLI) to simulate a batch of logs:
az sentinel transformation test --workspace <Workspace> --table <TableName> --file sample_logs.json
- Mitigating Common Pitfalls – KQL Performance and Data Loss Risks
Poorly written KQL conditions can cause performance degradation or accidental data loss. Always follow these hardening guidelines:
- Avoid cross‑workspace queries (e.g.,
union workspace(“…”)) inside transformations – they are not supported and will fail silently. - Do not use external data operators like `externaldata()` or `evaluate` plugins – ingestion‑time transformations only support basic KQL operators (
where,project,extend,parse, etc.). - Test with a representative sample – a condition that drops 99% of logs might seem great until you realise you dropped all critical detections.
Recovery command (Azure CLI) – disable a broken transformation:
az monitor log-analytics workspace table update --resource-group <RG> --workspace-name <Workspace> --name <TableName> --plan Analytics --no-retention-transform false
If you accidentally delete all data, note that transformations are irreversible on already‑ingested logs. Always run in a staging workspace first.
5. Monitoring Transformation Health with Sentinel Workbooks
Microsoft provides a built‑in Transformation Health Workbook. Access it from Sentinel → Threat Management → Workbooks → “Data Transformation Health”. This dashboard shows:
– Dropped vs. kept events per table.
– Split ratios (Analytics vs. Data lake).
– Top KQL errors (syntax, timeouts, unsupported operators).
Custom KQL query to track transformation drops:
_LogOperation | where Operation == "Ingestion Transformation" | extend Details = parse_json(Detail) | where Details.FunctionName == "filter" or Details.FunctionName == "split" | project TimeGenerated, TableName = Details.Table, Operation = Details.FunctionName, Status, Message = Details.Message | order by TimeGenerated desc
What Undercode Say:
- Filter and split transformations are game‑changers for SIEM cost control – but they require disciplined KQL writing. A single mistake can hide attacker activity for days.
- The Data lake tier is not a security blind spot – you can still query it for compliance or retrospective hunting, but you lose real‑time alerting. Design splits based on risk tolerance, not just cost.
Prediction:
By Q4 2026, most mature Sentinel deployments will adopt aggressive filtering and splitting as standard, driving down average ingestion costs by 40‑60%. However, we predict a wave of “silent breaches” where defenders filter out attacker staging traffic (e.g., PowerShell downgrade attacks) because they misclassify it as “noise.” The next evolution will be AI‑assisted transformation testing – Microsoft will likely integrate automated KQL anomaly detection that flags over‑aggressive filter rules before they go live.
▶️ Related Video (76% Match):
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Markolauren Filter – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
