6.3 let Statements and User-Defined Functions

Module 4 introduced let for named intermediate results and materialize for caching. This subsection covers advanced let patterns and user-defined functions that make complex queries reusable and maintainable.

Tabular let statements

A let statement can define a tabular expression (a query that returns a table) or a scalar expression (a single value):

// Scalar let — a single value
let lookback = 24h;
let targetUser = "j.morrison@northgateeng.com";
let threshold = 50;

// Tabular let — a query result
let riskyIPs = SigninLogs
    | where TimeGenerated > ago(lookback)
    | where ResultType != "0"
    | summarize FailCount = count() by IPAddress
    | where FailCount > threshold
    | project IPAddress;

// Use tabular let as a filter
SigninLogs
| where TimeGenerated > ago(lookback)
| where IPAddress in (riskyIPs)
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName, IPAddress

Scalar lets define configuration parameters. Tabular lets define intermediate results. Together they produce queries that are readable, configurable, and debuggable: each let can be tested independently.

Parameterised functions with let

// Function: get all activity for a user across multiple tables
let UserTimeline = (targetUser: string, lookback: timespan) {
    union
        (SigninLogs | where TimeGenerated > ago(lookback)
            | where UserPrincipalName =~ targetUser
            | extend Source = "SignIn", Detail = strcat(ResultType, " from ", IPAddress)
            | project TimeGenerated, Source, Detail),
        (OfficeActivity | where TimeGenerated > ago(lookback)
            | where UserId =~ targetUser
            | extend Source = "Office", Detail = Operation
            | project TimeGenerated, Source, Detail),
        (AuditLogs | where TimeGenerated > ago(lookback)
            | extend Actor = tostring(InitiatedBy.user.userPrincipalName)
            | where Actor =~ targetUser
            | extend Source = "Admin", Detail = OperationName
            | project TimeGenerated, Source, Detail)
    | sort by TimeGenerated asc
};
// Usage
UserTimeline("j.morrison@northgateeng.com", 24h)

The function encapsulates the multi-table union pattern from Module 4. Any analyst can call UserTimeline(upn, window) without understanding the union logic, reducing investigation time and eliminating copy-paste errors.

Saved functions in Sentinel

Functions defined with let exist only within the current query. For reuse across queries and detection rules, save the function in Log Analytics:

1. Navigate to Log Analytics → Functions → Save as function
2. Name: UserTimeline
3. Parameters: targetUser: string, lookback: timespan
4. Body: the union query above
5. Category: Investigation

After saving, the function is available as a table reference:

UserTimeline("j.morrison@northgateeng.com", 24h)
| where Source == "SignIn"
| where Detail has "high"

Best practice: Maintain a library of saved functions for common investigation patterns: UserTimeline, IPActivity, DeviceTimeline, AlertEntityLookup. These become your SOC's reusable query toolkit, consistent, tested, and maintained centrally.

Recursive let patterns for multi-hop investigation

// Step 1: compromised accounts
let step1 = materialize(
    SigninLogs | where TimeGenerated > ago(24h)
    | where RiskLevelDuringSignIn == "high" | where ResultType == "0"
    | distinct UserPrincipalName);
// Step 2: IPs used by compromised accounts
let step2 = materialize(
    SigninLogs | where TimeGenerated > ago(24h)
    | where UserPrincipalName in (step1) | where ResultType == "0"
    | distinct IPAddress);
// Step 3: OTHER accounts from those IPs
let step3 = SigninLogs | where TimeGenerated > ago(24h)
    | where IPAddress in (step2) | where ResultType == "0"
    | where UserPrincipalName !in (step1)
    | distinct UserPrincipalName;
// Combine all affected accounts
union (step1 | extend Hop = 0), (step3 | extend Hop = 1)

This two-hop investigation starts with known compromised accounts, pivots to their IPs, and discovers additional accounts accessed from the same adversary infrastructure. The Hop column shows whether each account was directly compromised (0) or indirectly linked (1). Module 8 (Graph Analysis) extends this pattern to arbitrary depth.

Pattern: configuration-driven detection rules

Use let statements to make detection rules configurable without modifying the query:

