Let’s Get Scalar: Creating Reusable KQL Functions for Cleaner Queries

Creating Reusable KQL Functions for Cleaner Queries

When working with large datasets in Azure Data Explorer, Log Analytics, or other platforms that support Kusto Query Language (KQL), queries can quickly become complex and unwieldy. Repeated logic, nested subqueries, and sprawling code make maintenance a nightmare. Enter KQL functions—a powerful way to encapsulate logic, improve readability, and make your queries reusable. In this post, we’ll explore how to create reusable KQL functions, why they matter, and provide practical examples to help you write cleaner, more maintainable queries.

Why Use KQL Functions?

KQL functions allow you to:

• Encapsulate logic: Package repetitive or complex logic into a single, reusable unit.

• Improve readability: Break down large queries into modular components.

• Enhance maintainability: Update logic in one place instead of multiple queries.

• Promote consistency: Ensure the same logic is applied uniformly across queries.

Think of functions as your personal query-building blocks. Instead of rewriting the same filtering or aggregation logic, you define it once and call it whenever needed.

Types of KQL Functions

KQL supports two main types of functions:

1. User-Defined Functions (UDFs): Custom functions you create to encapsulate logic, stored in the database.

2. Let Statements: Inline functions defined within a query for one-time or query-specific use.

We’ll cover both, with a focus on UDFs for reusability across queries.

Creating a User-Defined Function (UDF)

To create a UDF, you use the .create function command in KQL. The syntax is:

.create function FunctionName([parameters]) {
    // Logic here
}

Example 1: Filtering High-Severity Alerts

Let’s say you frequently query a SecurityAlerts table to find alerts with a severity of "High" or "Critical" from the last 7 days. Without a function, you’d repeat this logic in every query:

SecurityAlerts
| where TimeGenerated > ago(7d)
| where Severity in ("High", "Critical")

To make this reusable, create a UDF:

.create function HighSeverityAlerts() {
    SecurityAlerts
    | where TimeGenerated > ago(7d)
    | where Severity in ("High", "Critical")
}

Now, you can call this function in any query:

HighSeverityAlerts()
| summarize count() by AlertName

This function is stored in the database and can be reused across queries, reducing duplication and making updates easier. If you need to change the time range to 14 days, you update the function once, and all queries using it reflect the change.

Example 2: Parameterized Function for Flexible Filtering

Functions become even more powerful with parameters. Let’s create a function that filters alerts by a custom time range and severity level.

.create function FilterAlerts(TimeRange:timespan, SeverityLevel:string) {
    SecurityAlerts
    | where TimeGenerated > ago(TimeRange)
    | where Severity == SeverityLevel
}

Use it like this:

FilterAlerts(7d, "High")
| summarize count() by AlertName

Or with a different time range and severity:

FilterAlerts(1h, "Critical")
| project AlertName, TimeGenerated

Parameters make functions flexible, allowing you to reuse the same logic with different inputs.

Using Let Statements for Inline Functions

For one-off or query-specific logic, you can use let statements to define inline functions. These aren’t stored in the database but are great for breaking down complex queries within a single session.

Example 3: Inline Function for Aggregation

Suppose you’re analyzing web server logs and want to calculate average response times by endpoint. You might need to clean and preprocess the data repeatedly. Use a let statement:

let CleanWebLogs = () {
    WebLogs
    | where ResponseTime > 0
    | extend Endpoint = tolower(Endpoint)
};
CleanWebLogs()
| summarize AvgResponseTime = avg(ResponseTime) by Endpoint

You can also add parameters:

let CleanWebLogs = (MinResponseTime:real) {
    WebLogs
    | where ResponseTime > MinResponseTime
    | extend Endpoint = tolower(Endpoint)
};
CleanWebLogs(100)
| summarize AvgResponseTime = avg(ResponseTime) by Endpoint

let statements are scoped to the query, so they’re perfect for modularizing logic without cluttering the database with temporary functions.

Best Practices for KQL Functions

1. Use Descriptive Names: Name functions clearly (e.g., HighSeverityAlerts instead of Func1) to convey their purpose.

2. Keep Functions Focused: Each function should have a single, well-defined responsibility.

3. Leverage Parameters: Make functions flexible with parameters for time ranges, thresholds, or other variables.

4. Document Functions: Use comments or metadata to describe what the function does and its parameters.

5. Test Before Saving: Validate UDF logic in a query before creating the function to avoid errors.

6. Manage Permissions: Ensure appropriate access controls for UDFs in shared environments.

Real-World Example: Combining Functions for Complex Queries

Let’s combine what we’ve learned to tackle a more complex scenario. You’re monitoring a system with multiple data sources: SecurityAlerts and SystemLogs. You want to identify high-severity alerts and correlate them with system errors from the same time period.

First, create a UDF for high-severity alerts (as shown earlier):

.create function HighSeverityAlerts() {
    SecurityAlerts
    | where TimeGenerated > ago(7d)
    | where Severity in ("High", "Critical")
}

Next, create a UDF for system errors:

.create function SystemErrors(TimeRange:timespan) {
    SystemLogs
    | where TimeGenerated > ago(TimeRange)
    | where LogLevel == "Error"
}

Now, combine them in a query:

HighSeverityAlerts()
| join kind=inner (
    SystemErrors(7d)
) on $left.TimeGenerated == $right.TimeGenerated
| project AlertName, LogMessage, TimeGenerated

This query is clean, readable, and maintainable because the logic is encapsulated in reusable functions. If you need to adjust the severity levels or error conditions, you update the respective functions without touching the main query.

TLDR

KQL functions—whether user-defined or inline let statements—are a game-changer for managing complex queries. By encapsulating logic, you reduce duplication, improve readability, and make your queries easier to maintain. Start small with let statements for query-specific needs, then graduate to UDFs for reusable, database-stored functions. With parameters and best practices, you can build a library of functions that streamline your KQL workflows.

Try creating a few functions for your most common query patterns. You’ll be amazed at how much cleaner and more efficient your KQL queries become!