Reading width
Wide uses the full column for everything, text, diagrams, code, and exercises. Narrow keeps the standard reading width.
Text size
Scales the body text. Headings and code blocks keep their size.
In this section
KQL Foundations: make-series, Time Windows, prev() and row_cumsum()
Introduction
Time is the dimension every security question shares. How many per day, is this week unusual, which host departs from its own normal, how long between one event and the next: each is a question about periods or about order, and KQL has operators built for both. Each also has a trap.
A count by period leaves out the periods with nothing in them, and a comparison with the previous row means nothing until the rows have a defined order.
This lesson works with two ordinary programs from the lab's month: splwow64.exe, the helper Windows starts when a 32-bit application prints, and sudo on the Linux servers. It counts by day two ways and finds the days one of them left out, reads a whole series at once, compares this week with last, builds a series per host, measures the gaps between runs and keeps a running total.
The same runs counted by day two ways, and the zeros one of them never returns.
This is the ground Module 8 builds on. Every query on the page runs in your browser against the same month.
Counts That Miss the Zeros
summarize returns only periods with rowsThe obvious way to count by day is the one the fourth lesson used: summarize by bin(Timestamp, 1d). Here it is for the print helper, followed by a second summarize that describes the days it returned:
DeviceProcessEvents
| where FileName =~ 'splwow64.exe'
| summarize Runs = count() by Day = bin(Timestamp, 1d)
| summarize Days = count(), AvgPerDay = round(avg(Runs), 1), Fewest = min(Runs)
The first summarize makes one row per day, and the second counts those rows and averages them:
Days AvgPerDay Fewest
---- --------- ------
22 5.6 1
22 days, an average of 5.6 runs per day, and a fewest of 1. Nothing in that result is wrong, and it is still misleading.
The month the lab covers is longer than 22 days, and the fewest runs on any day was not 1: there were days with none. summarize groups the rows that exist, and a day on which the program never ran has no rows, so it has no group, so it has no row in the result.
The consequence spreads to everything computed from the result. The average excludes the empty days, so it describes days on which somebody printed rather than days in general. A minimum can never be zero.
A chart drawn from the rows joins the Friday before a weekend straight to the Monday after it, as if the weekend never happened. And a detection that looks for a day well above the average compares against an average that was inflated by leaving the quiet days out.
None of this produces an error or a warning. The result looks complete, its numbers are all real, and the only sign of the problem is a count of days that is smaller than the calendar says it should be. That is why the first check on any count over time is the number of periods it returned.
The fix is not to summarize differently. It is to use an operator whose job is to produce every period whether or not anything happened in it, which is what make-series does, and the rest of this lesson builds on it.
A Series Keeps Every Period
make-series with a defaultmake-series aggregates over a range of time like summarize does, but it produces one value for every step in the range, filling the steps with no rows with a default value. Its result is not a table of rows but a single row holding arrays: one for the time axis and one for each aggregate. series_stats_dynamic() then describes a whole array at once:
DeviceProcessEvents
| where FileName =~ 'splwow64.exe'
| make-series Runs = count() default = 0 on Timestamp
from datetime(2026-02-13) to datetime(2026-03-16) step 1d
| extend Stats = series_stats_dynamic(Runs)
| project Days = array_length(Runs), AvgPerDay = round(todouble(Stats.avg), 1),
Fewest = toint(Stats.min), Most = toint(Stats.max)
The series has one value per day from 13 February to 15 March, and the stats describe it:
Days AvgPerDay Fewest Most
---- --------- ------ ----
31 4 0 9
31 days, an average of 4 runs per day, a fewest of 0 and a most of 9. The difference from the first query is 9 days, every one of them a Saturday or a Sunday. The print helper is a working-day program, and its honest average per calendar day is 4, not 5.6.
The range was written explicitly, from 13 February to 16 March, and the end is not included: Microsoft documents the upper bound of make-series as non-inclusive, so the last day in the series is 15 March.
Writing the range out is the point. summarize can only report the periods the data happens to cover, while make-series reports the periods the question is about, and a query that names its range says exactly which periods those are.
The default is also a decision. default = 0 is right for counts, because a day with no runs had zero runs. For other aggregates it can be wrong: a day with no temperature reading did not have a temperature of zero.
Microsoft notes that the interpolation functions expect missing values as null, so a series meant to be filled in between readings should default to null instead. For counting events, which is most of security work, zero is the honest value.
A series is also cheaper to reason about than a table of days. Every function that works on series, from statistics to anomaly detection, takes the array as one value and returns one value, so a question about a whole month of days becomes one extend.
This Week Against Last
Two windows in one summarizeThe simplest time comparison needs no series at all. Two windows, each defined with ago(), counted with countif() in one summarize, give this week and last week side by side. Here it is for sudo on the Linux servers:
Syslog
| where ProcessName == 'sudo'
| summarize ThisWeek = countif(TimeGenerated > ago(7d)),
LastWeek = countif(TimeGenerated > ago(14d) and TimeGenerated <= ago(7d))
| extend Change = round(100.0 * (ThisWeek - LastWeek) / LastWeek, 1)
Each countif counts one window, and the extend turns the pair into a percentage change:
ThisWeek LastWeek Change
-------- -------- ------
83 79 5.1
83 uses of sudo in the last seven days and 79 in the seven before, a change of 5.1 percent. The windows are half-open, greater than the start and at most the end, so no event is counted in both, which is the same rule the second lesson used for days.
A single change between two weeks says little. Is 5.1 percent a lot for sudo on these servers? The only way to know is to compare it with how much the count usually moves from week to week, which needs more than two weeks.
That is why Module 8 builds week-against-week comparisons on top of a series: the series supplies the usual movement, and a change is only interesting when it is larger than that.
The two windows also have to be comparable. Both are seven days, and both end at the same time of day, so each contains the same mix of weekdays and weekend days. Comparing the last seven days with the last calendar week, or a week containing a holiday with one that does not, would measure the calendar again, as the fourth lesson's partial weeks did.
ago() makes these windows relative to now, which in the lab is fixed at noon on 15 March. In a real workspace, now moves, so a query run on Monday morning and on Friday afternoon compares different weeks. That is usually what is wanted from a recurring check, and it is worth remembering when two people compare results run at different times.
One Series per Entity
Every host against its own normalA count across a whole estate hides the entities in it. make-series accepts by, like summarize, and then produces one series per value: here, one series of daily sudo use for each Linux host. A second summarize describes the spread across hosts:
Syslog
| where ProcessName == 'sudo'
| make-series Uses = count() default = 0 on TimeGenerated
from datetime(2026-02-13) to datetime(2026-03-16) step 1d by Computer
| extend Stats = series_stats_dynamic(Uses)
| extend Avg = todouble(Stats.avg), Most = toint(Stats.max)
| summarize Hosts = count(), LowestAvg = round(min(Avg), 1),
HighestAvg = round(max(Avg), 1), BusiestDay = max(Most)
Each host gets its own month of daily counts, and the summary compares their averages:
Hosts LowestAvg HighestAvg BusiestDay
----- --------- ---------- ----------
8 1.1 1.6 4
8 hosts, with average daily sudo use between 1.1 and 1.6, and no host using it more than 4 times on any day. That is a tight, quiet baseline. A host that used sudo twenty times in a day would stand far outside its own history and every other host's, and a per-host series is how that would be seen.
The point of one series per entity is that normal differs between entities. A build server runs sudo constantly; a database server almost never. One threshold across the estate is either too low for the busy hosts or too high for the quiet ones.
A series per host lets each be compared with its own history, which is how most anomaly detection in KQL works, and Module 8's lesson on one series per entity builds it.
The by clause behaves as it does in summarize, with one difference worth knowing: make-series produces a series only for entities that appear in the data at least once in the range. A host that sent nothing all month has no series, not a series of zeros, so a missing host is still found by comparing against an inventory, as the fifth lesson did with a leftanti join.
The second summarize is the same two-step shape the fourth lesson used for downloads per user: the first step answers a question about each host, the second a question about hosts in general.
With series the first step is make-series and the per-host answer is a set of statistics, but the reasoning is identical, and so is the check: the number of series should match the number of hosts that sent any sudo message at all, counted directly with dcount(Computer).
The host names were summarized away here on purpose. Module 0 asks baseline questions of the month, and which host is busiest is a detail for the module that investigates it.
Order in a Table
sort, then prev()Questions about sequence need the rows in a defined order. prev() returns a column's value from the row before the current one, and next() from the row after, but only in a serialized row set, one whose order is fixed. sort by produces one. Here are the gaps between consecutive runs of the print helper across the estate:
DeviceProcessEvents
| where FileName =~ 'splwow64.exe'
| sort by Timestamp asc
| extend Gap = Timestamp - prev(Timestamp)
| summarize Runs = count(), MedianMinutes = round(percentile(Gap / 1m, 50), 0),
LongestHours = round(max(Gap) / 1h, 1)
The sort fixes the order, prev() reaches back one row, and the subtraction gives a timespan:
Runs MedianMinutes LongestHours
---- ------------- ------------
124 86 68.9
124 runs, with a median gap of 86 minutes between one and the next, and a longest gap of 68.9 hours. The longest gap is a weekend: the last print on a Friday to the first on a Monday. A gap measure finds what a count by day cannot, such as a burst of runs a few seconds apart, or a program that normally runs every hour and has stopped.
The first row has no previous row, and Microsoft documents that prev() returns null there unless a default is given. The median and maximum ignore it, so it does no harm here. In other queries it can: a first row whose gap is null may be dropped by a later filter, or counted as unusual by a test that does not expect a null.
Without the sort, prev() would be meaningless. KQL makes no promise about row order, as the third lesson showed with take, so the row before is whichever row the engine produced before, and the gap between two arbitrary rows is not a gap between events. Order is part of the question, and the query has to state it.
Per-entity sequences need one more step: the previous row must belong to the same entity. Sorting by the entity and then the time, and checking that the previous row's entity matches, is the standard pattern, and it is the core of Module 8's lesson on the row before and after.
Running Totals
row_cumsum and the previous periodrow_cumsum() adds each row's value to the total of the rows before it, which turns a count per period into a count so far. Like prev(), it needs a defined order. Here are the print helper's runs by week, with a running total and the previous week's count beside each:
DeviceProcessEvents
| where FileName =~ 'splwow64.exe'
| summarize Runs = count() by Week = startofweek(Timestamp)
| sort by Week asc
| extend RunningTotal = row_cumsum(Runs), PreviousWeek = prev(Runs)
| project Week = format_datetime(Week, 'yyyy-MM-dd'), Runs, PreviousWeek, RunningTotal
The weeks are sorted, then the running total and the previous week are added as columns:
Week Runs PreviousWeek RunningTotal
---------- ---- ------------ ------------
2026-02-08 5 5
2026-02-15 31 5 36
2026-02-22 33 31 69
2026-03-01 24 33 93
2026-03-08 31 24 124
The running total reaches 124, the month's total, which is the check that the weeks cover every run. The first week's previous value is empty, as prev() promises, and its count is small because the month starts on a Friday, the partial-week trap from the fourth lesson again.
The weekly counts then move within a narrow band, which is a baseline in the same sense as the sudo series: a print helper that suddenly ran three times as often in a week would be visible here before anyone looked at a single run.
Notice that this query used summarize by week rather than make-series. That is safe here only because every week in the range had runs; a week with none would have vanished from the result and from the running total's steps, exactly as the zero days did in the first section. For weeks of a busy program the shortcut holds, and for anything sparser the series is the safe form.
Running totals are how volumes are tracked against limits and how cumulative risk is shown: data transferred so far today, failed sign-ins so far this hour, alerts so far this shift. The total is only as meaningful as the order it was added in, and a running total computed on unsorted rows is a sum in a random order, which is not a running anything.
Weekly totals of a weekday-only program are also a check on the earlier sections. The weeks with a full five working days should carry similar counts, and they do; a week that fell well short would be the first place to look for a holiday, an outage or a sensor that stopped reporting, before reading anything into behavior.
The previous-period column turns the table into a comparison without a second query. Each row carries its own week and the week before it, so a change can be computed in the same extend, and the first row's empty value is the reminder that the first period in any window has nothing before it to compare with.
Questions About Time
Every period, or a defined orderEvery query on this page answered a question about time, and every one depended on one of two things being stated: which periods exist, or which order the rows are in. summarize by bin left the periods to the data and lost 9 days. make-series named the periods and kept them. prev() and row_cumsum() needed an order, and sort supplied it. Choosing the tool comes down to a short climb:
The first rung is the one most often skipped, because summarize by bin is familiar and its results look complete. The lower rungs are the ones most often wrong, because a missing sort produces numbers that look like gaps and totals without being either. Each mistake below happened on this page or would have with one line removed:
The ladder's rungs also combine. A per-host series answers which host departs from its normal, and a sorted table of that host's events with prev() then answers what happened around the departure. Most investigations of something unusual in time take both steps, first finding the period and the entity, then reading the order of events inside it.
The second row deserves emphasis because the engine will not catch it. Real Kusto refuses some window functions on rows that are not serialized, but an order produced by something other than the question, such as the order of an earlier operator, satisfies the engine and not the analyst.
What Module 8 builds
Module 8Module 8 builds on this lesson: counts that include the zeros, whole series, series functions, this week against last, a series per entity, order in a table, the row before and after, and running totals.
The habits to carry into it: count the periods a result returned, name the range with make-series, choose the default deliberately, compare windows of equal shape, give each entity its own baseline, and sort before any function that looks at another row.
Practice
Find the days summarize leaves out prepared evidenceThe prepared evidence below is the last two weeks of the print helper, counted by day with make-series, zeros included. Find the days with no runs, say how many rows a summarize by day would return for the same two weeks, and choose the average that describes a calendar day. Try each question before opening its answer, and say which column decided it.
Two weeks is long enough to contain two full weekends and short enough to read row by row. The weekday column is there so the pattern does not depend on knowing the calendar: the zeros line up with the same two weekdays each week, which is what a working-day program looks like.
The table is what summarize would never show you: every day in the range, including the ones on which nothing happened. Reading it beside the question of how many rows summarize would return is the clearest way to see the difference between the two operators.
Then run the queries on this page and change them. Replace splwow64.exe with powershell.exe in the series query and see whether PowerShell rests at weekends, then remove the sort from the gap query and watch the gaps lose their meaning.
A count by period needs every period, so use make-series with a named range and a deliberate default rather than summarize by bin. A question about sequence needs an order, so sort before prev(), next() or row_cumsum(). Compare windows of equal shape, give each entity its own baseline, and count the periods a result returned before reading its numbers.
The next lesson looks at relationships rather than rows: processes and their parents as a graph, and when a graph answers a question a join cannot.