Agent Foskett Academy • Lesson 128 • Advanced KQL

Advanced KQL: Advanced Time Series Analysis.

The event looked harmless on its own.

One failed sign-in. One PowerShell command. One outbound connection. One file copied to cloud storage.

But when Agent Foskett placed the events on a timeline, the pattern changed completely.

The compromise was not hiding in a single row. It was hiding in the rhythm of the activity over time.

Start Lesson 128 Back to Academy

Agent Foskett Academy lesson using advanced KQL time series analysis in Microsoft Defender XDR

Lesson overview

Learn how to analyse Microsoft Defender XDR telemetry over time using bin(), summarize, make-series, anomaly detection and timecharts.

Group security activity into time buckets

Build trends and baselines

Detect spikes, anomalies and quiet periods

Visualise behaviour across Defender XDR telemetry

Why time series analysis matters

Many security investigations only make sense when events are viewed over time. A single log entry can look normal, but repeated activity, sudden spikes, regular intervals or changing patterns can reveal compromise.

Single events can misleadOne failed sign-in may be noise. Hundreds of failures in a short window may be a password spray or brute-force attempt.

Patterns reveal behaviourBeaconing, data exfiltration, scripted activity and ransomware staging often appear as repeated actions over time.

Baselines expose anomaliesUnderstanding normal activity helps analysts spot sudden deviations that deserve investigation.

The time series mindset

Instead of asking whether one event is suspicious, ask whether the activity pattern is normal for that user, device, process or tenant.

Start with the questionAre we looking for spikes, trends, gaps, periodic activity, first-seen behaviour or unusual volume?

Choose the right time bucketMinutes may help with brute force and ransomware. Hours or days may help with long-running exfiltration and cloud activity.

Group by the right entityTime analysis becomes more useful when grouped by AccountUpn, DeviceName, RemoteUrl, SenderFromAddress or ProcessCommandLine.

Compare against normalA sudden jump in activity matters more when you understand the usual baseline.

Visualise when usefulTimecharts can quickly reveal spikes and quiet periods that are difficult to see in raw rows.

Keep performance in mindLarge time windows and high-cardinality groupings can become expensive. Filter early and project only useful columns.

Step 1 — Build simple time buckets with bin()

The bin() function groups timestamps into regular intervals, making it easier to count activity over time.

let TimeFrame = 7d;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| summarize Events = count()
      by TimeBucket = bin(Timestamp, 1h)
| order by TimeBucket asc

Step 2 — Trend failed sign-ins over time

Failed sign-in spikes can indicate password spray, brute force, misconfigured applications or attacker testing.

let TimeFrame = 14d;
IdentityLogonEvents
| where Timestamp > ago(TimeFrame)
| where ActionType =~ "LogonFailed"
| summarize FailedLogons = count(),
            Users = dcount(AccountUpn),
            IPAddresses = dcount(IPAddress)
      by TimeBucket = bin(Timestamp, 1h)
| order by TimeBucket asc

Step 3 — Compare users across time

Adding an entity dimension shows whether the spike affects one user, many users or a small targeted group.

let TimeFrame = 7d;
IdentityLogonEvents
| where Timestamp > ago(TimeFrame)
| where ActionType =~ "LogonFailed"
| summarize FailedLogons = count()
      by AccountUpn,
         TimeBucket = bin(Timestamp, 1h)
| where FailedLogons > 5
| order by TimeBucket asc, FailedLogons desc

Step 4 — Visualise endpoint activity with render timechart

Timecharts help analysts quickly see bursts of process execution, unusual timing or repeated automation.

let TimeFrame = 3d;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "cscript.exe", "mshta.exe")
| summarize Events = count()
      by TimeBucket = bin(Timestamp, 30m),
         FileName
| render timechart

Step 5 — Use make-series for regular time series output

make-series creates a regular series across a time range. This is useful when you need consistent time intervals for baselining or anomaly detection.

let StartTime = ago(7d);
let EndTime = now();
IdentityLogonEvents
| where Timestamp between (StartTime .. EndTime)
| where ActionType =~ "LogonFailed"
| make-series FailedLogons = count()
      default = 0
      on Timestamp
      from StartTime to EndTime step 1h

