6.2 Analyze Query Results Using KQL

Analyze Query Results Using KQL

Introduction

In subsection 6.1, you learned to filter and shape data — narrowing millions of rows to the ones relevant to your investigation. But individual rows are only part of the picture. To understand an attack, you need to ask aggregate questions: “How many failed sign-ins came from each country?” “Which IP addresses generated the most traffic?” “What does the sign-in pattern look like over time?”

This subsection teaches you the summarize operator and its aggregation functions — the tools that transform raw log rows into answers. You will learn to count events, count unique values, group results by dimensions, create time-series data, and render visual charts. By the end, you will write a single query that answers “how many failed sign-ins per country per hour for the last 7 days” and displays the result as a time chart.

The operators covered in this subsection are:

• summarize — the aggregation engine that groups rows and computes values
• Aggregation functions — count(), dcount(), sum(), avg(), min(), max(), make_set(), make_list(), arg_max(), percentile()
• bin() — grouping timestamps into time windows for trend analysis
• render — generating visual charts from query results
• top — a shortcut for getting the N highest or lowest results

Each function is introduced with what it does and why you need it, then demonstrated with an investigation scenario and practiced in your lab.

The summarize operator — aggregating data for analysis

What it is

summarize groups rows by one or more columns and computes aggregate values for each group. It transforms a table of individual events into a table of summaries — replacing thousands of rows with a concise answer.

Why it matters

During an investigation, individual events tell you what happened. Summaries tell you the scope and pattern. “There are 47 failed sign-ins” is more actionable than scrolling through 47 individual rows. “The failures come from 3 countries, with 41 from Russia and 6 from the Netherlands” is even more actionable. summarize produces these answers.

How it works

The basic syntax is: | summarize AggregationFunction by GroupingColumn

Start with a simple count:

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize FailedSignins = count() by IPAddress
| order by FailedSignins desc

This says: “Group all failed sign-ins by IP address and count how many came from each IP. Sort highest count first.”

Multiple aggregation functions in one query

You can compute multiple aggregations simultaneously:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize
    FailCount = count(),
    TargetedUsers = dcount(UserPrincipalName),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetApps = make_set(AppDisplayName)
    by IPAddress
| order by FailCount desc

The aggregation functions reference

These are the functions you will use inside summarize. Each has a specific investigation purpose.

The bin() function — grouping by time windows

What it is

bin() rounds timestamp values to a specified interval, grouping events into time windows. When combined with summarize, it creates time-series data — the foundation of trend analysis and visualization.

Why it matters

“47 failed sign-ins” is useful. “47 failed sign-ins, with 40 occurring in a single 5-minute window” is much more useful — it tells you the attack was concentrated, not spread over days. bin() reveals the temporal pattern.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize FailCount = count() by bin(TimeGenerated, 1h)
| order by TimeGenerated asc

This groups all failures into 1-hour windows and counts how many occurred in each window.

The render operator — visualizing results

What it is

render generates a visual chart from query results. It transforms tabular data into bar charts, time series, pie charts, and other visualizations directly in the Sentinel Logs query editor.

Why it matters

Patterns that are invisible in a table of numbers become obvious in a chart. A time series showing a spike is instantly readable. A bar chart showing one country dominating failure counts tells a story that 20 rows of numbers do not.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize FailCount = count() by bin(TimeGenerated, 1h)
| render timechart

This produces a line chart with time on the x-axis and failure count on the y-axis. The attack spike becomes an unmistakable visual peak.

Visualization types:

1
2
3
4
5
6
7

// Bar chart: failures by country
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize FailCount = count() by Country
| render barchart

1
2
3
4
5
6

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| summarize SigninCount = count() by AppDisplayName
| order by SigninCount desc
| render barchart

The top operator — a shortcut for ranked results

top N by Column returns the N rows with the highest (or lowest) value in the specified column. It is a shortcut for | order by Column desc | take N.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize FailCount = count() by IPAddress
| top 5 by FailCount

Returns the 5 IPs with the most failures. Quick, clean, and used frequently in investigation for rapid prioritization.

The arg_max pattern — most recent record per entity

What it is

arg_max(TimeGenerated, *) within a summarize ... by EntityId returns the entire most recent row for each entity. This is the deduplication pattern — essential when a table contains multiple records per device, user, or IP and you want only the latest.

Why it matters

DeviceInfo contains a row for every device check-in (multiple per day). If you want the current status of each device, you need the most recent record, not all of them.

1
2
3
4
5

DeviceInfo
| where TimeGenerated > ago(7d)
| summarize arg_max(TimeGenerated, *) by DeviceId
| project DeviceName, OSPlatform, OnboardingStatus, SensorHealthState,
    LastSeen = TimeGenerated

This returns exactly one row per device — the most recent — with all columns from that row. You used this pattern in Module 7 (if following the non-standard build order) and will use it in every device investigation.

Investigation scenario: building a failed sign-in dashboard query

Combine everything from this subsection into a single comprehensive query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize
    FailCount = count(),
    UniqueUsers = dcount(UserPrincipalName),
    UniqueApps = dcount(AppDisplayName),
    FirstAttempt = min(TimeGenerated),
    LastAttempt = max(TimeGenerated),
    Countries = make_set(Country)
    by IPAddress
| where FailCount > 5
| order by FailCount desc