GitHubContribute in GitHub: Edit online

make-series operator

Create a series of specified aggregated values along a specified axis.

Syntax

T | make-series [ Column =] Aggregation [default = DefaultValue] [, ...] on AxisColumn [from start] [to end] step step [ by [ Column =] GroupExpression [, ...]]

Arguments

Expr Type Required Description
T Table The tabular input whose records are to be filtered.
Column string The name for the result column. Defaults to a name derived from the expression.
Default Value scalar Default value to be used instead of absent values. If there are no rows with specific values of AxisColumn and GroupExpression, then in the results, the corresponding element of the array is assigned a DefaultValue. If the DefaultValue is omitted, then 0 is assumed.
Aggregation string A call to an aggregation function such as count() or avg(), with column names as arguments. See the list of aggregation functions. Only aggregation functions that return numeric results can be used with the make-series operator.
AxisColumn string A column on which the series is ordered, usually of type datetime or timespan, but all numeric types are also accepted.
start scalar The low bound value of the AxisColumn for each of the series to be built. start, end, and step are used to build an array of AxisColumn values within a specific range and that uses specified step. All Aggregation values are ordered respectively to this array. This AxisColumn array is also the last output column in the output that has the same name as AxisColumn. If a start value is not specified, the start is the first bin (step) which has data in each series.
end scalar The high bound (noninclusive) value of the AxisColumn. The last index of the time series is smaller than this value (and will be start plus integer multiple of step that is smaller than end). If end value is not provided, it is the upper bound of the last bin (step) which has data per each series.
step scalar The difference between two consecutive elements of the AxisColumn array (that is, the bin size). For a list of possible time intervals, see timespan.
GroupExpression: An expression over the columns that provides a set of distinct values. Typically it's a column name that already provides a restricted set of values.

Note

The arrays that are generated by using make-series are limited to 1048576 values (220). Trying to generate a larger array with make-series either results in an error or a truncated array.

Returns

The input rows are arranged into groups that have the same values of the by expressions and the bin_at(AxisColumn, step, start) expression. Then the specified aggregation functions are computed over each group, producing a row for each group. The result contains the by columns, AxisColumn column and also at least one column for each computed aggregate. (Aggregations over multiple columns or non-numeric results are not supported.)

This intermediate result has as many rows with distinct combinations of by and bin_at(AxisColumn, step, start) values.

Finally, the rows from the intermediate result that is arranged into groups have the same values of the by expressions and all the aggregated values are arranged into arrays (values of dynamic type). For each aggregation, a column with its array that has the same name exists. The last column is an array that has the values of AxisColumn binned according to the specified step.

Note

Although you can provide arbitrary expressions for both the aggregation and grouping expressions, it's more efficient to use simple column names.

List of aggregation functions

Function Description
avg() Returns an average value across the group
avgif() Returns an average with the predicate of the group
count() Returns a count of the group
countif() Returns a count with the predicate of the group
dcount() Returns an approximate distinct count of the group elements
dcountif() Returns an approximate distinct count with the predicate of the group
max() Returns the maximum value across the group
maxif() Returns the maximum value with the predicate of the group
min() Returns the minimum value across the group
minif() Returns the minimum value with the predicate of the group
percentile() Returns the percentile value across the group
take_any() Returns a random nonempty value for the group
stdev() Returns the standard deviation across the group
sum() Returns the sum of the elements within the group
sumif() Returns the sum of the elements with the predicate of the group

Examples

This example uses the make-series operator to aggregate the count of events over the last 4 hours, grouping them by data source name and category. The make-series operator groups the events by data source name and category aggregating the count of events into groups of 1 hour. The output of the query contains the results of the aggregated data in an array.

Notice that the example here uses the where operator to filter on the last 4 hours, instead of using the optional start and end argument.

events
| project data_source_name, original_time_group=original_time, low_level_categories
| where original_time_group > ago(4h)
| mv-expand category=low_level_categories to typeof(int)
| make-series aggregated_field=count() default=0 on original_time_group step 1h by data_source_name,category

Results

data_source_name category original_time_group aggregated_field
Linux OS @ 127.0.0.1 8052 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 50,3,13,3
Cisco Meraki @ 127.0.0.1 10009 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 4,1,4,8
microsoftWindowsSource 8110 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 6,8,8,5
Cisco ACE Firewall @ aceSource 4017 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 6,7,6,7

The optional start and end arguments can be used to filter a specific time range.

events
| project data_source_name, original_time_group, low_level_categories 
| mv-expand category=low_level_categories to typeof(int) 
| make-series aggregated_field=count() default=0 on original_time_group from datetime(2024-04-07T08:00:00) to datetime(2024-04-07T12:00:00) step 1h by data_source_name,category

Results

data_source_name category original_time_group aggregated_field
Linux OS @ 127.0.0.1 8052 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 50,3,13,3
Cisco Meraki @ 127.0.0.1 10009 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 4,1,4,8
microsoftWindowsSource 8110 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 6,8,8,5
Cisco ACE Firewall @ aceSource 4017 2024-04-07T08:00:00.000Z, 2024-04-07T09:00:00.000Z, 2024-04-07T10:00:00:00.000Z, 2024-04-07T11:00:00.000Z 6,7,6,7