KQL in SecOps (3) – Aggregation & Summarization

Welcome to Part 3 of our KQL in Security Operations Series.

In Part 1, we introduced KQL and explored its importance in SOC operations.

In Part 2, we covered filtering, projection, and sorting, which form the foundation of every KQL query.

We take it a step further by learning aggregation, summarization, and time-based analysis. These techniques allow SOC analysts to:

• Detect patterns over time

• Identify anomalies and outliers

• Build actionable alerts

• Generate SOC metrics for dashboards

By the end of this post, you’ll be able to summarize events, count occurrences, and analyze trends in Microsoft Sentinel.

Why Aggregation and Summarization Matter

In a SOC environment, analyzing individual events is not always enough. For example:

• Detecting brute-force attacks requires counting failed logins per account

• Monitoring endpoint activity involves identifying devices with abnormal processes

• Tracking cloud activity trends needs aggregating events over time

KQL provides powerful operators to summarize data, group events, and analyze trends, enabling analysts to move from reactive alert handling to proactive threat detection.

The summarize Operator

The summarize operator is used to aggregate rows based on one or more columns. It is essential for counting events, calculating averages, and finding distinct values.

Basic Syntax

TableName
| summarize AggregationFunction(Column) by GroupingColumn

• AggregationFunction: count(), sum(), avg(), min(), max(), dcount()

• GroupingColumn: Field by which you group the results

Example 1: Counting Failed Logins by User

SigninLogs
| where ResultType != 0
| summarize FailedAttempts = count() by UserPrincipalName
| top 10 by FailedAttempts desc

• count() counts failed logins per user

• top 10 by FailedAttempts desc identifies the 10 users with the most failed attempts

This is a classic brute-force detection query.

Example 2: Counting Failed Logins by Hour

Time-based aggregation helps analysts see patterns and spikes:

SigninLogs
| where ResultType != 0
| summarize FailedAttempts = count() by bin(TimeGenerated, 1h), UserPrincipalName
| sort by TimeGenerated desc

• bin(TimeGenerated, 1h) groups events into 1-hour intervals

• Analysts can detect periods with unusual login activity

Example 3: Distinct Count of Devices per User

SigninLogs
| summarize DeviceCount = dcount(DeviceDetail) by UserPrincipalName
| top 5 by DeviceCount desc

• dcount() counts unique devices per user

• Useful for detecting compromised accounts with multiple device logins

Time Binning with bin()

bin() is used to aggregate data into regular time intervals, which is essential for trend analysis.

Common Use Cases

• Hourly failed login counts

• Daily security alert volume

• Weekly endpoint process activity

Example: Daily Alert Trends

SecurityAlert
| summarize AlertCount = count() by bin(TimeGenerated, 1d), AlertSeverity
| sort by TimeGenerated desc

• 1d = daily bins

• Analysts can see alert trends over time, by severity

Visualizing Trends

Binned and summarized data is perfect for workbooks and dashboards:

SecurityEvent
| where EventID == 4688
| summarize ProcessCount = count() by bin(TimeGenerated, 1h), ProcessName
| sort by TimeGenerated asc

• Shows process creation trends over time

• Helps detect unusual spikes in PowerShell or other high-risk processes

Combining summarize with Filtering and Projection

Summarization is most effective when combined with filtering (where) and projection (project).

Example: Brute-Force Detection Over 24 Hours

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| project TimeGenerated, UserPrincipalName, IPAddress
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where FailedAttempts > 5
| sort by FailedAttempts desc

• Filters only failed logins in the last 24 hours

• Projects relevant columns

• Summarizes by user and hour

• Filters users exceeding threshold

• Sorts results for easy review

This query is ready to be converted into a Sentinel analytics rule.

Aggregation Across Multiple Fields

You can aggregate by multiple columns to gain deeper insights.

Example: Failed Logins by User and Device

SigninLogs
| where ResultType != 0
| summarize FailedAttempts = count() by UserPrincipalName, DeviceDetail
| top 10 by FailedAttempts desc

• Helps identify users and devices with unusual activity

• Useful for detecting compromised endpoints

Calculating Additional Metrics

KQL supports other aggregation functions for advanced metrics:

• avg() – average value

• min() / max() – earliest/latest timestamp or numeric values

• dcount() – distinct count

• sum() – sum of numeric fields

Example: Average Failed Logins per User per Hour

SigninLogs
| where ResultType != 0
| summarize AvgFailedLogins = avg(count()) by bin(TimeGenerated, 1h), UserPrincipalName

• Provides baseline behavior for anomaly detection

Real-World SOC Use Cases for Aggregation

1. Brute-Force Attack Detection - Summarize failed logins by user and time intervals to detect spikes.

2. Unusual Device Access - Count unique devices per user to detect suspicious logins.

3. Process Execution Monitoring - Track high-risk processes over time, e.g., PowerShell or wmic.exe.

4. Cloud Activity Trends - Aggregate sign-ins, role assignments, or audit events to detect anomalies.

5. Alert Volume Analysis - Summarize alerts by severity, source, and time for SOC performance reporting.

   
   

Tips for Effective Aggregation

1. Always filter before summarizing – reduces the volume of data processed.

2. Use bin() for time grouping – simplifies trend analysis.

3. Combine with top for actionable insights – focus on the most critical events.

4. Document your thresholds – e.g., failed login counts or unusual process counts.

5. Test incrementally – verify each aggregation step before using it in rules.

Looking Ahead: Part 4 Preview

We’ll dive into advanced string operations and pattern matching, enabling SOC analysts to:

• Detect malicious commands in PowerShell or other scripts

• Identify rare or suspicious login patterns

• Use regex and parsing functions for advanced threat hunting

  
  

By Part 4, you’ll be able to write queries that detect sophisticated attacks using textual analysis.

Conclusion

Summarization and time-based aggregation are essential skills for SOC analysts using Microsoft Sentinel. They enable you to:

• Detect patterns and anomalies over time

• Quantify and monitor high-risk behavior

• Build actionable dashboards and alerts

• Move from reactive to proactive security operations

  
  

You learned how to:

• Use summarize to aggregate events

• Apply bin() for time-based analysis

• Combine aggregation with filtering and projection

• Calculate key metrics for threat detection and SOC reporting

  
  

Mastering aggregation prepares you for Part 4, where you’ll analyze logs at a deeper level using string operations, parsing, and pattern matching.

✅ Key Takeaways for Part 3

1. summarize is the core operator for aggregation in KQL.

2. bin() allows grouping data by consistent time intervals.

3. Combining aggregation with filtering and projection creates efficient, actionable queries.

4. Time-based analysis is crucial for trend detection and anomaly identification.

5. These techniques form the foundation for alerting, threat hunting, and SOC metrics.