Kusto KQL - Part 3B - Sort, Summarize, Count & Distinct

Summarize and Count

Simple Sorts:

| sort by fieldname asc
//or
| sort by fieldname desc

Simple Summarize:

| summarize by fieldname desc

Adding summarize and then counting"

| summarize count () by fieldname

Different example:

| summarize count() by columname1, columnname2

| sort by count_

Other option for summarize and count and then filtering the output to only counts over certain limits.

| summarize EventCount = count() by fieldname
| where EventCount > 1000

To see the 1st value of something the summarize by arg_min or arg_max can be useful. This can be useful with the TimeGenerated field to determine the 1st time something is seen.

| summarize arg_min(TimeGenerated, AccountType) by account

| summarize arg_max(TimeGenerated, AccountType) by account

Summarizing based on a condition

| summarize count_if(condition)

Summarizing and placing in buckets using the 'bin' operator. The bin function rounds values down to the nearest integer multiple of a specified bin size, making it a useful tool when combined with summarize by. It groups scattered values into a more concise set of defined values, simplifying data analysis.: https://learn.microsoft.com/en-us/kusto/query/bin-function?view=microsoft-fabric

| summarize count() by bin(TimeGenerated, 1d)

Distinct

Within your query time period there could be thousands of logs with the same field value that your interested (i.e., username, computername, IP address, etc). Using distinct will just display all unique values for the field in question. Combined with count it also can count the number of field values for each unique value.

| summarize count() by computername
| distinct computer, _count

Ordering data.

The ‘asc’ or 'desc' in the query in the Order Data step is what produces this ordering. If we wanted descending order, we’d use ‘desc’.

KQL sort operators:

https://learn.microsoft.com/en-us/kusto/query/sort-operator?view=azure-data-explorer&preserve-view=true