Step 6 — Detect anomalies with series_decompose_anomalies()

Anomaly detection helps identify unusual spikes compared with the surrounding pattern, especially when raw counts are difficult to interpret manually.

let StartTime = ago(14d);
let EndTime = now();
IdentityLogonEvents
| where Timestamp between (StartTime .. EndTime)
| where ActionType =~ "LogonFailed"
| make-series FailedLogons = count()
      default = 0
      on Timestamp
      from StartTime to EndTime step 1h
| extend Anomalies = series_decompose_anomalies(FailedLogons)
| render anomalychart

Step 7 — Hunt regular beaconing behaviour

Command and control traffic often appears as repeated network connections at regular intervals. Time buckets can make this rhythm visible.

let TimeFrame = 24h;
DeviceNetworkEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(RemoteUrl) or isnotempty(RemoteIP)
| summarize Connections = count(),
            FirstSeen = min(Timestamp),
            LastSeen = max(Timestamp)
      by DeviceName,
         RemoteUrl,
         RemoteIP,
         TimeBucket = bin(Timestamp, 10m)
| where Connections > 2
| order by DeviceName asc, RemoteUrl asc, TimeBucket asc

Step 8 — Identify data movement over time

Large cloud uploads, downloads or file access bursts can indicate exfiltration, staging or unusual user behaviour.

let TimeFrame = 7d;
CloudAppEvents
| where Timestamp > ago(TimeFrame)
| where ActionType has_any ("FileDownloaded", "FileUploaded", "FileAccessed", "FileShared")
| summarize CloudActions = count(),
            Objects = dcount(ObjectName),
            Applications = make_set(Application, 10)
      by AccountDisplayName,
         TimeBucket = bin(Timestamp, 1h)
| where CloudActions > 20
| order by TimeBucket asc, CloudActions desc

Step 9 — Build a ransomware activity timeline

Ransomware investigations often depend on understanding when process execution, file changes and network activity accelerated.

let TimeFrame = 48h;
let SuspiciousProcesses =
    DeviceProcessEvents
    | where Timestamp > ago(TimeFrame)
    | where FileName has_any ("powershell", "cmd", "vssadmin", "wbadmin", "bcdedit")
    | project Timestamp, DeviceName, Source = "Process", Detail = FileName;
let FileActivity =
    DeviceFileEvents
    | where Timestamp > ago(TimeFrame)
    | where ActionType has_any ("FileCreated", "FileModified", "FileRenamed", "FileDeleted")
    | project Timestamp, DeviceName, Source = "File", Detail = ActionType;
union SuspiciousProcesses, FileActivity
| summarize Events = count()
      by DeviceName,
         Source,
         TimeBucket = bin(Timestamp, 15m)
| order by TimeBucket asc

Step 10 — Compare current activity with a baseline

A simple baseline compares recent behaviour against an earlier period. This helps highlight activity that is unusual for a user or device.

let BaselineStart = ago(14d);
let BaselineEnd = ago(2d);
let CurrentStart = ago(2d);
let Baseline =
    DeviceProcessEvents
    | where Timestamp between (BaselineStart .. BaselineEnd)
    | where FileName in~ ("powershell.exe", "cmd.exe", "mshta.exe")
    | summarize BaselineEvents = count() by DeviceName, FileName;
let Current =
    DeviceProcessEvents
    | where Timestamp > CurrentStart
    | where FileName in~ ("powershell.exe", "cmd.exe", "mshta.exe")
    | summarize CurrentEvents = count() by DeviceName, FileName;
Current
| join kind=leftouter Baseline on DeviceName, FileName
| extend BaselineEvents = coalesce(BaselineEvents, 0)
| extend Increase = CurrentEvents - BaselineEvents
| where Increase > 20
| order by Increase desc

Time series investigation patterns

Different threat behaviours leave different time-based shapes. Learning to recognise those shapes helps analysts choose the right query strategy.

SpikeA sudden burst of activity can indicate password spraying, malware execution, mass downloads or attacker automation.

Slow burnLow but persistent activity over days may indicate stealthy reconnaissance, data collection or long-running cloud access.

Regular intervalRepeated activity every few minutes can suggest beaconing, scheduled tasks, scripted polling or command and control.

