Aggregation functions

Statistical functions

All of these functions are used in the context of Summarize operator

Function NameDescription
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.
stdevifCalculates 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.

Each argument has a required section which is denoted with required or optional

  • If it's denoted by required it means the argument must be passed into that function before it'll work.
  • if it's denoted by optional it means the function can work without passing the argument value.

avg()

Calculates the average (arithmetic mean) of Expr across the group.

Arguments

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredPredicate 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

NameTypeRequired or OptionalDescription
PredicatestringRequiredExpression 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.

Example

countif (Predicate)

dcount()

Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.

Arguments

NameTypeRequired or OptionalDescription
ExprscalarRequiredA 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredExpression 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_tls

percentile(), percentiles_array()

Calculates the requested percentile of the group and produces a timeseries chart.

Arguments

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PercentiledoubleRequiredA 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredPredicate 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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression 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_type

stdevif()

Calculates the standard deviation of Expr in records for which Predicate evaluates to true.

Arguments

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculation.
PredicatestringRequiredPredicate 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

NameTypeRequired or OptionalDescription
ExprdynamicRequiredExpression that will be used for aggregation calculations.
MaxSizeintegerOptionalThe limit on the maximum number of elements returned.

Returns

Returns a dynamic JSON array of all the values of Expr in the group.

Example

make_list (Expr [, MaxSize])

make_list_if()

Creates a dynamic JSON object (array) of Expr values in the group for which Predicate evaluates to true.

Arguments

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculations.
PredicatestringRequiredPredicate that has to evaluate to true, in order for Expr to be added to the result.
MaxSizeintegerOptionalThe 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.

Example

make_list_if (Expr, Predicate [, MaxSize])

make_set()

Creates a dynamic JSON array of the set of distinct values that Expression takes in the group.

Arguments

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculations.
MaxSizeintegerOptionalThe 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])

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

NameTypeRequired or OptionalDescription
ExprstringRequiredExpression that will be used for aggregation calculations.
PredicatestringRequiredPredicate that has to evaluate to true, in order for Expr to be added to the result.
MaxSizeintegerOptionalThe 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])

Was this page helpful?