Aggregation functions
Statistical functions
All of these functions are used in the context of Summarize operator
| Function Name | Description |
|---|---|
| avg() | Returns an average value across the group. |
| avgif() | Calculates the average value of Expr in records for which Predicate evaluates to true. |
| count() | Returns a count of the group without/with a predicate. |
| countif() | Returns a count of rows for which Predicate evaluates to true |
| dcount() | Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group. |
| dcountif() | Returns an estimate of the number of distinct values of Expr of rows for which Predicate evaluates to true. |
| max() | Returns the maximum value across the group. |
| maxif() | Calculates the maximum value of Expr in records for which Predicate evaluates to true. |
| min() | Returns the minimum value across the group. |
| minif() | Returns the minimum of Expr in records for which Predicate evaluates to true. |
| sum() | Calculates the sum of Expr across the group. |
| sumif() | Calculates the sum of Expr in records for which Predicate evaluates to true. |
| histogram() | Returns a timeseries heatmap chart across the group. |
| topk() | calculates the top values of Expr across the group in a dataset |
| percentile() | calculates the requested percentiles of the group and produces a timeseries chart. |
| variance() | Calculates the variance of Expr across the group. |
| varienceif() | Calculates the variance of Expr in records for which Predicate evaluates to true. |
| stdev() | Calculates the standard deviation of Expr across the group. |
| stdevif | Calculates the standard deviation of Expr in records for which Predicate evaluates to true. |
| make_list() | Creates a dynamic JSON object (array) of all the values of Expr in the group |
| make_list_if() | Creates a dynamic JSON object (array) of Expr values in the group for which Predicate evaluates to true. |
| make_set() | Creates a dynamic JSON array of the set of distinct values that Expr takes in the group. |
| make_set_if() | Creates a dynamic JSON object (array) of the set of distinct values that Expr takes in records for which Predicate evaluates to true. |
| rate() | Calculates the rate of values in a group per second. |
Each argument has a required section which is denoted with required or optional
- If it's denoted by
requiredit means the argument must be passed into that function before it'll work. - if it's denoted by
optionalit means the function can work without passing the argument value.
expr is short for expression In APL, an expression is a combination of functions, operators, and constants used to compute a value or produce a result.
In the context of data explorer, an expression is used to define the specific data manipulation or extraction operation you want to perform on your dataset.
avg()
Calculates the average (arithmetic mean) of Expr across the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation |
Returns
The average value of Expr across the group.
Examples
avg (Expr)['sample-http-logs']
| summarize avg( req_duration_ms)avgif()
Calculates the average value of Expr in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Predicate that if true, the Expr calculated value will be added to the average. |
Returns
Returns the average value of Expr in records for which Predicate evaluates to true.
Examples
avgif (Expr, Predicate)['github-push-event']
| summarize avgif( size, true) by bin_auto(_time)count()
Returns a count of the records per summarization group (or in total, if summarization is done without grouping).
Returns
Returns a count of the records per summarization group.
Examples
count ()['sample-http-logs']
| summarize count()countif()
Returns a count of rows for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Predicate | string | Required | Expression that will be used for aggregation calculation. Predicate can be any scalar expression with return type of bool (evaluating to true/false). |
Returns
Returns a count of rows for which Predicate evaluates to true.
Examples
countif (Predicate)dcount()
Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | scalar | Required | A scalar expression whose distinct values are to be counted |
Returns
Returns an estimate of the number of distinct values of Expr in the group.
Examples
dcount (Expr)['sample-http-logs']
| summarize dcount( resp_body_size_bytes )dcountif()
Returns an estimate of the number of distinct values of Expr of rows for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Expression that will be used to filter rows. |
Returns
Returns an estimate of the number of distinct values of Expr of rows for which Predicate evaluates to true in the group.
Example
dcountif (Expr, Predicate)max()
Returns the maximum value across the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
Returns
The maximum value of Expr across the group.
Examples
max (Expr)['sample-http-logs']
| summarize max( resp_body_size_bytes)['sample-http-logs']
| summarize max( req_duration_ms) by bin_auto(_time)maxif()
Calculates the maximum value of Expr in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Expression that will be used to filter rows. |
Returns
Returns the maximum value of Expr in records for which Predicate evaluates to true.
Examples
maxif (Expr,Predicate)['github-push-event']
| summarize maxif(push_id, true) by bin_auto(_time)min()
Returns the minimum value across the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
Returns
The minimum value of Expr across the group.
Example
min (Expr)['sample-http-logs']
| summarize min( resp_body_size_bytes)['sample-http-logs']
| summarize min( req_duration_ms) by bin_auto(_time)minif()
Returns the minimum of Expr in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Expression that will be used to filter rows. |
Returns
The minimum value of Expr in records for which Predicate evaluates to true.
Examples
minif (Expr,Predicate)['github-push-event']
| summarize minif(push_id, true) by bin_auto(_time)sum()
Calculates the sum of Expr across the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
Returns
The sum value of Expr across the group.
Examples
sum (Expr)['sample-http-logs']
| summarize sum( resp_body_size_bytes)['sample-http-logs']
| summarize sum(resp_header_size_bytes) by bin_auto(_time)sumif()
Calculates the sum of Expr in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Expression that will be used to filter rows. |
Returns
Returns the sum of Expr for which Predicate evaluates to true.
Examples
sumif (Expr,Predicate)['github-push-event']
| summarize sumif(size, true) by bin_auto(_time)histogram()
Returns a timeseries heatmap chart across the group
Arguments
- Expr: Expression that will be used for aggregation calculation.
Returns
Returns a timeseries heatmap chart across the group
Examples
histogram(Expr)['sample-http-logs']
| summarize histogram(resp_header_size_bytes, 10) by bin_auto(_time)['sample-http-logs']
| summarize histogram(resp_header_size_bytes, 10) by bin_auto(_time), ['geo.country']topk()
calculates the top values of Expr across the group in a dataset
Arguments
- Expr: Expression that will be used for aggregation calculation.
Returns
- A separate result for each group plotted on a timeseries chart.
Examples
topk(Expr)['sample-http-logs']
| summarize topk(method, 4) by bin_auto(_time)['sample-http-logs']
| summarize topk(method, 10) by bin_auto(_time), ['geo.city'], is_tlspercentile(), percentiles_array()
Calculates the requested percentile of the group and produces a timeseries chart.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Percentile | double | Required | A constant that specifies the percentile. |
Returns
A separate result for each group plotted on a horizontal bar chart, allowing for visual comparison across the groups.
Examples
percentile(Expr, percentile)percentiles_array (Expr, Percentile1 [,Percentile2])['sample-http-logs']
| summarize percentile(resp_header_size_bytes, 10) by bin_auto(_time)variance()
Calculates the variance of Expr across the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
Returns
The variance value of Expr across the group.
Examples
variance (Expr)['sample-http-logs']
| summarize variance(resp_header_size_bytes) by bin_auto(_time)varienceif()
Calculates the variance of Expr in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Predicate that if true, the Expr calculated value will be added to the variance. |
Returns
Returns the variance value of Expr in records for which Predicate evaluates to true.
Examples
varianceif (Expr, Predicate)['github-push-event']
| summarize varianceif(size, true) by bin_auto(_time)stdev()
Calculates the standard deviation of Expr across the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
Returns
The standard deviation value of Expr across the group.
Examples
stdev (Expr)['sample-http-logs']
| summarize stdev(resp_header_size_bytes) by bin_auto(_time)['sample-http-logs']
| summarize stdev( req_duration_ms) by bin_auto(_time), content_typestdevif()
Calculates the standard deviation of Expr in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculation. |
| Predicate | string | Required | Predicate that has to evaluate to true, in order for Expr to be added to the result. |
Returns
Returns the standard deviation value of Expr in records for which Predicate evaluates to true.
Examples
stdevif (Expr,Predicate)['github-push-event']
| summarize stdevif(size, true) by bin_auto(_time)make_list()
Creates a dynamic JSON object (array) of all the values of Expression in the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | dynamic | Required | Expression that will be used for aggregation calculations. |
| MaxSize | integer | Optional | The limit on the maximum number of elements returned. |
Returns
Returns a dynamic JSON array of all the values of Expr in the group.
Examples
make_list (Expr [, MaxSize])['sample-http-logs']
| summarize dynamic_list = make_list(status)make_list_if()
Creates a dynamic JSON object (array) of Expr values in the group for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculations. |
| Predicate | string | Required | Predicate that has to evaluate to true, in order for Expr to be added to the result. |
| MaxSize | integer | Optional | The limit on the maximum number of elements returned. |
Returns
Returns a dynamic JSON object (array) of Expr vlaues in the group for which Predicate evaluates to true.
Examples
make_list_if (Expr, Predicate [, MaxSize])['sample-http-logs']
| summarize dynamic_dated_list = make_list_if(resp_body_size_bytes, true)make_set()
Creates a dynamic JSON array of the set of distinct values that Expression takes in the group.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculations. |
| MaxSize | integer | Optional | The limit on the maximum number of elements returned. |
Returns
Returns a dynamic JSON array of the set of distinct values that Expr takes in the group. The array's sort order is undefined.
Example
make_set (Expr [, MaxSize])['sample-http-logs']
| summarize make_set(resp_body_size_bytes)make_set_if()
Creates a dynamic JSON object (array) of the set of distinct values that Expr takes in records for which Predicate evaluates to true.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | string | Required | Expression that will be used for aggregation calculations. |
| Predicate | string | Required | Predicate that has to evaluate to true, in order for Expr to be added to the result. |
| MaxSize | integer | Optional | The limit on the maximum number of elements returned. |
Returns
Returns a dynamic JSON object (array) of the set of distinct values that Expr takes in records for which Predicate evaluates to true.
Example
make_set_if (Expr, Predicate [, MaxSize])['sample-http-logs']
| summarize data_metrics = make_set_if(resp_body_size_bytes, true)rate()
Calculates the rate of values in a group per second.
Arguments
| Name | Type | Required or Optional | Description |
|---|---|---|---|
| Expression | The column can contain one of the following data types: integer, float, or timespan | Required | Expression that will be used for aggregation calculation. |
Returns
Returns the rate of X per second, as a float.
X = Column with summable data (i.e. request duration, file size, $ spent, etc.)
Tip: If you want to see the rate of events, use Rate(1).
Examples
rate (Expr)['http-logs']
| summarize rate(resp_body_size_bytes) by bin_auto(_time)Real-Time HTTP Request Rate per Second
['sample-http-logs']
| where _time >= ago(10s)
| summarize Rate=rate(1) by bin(_time, 1s)HTTP Request Duration Rate per Second Analysis
['sample-http-logs']
| where _time >= ago(10s)
| summarize Rate=rate(req_duration_ms) by bin(_time, 1s)Automatically Binned Analysis of Request Duration Rates Over Time
['sample-http-logs']
| where _time >= ago(10s)
| summarize Rate=rate(req_duration_ms) by bin_auto(_time)