AzureFeeds

Kusto Query Language (KQL) is a powerful querying language that is used across a number of Azure products. 

In this post, we’ll dive into some of the most commonly used KQL functions, explain how they work, and provide practical examples to help you write more efficient and insightful queries.

KQL Functions

Functions within KQL allow you to manipulate, analyse, and derive meaningful insights from data. Sometimes they are used on their own or they are combined with KQL operators to pull out insight information from your dataset. 

Scalar Functions

Scalar functions are used to look at individual values and perform basic operations like string manipulation, mathematical calculations, or type conversions. These functions operate on a single value and return a single result.

The tolower() function is an example of a scalar function that converts a string to lowercase.  We can use it in a query to convert the EventType column to lowercase making it easier to compare or analyse case-insensitive data. 

StormEvents
| extend LowerCaseEvents = tolower(EventType)

Aggregating Functions

Aggregating functions are used to perform calculations on multiple rows of data, providing insights like counts, averages, sums, and other statistical measures. These functions are commonly used with the summarize operator to group and summarize data.

In an example query we can calculate the average value of the DamageProperty column, helping to understand the typical property damage for storm events.

StormEvents
| summarize AverageDamage = avg(DamageProperty)

Date and Time Functions

Within KQL there are a variety of date and time functions that can be used when querying data that has time-based information.  These functions can allow you to extract specific data based on timestamps, or allow you to calculate durations or manipulate time values. 

The ago() function returns a period relative to the current time, in this example we can query data that happened in the last 7 days. 

StormEvents| where TimeGenerated > ago(7d)

Or we can use the datetime() function to create a specific date and time value, in this example, we are looking for events that are greater or have happened after the 1st of January 2025. 

StormEvents
| where TimeGenerated > datetime(2025-01-01)

String Functions

The string functions can be used to manipulate or analyse text data.  They can help you extract substrings, match patterns or clean data for further analysis. 

We can use the endswith function to filter records that end with a specific word or phrase. For example, to search the StormEvents dataset and find records in the EventType column that end with the word “wind,” the query would be:

StormEvents
| where EventType endswith "wind"

Conditional Functions

Conditional functions when used in KQL queries allow you to perform different actions based on specific conditions, similar to if-else programming logic.  These functions can help add flexibility to your queries. 

One conditional function is the iif() function that returns one value if the condition is true and another value if it is false.  If we build an example query using the iif() function we can check if the DamageProperty column is greater than 1,000,000 and label the event as either “Severe” or “Moderate” based on the result.  

StormEvents
| extend StormSeverity = iif(DamageProperty > 1000000, "Severe", "Moderate")

(adsbygoogle = window.adsbygoogle || []).push({});

Collection Functions

The collection functions within KQL allow you to work with arrays, sets, and other collections of data.  These functions help you analyse groups of valves and perform operations on them. 

For example, we can use the in() function to check if a value exists in a list or set. And in this query example, we are looking to show only events, hurricane, tornado or flood. 

StormEvents
| where EventType in ('Hurricane', 'Tornado', 'Flood')

Mathematical Functions

With mathematical functions we can perform calculations, from basic arithmetic to more complex operations like logarithms and rounding. 

We can use the round() function to round the data to a specified number of decimal places, in this example query we are looking to round the DamageProperty output to two decimal places. 

StormEvents
| extend RoundedDamage = round(DamageProperty, 2)

Conclusion

Functions within KQL are a great way of manipulating or analysing data.  Whether you’re working with individual values, performing aggregations, or analyzing time-based data, KQL functions can help streamline your queries and enhance your data analysis.

With practice, you can unlock the full potential of this query language, stay tuned for more insights into KQL!