// Configuration block — change these to tune the rule
let lookback = 75m;
let failThreshold = 50;
let userThreshold = 5;
let excludedIPs = _GetWatchlist('ExcludedIPs') | project SearchKey;
let excludedUsers = _GetWatchlist('ServiceAccounts') | project SearchKey;
let excludedApps = dynamic(["Meeting Room Display", "Legacy POS Terminal"]);

// Detection logic — does not change when tuning
SigninLogs
| where TimeGenerated > ago(lookback)
| where ResultType in ("50126", "50053")
| where IPAddress !in (excludedIPs)
| where UserPrincipalName !in (excludedUsers)
| where AppDisplayName !in (excludedApps)
| summarize FailCount = count(), TargetedUsers = dcount(UserPrincipalName),
    UserSample = make_set(UserPrincipalName, 10) by IPAddress
| where FailCount > failThreshold and TargetedUsers > userThreshold
| project TimeGenerated = now(), IPCustomEntity = IPAddress,
    FailCount, TargetedUsers, UserSample

The configuration block at the top contains every tunable parameter. The detection logic below is clean and readable. When the SOC needs to adjust thresholds or add exclusions, they modify only the configuration block: the logic remains unchanged.

Function composition, building complex analysis from simple functions

// Function 1: Get all IPs for a user
let UserIPs = (upn: string, lookback: timespan) {
    SigninLogs | where TimeGenerated > ago(lookback)
    | where UserPrincipalName =~ upn | where ResultType == "0"
    | distinct IPAddress
};

// Function 2: Get all users from an IP
let IPUsers = (ip: string, lookback: timespan) {
    SigninLogs | where TimeGenerated > ago(lookback)
    | where IPAddress == ip | where ResultType == "0"
    | distinct UserPrincipalName
};

// Composed: find all users who share infrastructure with a compromised user
let compromisedIPs = UserIPs("compromised@northgateeng.com", 24h);
let relatedUsers = SigninLogs
    | where TimeGenerated > ago(24h)
    | where IPAddress in (compromisedIPs)
    | where UserPrincipalName != "compromised@northgateeng.com"
    | distinct UserPrincipalName;
relatedUsers

Function composition mirrors the analyst's thought process: "Show me the IPs for this user → now show me the OTHER users from those IPs." Each function is simple, testable, and reusable. The composition builds complex analysis without complex queries.

Dynamic threshold functions

// Function: calculate dynamic threshold for any metric
let DynamicThreshold = (baselineData: (*), metricCol: string, multiplier: real) {
    baselineData
    | extend MetricValue = toreal(column_ifexists(metricCol, 0))
    | summarize Avg = avg(MetricValue), StdDev = stdev(MetricValue)
    | extend Threshold = Avg + (multiplier * StdDev)
    | project Threshold
};

This pattern creates reusable threshold calculation. Any detection rule can compute its own per-entity threshold without duplicating the statistics logic.

Error handling in let statements

Let statements fail silently, if the inner query returns no rows, the let variable is an empty table. Downstream operations that reference an empty table produce no output, which looks like "working correctly with no results" rather than "failed to find data."

Defense: add validation after critical let statements:

let compromisedUsers = SigninLogs
    | where TimeGenerated > ago(24h)
    | where RiskLevelDuringSignIn == "high"
    | distinct UserPrincipalName;
// Validate: are there any compromised users?
let userCount = toscalar(compromisedUsers | count);
// If userCount == 0, the rest of the query produces no results
// This is expected during quiet periods — not an error

Query templates for SOC runbooks

The most impactful use of saved functions is as standardized SOC runbook queries. Each alert type has a defined investigation procedure: the function encapsulates the first query an analyst runs:

// Runbook query: Credential Spray Investigation
let InvestigateCredentialSpray = (attackIP: string, lookback: timespan) {
    let targetedUsers = SigninLogs
        | where TimeGenerated > ago(lookback)
        | where IPAddress == attackIP
        | where ResultType != "0"
        | summarize FailCount = count() by UserPrincipalName
        | sort by FailCount desc;
    let compromisedUsers = SigninLogs
        | where TimeGenerated > ago(lookback)
        | where IPAddress == attackIP
        | where ResultType == "0"
        | distinct UserPrincipalName;
    let postCompromiseActivity = OfficeActivity
        | where TimeGenerated > ago(lookback)
        | where UserId in (compromisedUsers)
        | summarize Actions = make_set(Operation, 20) by UserId;
    targetedUsers
    | extend WasCompromised = iff(UserPrincipalName in (compromisedUsers), "YES", "No")
    | join kind=leftouter postCompromiseActivity on $left.UserPrincipalName == $right.UserId
    | project UserPrincipalName, FailCount, WasCompromised, PostCompromiseActions = Actions
};
// Usage when an alert fires:
InvestigateCredentialSpray("198.51.100.44", 24h)

