Kusto Query Language (KQL) ships with dozens of functions that you can call from within your queries. However, you may also write your own functions and call those.

Setup

This article assumes that you have an Azure subscription, an ADX cluster, and an ADX database. See the previous articles in this series to learn how to create an ADX cluster and/or database.

For the examples in this article, we will use a table created with the following ADX commands:

.drop table droneData

.create-merge table droneData
(
DroneId:int,
TimeStamp:datetime,
Longitude:decimal,
Latitude:decimal,
BatteryLife:decimal
)

.ingest inline into table droneData <|
1, datetime(2022-01-10 10:45:00), -73.988888888888888, 40.742222222222222, 100.0
1, datetime(2022-01-10 11:00:00), -73.988888844444444, 40.742222215555222, 100.0
1, datetime(2022-01-10 11:15:00), -73.978820800000000, 40.741862453111111, 99.9
1, datetime(2022-01-10 11:30:00), -73.978800000000000, 40.741862453112222, 99.8
1, datetime(2022-01-10 12:45:00), -73.970000000000000, 40.741862453122222, 99.7
1, datetime(2022-01-10 12:00:00), -73.960555555555555, 40.741862453122222, 99.6
1, datetime(2022-01-10 12:15:00), -73.960000000000000, 40.741862453122222, 99.5
1, datetime(2022-01-10 12:30:00), -73.960000088888888, 40.741862453122222, 99.4
1, datetime(2022-01-10 12:45:00), -73.960000055555555, 40.741862453122222, 99.4
1, datetime(2022-01-10 13:00:00), -73.960000011111111, 40.741862453122222, 99.3
1, datetime(2022-01-10 13:15:00), -73.955555555555555, 40.741862453122222, 99.2
1, datetime(2022-01-10 13:30:00), -73.950000000555555, 40.741862453122222, 99.2
1, datetime(2022-01-10 13:45:00), -73.960000000000000, 40.741862453122222, 99.0
1, datetime(2022-01-10 14:00:00), -73.960555555555555, 40.741862453122222, 98.9
1, datetime(2022-01-10 14:15:00), -73.960555555555555, 40.741862453122222, 98.8

Syntax

Use the .create-or-alter function command to create a new function or modify one that already exists. The syntax is:

.create-or-alter function with (docstring = description, folder=folder_name] name_of_function ( parameter_list ) { KQL_Script }

where:

• description is a brief description of the function. This is optional, but it is useful to help others understand the purpose of your function.
• folder_name is a logical folder in which to store the function. This is optional, but it can help to organize your functions if you have many of them.
• parameter_list is a list of input parameters to the function.
• KQL_Script is the KQL code to execute when the function is called.

Parameters

Parameters are passed to a function as a comma-separated list of name/data type pairs, for example:

startDateTime:datetime,
endDateTime:datetime,

You can make a parameter optional by adding a default value, as shown below:

timeBinLength:timespan = 1h

This creates an optional param named "timeBinLength" of type timespan. If this parameter is not passed to the function, it will default to 1 hour.

Sample

Here is an example of an ADX function that accepts as input parameters a start and end time and (optionally) a timespan that defines a bin size. It returns a dataset of drone locations between the start and end time, but only returns one row per timespan defined by the timeBinLength parameter.

.create-or-alter function
with (docstring = 'Points drone passed through rolled up by time period', folder='Samples')
DroneRoute(
startDateTime:datetime,
endDateTime:datetime,
timeBinLength:timespan = 1h
)
{
droneData
| where TimeStamp between (startDateTime .. endDateTime)
| summarize arg_max(TimeStamp, Longitude, Latitude) by bin(TimeStamp, timeBinLength)
| order by TimeStamp asc 
| project TimeStamp, Longitude, Latitude
}

Calling the Function

Calling your function requires only the function name, followed by parameters in parentheses.

The code below calls the DroneRoute function, setting the timespan to 30 minutes.

DroneRoute('2022-01-10 11:00:00', '2022-01-10 13:00:00', 30m)

The results of that call are in Fig. 1.

Fig. 1

The code below also calls the DroneRoute function, but omits the optional timeBinLength parameter, so the default of 1 hour is used.

DroneRoute('2022-01-10 11:00:00', '2022-01-10 13:00:00')

The results of that call are in Fig. 2.

Fig. 2

Conclusion

In this article, you learned how to create and call a User-Defined Tabular Data Function in Azure Data Explorer.