SPL to KQL: Top Blocked Source IPs

By NetMon Hub Editorial · 8 July 2026

Splunk spl → Other kql fidelity: close

Intent: Rank source IPs by blocked-connection count.

Same intent; minor behavioural differences.

What this conversion covers

The source query finds the most frequent blocked source IPs in firewall or network-device logs ingested into Splunk — a standard triage starting point when you need to know who’s hammering your perimeter. This page shows the equivalent in Kusto Query Language (KQL) for Microsoft Sentinel or Azure Monitor Log Analytics, explains where the two diverge, and tells you what to watch for before you treat the output as equivalent.

Validated against:

• Splunk SPL search syntax (stable across the Splunk Enterprise 9.x and 10.x lines; 10.x current as of July 2026)
• Kusto Query Language as documented in the Microsoft Learn Kusto reference
• Microsoft Sentinel CommonSecurityLog and AzureFirewallNetworkRule tables

Source: SPL

index=firewall action=blocked
| stats count AS blocked_count BY src_ip
| sort -blocked_count
| head 20

What it does, line by line:

stats in SPL is a streaming aggregation: it processes events in chunks and produces a result table. The field names (src_ip, action) are whatever the field extractions in your Splunk app produce — there is no enforced schema.

Target: KQL

The table and field names below assume logs are landing in CommonSecurityLog, which is the standard CEF-over-Syslog sink used by most firewall vendors (Palo Alto, Check Point, Fortinet, Cisco ASA, etc.) when forwarding to Sentinel. If your environment uses AzureFirewallNetworkRule or a custom table, swap the table name and adjust field references accordingly — see the alternative table variant below.

CommonSecurityLog
| where DeviceAction =~ "Deny"
| summarize BlockedCount = count() by SourceIP
| sort by BlockedCount desc
| take 20

What it does, line by line:

Alternative: Azure Firewall native table

If you are using the native Azure Firewall diagnostic logs rather than CEF forwarding:

AzureFirewallNetworkRule
| where Action =~ "Deny"
| summarize BlockedCount = count() by SourceIp
| sort by BlockedCount desc
| take 20

Note the field is SourceIp (capital I, lowercase p) in AzureFirewallNetworkRule, not SourceIP. Kusto is case-sensitive for column names.

Adding a time window (recommended)

Neither version above constrains by time — they scan the full retention window, which can be expensive and slow on busy workspaces. Scope it:

CommonSecurityLog
| where TimeGenerated >= ago(24h)
| where DeviceAction =~ "Deny"
| summarize BlockedCount = count() by SourceIP
| sort by BlockedCount desc
| take 20

In SPL, the time window is usually set via the time-range picker or earliest/latest in the search string. In KQL you make it explicit in the query — treat this as good hygiene rather than an optional add-on.

Fidelity: close

This conversion is rated close, not exact. Here is why.

stats vs summarize — same idea, different execution model

Both aggregate events into a grouped count, and for this particular query the output shape is identical: a table of (source_ip, count) pairs sorted descending. The operational difference is that summarize in KQL is a blocking operator — it waits for all upstream rows before emitting results — whereas Splunk’s stats can be parallelised across indexers and streamed. For query authoring purposes this does not matter; for very large result sets it affects how the engine schedules work, not what comes back.

Field-name mapping is not guaranteed

SPL field names (src_ip, action) come from your Splunk field extractions, which vary by app and sourcetype. KQL column names (SourceIP, DeviceAction) come from the CEF schema as normalised by the Log Analytics agent or the Sentinel data connector. If your Splunk app used a non-standard extraction (e.g. src_addr instead of src_ip), the SPL query itself differs from what is shown here — this KQL translation targets the canonical CEF mapping.

Action string caveat: This is the most common source of broken results. Palo Alto sends "deny", Fortinet sends "blocked", Cisco ASA sends "denied" — all landing in DeviceAction. Before going live, run this to see what your environment actually produces:

CommonSecurityLog
| where TimeGenerated >= ago(7d)
| summarize count() by DeviceAction
| sort by count_ desc

Then adjust the where DeviceAction =~ "Deny" filter to match, or use in~:

| where DeviceAction in~ ("deny", "block", "blocked", "denied", "drop")

head vs take

These are direct equivalents for this use case — both return at most N rows from the top of the result set after sorting. No fidelity gap here.

Time semantics

Splunk searches run over a defined time range set outside the query (or via earliest/latest). KQL queries run over whatever the table contains unless you add a where TimeGenerated clause. An unbounded KQL query on a busy workspace will be both slow and potentially misleading — you are comparing an implicitly time-bounded SPL search against an unbounded scan. Always add the time filter in KQL.

Caveats summary

Note: Before using this query in production:

1. Confirm which table your firewall logs land in (CommonSecurityLog, AzureFirewallNetworkRule, or a custom table).
2. Check DeviceAction values in your workspace — they are vendor-specific.
3. Add a TimeGenerated filter. An unbounded scan is expensive and misleading.
4. If your Splunk extractions used non-standard field names, your actual SPL may differ from the source shown here.

Sources

• Kusto summarize operator — Microsoft Learn
• Kusto sort operator — Microsoft Learn
• Kusto take operator — Microsoft Learn
• CommonSecurityLog table schema — Microsoft Learn
• AzureFirewallNetworkRule table schema — Microsoft Learn
• Splunk stats command reference — Splunk Docs
• CEF field mapping for Microsoft Sentinel — Microsoft Learn