Agent Foskett Academy • Lesson 125 • Advanced KQL

Advanced KQL: Building Reusable KQL Functions.

The hunt worked perfectly.

So the analyst copied it into another query. Then another. Then another.

Six months later, the same logic existed in twenty different places.

Agent Foskett realised that professional KQL is not just about writing a query that works. It is about building trusted logic once, then reusing it everywhere.

Start Lesson 125 Back to Academy

Agent Foskett Academy lesson building reusable KQL functions for Microsoft Defender XDR

Lesson overview

Learn how to build reusable KQL functions that standardise threat hunting logic, simplify complex investigations and support scalable Microsoft Defender XDR workflows.

Create reusable KQL functions

Pass users, devices and time ranges as parameters

Standardise investigation logic across hunts

Build repeatable hunting libraries

Why reusable KQL functions matter

Reusable functions turn useful one-off hunts into repeatable investigation building blocks. They reduce copy-and-paste errors, make queries easier to maintain and help teams standardise the way they investigate Microsoft Defender XDR telemetry.

Reduce duplicationWrite the logic once instead of copying the same filters, joins and parsing steps into every new hunting query.

Standardise investigationsEvery analyst can call the same trusted function instead of creating slightly different versions of the same detection logic.

Simplify complex huntingHide complex logic inside named functions so the final investigation query is easier to read, review and maintain.

The reusable function mindset

Before writing another long query, ask whether part of the logic will be useful again. If it will, turn it into a function.

Think in building blocksIdentity filters, suspicious PowerShell logic, phishing indicators, timeline builders and device scoping can all become reusable components.

Use clear namesA function called SuspiciousPowerShell() or UserLogonTimeline() explains intent faster than a block of repeated KQL.

Pass parametersFunctions become far more useful when the analyst can pass in a user, device, IP address, file hash or timeframe.

Keep functions focusedEach function should do one useful job well. Small, focused functions are easier to test and reuse.

Build trusted librariesOver time, a SOC can build identity, endpoint, email, cloud and network function libraries.

Improve maintainabilityWhen detection logic changes, update the function once instead of manually editing dozens of copied queries.

Step 1 — Create your first reusable function

Start with a simple function that returns recent identity logons. The function can then be called like a table.

let RecentLogons = () {
    IdentityLogonEvents
    | where Timestamp > ago(7d)
    | project Timestamp,
              AccountUpn,
              ActionType,
              IPAddress,
              Location,
              DeviceName
};
RecentLogons()
| order by Timestamp desc

Step 2 — Add a user parameter

Parameters make the same function useful for different investigations without editing the function body.

let UserLogons = (User:string) {
    IdentityLogonEvents
    | where Timestamp > ago(14d)
    | where AccountUpn =~ User
    | project Timestamp,
              AccountUpn,
              ActionType,
              IPAddress,
              Location,
              DeviceName
};
UserLogons("user@contoso.com")
| order by Timestamp asc

Step 3 — Pass a timeframe into the function

Time windows should not be hardcoded when the function may be used for triage, incident response and long-range hunting.

let UserLogons = (User:string, Lookback:timespan) {
    IdentityLogonEvents
    | where Timestamp > ago(Lookback)
    | where AccountUpn =~ User
    | project Timestamp,
              AccountUpn,
              ActionType,
              IPAddress,
              Location,
              DeviceName
};
UserLogons("user@contoso.com", 30d)
| order by Timestamp asc

Step 4 — Build a reusable high-risk logon filter

Reusable filters help analysts apply the same risk logic consistently across different hunts.

let HighRiskLogons = (Lookback:timespan) {
    IdentityLogonEvents
    | where Timestamp > ago(Lookback)
    | where ActionType has_any ("LogonFailed", "LogonSuccess")
    | where FailureReason has_any ("MFA", "Conditional Access", "Risk")
        or Location !in ("Australia", "New Zealand")
    | project Timestamp,
              AccountUpn,
              ActionType,
              IPAddress,
              Location,
              FailureReason
};
HighRiskLogons(14d)
| summarize Events = count(), FirstSeen = min(Timestamp), LastSeen = max(Timestamp) by AccountUpn, Location
| order by Events desc

