❕This article has not been completed yet. However, it may already contain helpful Information and therefore it has been published at this stage

KQL OPERATOR: Summarize

Description:

The summarize operator in Kusto Query Language (KQL) is used to aggregate data by one or more columns (see all supported aggregation function types) . It takes in a table of data and outputs a new table that is aggregated based on the specified columns.

Syntax

T | summarize [ SummarizeParameters ] [[Column =] Aggregation [, ...]] [by [Column =] GroupExpression [, ...]]

Use-Cases (leveraging the DeviceNetworkEvents Table):

#1 Basic usage:

This command 👇 returns the count of records for each Actiontypevalue in the DeviceNetworkEvents table (for the last 24h + Show limit: 1000 results)

DeviceNetworkEvents
| summarize count() by ActionType

#2 Multiple Aggregations:

This query 👇 provides a count of network events for each device and action type, sorted by the device name (for the last 24h + Show limit: 1000 results).

DeviceNetworkEvents
| project DeviceName, ActionType
| summarize count()by DeviceName, ActionType
| sort by DeviceName asc

#3 Conditional Aggregation:

This KQL 👇 filters network events from the past 30 days to include only those with a non-empty initiating process parent file name, identifies the most recent event for each device, and then counts how many of these events were initiated by a parent process named python3.6.

DeviceNetworkEvents
| where TimeGenerated > ago (30d)
        and isnotempty(InitiatingProcessParentFileName)
| summarize arg_max(TimeGenerated, *) by DeviceName
| summarize Count = countif(InitiatingProcessParentFileName == 'python3.6')

#4 Grouping

This KQL 👇 filters network events from the past week (see my blog post about the where operator), identifies the most recent action type for each device, and then counts how many times each action type occurred

DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| summarize arg_max(TimeGenerated,ActionType) by DeviceName
| summarize count() by ActionType

#5 Calculate percentage based on two columns

This KQL 👇 analyzes the connection actions of devices by summarizing the total number of connection attempts, the number of failed and successful connections, and the percentage of failed connections for each device. It then sorts the results by the number of failed connections.

DeviceNetworkEvents
| summarize 
    TotalActions = countif(ActionType == 'ConnectionFailed' or ActionType == 'ConnectionSuccess'),
    ConnectionFailed = countif(ActionType == 'ConnectionFailed'),
    ConnectionSuccess = countif(ActionType == 'ConnectionSuccess')
    by DeviceName
| extend PercentFailed = 
    round((todouble(ConnectionFailed) / TotalActions * 100), 2)
| sort by ConnectionFailed

#5 Visualization

This KQL 👇 is used to analyze device network events, specifically focusing on connection actions, and then visualize the results using a bar chart.

DeviceNetworkEvents
| summarize 
    ConnectionFailed = countif(ActionType == 'ConnectionFailed'),
    ConnectionSuccess = countif(ActionType == 'ConnectionSuccess')
    by DeviceName
| sort by ConnectionFailed
| render barchart

WHEN TO USE IT:

to calculate aggregate values such as counts, sums, averages, minimums, or maximums across different columns.
to group data by specific columns and perform calculations on each group.
to reorganize data by converting rows into columns to highlight key patterns and relationships.
to filter and sort aggregated data to focus on specific insights.
to optimize query performance by reducing the amount of data processed.

THINGS TO KEEP IN MIND

the summarize operator supports various aggregation functions such as count(), sum(), avg(), min(), max(), arg_min(), arg_max(), make_list(), and make_set(). Choose the appropriate function based on the type of analysis you need.