NQL functions

Functions are predefined operations that aggregate, format or extract data, enabling further analysis. They include operations like summing, averaging, and counting, often within grouped data.

Depending on the specific function, you can use it with:

• compute

• summarize

• list

• sort

• where

Syntax

...
... <metric>.<function>.(<optional: function parameters>)

Examples

Aggregate functions

sum()

devices during past 7d 
| include device_performance.system_crashes during past 7d 
| compute number_of_crashes = number_of_system_crashes.sum()

countif()

collaboration.sessions during past 24h
| summarize ratio_of_poor_calls = countif(session.audio.quality = poor or session.video.quality = poor) / count() by connection_type

Format function

as()

devices
| summarize total_cost = count() * 2000
| list total_cost.as(format = currency, code  = USD)

Timestamp functions

time_elapsed()

devices
| where operating_system.last_update.time_elapsed() > 15d

hour()

device_performance.events during past 24h
| where start_time.hour() >= 9 and end_time.hour() <= 17

Aggregated metrics

It's important to differentiate between functions and aggregated metrics. The data model contains various aggregated metrics simplifying access to information. They are defined as fields of the data model.

Field	Description	Example
<metric>.avg	Average value of the metric aggregated in the bucket.	where unload_event.avg > 1.0
<metric>.sum	Sum of all values of the metric aggregated in the bucket.	where unload_event.sum == 10
<metric>.count	Number of aggregated values in the bucket.	where unload_event.count <= 4
<metric>.min	Minimum value of the metric in the bucket.	where unload_event.min < 1.0
<metric>.max	Maximum value of the metric in the bucket.	where unload_event.max > 1.0

Smart aggregates

A smart aggregate is an aggregate on an aggregated metrics that abstracts the underlying computation. They are not fields of the data model. During the execution of a query, the parser computes them on the fly.

Aggregate	Description
<metric>.avg()	Average value of the metric. It is equivalent to <metric>.sum.sum() / <metric>.count.sum()
<metric>.sum()	Sum of all values of the metric. It is equivalent to <metric>.sum.sum()
<metric>.max()	Maximum value of the metric. It is equivalent to <metric>.max.max()
<metric>.min()	Minimum value of the metric. It is equivalent to <metric>.min.min()
<metric>.count()	Number of aggregated values. It is equivalent to <metric>.count.sum()

Example:

Retrieve a list of devices with less than 3GB of average free memory. The following query includes the free_memory.avg() smart aggregate in a compute clause. It computes the average free memory based on the same underlying data points as free_memory.avg aggregated metrics. It is equivalent to free_memory.avg.avg().

devices during past 7d
| with device_performance.events during past 7d
| compute avg_free_memory = free_memory.avg()
| where avg_free_memory < 3GB

Chaining of functions

You can call more than one function on the same field. Currently, the system supports chaining of the time_elapsed() function.

Example:

The following query returns the list of devices with the time elapsed since their last fast startup.

devices
| include device_performance.boots
| where type == fast_startup
| compute time_since_last_fast_startup = time.last().time_elapsed()

In the following section you can find a list of all available functions with usage rules and examples.