Step 5 — Build a reusable suspicious PowerShell function

Endpoint hunting often repeats the same PowerShell indicators. Put those indicators in one reusable function.

let SuspiciousPowerShell = (Lookback:timespan) {
    DeviceProcessEvents
    | where Timestamp > ago(Lookback)
    | where FileName in~ ("powershell.exe", "pwsh.exe")
    | where ProcessCommandLine has_any ("-enc", "-encodedcommand", "downloadstring", "iex", "bypass", "hidden")
    | project Timestamp,
              DeviceName,
              InitiatingProcessAccountName,
              FileName,
              ProcessCommandLine,
              InitiatingProcessFileName
};
SuspiciousPowerShell(7d)
| order by Timestamp desc

Step 6 — Build a reusable network connection function

Functions can also standardise network triage by returning outbound connections for a device or process.

let DeviceOutboundConnections = (Device:string, Lookback:timespan) {
    DeviceNetworkEvents
    | where Timestamp > ago(Lookback)
    | where DeviceName =~ Device
    | where isnotempty(RemoteUrl) or isnotempty(RemoteIP)
    | project Timestamp,
              DeviceName,
              InitiatingProcessFileName,
              RemoteUrl,
              RemoteIP,
              RemotePort,
              Protocol
};
DeviceOutboundConnections("device01.contoso.com", 24h)
| order by Timestamp desc

Step 7 — Build a reusable email investigation function

Email investigations become easier when common sender, recipient and subject pivots are wrapped into a function.

let RecentEmailFromSender = (Sender:string, Lookback:timespan) {
    EmailEvents
    | where Timestamp > ago(Lookback)
    | where SenderFromAddress =~ Sender or SenderMailFromAddress =~ Sender
    | project Timestamp,
              SenderFromAddress,
              SenderMailFromAddress,
              RecipientEmailAddress,
              Subject,
              DeliveryAction,
              ThreatTypes,
              NetworkMessageId
};
RecentEmailFromSender("sender@example.com", 7d)
| order by Timestamp desc

Step 8 — Build a reusable user timeline function

A timeline function can normalise identity and cloud evidence into one investigation view.

let UserActivityTimeline = (User:string, Lookback:timespan) {
    let IdentityActivity =
        IdentityLogonEvents
        | where Timestamp > ago(Lookback)
        | where AccountUpn =~ User
        | project Timestamp,
                  Source = "IdentityLogonEvents",
                  Account = AccountUpn,
                  Activity = ActionType,
                  Detail = strcat(IPAddress, " ", Location);
    let CloudActivity =
        CloudAppEvents
        | where Timestamp > ago(Lookback)
        | where AccountDisplayName =~ User
        | project Timestamp,
                  Source = "CloudAppEvents",
                  Account = AccountDisplayName,
                  Activity = ActionType,
                  Detail = strcat(Application, " ", ObjectName);
    union IdentityActivity, CloudActivity
};
UserActivityTimeline("user@contoso.com", 14d)
| order by Timestamp asc

Step 9 — Call one function from another

Advanced reusable KQL often combines smaller trusted functions into a higher-level investigation workflow.

let SuspiciousPowerShell = (Lookback:timespan) {
    DeviceProcessEvents
    | where Timestamp > ago(Lookback)
    | where FileName in~ ("powershell.exe", "pwsh.exe")
    | where ProcessCommandLine has_any ("-enc", "downloadstring", "bypass", "hidden")
    | project Timestamp, DeviceName, Account = InitiatingProcessAccountName, ProcessCommandLine
};
let PowerShellByUser = (User:string, Lookback:timespan) {
    SuspiciousPowerShell(Lookback)
    | where Account =~ User
};
PowerShellByUser("alice", 7d)
| order by Timestamp desc

Step 10 — Build a reusable investigation summary

Functions can return summarised results for dashboards, hunting packs and workbooks.

let SuspiciousPowerShellSummary = (Lookback:timespan) {
    DeviceProcessEvents
    | where Timestamp > ago(Lookback)
    | where FileName in~ ("powershell.exe", "pwsh.exe")
    | where ProcessCommandLine has_any ("-enc", "downloadstring", "bypass", "hidden")
    | summarize Events = count(),
                Devices = dcount(DeviceName),
                Users = dcount(InitiatingProcessAccountName),
                FirstSeen = min(Timestamp),
                LastSeen = max(Timestamp)
          by FileName
};
SuspiciousPowerShellSummary(30d)
| order by Events desc