This function answers every question the analyst needs in one call: who was targeted, who was compromised, and what did the adversary do after compromise. Save functions like this for every alert type in your environment.

let with toscalar, extracting single values

toscalar converts a single-row, single-column table expression into a scalar value:

let totalUsers = toscalar(IdentityInfo 
    | where TimeGenerated > ago(14d) 
    | where IsAccountEnabled == true 
    | summarize dcount(AccountUPN));
let riskyUsers = toscalar(SigninLogs
    | where TimeGenerated > ago(24h)
    | where RiskLevelDuringSignIn in ("high", "medium")
    | summarize dcount(UserPrincipalName));
print RiskyUserPercentage = round(100.0 * riskyUsers / totalUsers, 2)

This calculates the percentage of users with risky sign-ins: a single metric for SOC dashboards. toscalar is essential when you need to use a query result as a threshold or comparison value in subsequent logic.

Tabular function chaining, pipeline within a function

Functions can return tabular output that the caller pipes into additional operators. This enables function chaining, small, focused functions composed into complex analysis:

// Function: filter to external IPs only
let ExternalOnly = (data: (*), ipCol: string) {
    data | where not(ipv4_is_private(column_ifexists(ipCol, "")))
};

// Function: add risk classification
let AddRiskLevel = (data: (*)) {
    data | extend RiskClassification = case(
        RiskLevelDuringSignIn == "high", "Critical",
        RiskLevelDuringSignIn == "medium", "Elevated",
        "Normal"
    )
};

// Chained usage:
SigninLogs
| where TimeGenerated > ago(1h)
| invoke ExternalOnly("IPAddress")
| invoke AddRiskLevel()
| where RiskClassification != "Normal"

The invoke operator calls a tabular function on the current pipeline: each function transforms the data and passes it to the next. This modular approach keeps each function simple and testable while enabling complex analysis through composition. Note that invoke with custom functions requires the functions to be saved in the workspace, inline let functions use direct calling syntax instead.

Create a saved function called IPInvestigation that accepts an IP address and a lookback window. The function should return: all users who authenticated from that IP, all applications accessed, the country/city, and the success/failure ratio. This is the query every analyst runs when an IP appears in an alert, save it once, use it forever.

NE environmental considerations

NE's detection environment includes specific factors that influence this rule's operation:

Device diversity: 768 P2 corporate workstations with full Defender for Endpoint telemetry, 58 P1 manufacturing workstations with basic cloud-delivered protection, and 3 RHEL rendering servers with Syslog-only coverage. Rules targeting DeviceProcessEvents operate with full fidelity on P2 devices but may have reduced visibility on P1 devices. Manufacturing workstations in Sheffield and Sunderland represent a detection gap for endpoint-level detections.

Network topology: 11 offices connected via Palo Alto SD-WAN with full-mesh connectivity. The SD-WAN firewall logs feed CommonSecurityLog in Sentinel. Cross-site lateral movement generates firewall allow events that correlate with DeviceLogonEvents, enabling multi-source detection that single-table rules cannot achieve.

User population: 810 users with distinct behavioral profiles, office workers (predictable hours, consistent applications), field engineers (variable hours, travel patterns), IT administrators (elevated privilege, broad access patterns), and manufacturing operators (fixed shifts, limited application access). Each user population has different detection baselines.

Troubleshooting

"The query returns an error I do not understand." KQL error messages reference the specific line and operator that failed. Read the error message from left to right: it names the operator, the expected input type, and the actual input type. Most errors are type mismatches (passing a string where a datetime is expected) or field name typos. The getschema operator shows every field name and type for any table: TableName | getschema.

"The query runs but returns unexpected results." Add | take 10 after each operator in the pipeline and examine the intermediate output. This reveals WHERE the data transforms in a way you did not expect. Debug the pipeline stage by stage, not the entire query at once.