Quiet gapA sudden absence of expected activity may matter when a security tool stops reporting or a device goes silent.

First seenA process, domain, IP address or user behaviour appearing for the first time can be highly valuable during hunting.

Baseline deviationThe most important clue is often not the highest number, but the activity that is unusual for that entity.

Common time series mistakes

Time analysis is powerful, but poor bucket choices and noisy grouping can hide the behaviour you are trying to reveal.

Using buckets that are too largeDaily buckets may hide short attacks such as brute force bursts, ransomware staging or rapid file encryption.

Using buckets that are too smallOne-minute buckets across long timeframes can create noisy output and expensive queries.

Grouping by too many columnsHigh-cardinality grouping can fragment the pattern and make the result difficult to read.

Ignoring timezone contextBusiness hours, overnight activity and regional behaviour matter during investigation.

Forgetting normal behaviourA spike may be normal for a backup server, deployment system or scheduled automation platform.

Rendering before filteringVisualisations become useful only after the query has been narrowed to relevant events.

Investigation checklist

Use this checklist before closing a time-based Microsoft Defender XDR investigation.

What behaviour am I measuring?Define whether you are measuring logons, processes, network connections, file activity or cloud actions.

What time bucket makes sense?Choose minutes, hours or days based on the speed of the behaviour you are investigating.

What entity should I group by?User, device, IP, process, sender, URL and application all answer different investigation questions.

Is there a baseline?Compare recent activity with older activity to understand what changed.

Is the pattern visual?Use render timechart or anomalychart when a visual view will make the behaviour clearer.

Could this be legitimate?Scheduled jobs, backups, updates and business workflows can create time-based patterns that look suspicious.

Related Agent Foskett Academy lessons

These lessons support advanced KQL time series analysis and behaviour-based threat hunting.

Lesson 90 — Finding Time Patterns with bin()Review the foundation of grouping Defender XDR events into useful time buckets.

Lesson 121 — Advanced KQL: Mastering Join OperationsCombine time-based evidence from multiple Defender XDR tables.

Lesson 124 — Advanced KQL: Optimising Query PerformanceKeep large time-window hunts efficient and scalable.

Lesson 125 — Advanced KQL: Building Reusable KQL FunctionsTurn repeatable time series analysis into reusable investigation functions.

Lesson 127 — Advanced KQL: Advanced Regular ExpressionsUse regex to identify suspicious strings before analysing their behaviour over time.

Lesson 129 — Building Enterprise Hunting QueriesMove from individual time-based hunts to enterprise-scale detection logic.

Coming next

The Advanced KQL series continues by bringing advanced techniques together into enterprise-grade hunting queries.

Lesson 129 — Advanced KQL: Building Enterprise Hunting QueriesNext, Agent Foskett combines joins, dynamic data, reusable functions, regex, performance tuning and time analysis into complete enterprise hunting logic.

Why this mattersTime series analysis shows behaviour across time. Enterprise hunting queries bring that behaviour together with enrichment, scoring, tuning and repeatable investigation workflows.

Final thought

Some attacks do not reveal themselves in one row. They reveal themselves in rhythm, repetition and change.

Agent Foskett mindsetDo not only ask what happened. Ask when it happened, how often it happened and whether that timing makes sense.

Advanced KQL SeriesLesson 128 moves from pattern matching inside text to pattern recognition across time.

Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Advanced KQL Advanced Time Series Analysis in Microsoft Defender XDR

Agent Foskett Academy Lesson 128 teaches defenders how to use advanced time series analysis in KQL for Microsoft Defender XDR investigations, including bin(), summarize, make-series, anomaly detection and timechart visualisation.

Learn KQL time series analysis for Microsoft security investigations

Time series analysis helps security analysts detect spikes, trends, anomalies, regular intervals, beaconing behaviour, failed sign-in bursts, ransomware timelines and data exfiltration patterns across Defender XDR telemetry.

Microsoft Defender XDR KQL timechart and anomaly detection tutorial

This lesson explains how to group events into time buckets, compare current activity with baselines, visualise process and identity behaviour, and investigate suspicious activity over time using Microsoft Defender XDR advanced hunting.