Function design checklist

A reusable function should be easy to understand, easy to call and safe to reuse in large Defender XDR environments.

Can this logic be reused?If the same filter, join, parser or timeline appears in multiple hunts, it is a good function candidate.

Are the inputs clear?Use meaningful parameters such as User, Device, Sender, RemoteIP, FileHash and Lookback.

Is the output predictable?Return consistent column names so the function can be joined, unioned or summarised by other queries.

Is the function focused?Avoid building one giant function that tries to solve every investigation problem at once.

Will another analyst understand it?Clear names and clean output are more valuable than clever but confusing query logic.

Can it scale?Apply the Lesson 124 performance mindset: filter early, project only useful columns and avoid unnecessary expensive operations.

Common reusable KQL function libraries

Mature security teams often organise reusable KQL into libraries that match common investigation domains.

Identity libraryFunctions for risky sign-ins, MFA failures, impossible travel, privileged role activity and user timelines.

Endpoint libraryFunctions for suspicious processes, persistence, LOLBins, file activity, registry changes and device timelines.

Email libraryFunctions for sender pivots, phishing subjects, malicious URLs, attachments, delivery actions and recipient scoping.

Cloud application libraryFunctions for OAuth consent, app activity, file sharing, cloud downloads and suspicious SaaS access.

Network libraryFunctions for remote connections, rare domains, suspicious ports, process-to-network pivots and external IP scoping.

Executive reporting libraryFunctions that summarise hunting output for workbooks, dashboards and management reporting.

Related Agent Foskett Academy lessons

These lessons support reusable KQL function design and enterprise-grade hunting workflows.

Lesson 121 — Advanced KQL: Mastering Join OperationsUse joins inside functions to enrich identity, device, email and cloud evidence.

Lesson 122 — Advanced KQL: Working with Dynamic DataReusable functions often normalise dynamic fields before the wider hunt uses them.

Lesson 123 — Advanced KQL: Mastering mv-expandTurn controlled mv-expand logic into reusable functions for arrays and multi-value fields.

Lesson 124 — Advanced KQL: Optimising Query PerformanceReusable functions should still be fast, targeted and scalable across large Defender XDR tenants.

Lesson 126 — Advanced KQL: Advanced parse_json()The next lesson explores deeper JSON parsing patterns that can be wrapped into reusable functions.

Lesson 129 — Building Enterprise Hunting QueriesReusable functions become the foundation for enterprise-grade threat hunting logic.

Coming next

The Advanced KQL series continues by going deeper into complex JSON and dynamic telemetry parsing.

Lesson 126 — Advanced KQL: Advanced parse_json()Next, Agent Foskett explores advanced parse_json() techniques for nested Microsoft Defender XDR telemetry, dynamic arrays, evidence fields and complex investigation data.

Why this mattersReusable functions are even more powerful when they can hide complex parsing logic behind a clean, trusted function call.

Final thought

Professional KQL is not measured by how many lines you write. It is measured by how many times you do not have to write them again.

Agent Foskett mindsetDo not copy the same investigation logic forever. Build it once, name it clearly, test it properly and reuse it everywhere.

Advanced KQL SeriesLesson 125 moves from writing individual queries to building reusable hunting components for Microsoft Defender XDR.

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

Advanced KQL Building Reusable KQL Functions in Microsoft Defender XDR

Agent Foskett Academy Lesson 125 teaches defenders how to build reusable KQL functions for Microsoft Defender XDR, parameterise hunting logic and create scalable investigation libraries.

Learn reusable KQL functions for Microsoft security investigations

Reusable KQL functions help security analysts reduce duplication, standardise investigations, simplify complex hunting queries and improve maintainability across Defender XDR and Microsoft Sentinel environments.

Microsoft Defender XDR KQL function tutorial

This lesson explains how to create KQL functions, pass parameters, build reusable filters, create timeline functions and design enterprise hunting libraries for identity, endpoint, email, cloud and network investigations.