range operator

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Generates a single-column table of values.

Note

This operator doesn't take a tabular input.

Syntax

range columnName from start to stop step step

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
columnName	string	✔️	The name of the single column in the output table.
start	int, long, real, datetime, or timespan	✔️	The smallest value in the output.
stop	int, long, real, datetime, or timespan	✔️	The highest value being generated in the output or a bound on the highest value if step is over this value.
step	int, long, real, datetime, or timespan	✔️	The difference between two consecutive values.

Note

The values can't reference the columns of any table. If you want to compute the range based on an input table, use the range function potentially with the mv-expand operator.

Returns

A table with a single column called columnName, whose values are start, start + step, ... up to and until stop.

Examples

The example in this section shows how to use the syntax to help you get started.

The examples in this article use publicly available tables, such as the StormEvents table in the Weather analytics sample data.

Range over the past seven days

The following example creates a table with entries for the current time stamp extended over the past seven days, once a day.

range LastWeek from ago(7d) to now() step 1d

Output

LastWeek
2015-12-05 09:10:04.627
2015-12-06 09:10:04.627
...
2015-12-12 09:10:04.627

Combine different stop times

The following example shows how to extend ranges to use multiple stop times by using the union operator.

let Range1 = range Time from datetime(2024-01-01) to datetime(2024-01-05) step 1d;
let Range2 = range Time from datetime(2024-01-06) to datetime(2024-01-10) step 1d;
union Range1, Range2
| order by Time asc

Output

Time
2024-01-04 00:00:00.0000000
2024-01-05 00:00:00.0000000
2024-01-06 00:00:00.0000000
2024-01-07 00:00:00.0000000
2024-01-08 00:00:00.0000000
2024-01-09 00:00:00.0000000
2024-01-10 00:00:00.0000000

Range using parameters

The following example shows how to use the range operator with parameters, which are then extended and consumed as a table.

let toUnixTime = (dt:datetime) 
{ 
    (dt - datetime(1970-01-01)) / 1s 
};
let MyMonthStart = startofmonth(now()); //Start of month
let StepBy = 4.534h; //Supported timespans
let nn = 64000; // Row Count parametrized
let MyTimeline = range MyMonthHour from MyMonthStart to now() step StepBy
| extend MyMonthHourinUnixTime = toUnixTime(MyMonthHour), DateOnly = bin(MyMonthHour,1d), TimeOnly = MyMonthHour - bin(MyMonthHour,1d)
; MyTimeline | order by MyMonthHour asc | take nn

Output

MyMonthHour	MyMonthHourinUnixTime	DateOnly	TimeOnly
2023-02-01	00:00:00.0000000	1675209600	2023-02-01 00:00:00.0000000
2023-02-01	04:32:02.4000000	1675225922.4	2023-02-01 00:00:00.0000000
2023-02-01	09:04:04.8000000	1675242244.8	2023-02-01 00:00:00.0000000
2023-02-01	13:36:07.2000000	1675258567.2	2023-02-01 00:00:00.0000000
...	...	...	...

Incremented steps

The following example creates a table with a single column called Steps whose type is long and results in values from one to eight incremented by three.

range Steps from 1 to 8 step 3

Output

Steps
1
4
7

Traces over a time range

The following example shows how the range operator can be used to create a dimension table that is used to introduce zeros where the source data has no values. It takes timestamps from the last four hours and counts traces for each one-minute interval. When there are no traces for a specific interval, the count is zero.

range TIMESTAMP from ago(4h) to now() step 1m
| join kind=fullouter
  (Traces
      | where TIMESTAMP > ago(4h)
      | summarize Count=count() by bin(TIMESTAMP, 1m)
  ) on TIMESTAMP
| project Count=iff(isnull(Count), 0, Count), TIMESTAMP
| render timechart