Tabular operators

• 1: Join operator
• 1.1: join flavors
• 1.1.1: fullouter join
• 1.1.2: inner join
• 1.1.3: innerunique join
• 1.1.4: leftanti join
• 1.1.5: leftouter join
• 1.1.6: leftsemi join
• 1.1.7: rightanti join
• 1.1.8: rightouter join
• 1.1.9: rightsemi join
• 1.2: Broadcast join
• 1.3: Cross-cluster join
• 1.4: join operator
• 1.5: Joining within time window
• 2: Render operator
• 2.1: visualizations
• 2.1.1: Anomaly chart visualization
• 2.1.2: Area chart visualization
• 2.1.3: Bar chart visualization
• 2.1.4: Card visualization
• 2.1.5: Column chart visualization
• 2.1.6: Ladder chart visualization
• 2.1.7: Line chart visualization
• 2.1.8: Pie chart visualization
• 2.1.9: Pivot chart visualization
• 2.1.10: Plotly visualization
• 2.1.11: Scatter chart visualization
• 2.1.12: Stacked area chart visualization
• 2.1.13: Table visualization
• 2.1.14: Time chart visualization
• 2.1.15: Time pivot visualization
• 2.1.16: Treemap visualization
• 2.2: render operator
• 3: Summarize operator
• 3.1: Kusto partition & compose intermediate aggregation results
• 3.2: summarize operator
• 4: as operator
• 5: consume operator
• 6: count operator
• 7: datatable operator
• 8: distinct operator
• 9: evaluate plugin operator
• 10: extend operator
• 11: externaldata operator
• 12: facet operator
• 13: find operator
• 14: fork operator
• 15: getschema operator
• 16: invoke operator
• 17: lookup operator
• 18: mv-apply operator
• 19: mv-expand operator
• 20: parse operator
• 21: parse-kv operator
• 22: parse-where operator
• 23: partition operator
• 24: print operator
• 25: Project operator
• 26: project-away operator
• 27: project-keep operator
• 28: project-rename operator
• 29: project-reorder operator
• 30: Queries
• 31: range operator
• 32: reduce operator
• 33: sample operator
• 34: sample-distinct operator
• 35: scan operator
• 36: search operator
• 37: serialize operator
• 38: Shuffle query
• 39: sort operator
• 40: take operator
• 41: top operator
• 42: top-hitters operator
• 43: top-nested operator
• 44: union operator
• 45: where operator

1 - Join operator

1.1 - join flavors

1.1.1 - fullouter join

A fullouter join combines the effect of applying both left and right outer-joins. For columns of the table that lack a matching row, the result set contains null values. For those records that do match, a single row is produced in the result set containing fields populated from both tables.

Syntax

LeftTable | join kind=fullouter [ Hints ] RightTable on Conditions

Returns

Schema: All columns from both tables, including the matching keys.
Rows: All records from both tables with unmatched cells populated with null.

Example

This example query combines rows from both tables X and Y, filling in missing values with NULL where there’s no match in the other table. This allows you to see all possible combinations of keys from both tables.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=fullouter Y on Key

Output

Related content

• Learn about other join flavors

1.1.2 - inner join

The inner join flavor is like the standard inner join from the SQL world. An output record is produced whenever a record on the left side has the same join key as the record on the right side.

Syntax

LeftTable | join kind=inner [ Hints ] RightTable on Conditions

Returns

Schema: All columns from both tables, including the matching keys.
Rows: Only matching rows from both tables.

Example

The example query combines rows from tables X and Y where the keys match, showing only the rows that exist in both tables.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'k',5,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40,
    'k',50
];
X | join kind=inner Y on Key

Output

Related content

• Learn about other join flavors

1.1.3 - innerunique join

The innerunique join flavor removes duplicate keys from the left side. This behavior ensures that the output contains a row for every combination of unique left and right keys.

By default, the innerunique join flavor is used if the kind parameter isn’t specified. This default implementation is useful in log/trace analysis scenarios, where you aim to correlate two events based on a shared correlation ID. It allows you to retrieve all instances of the phenomenon while disregarding duplicate trace records that contribute to the correlation.

Syntax

LeftTable | join kind=innerunique [ Hints ] RightTable on Conditions

Returns

Schema: All columns from both tables, including the matching keys.
Rows: All deduplicated rows from the left table that match rows from the right table.

Examples

Review the examples and run them in your Data Explorer query page.

Use the default innerunique join

The example query combines rows from tables X and Y where the keys match, showing only the rows that exist in both tables

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join Y on Key

Output

The query executed the default join, which is an inner join after deduplicating the left side based on the join key. The deduplication keeps only the first record. The resulting left side of the join after deduplication is:

Two possible outputs from innerunique join

let t1 = datatable(key: long, value: string)  
    [
    1, "val1.1",  
    1, "val1.2"  
];
let t2 = datatable(key: long, value: string)  
    [  
    1, "val1.3",
    1, "val1.4"  
];
t1
| join kind = innerunique
    t2
    on key

Output

let t1 = datatable(key: long, value: string)  
    [
    1, "val1.1",  
    1, "val1.2"  
];
let t2 = datatable(key: long, value: string)  
    [  
    1, "val1.3", 
    1, "val1.4"  
];
t1
| join kind = innerunique
    t2
    on key

Output

• Kusto is optimized to push filters that come after the join, towards the appropriate join side, left or right, when possible.
• Sometimes, the flavor used is innerunique and the filter is propagated to the left side of the join. The flavor is automatically propagated and the keys that apply to that filter appear in the output.
• Use the previous example and add a filter where value == "val1.2" . It gives the second result and will never give the first result for the datasets:

let t1 = datatable(key: long, value: string)  
    [
    1, "val1.1",  
    1, "val1.2"  
];
let t2 = datatable(key: long, value: string)  
    [  
    1, "val1.3", 
    1, "val1.4"  
];
t1
| join kind = innerunique
    t2
    on key
| where value == "val1.2"

Output

Get extended sign-in activities

Get extended activities from a login that some entries mark as the start and end of an activity.

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityId, StartTime=timestamp
| join (Events
    | where Name == "Stop"
        | project StopTime=timestamp, ActivityId)
    on ActivityId
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityIdLeft = ActivityId, StartTime=timestamp
| join (Events
        | where Name == "Stop"
        | project StopTime=timestamp, ActivityIdRight = ActivityId)
    on $left.ActivityIdLeft == $right.ActivityIdRight
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

Related content

• Learn about other join flavors

1.1.4 - leftanti join

The leftanti join flavor returns all records from the left side that don’t match any record from the right side. The anti join models the “NOT IN” query.

Syntax

LeftTable | join kind=leftanti [ Hints ] RightTable on Conditions

Returns

Schema: All columns from the left table.
Rows: All records from the left table that don’t match records from the right table.

Example

The example query combines rows from tables X and Y where there is no match in Y for the keys in X, effectively filtering out any rows in X that have corresponding rows in Y.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftanti Y on Key

Output

Related content

• Learn about other join flavors

1.1.5 - leftouter join

The leftouter join flavor returns all the records from the left side table and only matching records from the right side table.

Syntax

LeftTable | join kind=leftouter [ Hints ] RightTable on Conditions

Returns

Schema: All columns from both tables, including the matching keys.
Rows: All records from the left table and only matching rows from the right table.

Example

The result of a left outer join for tables X and Y always contains all records of the left table (X), even if the join condition doesn’t find any matching record in the right table (Y).

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftouter Y on Key

Output

Related content

• Learn about other join flavors

1.1.6 - leftsemi join

The leftsemi join flavor returns all records from the left side that match a record from the right side. Only columns from the left side are returned.

Syntax

LeftTable | join kind=leftsemi [ Hints ] RightTable on Conditions

Returns

Schema: All columns from the left table.
Rows: All records from the left table that match records from the right table.

Example

This query filters and returns only those rows from table X that have a matching key in table Y.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftsemi Y on Key

Output

Related content

• Learn about other join flavors

1.1.7 - rightanti join

The rightanti join flavor returns all records from the right side that don’t match any record from the left side. The anti join models the “NOT IN” query.

Syntax

LeftTable | join kind=rightanti [ Hints ] RightTable on Conditions

Returns

Schema: All columns from the right table.
Rows: All records from the right table that don’t match records from the left table.

Example

This query filters and returns only those rows from table Y that do not have a matching key in table X.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightanti Y on Key

Output

Related content

• Learn about other join flavors

1.1.8 - rightouter join

The rightouter join flavor returns all the records from the right side and only matching records from the left side. This join flavor resembles the leftouter join flavor, but the treatment of the tables is reversed.

Syntax

LeftTable | join kind=rightouter [ Hints ] RightTable on Conditions

Returns

Schema: All columns from both tables, including the matching keys.
Rows: All records from the right table and only matching rows from the left table.

Example

This query returns all rows from table Y and any matching rows from table X, filling in NULL values where there is no match from X.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightouter Y on Key

Output

Related content

• Learn about other join flavors

1.1.9 - rightsemi join

The rightsemi join flavor returns all records from the right side that match a record from the left side. Only columns from the right side are returned.

Syntax

LeftTable | join kind=rightsemi [ Hints ] RightTable on Conditions

Returns

Schema: All columns from the right table.
Rows: All records from the right table that match records from the left table.

Example

This query filters and returns only those rows from table Y that have a matching key in table X.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightsemi Y on Key

Output

Related content

• Learn about other join flavors

1.2 - Broadcast join

Today, regular joins are executed on a cluster single node. Broadcast join is an execution strategy of join that distributes the join over cluster nodes. This strategy is useful when the left side of the join is small (up to several tens of MBs). In this case, a broadcast join is more performant than a regular join.

Today, regular joins are executed on an Eventhouse single node. Broadcast join is an execution strategy of join that distributes the join over Eventhouse nodes. This strategy is useful when the left side of the join is small (up to several tens of MBs). In this case, a broadcast join is more performant than a regular join.

Use the lookup operator if the right side is smaller than the left side. The lookup operator runs in broadcast strategy by default when the right side is smaller than the left.

If left side of the join is a small dataset, then you may run join in broadcast mode using the following syntax (hint.strategy = broadcast):

leftSide 
| join hint.strategy = broadcast (factTable) on key

The performance improvement is more noticeable in scenarios where the join is followed by other operators such as summarize. See the following query for example:

leftSide 
| join hint.strategy = broadcast (factTable) on Key
| summarize dcount(Messages) by Timestamp, Key

Related content

• lookup operator.

1.3 - Cross-cluster join

A cross-cluster join involves joining data from datasets that reside in different clusters.

In a cross-cluster join, the query can be executed in three possible locations, each with a specific designation for reference throughout this document:

• Local cluster: The cluster to which the request is sent, which is also known as the cluster hosting the database in context.
• Left cluster: The cluster hosting the data on the left side of the join operation.
• Right cluster: The cluster hosting the data on the right side of the join operation.

The cluster that runs the query fetches the data from the other cluster.

Syntax

[ cluster(ClusterName).database(DatabaseName).]LeftTable | …
| join [ hint.remote=Strategy ] (
  [ cluster(ClusterName).database(DatabaseName).]RightTable | …
 ) on Conditions

Parameters

Strategies

The following list explains the supported values for the Strategy parameter:

• left: Execute join on the cluster of the left table, or left cluster.
• right: Execute join on the cluster of the right table, or right cluster.
• local: Execute join on the cluster of the current cluster, or local cluster.
• auto: (Default) Kusto makes the remoting decision.

How the auto strategy works

By default, the auto strategy determines where the cross-cluster join is executed based on the following rules:

• If one of the tables is hosted in the local cluster, then the join is performed on the local cluster. For example, with the auto strategy, this query is executed on the local cluster:

  T | ... | join (cluster("B").database("DB").T2 | ...) on Col1
  

• If both tables are hosted outside of the local cluster, then join is performed on the right cluster. For example, assuming neither cluster is the local cluster, the join would be executed on the right cluster:

  cluster("B").database("DB").T | ... | join (cluster("C").database("DB2").T2 | ...) on Col1
  

Performance considerations

For optimal performance, we recommend running the query on the cluster that contains the largest table.

In the following example, if the dataset produced by T | ... is smaller than one produced by cluster("B").database("DB").T2 | ... then it would be more efficient to execute the join operation on cluster B, in this case the right cluster instead of on the local cluster.

T | ... | join (cluster("B").database("DB").T2 | ...) on Col1

You can rewrite the query to use hint.remote=right to optimize the performance. In this way, the join operation is performed on the right cluster, even if left table is in the local cluster.

T | ... | join hint.remote=right (cluster("B").database("DB").T2 | ...) on Col1

Related content

• join operator
• Cross-cluster or cross-database queries

1.4 - join operator

Merge the rows of two tables to form a new table by matching values of the specified columns from each table.

Kusto Query Language (KQL) offers many kinds of joins that each affect the schema and rows in the resultant table in different ways. For example, if you use an inner join, the table has the same columns as the left table, plus the columns from the right table. For best performance, if one table is always smaller than the other, use it as the left side of the join operator.

The following image provides a visual representation of the operation performed by each join. The color of the shading represents the columns returned, and the areas shaded represent the rows returned.

Syntax

LeftTable | join [ kind = JoinFlavor ] [ Hints ] (RightTable) on Conditions

Parameters

Hints

Returns

The return schema and rows depend on the join flavor. The join flavor is specified with the kind keyword. The following table shows the supported join flavors. To see examples for a specific join flavor, select the link in the Join flavor column.

Cross-join

KQL doesn’t provide a cross-join flavor. However, you can achieve a cross-join effect by using a placeholder key approach.

In the following example, a placeholder key is added to both tables and then used for the inner join operation, effectively achieving a cross-join-like behavior:

X | extend placeholder=1 | join kind=inner (Y | extend placeholder=1) on placeholder

Related content

• Write multi-table queries
• Cross-cluster join
• Broadcast join
• Shuffle query

1.5 - Joining within time window

It’s often useful to join between two large datasets on some high-cardinality key, such as an operation ID or a session ID, and further limit the right-hand-side ($right) records that need to match up with each left-hand-side ($left) record by adding a restriction on the “time-distance” between datetime columns on the left and on the right.

The above operation differs from the usual join operation, since for the equi-join part of matching the high-cardinality key between the left and right datasets, the system can also apply a distance function and use it to considerably speed up the join.

Example to identify event sequences without time window

To identify event sequences within a relatively small time window, this example uses a table T with the following schema:

• SessionId: A column of type string with correlation IDs.
• EventType: A column of type string that identifies the event type of the record.
• Timestamp: A column of type datetime indicates when the event described by the record happened.

The following query creates the dataset and then identifies all the session IDs in which event type A was followed by an event type B within a 1min time window.

let T = datatable(SessionId:string, EventType:string, Timestamp:datetime)
[
    '0', 'A', datetime(2017-10-01 00:00:00),
    '0', 'B', datetime(2017-10-01 00:01:00),
    '1', 'B', datetime(2017-10-01 00:02:00),
    '1', 'A', datetime(2017-10-01 00:03:00),
    '3', 'A', datetime(2017-10-01 00:04:00),
    '3', 'B', datetime(2017-10-01 00:10:00),
];
T
| where EventType == 'A'
| project SessionId, Start=Timestamp
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp
    ) on SessionId
| where (End - Start) between (0min .. 1min)
| project SessionId, Start, End 

Output

Example optimized with time window

To optimize this query, we can rewrite it to account for the time window. THe time window is expressed as a join key. Rewrite the query so that the datetime values are “discretized” into buckets whose size is half the size of the time window. Use equi-join to compare the bucket IDs.

The query finds pairs of events within the same session (SessionId) where an ‘A’ event is followed by a ‘B’ event within 1 minute. It projects the session ID, the start time of the ‘A’ event, and the end time of the ‘B’ event.

let T = datatable(SessionId:string, EventType:string, Timestamp:datetime)
[
    '0', 'A', datetime(2017-10-01 00:00:00),
    '0', 'B', datetime(2017-10-01 00:01:00),
    '1', 'B', datetime(2017-10-01 00:02:00),
    '1', 'A', datetime(2017-10-01 00:03:00),
    '3', 'A', datetime(2017-10-01 00:04:00),
    '3', 'B', datetime(2017-10-01 00:10:00),
];
let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0;
T 
| where EventType == 'A'
| project SessionId, Start=Timestamp, TimeKey = bin(Timestamp, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp,
              TimeKey = range(bin(Timestamp-lookupWindow, lookupBin),
                              bin(Timestamp, lookupBin),
                              lookupBin)
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End 

Output

5 million data query

The next query emulates an extensive dataset of 5M records and approximately 1M Session IDs and runs the query with the time window technique.

let T = range x from 1 to 5000000 step 1
| extend SessionId = rand(1000000), EventType = rand(3), Time=datetime(2017-01-01)+(x * 10ms)
| extend EventType = case(EventType < 1, "A",
                          EventType < 2, "B",
                          "C");
let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0;
T 
| where EventType == 'A'
| project SessionId, Start=Time, TimeKey = bin(Time, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Time, 
              TimeKey = range(bin(Time-lookupWindow, lookupBin), 
                              bin(Time, lookupBin),
                              lookupBin)
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End 
| count 

Output

Related content

• join operator

2 - Render operator

2.1 - visualizations

2.1.1 - Anomaly chart visualization

The anomaly chart visualization is similar to a timechart, but highlights anomalies using the series_decompose_anomalies function.

Syntax

T | render anomalychart [with ( propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

ysplit property

This visualization supports splitting into multiple y-axis values. The supported values of this property are:

Example

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

let min_t = datetime(2017-01-05);
let max_t = datetime(2017-02-03 22:00);
let dt = 2h;
demo_make_series2
| make-series num=avg(num) on TimeStamp from min_t to max_t step dt by sid 
| where sid == 'TS1'   //  select a single time series for a cleaner visualization
| extend (anomalies, score, baseline) = series_decompose_anomalies(num, 1.5, -1, 'linefit')
| render anomalychart with(anomalycolumns=anomalies, title='Web app. traffic of a month, anomalies') //use "| render anomalychart with anomalycolumns=anomalies" to render the anomalies as bold points on the series charts.

2.1.2 - Area chart visualization

The area chart visual shows a time-series relationship. The first column of the query should be numeric and is used as the x-axis. Other numeric columns are the y-axes. Unlike line charts, area charts also visually represent volume. Area charts are ideal for indicating the change among different datasets.

Syntax

T | render areachart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

ysplit property

This visualization supports splitting into multiple y-axis values:

Supported properties

All properties are optional.

kind property

This visualization can be further elaborated by providing the kind property. The supported values of this property are:

Examples

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

Simple area chart

The following example shows a basic area chart visualization.

demo_series3
| render areachart

Area chart using properties

The following example shows an area chart using multiple property settings.

OccupancyDetection
| summarize avg_temp= avg(Temperature), avg_humidity= avg(Humidity) by bin(Timestamp, 1h)
| render areachart
    with ( 
        kind = unstacked,
        legend = visible,
        ytitle ="Sample value",
        ymin = 10,
        ymax =100,
        xtitle = "Time",    
        title ="Humidity and temperature"
    )

Area chart using split panels

The following example shows an area chart using split panels. In this example, the ysplit property is set to panels.

StormEvents
| where State in ("TEXAS", "NEBRASKA", "KANSAS") and EventType == "Hail"
| summarize count=count() by State, bin(StartTime, 1d)
| render areachart
    with (
        ysplit= panels,
        legend = visible,
        ycolumns=count,
        yaxis =log,
        ytitle ="Count",
        ymin = 0,
        ymax =100,
        xaxis = linear,
        xcolumn = StartTime,
        xtitle = "Date",    
        title ="Hail events"
    )

2.1.3 - Bar chart visualization

The bar chart visual needs a minimum of two columns in the query result. By default, the first column is used as the y-axis. This column can contain text, datetime, or numeric data types. The other columns are used as the x-axis and contain numeric data types to be displayed as horizontal lines. Bar charts are used mainly for comparing numeric and nominal discrete values, where the length of each line represents its value.

Syntax

T | render barchart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

ysplit property

This visualization supports splitting into multiple y-axis values:

Supported properties

All properties are optional.

kind property

This visualization can be further elaborated by providing the kind property. The supported values of this property are:

Examples

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

Render a bar chart

The following query creates a bar chart displaying the number of storm events for each state, filtering only those states with more than 10 events. The chart provides a visual representation of the event distribution across different states.

StormEvents
| summarize event_count=count() by State
| project State, event_count
| render barchart
    with (
    title="Storm count by state",
    ytitle="Storm count",
    xtitle="State",
    legend=hidden
    )

Render a stacked bar chart

The following query creates a stacked bar chart that shows the total count of storm events by their type for selected states of Texas, California, and Florida. Each bar represents a storm event type, and the stacked bars show the breakdown of storm events by state within each type.

StormEvents
| where State in ("TEXAS", "CALIFORNIA", "FLORIDA")
| summarize EventCount = count() by EventType, State
| order by EventType asc, State desc
| render barchart with (kind=stacked)

Render a stacked100 bar chart

The following query creates a stacked100 bar chart that shows the total count of storm events by their type for selected states of Texas, California, and Florida. The chart shows the distribution of storm events across states within each type. Although the stacks visually sum up to 100, the values actually represent the number of events, not percentages. This visualization is helpful for understanding both the percentages and the actual event counts.

StormEvents
| where State in ("TEXAS", "CALIFORNIA", "FLORIDA")
| summarize EventCount = count() by EventType, State
| order by EventType asc, State desc
| render barchart with (kind=stacked100)

Use the ysplit property

The following query provides a daily summary of storm-related injuries and deaths, visualized as a bar chart with split axes/panels for better comparison.

StormEvents
| summarize
    TotalInjuries = sum(InjuriesDirect) + sum(InjuriesIndirect),
    TotalDeaths = sum(DeathsDirect) + sum(DeathsIndirect)
    by bin(StartTime, 1d)
| project StartTime, TotalInjuries, TotalDeaths
| render barchart with (ysplit=axes)

To split the view into separate panels, specify panels instead of axes:

StormEvents
| summarize
    TotalInjuries = sum(InjuriesDirect) + sum(InjuriesIndirect),
    TotalDeaths = sum(DeathsDirect) + sum(DeathsIndirect)
    by bin(StartTime, 1d)
| project StartTime, TotalInjuries, TotalDeaths
| render barchart with (ysplit=panels)

2.1.4 - Card visualization

The card visual only shows one element. If there are multiple columns and rows in the output, the first result record is treated as set of scalar values and shows as a card.

Syntax

T | render card [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

Example

This query provides a count of flood events in Virginia and displays the result in a card format.

StormEvents
| where State=="VIRGINIA" and EventType=="Flood"
| count
| render card with (title="Floods in Virginia")

2.1.5 - Column chart visualization

The column chart visual needs a minimum of two columns in the query result. By default, the first column is used as the x-axis. This column can contain text, datetime, or numeric data types. The other columns are used as the y-axis and contain numeric data types to be displayed as vertical lines. Column charts are used for comparing specific sub category items in a main category range, where the length of each line represents its value.

Syntax

T | render columnchart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

ysplit property

This visualization supports splitting into multiple y-axis values:

Supported properties

All properties are optional.

kind property

This visualization can be further elaborated by providing the kind property. The supported values of this property are:

Examples

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

Render a column chart

This query provides a visual representation of states with a high frequency of storm events, specifically those with more than 10 events, using a column chart.

StormEvents
| summarize event_count=count() by State
| where event_count > 10
| project State, event_count
| render columnchart

Use the ysplit property

This query provides a daily summary of storm-related injuries and deaths, visualized as a column chart with split axes/panels for better comparison.

StormEvents
| summarize
    TotalInjuries = sum(InjuriesDirect) + sum(InjuriesIndirect),
    TotalDeaths = sum(DeathsDirect) + sum(DeathsIndirect)
    by bin(StartTime, 1d)
| project StartTime, TotalInjuries, TotalDeaths
| render columnchart with (ysplit=axes)

To split the view into separate panels, specify panels instead of axes:

StormEvents
| summarize
    TotalInjuries = sum(InjuriesDirect) + sum(InjuriesIndirect),
    TotalDeaths = sum(DeathsDirect) + sum(DeathsIndirect)
    by bin(StartTime, 1d)
| project StartTime, TotalInjuries, TotalDeaths
| render columnchart with (ysplit=panels)

Example

This query helps you identify states with a significant number of storm events and presents the information in a clear, visual format.

StormEvents
| summarize event_count=count() by State
| where event_count > 10
| project State, event_count
| render columnchart

2.1.6 - Ladder chart visualization

The last two columns are the x-axis, and the other columns are the y-axis.

Syntax

T | render ladderchart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

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 in the help cluster, such as the StormEvents table in the Samples database.

Dates of storms by state

This query outputs a state-wise visualization of the duration of rain-related storm events, displayed as a ladder chart to help you analyze the temporal distribution of these events.

StormEvents
| where EventType  has "rain"
| summarize min(StartTime), max(EndTime) by State
| render ladderchart

Dates of storms by event type

This query outputs a visualization of the duration of various storm events in Washington, displayed as a ladder chart to help you analyze the temporal distribution of these events by type.

StormEvents
| where State == "WASHINGTON"
| summarize min(StartTime), max(EndTime) by EventType
| render ladderchart

Dates of storms by state and event type

This query outputs a visualization of the duration of various storm events in states starting with “W”, displayed as a ladder chart to help you analyze the temporal distribution of these events by state and event type.

StormEvents
| where State startswith "W"
| summarize min(StartTime), max(EndTime) by State, EventType
| render ladderchart with (series=State, EventType)

2.1.7 - Line chart visualization

The line chart visual is the most basic type of chart. The first column of the query should be numeric and is used as the x-axis. Other numeric columns are the y-axes. Line charts track changes over short and long periods of time. When smaller changes exist, line graphs are more useful than bar graphs.

Syntax

T | render linechart [with ( propertyName = propertyValue [, …] )]

Parameters

Supported properties

All properties are optional.

ysplit property

This visualization supports splitting into multiple y-axis values:

Examples

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

Render a line chart

This query retrieves storm events in Virginia, focusing on the start time and property damage, and then displays this information in a line chart.

StormEvents
| where State=="VIRGINIA"
| project StartTime, DamageProperty
| render linechart 

Label a line chart

This query retrieves storm events in Virginia, focusing on the start time and property damage, and then displays this information in a line chart with specified titles for better clarity and presentation.

StormEvents
| where State=="VIRGINIA"
| project StartTime, DamageProperty
| render linechart
    with (
    title="Property damage from storms in Virginia",
    xtitle="Start time of storm",
    ytitle="Property damage"
    )

Limit values displayed on the y-axis

This query retrieves storm events in Virginia, focusing on the start time and property damage, and then displays this information in a line chart with specified y-axis limits for better visualization of the data.

StormEvents
| where State=="VIRGINIA"
| project StartTime, DamageProperty
| render linechart with (ymin=7000, ymax=300000)

View multiple y-axes

This query retrieves hail events in Texas, Nebraska, and Kansas. It counts the number of hail events per day for each state, and then displays this information in a line chart with separate panels for each state.

StormEvents
| where State in ("TEXAS", "NEBRASKA", "KANSAS") and EventType == "Hail"
| summarize count() by State, bin(StartTime, 1d)
| render linechart with (ysplit=panels)

Related content

• Add a query visualization in the web UI.

2.1.8 - Pie chart visualization

The pie chart visual needs a minimum of two columns in the query result. By default, the first column is used as the color axis. This column can contain text, datetime, or numeric data types. Other columns will be used to determine the size of each slice and contain numeric data types. Pie charts are used for presenting a composition of categories and their proportions out of a total.

The pie chart visual can also be used in the context of Geospatial visualizations.

Syntax

T | render piechart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

kind property

This visualization can be further elaborated by providing the kind property. The supported values of this property are:

Example

This query provides a visual representation of the top 10 states with the highest number of storm events, displayed as a pie chart

StormEvents
| summarize statecount=count() by State
| sort by statecount 
| limit 10
| render piechart with(title="Storm Events by State")

2.1.9 - Pivot chart visualization

Displays a pivot table and chart. You can interactively select data, columns, rows, and various chart types.

Syntax

T | render pivotchart

Parameters

Example

This query provides a detailed analysis of sales for Contoso computer products within the specified date range, visualized as a pivot chart.

SalesFact
| join kind= inner Products on ProductKey
| where ProductCategoryName has "Computers" and ProductName has "Contoso"
| where DateKey between (datetime(2006-12-31) .. datetime(2007-02-01))
| project SalesAmount, ProductName, DateKey
| render pivotchart

Output

2.1.10 - Plotly visualization

The Plotly graphics library supports ~80 chart types that are useful for advanced charting including geographic, scientific, machine learning, 3d, animation, and many other chart types. For more information, see Plotly.

To render a Plotly visual in Kusto Query Language, the query must generate a table with a single string cell containing Plotly JSON. This Plotly JSON string can be generated by one of the following two methods:

• Write your own Plotly visualization in Python
• Use a preprepared Plotly template

Write your own Plotly visualization in Python

In this method, you dynamically create the Plotly JSON string in Python using the Plotly package. This process requires use of the python() plugin. The Python script is run on the existing nodes using the inline python() plugin. It generates a Plotly JSON that is rendered by the client application.

All types of Plotly visualizations are supported.

Example

The following query uses inline Python to create a 3D scatter chart:

OccupancyDetection
| project Temperature, Humidity, CO2, Occupancy
| where rand() < 0.1
| evaluate python(typeof(plotly:string),
```if 1:
    import plotly.express as px
    fig = px.scatter_3d(df, x='Temperature', y='Humidity', z='CO2', color='Occupancy')
    fig.update_layout(title=dict(text="Occupancy detection, plotly 5.11.0"))
    plotly_obj = fig.to_json()
    result = pd.DataFrame(data = [plotly_obj], columns = ["plotly"])
```)

The Plotly graphics library supports ~80 chart types including basic charts, scientific, statistical, financial, maps, 3D, animations, and more. To render a Plotly visual in KQL, the query must generate a table with a single string cell containing Plotly JSON.

Since python isn’t available in this service, you create this Plotly JSON using a preprepared template.

Use a preprepared Plotly template

In this method, a preprepared Plotly JSON for specific visualization can be reused by replacing the data objects with the required data to be rendered. The templates can be stored in a standard table, and the data replacement logic can be packed in a stored function.

Currently, the supported templates are: plotly_anomaly_fl() and plotly_scatter3d_fl(). Refer to these documents for syntax and usage.

Example

let plotly_scatter3d_fl=(tbl:(*), x_col:string, y_col:string, z_col:string, aggr_col:string='', chart_title:string='3D Scatter chart')
{
    let scatter3d_chart = toscalar(PlotlyTemplate | where name == "scatter3d" | project plotly);
    let tbl_ex = tbl | extend _x = column_ifexists(x_col, 0.0), _y = column_ifexists(y_col, 0.0), _z = column_ifexists(z_col, 0.0), _aggr = column_ifexists(aggr_col, 'ALL');
    tbl_ex
    | serialize 
    | summarize _x=pack_array(make_list(_x)), _y=pack_array(make_list(_y)), _z=pack_array(make_list(_z)) by _aggr
    | summarize _aggr=make_list(_aggr), _x=make_list(_x), _y=make_list(_y), _z=make_list(_z)
    | extend plotly = scatter3d_chart
    | extend plotly=replace_string(plotly, '$CLASS1$', tostring(_aggr[0]))
    | extend plotly=replace_string(plotly, '$CLASS2$', tostring(_aggr[1]))
    | extend plotly=replace_string(plotly, '$CLASS3$', tostring(_aggr[2]))
    | extend plotly=replace_string(plotly, '$X_NAME$', x_col)
    | extend plotly=replace_string(plotly, '$Y_NAME$', y_col)
    | extend plotly=replace_string(plotly, '$Z_NAME$', z_col)
    | extend plotly=replace_string(plotly, '$CLASS1_X$', tostring(_x[0]))
    | extend plotly=replace_string(plotly, '$CLASS1_Y$', tostring(_y[0]))
    | extend plotly=replace_string(plotly, '$CLASS1_Z$', tostring(_z[0]))
    | extend plotly=replace_string(plotly, '$CLASS2_X$', tostring(_x[1]))
    | extend plotly=replace_string(plotly, '$CLASS2_Y$', tostring(_y[1]))
    | extend plotly=replace_string(plotly, '$CLASS2_Z$', tostring(_z[1]))
    | extend plotly=replace_string(plotly, '$CLASS3_X$', tostring(_x[2]))
    | extend plotly=replace_string(plotly, '$CLASS3_Y$', tostring(_y[2]))
    | extend plotly=replace_string(plotly, '$CLASS3_Z$', tostring(_z[2]))
    | extend plotly=replace_string(plotly, '$TITLE$', chart_title)
    | project plotly
};
Iris
| invoke plotly_scatter3d_fl(x_col='SepalLength', y_col='PetalLength', z_col='SepalWidth', aggr_col='Class', chart_title='3D scatter chart using plotly_scatter3d_fl()')
| render plotly

Related content

• plotly_anomaly_fl()
• plotly_scatter3d_fl()

2.1.11 - Scatter chart visualization

In a scatter chart visual, the first column is the x-axis and should be a numeric column. Other numeric columns are y-axes. Scatter plots are used to observe relationships between variables. The scatter chart visual can also be used in the context of Geospatial visualizations.

Syntax

T | render scatterchart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

kind property

This visualization can be further elaborated by providing the kind property. The supported values of this property are:

Example

This query provides a scatter chart that helps you analyze the correlation between state populations and the total property damage caused by storm events.

StormEvents
| summarize sum(DamageProperty)by State
| lookup PopulationData on State
| project-away State
| render scatterchart with (xtitle="State population", title="Property damage by state", legend=hidden)

2.1.12 - Stacked area chart visualization

The stacked area chart visual shows a continuous relationship. This visual is similar to the Area chart, but shows the area under each element of a series. The first column of the query should be numeric and is used as the x-axis. Other numeric columns are the y-axes. Unlike line charts, area charts also visually represent volume. Area charts are ideal for indicating the change among different datasets.

Syntax

T | render stackedareachart [with (propertyName = propertyValue [, …])]

Supported parameters

Supported properties

All properties are optional.

Example

The following query summarizes data from the nyc_taxi table by number of passengers and visualizes the data in a stacked area chart. The x-axis shows the pickup time in two day intervals, and the stacked areas represent different passenger counts.

nyc_taxi
| summarize count() by passenger_count, bin(pickup_datetime, 2d)
| render stackedareachart with (xcolumn=pickup_datetime, series=passenger_count)

Output

Related content

• render operator
• bin()
• summarize operator

2.1.13 - Table visualization

Default - results are shown as a table.

Syntax

T | render table [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

Example

This query outputs a snapshot of the first 10 storm event records, displayed in a table format.

StormEvents
| take 10 
| render table 

2.1.14 - Time chart visualization

A time chart visual is a type of line graph. The first column of the query is the x-axis, and should be a datetime. Other numeric columns are y-axes. One string column values are used to group the numeric columns and create different lines in the chart. Other string columns are ignored. The time chart visual is like a line chart except the x-axis is always time.

Syntax

T | render timechart [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

ysplit property

This visualization supports splitting into multiple y-axis values:

Examples

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

Render a timechart

The following example renders a timechart with a title “Web app. traffic over a month, decomposing” that decomposes the data into baseline, seasonal, trend, and residual components.

let min_t = datetime(2017-01-05);
let max_t = datetime(2017-02-03 22:00);
let dt = 2h;
demo_make_series2
| make-series num=avg(num) on TimeStamp from min_t to max_t step dt by sid 
| where sid == 'TS1'   //  select a single time series for a cleaner visualization
| extend (baseline, seasonal, trend, residual) = series_decompose(num, -1, 'linefit')  //  decomposition of a set of time series to seasonal, trend, residual, and baseline (seasonal+trend)
| render timechart with(title='Web app. traffic over a month, decomposition')

Label a timechart

The following example renders a timechart that depicts crop damage grouped by week. The timechart x axis label is “Date” and the y axis label is “Crop damage.”

StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31)) 
    and DamageCrops > 0
| summarize EventCount = count() by bin(StartTime, 7d)
| render timechart
    with (
    title="Crop damage over time",
    xtitle="Date",
    ytitle="Crop damage",
    legend=hidden
    )

View multiple y-axes

The following example renders daily hail events in the states of Texas, Nebraska, and Kansas. The visualization uses the ysplit property to render each state’s events in separate panels for comparison.

StormEvents
| where State in ("TEXAS", "NEBRASKA", "KANSAS") and EventType == "Hail"
| summarize count() by State, bin(StartTime, 1d)
| render timechart with (ysplit=panels)

Related content

• Add a query visualization in the web UI

Supported properties

All properties are optional.

Example

The following example renders a timechart with a title “Web app. traffic over a month, decomposing” that decomposes the data into baseline, seasonal, trend, and residual components.

let min_t = datetime(2017-01-05);
let max_t = datetime(2017-02-03 22:00);
let dt = 2h;
demo_make_series2
| make-series num=avg(num) on TimeStamp from min_t to max_t step dt by sid 
| where sid == 'TS1'   //  select a single time series for a cleaner visualization
| extend (baseline, seasonal, trend, residual) = series_decompose(num, -1, 'linefit')  //  decomposition of a set of time series to seasonal, trend, residual, and baseline (seasonal+trend)
| render timechart with(title='Web app. traffic of a month, decomposition')

2.1.15 - Time pivot visualization

The time pivot visualization is an interactive navigation over the events time-line pivoting on time axis.

Syntax

T | render timepivot [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

Example

This query outputs a visualization of flood events in the specified Midwestern states, displayed as a time pivot chart.

let midwesternStates = dynamic([
    "ILLINOIS", "INDIANA", "IOWA", "KANSAS", "MICHIGAN", "MINNESOTA",
    "MISSOURI", "NEBRASKA", "NORTH DAKOTA", "OHIO", "SOUTH DAKOTA", "WISCONSIN"
]);
StormEvents
| where EventType == "Flood" and State in (midwesternStates)
| render timepivot with (xcolumn=State)

Output

:::image type=“content” source=“media/visualization-timepivot/time-pivot-visualization.jpg” lightbox=“media/visualization-timepivot/time-pivot-visualization.jpg” alt-text=“Screenshot of timepivot in Kusto.Explorer.”:::

2.1.16 - Treemap visualization

Treemaps display hierarchical data as a set of nested rectangles. Each level of the hierarchy is represented by a colored rectangle (branch) containing smaller rectangles (leaves).

Syntax

T | render treemap [with (propertyName = propertyValue [, …])]

Parameters

Supported properties

All properties are optional.

Example

This query counts the number of storm events for each type and state, sorts them in descending order, limits the results to the top 30, and then visualizes the data as a treemap.

StormEvents
| summarize StormEvents=count() by EventType, State
| sort by StormEvents
| limit 30
| render treemap with(title="Storm Events by EventType and State")

2.2 - render operator

Instructs the user agent to render a visualization of the query results.

The render operator must be the last operator in the query, and can only be used with queries that produce a single tabular data stream result. The render operator doesn’t modify data. It injects an annotation (“Visualization”) into the result’s extended properties. The annotation contains the information provided by the operator in the query. The interpretation of the visualization information is done by the user agent. Different agents, such as Kusto.Explorer or Azure Data Explorer web UI, may support different visualizations.

The data model of the render operator looks at the tabular data as if it has three kinds of columns:

• The x axis column (indicated by the xcolumn property).

• The series columns (any number of columns indicated by the series property.) For each record, the combined values of these columns define a single series, and the chart has as many series as there are distinct combined values.

• The y axis columns (any number of columns indicated by the ycolumns property). For each record, the series has as many measurements (“points” in the chart) as there are y-axis columns.

  by the query. In particular, having “uninteresting” columns in the schema of the result might translate into them guessing wrong. Try projecting-away such columns when that happens.

Syntax

T | render visualization [with ( propertyName = propertyValue [, …])]

Parameters

Visualization

Supported properties

PropertyName/PropertyValue indicate additional information to use when rendering. All properties are optional. The supported properties are:

kind property

This visualization can be further elaborated by providing the kind property. The supported values of this property are:

ysplit property

Some visualizations support splitting into multiple y-axis values:

How to render continuous data

Several visualizations are used for rendering sequences of values, for example, linechart, timechart, and areachart. These visualizations have the following conceptual model:

• One column in the table represents the x-axis of the data. This column can be explicitly defined using the xcolumn property. If not defined, the user agent picks the first column that is appropriate for the visualization.
  • For example: in the timechart visualization, the user agent uses the first datetime column.
  • If this column is of type dynamic and it holds an array, the individual values in the array will be treated as the values of the x-axis.
• One or more columns in the table represent one or more measures that vary by the x-axis. These columns can be explicitly defined using the ycolumns property. If not defined, the user agent picks all columns that are appropriate for the visualization.
  • For example: in the timechart visualization, the user agent uses all columns with a numeric value that haven’t been specified otherwise.
  • If the x-axis is an array, the values of each y-axis should also be an array of a similar length, with each y-axis occurring in a single column.
• Zero or more columns in the table represent a unique set of dimensions that group together the measures. These columns can be specified by the series property, or the user agent will pick them automatically from the columns that are otherwise unspecified.

Related content

• Add a query visualization in the web UI
• Customize dashboard visuals
• Rendering examples in the tutorial
• Anomaly detection

three kinds of columns: property). For each record, the series has as many measurements (“points” in the chart) as there are y-axis columns.

Example

InsightsMetrics
| where Computer == "DC00.NA.contosohotels.com"
| where Namespace  == "Processor" and Name == "UtilizationPercentage"
| summarize avg(Val) by Computer, bin(TimeGenerated, 1h)
| render timechart

3 - Summarize operator

3.1 - Kusto partition & compose intermediate aggregation results

Suppose you want to calculate the count of distinct users every day over the last seven days. You can run summarize dcount(user) once a day with a span filtered to the last seven days. This method is inefficient, because each time the calculation is run, there’s a six-day overlap with the previous calculation. You can also calculate an aggregate for each day, and then combine these aggregates. This method requires you to “remember” the last six results, but it’s much more efficient.

Partitioning queries as described is easy for simple aggregates, such as count() and sum(). It can also be useful for complex aggregates, such as dcount() and percentiles(). This article explains how Kusto supports such calculations.

The following examples show how to use hll/tdigest and demonstrate that using these commands is highly performant in some scenarios:

range x from 1 to 1000000 step 1
| summarize hll(x,4)
| project sizeInMb = estimate_data_size(hll_x) / pow(1024,2)

Output

Ingesting this object into a table before applying this kind of policy will ingest null:

.set-or-append MyTable <| range x from 1 to 1000000 step 1
| summarize hll(x,4)

MyTable
| project isempty(hll_x)

Output

To avoid ingesting null, use the special encoding policy type bigobject, which overrides the MaxValueSize to 2 MB like this:

.alter column MyTable.hll_x policy encoding type='bigobject'

Ingesting a value now to the same table above:

.set-or-append MyTable <| range x from 1 to 1000000 step 1
| summarize hll(x,4)

ingests the second value successfully:

MyTable
| project isempty(hll_x)

Output

Example: Count with binned timestamp

There’s a table, PageViewsHllTDigest, containing hll values of Pages viewed in each hour. You want these values binned to 12h. Merge the hll values using the hll_merge() aggregate function, with the timestamp binned to 12h. Use the function dcount_hll to return the final dcount value:

PageViewsHllTDigest
| summarize merged_hll = hll_merge(hllPage) by bin(Timestamp, 12h)
| project Timestamp , dcount_hll(merged_hll)

Output

To bin timestamp for 1d:

PageViewsHllTDigest
| summarize merged_hll = hll_merge(hllPage) by bin(Timestamp, 1d)
| project Timestamp , dcount_hll(merged_hll)

Output

The same query may be done over the values of tdigest, which represent the BytesDelivered in each hour:

PageViewsHllTDigest
| summarize merged_tdigests = merge_tdigest(tdigestBytesDel) by bin(Timestamp, 12h)
| project Timestamp , percentile_tdigest(merged_tdigests, 95, typeof(long))

Output

Example: Temporary table

Kusto limits are reached with datasets that are too large, where you need to run periodic queries over the dataset, but run the regular queries to calculate percentile() or dcount() over large datasets.

To solve this problem, newly added data may be added to a temp table as hll or tdigest values using hll() when the required operation is dcount or tdigest() when the required operation is percentile using set/append or update policy. In this case, the intermediate results of dcount or tdigest are saved into another dataset, which should be smaller than the target large one.

To solve this problem, newly added data may be added to a temp table as hll or tdigest values using hll() when the required operation is dcount. In this case, the intermediate results of dcount are saved into another dataset, which should be smaller than the target large one.

When you need to get the final results of these values, the queries may use hll/tdigest mergers: hll-merge()/tdigest_merge(). Then, after getting the merged values, percentile_tdigest() / dcount_hll() may be invoked on these merged values to get the final result of dcount or percentiles.

Assuming there’s a table, PageViews, into which data is ingested daily, every day on which you want to calculate the distinct count of pages viewed per minute later than date = datetime(2016-05-01 18:00:00.0000000).

Run the following query:

PageViews
| where Timestamp > datetime(2016-05-01 18:00:00.0000000)
| summarize percentile(BytesDelivered, 90), dcount(Page,2) by bin(Timestamp, 1d)

Output

This query aggregates all the values every time you run this query (for example, if you want to run it many times a day).

If you save the hll and tdigest values (which are the intermediate results of dcount and percentile) into a temp table, PageViewsHllTDigest, using an update policy or set/append commands, you may only merge the values and then use dcount_hll/percentile_tdigest using the following query:

PageViewsHllTDigest
| summarize  percentile_tdigest(merge_tdigest(tdigestBytesDel), 90), dcount_hll(hll_merge(hllPage)) by bin(Timestamp, 1d)

Output

This query should be more performant, as it runs over a smaller table. In this example, the first query runs over ~215M records, while the second one runs over just 32 records:

Example: Intermediate results

The Retention Query. Assume you have a table that summarizes when each Wikipedia page was viewed (sample size is 10M), and you want to find for each date1 date2 the percentage of pages reviewed in both date1 and date2 relative to the pages viewed on date1 (date1 < date2).

The trivial way uses join and summarize operators:

// Get the total pages viewed each day
let totalPagesPerDay = PageViewsSample
| summarize by Page, Day = startofday(Timestamp)
| summarize count() by Day;
// Join the table to itself to get a grid where 
// each row shows foreach page1, in which two dates
// it was viewed.
// Then count the pages between each two dates to
// get how many pages were viewed between date1 and date2.
PageViewsSample
| summarize by Page, Day1 = startofday(Timestamp)
| join kind = inner
(
    PageViewsSample
    | summarize by Page, Day2 = startofday(Timestamp)
)
on Page
| where Day2 > Day1
| summarize count() by Day1, Day2
| join kind = inner
    totalPagesPerDay
on $left.Day1 == $right.Day
| project Day1, Day2, Percentage = count_*100.0/count_1

Output

The above query took ~18 seconds.

When you use the hll(), hll_merge(), and dcount_hll() functions, the equivalent query will end after ~1.3 seconds and show that the hll functions speeds up the query above by ~14 times:

let Stats=PageViewsSample | summarize pagehll=hll(Page, 2) by day=startofday(Timestamp); // saving the hll values (intermediate results of the dcount values)
let day0=toscalar(Stats | summarize min(day)); // finding the min date over all dates.
let dayn=toscalar(Stats | summarize max(day)); // finding the max date over all dates.
let daycount=tolong((dayn-day0)/1d); // finding the range between max and min
Stats
| project idx=tolong((day-day0)/1d), day, pagehll
| mv-expand pidx=range(0, daycount) to typeof(long)
// Extend the column to get the dcount value from hll'ed values for each date (same as totalPagesPerDay from the above query)
| extend key1=iff(idx < pidx, idx, pidx), key2=iff(idx < pidx, pidx, idx), pages=dcount_hll(pagehll)
// For each two dates, merge the hll'ed values to get the total dcount over each two dates, 
// This helps to get the pages viewed in both date1 and date2 (see the description below about the intersection_size)
| summarize (day1, pages1)=arg_min(day, pages), (day2, pages2)=arg_max(day, pages), union_size=dcount_hll(hll_merge(pagehll)) by key1, key2
| where day2 > day1
// To get pages viewed in date1 and also date2, look at the merged dcount of date1 and date2, subtract it from pages of date1 + pages on date2.
| project pages1, day1,day2, intersection_size=(pages1 + pages2 - union_size)
| project day1, day2, Percentage = intersection_size*100.0 / pages1

Output

3.2 - summarize operator

Produces a table that aggregates the content of the input table.

Syntax

T | summarize [ SummarizeParameters ] [[Column =] Aggregation [, …]] [by [Column =] GroupExpression [, …]]

Parameters

Supported parameters

Returns

The input rows are arranged into groups having the same values of the by expressions. Then the specified aggregation functions are computed over each group, producing a row for each group. The result contains the by columns and also at least one column for each computed aggregate. (Some aggregation functions return multiple columns.)

The result has as many rows as there are distinct combinations of by values (which may be zero). If there are no group keys provided, the result has a single record.

To summarize over ranges of numeric values, use bin() to reduce ranges to discrete values.

Default values of aggregations

The following table summarizes the default values of aggregations:

Examples

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

Unique combination

The following query determines what unique combinations of State and EventType there are for storms that resulted in direct injury. There are no aggregation functions, just group-by keys. The output will just show the columns for those results.

StormEvents
| where InjuriesDirect > 0
| summarize by State, EventType

Output

The following table shows only the first 5 rows. To see the full output, run the query.

Minimum and maximum timestamp

Finds the minimum and maximum heavy rain storms in Hawaii. There’s no group-by clause, so there’s just one row in the output.

StormEvents
| where State == "HAWAII" and EventType == "Heavy Rain"
| project Duration = EndTime - StartTime
| summarize Min = min(Duration), Max = max(Duration)

Output

Distinct count

The following query calculates the number of unique storm event types for each state and sorts the results by the number of unique storm types:

StormEvents
| summarize TypesOfStorms=dcount(EventType) by State
| sort by TypesOfStorms

Output

The following table shows only the first 5 rows. To see the full output, run the query.

Histogram

The following example calculates a histogram storm event types that had storms lasting longer than 1 day. Because Duration has many values, use bin() to group its values into 1-day intervals.

StormEvents
| project EventType, Duration = EndTime - StartTime
| where Duration > 1d
| summarize EventCount=count() by EventType, Length=bin(Duration, 1d)
| sort by Length

Output

Aggregates default values

When the input of summarize operator has at least one empty group-by key, its result is empty, too.

When the input of summarize operator doesn’t have an empty group-by key, the result is the default values of the aggregates used in the summarize For more information, see Default values of aggregations.

datatable(x:long)[]
| summarize any_x=take_any(x), arg_max_x=arg_max(x, *), arg_min_x=arg_min(x, *), avg(x), buildschema(todynamic(tostring(x))), max(x), min(x), percentile(x, 55), hll(x) ,stdev(x), sum(x), sumif(x, x > 0), tdigest(x), variance(x)

Output

The result of avg_x(x) is NaN due to dividing by 0.

datatable(x:long)[]
| summarize  count(x), countif(x > 0) , dcount(x), dcountif(x, x > 0)

Output

datatable(x:long)[]
| summarize  make_set(x), make_list(x)

Output

The aggregate avg sums all the non-nulls and counts only those which participated in the calculation (won’t take nulls into account).

range x from 1 to 4 step 1
| extend y = iff(x == 1, real(null), real(5))
| summarize sum(y), avg(y)

Output

The regular count will count nulls:

range x from 1 to 2 step 1
| extend y = iff(x == 1, real(null), real(5))
| summarize count(y)

Output

range x from 1 to 2 step 1
| extend y = iff(x == 1, real(null), real(5))
| summarize make_set(y), make_set(y)

Output

4 - as operator

Binds a name to the operator’s input tabular expression. This operator allows the query to reference the value of the tabular expression multiple times without breaking the query and binding a name through the let statement.

To optimize multiple uses of the as operator within a single query, see Named expressions.

Syntax

T | as [hint.materialized = Materialized] Name

Parameters

Examples

In the following two examples, the generated TableName column consists of ‘T1’ and ‘T2’.

range x from 1 to 5 step 1 
| as T1 
| union withsource=TableName (range x from 1 to 5 step 1 | as T2)

Alternatively, you can write the same example as follows:

union withsource=TableName (range x from 1 to 5 step 1 | as T1), (range x from 1 to 5 step 1 | as T2)

Output

In the following example, the ’left side’ of the join is: MyLogTable filtered by type == "Event" and Name == "Start" and the ‘right side’ of the join is: MyLogTable filtered by type == "Event" and Name == "Stop"

MyLogTable  
| where type == "Event"
| as T
| where Name == "Start"
| join (
    T
    | where Name == "Stop"
) on ActivityId

5 - consume operator

Consumes the tabular data stream handed to the operator.

The consume operator is mostly used for triggering the query side-effect without actually returning the results back to the caller.

The consume operator can be used for estimating the cost of a query without actually delivering the results back to the client. (The estimation isn’t exact for various reasons; for example, consume is calculated distributively, so T | consume won’t transmit the table’s data between the nodes of the cluster.)

Syntax

consume [decodeblocks = DecodeBlocks]

Parameters

6 - count operator

Returns the number of records in the input record set.

Syntax

T | count

Parameters

Returns

This function returns a table with a single record and column of type long. The value of the only cell is the number of records in T.

Example

When you use the count operator with a table name, like StormEvents, it will return the total number of records in that table.

StormEvents | count

Output

Related content

For information about the count() aggregation function, see count() (aggregation function).

7 - datatable operator

Returns a table whose schema and values are defined in the query itself.

Syntax

datatable( ColumnName : ColumnType [, …]) [ ScalarValue [, …] ]

Parameters

Returns

This operator returns a data table of the given schema and data.

Example

This example creates a table with Date, Event, and MoreData columns, filters rows with Event descriptions longer than 4 characters, and adds a new column key2 to each row from the MoreData dynamic object.

datatable(Date:datetime, Event:string, MoreData:dynamic) [
    datetime(1910-06-11), "Born", dynamic({"key1":"value1", "key2":"value2"}),
    datetime(1930-01-01), "Enters Ecole Navale", dynamic({"key1":"value3", "key2":"value4"}),
    datetime(1953-01-01), "Published first book", dynamic({"key1":"value5", "key2":"value6"}),
    datetime(1997-06-25), "Died", dynamic({"key1":"value7", "key2":"value8"}),
]
| where strlen(Event) > 4
| extend key2 = MoreData.key2

Output

8 - distinct operator

Produces a table with the distinct combination of the provided columns of the input table.

Syntax

T | distinct ColumnName[,ColumnName2, ...]

Parameters

Example

Shows distinct combination of states and type of events that led to over 45 direct injuries.

StormEvents
| where InjuriesDirect > 45
| distinct State, EventType

Output

Related content

If the group by keys are of high cardinalities, try summarize by ... with the shuffle strategy.

9 - evaluate plugin operator

Invokes a service-side query extension (plugin).

The evaluate operator is a tabular operator that allows you to invoke query language extensions known as plugins. Unlike other language constructs, plugins can be enabled or disabled. Plugins aren’t “bound” by the relational nature of the language. In other words, they may not have a predefined, statically determined, output schema.

Syntax

[T |] evaluate [ evaluateParameters ] PluginName ([ PluginArgs ])

Parameters

Evaluate parameters

The following parameters are supported:

Plugins

The following plugins are supported:

• autocluster plugin
• azure-digital-twins-query-request plugin
• bag-unpack plugin
• basket plugin
• cosmosdb-sql-request plugin
• dcount-intersect plugin
• diffpatterns plugin
• diffpatterns-text plugin
• infer-storage-schema plugin
• ipv4-lookup plugin
• ipv6-lookup plugin
• mysql-request-plugin
• narrow plugin
• pivot plugin
• preview plugin
• R plugin
• rolling-percentile plugin
• rows-near plugin
• schema-merge plugin
• sql-request plugin
• sequence-detect plugin

Distribution hints

Distribution hints specify how the plugin execution will be distributed across multiple cluster nodes. Each plugin may implement a different support for the distribution. The plugin’s documentation specifies the distribution options supported by the plugin.

Possible values:

• single: A single instance of the plugin will run over the entire query data.
• per_node: If the query before the plugin call is distributed across nodes, then an instance of the plugin will run on each node over the data that it contains.
• per_shard: If the data before the plugin call is distributed across shards, then an instance of the plugin will run over each shard of the data.

10 - extend operator

Creates calculated columns and append them to the result set.

Syntax

T | extend [ColumnName | (ColumnName[, …]) =] Expression [, …]

Parameters

• If ColumnName is omitted, the output column name of Expression is automatically generated.
• If Expression returns more than one column, a list of column names can be specified in parentheses. Then, Expression’s output columns is given the specified names. If a list of the column names isn’t specified, all Expression’s output columns with generated names are added to the output.

Returns

A copy of the input tabular result set, such that:

1. Column names noted by extend that already exist in the input are removed and appended as their new calculated values.
2. Column names noted by extend that don’t exist in the input are appended as their new calculated values.

not have an index. In most cases, if the new column is set to be exactly the same as an existing table column that has an index, Kusto can automatically use the existing index. However, in some complex scenarios this propagation is not done. In such cases, if the goal is to rename a column, use the project-rename operator instead.

Example

StormEvents
| project EndTime, StartTime
| extend Duration = EndTime - StartTime

The following table shows only the first 10 results. To see the full output, run the query.

Related content

• Use series_stats to return multiple columns

11 - externaldata operator

The externaldata operator returns a table whose schema is defined in the query itself, and whose data is read from an external storage artifact, such as a blob in Azure Blob Storage or a file in Azure Data Lake Storage.

Syntax

externaldata (columnName:columnType [, …] ) [ storageConnectionString [, …] ] [with ( propertyName = propertyValue [, …])]

Parameters

Supported properties

Returns

The externaldata operator returns a data table of the given schema whose data was parsed from the specified storage artifact, indicated by the storage connection string.

Examples

The examples query data in an external storage file.

Fetch a list of user IDs stored in Azure Blob Storage

The following example shows how to find all records in a table whose UserID column falls into a known set of IDs, held (one per line) in an external storage file. Since the data format isn’t specified, the detected data format is TXT.

Users
| where UserID in ((externaldata (UserID:string) [
    @"https://storageaccount.blob.core.windows.net/storagecontainer/users.txt" 
      h@"?...SAS..." // Secret token needed to access the blob
    ]))
| ...

Query multiple data files

The following example queries multiple data files stored in external storage.

externaldata(Timestamp:datetime, ProductId:string, ProductDescription:string)
[
  h@"https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/01/part-00000-7e967c99-cf2b-4dbb-8c53-ce388389470d.csv.gz?...SAS...",
  h@"https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/02/part-00000-ba356fa4-f85f-430a-8b5a-afd64f128ca4.csv.gz?...SAS...",
  h@"https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/03/part-00000-acb644dc-2fc6-467c-ab80-d1590b23fc31.csv.gz?...SAS..."
]
with(format="csv")
| summarize count() by ProductId

The above example can be thought of as a quick way to query multiple data files without defining an external table.

Query hierarchical data formats

To query hierarchical data format, such as JSON, Parquet, Avro, or ORC, ingestionMapping must be specified in the operator properties. In this example, there’s a JSON file stored in Azure Blob Storage with the following contents:

{
  "timestamp": "2019-01-01 10:00:00.238521",   
  "data": {    
    "tenant": "e1ef54a6-c6f2-4389-836e-d289b37bcfe0",   
    "method": "RefreshTableMetadata"   
  }   
}   
{
  "timestamp": "2019-01-01 10:00:01.845423",   
  "data": {   
    "tenant": "9b49d0d7-b3e6-4467-bb35-fa420a25d324",   
    "method": "GetFileList"   
  }   
}
...

To query this file using the externaldata operator, a data mapping must be specified. The mapping dictates how to map JSON fields to the operator result set columns:

externaldata(Timestamp: datetime, TenantId: guid, MethodName: string)
[ 
   h@'https://mycompanystorage.blob.core.windows.net/events/2020/09/01/part-0000046c049c1-86e2-4e74-8583-506bda10cca8.json?...SAS...'
]
with(format='multijson', ingestionMapping='[{"Column":"Timestamp","Properties":{"Path":"$.timestamp"}},{"Column":"TenantId","Properties":{"Path":"$.data.tenant"}},{"Column":"MethodName","Properties":{"Path":"$.data.method"}}]')

The MultiJSON format is used here because single JSON records are spanned into multiple lines.

Related content

For more info on mapping syntax, see data mappings.

12 - facet operator

Returns a set of tables, one for each column specified in the facet clause. Each table contains the list of values taken by its column. An additional table can be created by using the with clause. Facet result tables can’t be renamed or referenced by any additional operators.

Syntax

T | facet by ColumnName [, ColumnName2, …] [with ( filterPipe )]

Parameters

Returns

Multiple tables: one for the with clause, and one for each column.

Example

StormEvents
| where State startswith "A" and EventType has "Heavy"
| facet by State, EventType
    with 
    (
    where StartTime between(datetime(2007-01-04) .. 7d) 
    | project State, StartTime, Source, EpisodeId, EventType
    | take 5
    )

The following is the table generated by the with clause.

The following table is the State facet output table.

The following table is the EventType facet output table.

13 - find operator

Finds rows that match a predicate across a set of tables.

The scope of the find operator can also be cross-database or cross-cluster.

find in (Table1, Table2, Table3) where Fruit=="apple"

find in (database('*').*) where Fruit == "apple"

find in (cluster('cluster_name').database('MyDB*').*) where Fruit == "apple"

find in (Table1, Table2, Table3) where Fruit=="apple"

Syntax

• find [withsource= ColumnName] [in (Tables)] where Predicate [project-smart | project ColumnName[: ColumnType , … ] [, pack_all()]]

• find Predicate [project-smart | project ColumnName[: ColumnType , … ] [, pack_all()]]

Parameters

• withsource=ColumnName: Optional. By default, the output includes a column called source_ whose values indicate which source table contributed each row. If specified, ColumnName is used instead of source_.

• Predicate: A boolean expression over the columns of the input tables Table [, Table, …]. It’s evaluated for each row in each input table. For more information, see predicate-syntax details.

• Tables: Optional. Zero or more comma-separated table references. By default find searches all tables for:

  • The name of a table, such as Events
  • A query expression, such as (Events | where id==42)
  • A set of tables specified with a wildcard. For example, E* would form the union of all the tables whose names begin with E.

• project-smart | project: If not specified project-smart is used by default. For more information, see output-schema details.

Returns

Transformation of rows in Table [, Table, …] for which Predicate is true. The rows are transformed according to the output schema.

Output schema

source_ column

The find operator output always includes a source_ column with the source table name. The column can be renamed using the withsource parameter.

results columns

Source tables that don’t contain any column used by the predicate evaluation, are filtered out.

When you use project-smart, the columns that appear in the output are:

• Columns that appear explicitly in the predicate.
• Columns that are common to all the filtered tables.

The rest of the columns are packed into a property bag and appear in an extra pack column. A column that is referenced explicitly by the predicate and appears in multiple tables with multiple types, has a different column in the result schema for each such type. Each of the column names is constructed from the original column name and type, separated by an underscore.

When using project ColumnName[: ColumnType , … ] [, pack_all()]:

• The result table includes the columns specified in the list. If a source table doesn’t contain a certain column, the values in the corresponding rows are null.
• When you specify a ColumnType with a ColumnName, this column in the “result” has the given type, and the values are cast to that type if needed. The casting doesn’t have an effect on the column type when evaluating the Predicate.
• When pack_all() is used, all the columns, including the projected columns, are packed into a property bag and appear in an extra column, by default ‘column1’. In the property bag, the source column name serves as the property name and the column’s value serves as the property value.

Predicate syntax

The find operator supports an alternative syntax for the * has term, and using just term, searches a term across all input columns.

For a summary of some filtering functions, see where operator.

Considerations

• If the project clause references a column that appears in multiple tables and has multiple types, a type must follow this column reference in the project clause
• If a column appears in multiple tables and has multiple types and project-smart is in use, there’s a corresponding column for each type in the find’s result, as described in union
• When you use project-smart, changes in the predicate, in the source tables set, or in the tables schema, might result in a change to the output schema. If a constant result schema is needed, use project instead
• find scope can’t include functions. To include a function in the find scope, define a let statement with view keyword.

Performance tips

• Use tables as opposed to tabular expressions. If tabular expression, the find operator falls back to a union query that can result in degraded performance.
• If a column that appears in multiple tables and has multiple types, is part of the project clause, prefer adding a ColumnType to the project clause over modifying the table before passing it to find.
• Add time-based filters to the predicate. Use a datetime column value or ingestion_time().
• Search in specific columns rather than a full text search.
• It’s better not to reference columns that appear in multiple tables and have multiple types. If the predicate is valid when resolving such columns type for more than one type, the query falls back to union. For example, see examples of cases where find acts as a union.

Examples

Term lookup across all tables

The query finds all rows from all tables in the current database in which any column includes the word Hernandez. The resulting records are transformed according to the output schema. The output includes rows from the Customers table and the SalesTable table of the ContosoSales database.

find "Hernandez"

Output

This table shows the first three rows of the output.

Term lookup across all tables matching a name pattern

The query finds all rows from all tables in the current database whose name starts with C, and in which any column includes the word Hernandez. The resulting records are transformed according to the output schema. Now, the output only contains records from the Customers table.

find in (C*) where * has "Hernandez"

Output

This table shows the first three rows of the output.

Term lookup across the cluster

The query finds all rows from all tables in all databases in the cluster in which any column includes the word Kusto. This query is a cross-database query. The resulting records are transformed according to the output schema.

find in (database('*').*) where * has "Kusto"

Output

This table shows the first three rows of the output.

Term lookup matching a name pattern in the cluster

The query finds all rows from all tables whose name starts with K in all databases whose name start with B and in which any column includes the word Kusto. The resulting records are transformed according to the output schema.

find in (database("S*").C*) where * has "Kusto"

Output

This table shows the first three rows of the output.

Term lookup in several clusters

The query finds all rows from all tables whose name starts with K in all databases whose name start with B and in which any column includes the word Kusto. The resulting records are transformed according to the output schema.

find in (cluster("cluster1").database("B*").K*, cluster("cluster2").database("C*".*))
where * has "Kusto"

Term lookup across all tables

The query finds all rows from all tables in which any column includes the word Kusto. The resulting records are transformed according to the output schema.

find "Kusto"

Examples of find output results

The following examples show how find can be used over two tables: EventsTable1 and EventsTable2. Assume we have the next content of these two tables:

EventsTable1

EventsTable2

Search in common columns, project common, and uncommon columns, and pack the rest

The query searches for specific records in EventsTable1 and EventsTable2 based on a given Session_Id and an Error Level. It then projects three specific columns: EventText, Version, and EventName, and packs all other remaining columns into a dynamic object.

find in (EventsTable1, EventsTable2) 
     where Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' and Level == 'Error' 
     project EventText, Version, EventName, pack_all()

Output

Search in common and uncommon columns

The query searches for records that either have Version as ‘v1.0.0’ or EventName as ‘Event1’, and then it projects (selects) four specific columns: Session_Id, EventText, Version, and EventName from those filtered results.

find Version == 'v1.0.0' or EventName == 'Event1' project Session_Id, EventText, Version, EventName

Output

Use abbreviated notation to search across all tables in the current database

This query searches the database for any records with a Session_Id that matches ‘acbd207d-51aa-4df7-bfa7-be70eb68f04e’. It retrieves records from all tables and columns that contain this specific Session_Id.

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e'

Output

Return the results from each row as a property bag

This query searches the database for records with the specified Session_Id and returns all columns of those records as a single dynamic object.

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' project pack_all()

Output

Examples of cases where find acts as union

The find operator in Kusto can sometimes act like a union operator, mainly when it’s used to search across multiple tables.

Using a nontabular expression as find operand

The query first creates a view that filters EventsTable1 to only include error-level records. Then, it searches within this filtered view and the EventsTable2 table for records with a specific Session_Id.

let PartialEventsTable1 = view() { EventsTable1 | where Level == 'Error' };
find in (PartialEventsTable1, EventsTable2) 
     where Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e'

Referencing a column that appears in multiple tables and has multiple types

For this example, create two tables by running:

.create tables 
  Table1 (Level:string, Timestamp:datetime, ProcessId:string),
  Table2 (Level:string, Timestamp:datetime, ProcessId:int64)

• The following query is executed as union.

find in (Table1, Table2) where ProcessId == 1001

The output result schema is (Level:string, Timestamp, ProcessId_string, ProcessId_int).

• The following query is executed as union, but produces a different result schema.

find in (Table1, Table2) where ProcessId == 1001 project Level, Timestamp, ProcessId:string 

The output result schema is (Level:string, Timestamp, ProcessId_string)

14 - fork operator

Runs multiple consumer operators in parallel.

Syntax

T | fork [name=](subquery) [name=](subquery) …

Parameters

Supported query operators

• as
• count
• extend
• parse
• where
• take
• project
• project-away
• project-keep
• project-rename
• project-reorder
• summarize
• top
• top-nested
• sort
• mv-expand
• reduce

Returns

Multiple result tables, one for each of the subquery arguments.

Tips

• Use materialize as a replacement for join or union on fork legs. The input stream is cached by materialize and then the cached expression can be used in join/union legs.

• Use batch with materialize of tabular expression statements instead of the fork operator.

Examples

The examples output multiple tables, with named and umnamed columns.

Unnamed subqueries

StormEvents
| where State == "FLORIDA"
| fork
    ( where DeathsDirect + DeathsIndirect > 1)
    ( where InjuriesDirect + InjuriesIndirect > 1)

Output

This output shows the first few rows and columns of the result table.

GenericResult

GenericResult

Named subqueries

In the following examples, the result table is named “StormsWithDeaths” and “StormsWithInjuries”.

StormEvents
| where State == "FLORIDA"
| fork
    (where DeathsDirect + DeathsIndirect > 1 | as StormsWithDeaths)
    (where InjuriesDirect + InjuriesIndirect > 1 | as StormsWithInjuries)

StormEvents
| where State == "FLORIDA"
| fork
    StormsWithDeaths = (where DeathsDirect + DeathsIndirect > 1)
    StormsWithInjuries = (where InjuriesDirect + InjuriesIndirect > 1)

Output

This output shows the first few rows and columns of the result table.

StormsWithDeaths

StormsWithInjuries

SamplePowerRequirementHistorizedData
| fork
    Dataset2 = (where twinId  <> "p_sol_01" | summarize count() by twinId, name)
    Dataset3 = (summarize count() by WeekOfYear = week_of_year(timestamp))

It is possible to use almost all the known features of the KQL language inside every single “sub” result set. For instance, the join operator inside a sub-statement does not work. This is not allowed by the engine.

15 - getschema operator

Produce a table that represents a tabular schema of the input.

Syntax

T | getschema

Example

StormEvents
| getschema

Output

16 - invoke operator

Invokes a lambda expression that receives the source of invoke as a tabular argument.

Syntax

T | invoke function([param1, param2])

Parameters

Returns

Returns the result of the evaluated expression.

Example

This example shows how to use the invoke operator to call lambda let expression:

// clipped_average(): calculates percentiles limits, and then makes another 
//                    pass over the data to calculate average with values inside the percentiles
let clipped_average = (T:(x: long), lowPercentile:double, upPercentile:double)
{
   let high = toscalar(T | summarize percentiles(x, upPercentile));
   let low = toscalar(T | summarize percentiles(x, lowPercentile));
   T 
   | where x > low and x < high
   | summarize avg(x) 
};
range x from 1 to 100 step 1
| invoke clipped_average(5, 99)

Output

17 - lookup operator

Extends the columns of a fact table with values looked-up in a dimension table.

For example, the following query results in a table that extends the FactTable ($left) with data from the DimensionTable ($right) by performing a lookup. The lookup matches each pair (CommonColumn, Col1) from FactTable with each pair (CommonColumn, Col2) in the DimensionTable. For the differences between fact and dimension tables, see fact and dimension tables.

FactTable | lookup kind=leftouter (DimensionTable) on CommonColumn, $left.Col1 == $right.Col2

The lookup operator performs an operation similar to the join operator with the following differences:

• The result doesn’t repeat columns from the $right table that are the basis for the join operation.
• Only two kinds of lookup are supported, leftouter and inner, with leftouter being the default.
• In terms of performance, the system by default assumes that the $left table is the larger (facts) table, and the $right table is the smaller (dimensions) table. This is exactly opposite to the assumption used by the join operator.
• The lookup operator automatically broadcasts the $right table to the $left table (essentially, behaves as if hint.broadcast was specified). This limits the size of the $right table.

Syntax

LeftTable | lookup [kind = (leftouter|inner)] (RightTable) on Attributes

Parameters

Rules

Returns

A table with:

• A column for every column in each of the two tables, including the matching keys. The columns of the right side are automatically renamed if there are name conflicts.
• A row for every match between the input tables. A match is a row selected from one table that has the same value for all the on fields as a row in the other table.
• The Attributes (lookup keys) appear only once in the output table.
• If kind is unspecified or kind=leftouter, then in addition to the inner matches, there’s a row for every row on the left (and/or right), even if it has no match. In that case, the unmatched output cells contain nulls.
• If kind=inner, then there’s a row in the output for every combination of matching rows from left and right.

Example

The following example shows how to perform a left outer join between the FactTable and DimTable, based on matching values in the Personal and Family columns.

let FactTable=datatable(Row:string,Personal:string,Family:string) [
  "1", "Rowan",   "Murphy",
  "2", "Ellis",   "Turner",
  "3", "Ellis",   "Turner",
  "4", "Maya",  "Robinson",
  "5", "Quinn",    "Campbell"
];
let DimTable=datatable(Personal:string,Family:string,Alias:string) [
  "Rowan",  "Murphy",   "rowanm",
  "Ellis",  "Turner", "ellist",
  "Maya", "Robinson", "mayar",
  "Quinn",   "Campbell",    "quinnc"
];
FactTable
| lookup kind=leftouter DimTable on Personal, Family

Output

Related content

• Broadcast join
• leftouter join
• inner join
• join operator

18 - mv-apply operator

Applies a subquery to each record, and returns the union of the results of all subqueries.

For example, assume a table T has a column Metric of type dynamic whose values are arrays of real numbers. The following query locates the two biggest values in each Metric value, and return the records corresponding to these values.

T | mv-apply Metric to typeof(real) on 
(
   top 2 by Metric desc
)

The mv-apply operator has the following processing steps:

1. Uses the mv-expand operator to expand each record in the input into subtables (order is preserved).
2. Applies the subquery for each of the subtables.
3. Adds zero or more columns to the resulting subtable. These columns contain the values of the source columns that aren’t expanded, and are repeated where needed.
4. Returns the union of the results.

The mv-apply operator gets the following inputs:

1. One or more expressions that evaluate into dynamic arrays to expand. The number of records in each expanded subtable is the maximum length of each of those dynamic arrays. Null values are added where multiple expressions are specified and the corresponding arrays have different lengths.

2. Optionally, the names to assign the values of the expressions after expansion. These names become the columns names in the subtables. If not specified, the original name of the column is used when the expression is a column reference. A random name is used otherwise.

   [!NOTE] It is recommended to use the default column names.

3. The data types of the elements of those dynamic arrays, after expansion. These become the column types of the columns in the subtables. If not specified, dynamic is used.

4. Optionally, the name of a column to add to the subtables that specifies the 0-based index of the element in the array that resulted in the subtable record.

5. Optionally, the maximum number of array elements to expand.

The mv-apply operator can be thought of as a generalization of the mv-expand operator (in fact, the latter can be implemented by the former, if the subquery includes only projections.)

Syntax

T | mv-apply [ItemIndex] ColumnsToExpand [RowLimit] on ( SubQuery )

Where ItemIndex has the syntax:

with_itemindex = IndexColumnName

ColumnsToExpand is a comma-separated list of one or more elements of the form:

[Name =] ArrayExpression [to typeof (Typename)]

RowLimit is simply:

limit RowLimit

and SubQuery has the same syntax of any query statement.

Parameters

Examples

Review the examples and run them in your Data Explorer query page.

Getting the largest element from the array

The query outputs the smallest even number (2) and the smallest odd number (1).

let _data =
    range x from 1 to 8 step 1
    | summarize l=make_list(x) by xMod2 = x % 2;
_data
| mv-apply element=l to typeof(long) on 
    (
    top 1 by element
    )

Output

Calculating the sum of the largest two elements in an array

The query outputs the sum of the top 2 even numbers (6 + 8 = 14) and the sum of the top 2 odd numbers (5 + 7 = 12).

let _data =
    range x from 1 to 8 step 1
    | summarize l=make_list(x) by xMod2 = x % 2;
_data
| mv-apply l to typeof(long) on
    (
    top 2 by l
    | summarize SumOfTop2=sum(l)
    )

Output

Select elements in arrays

The query identifies the top 2 elements from each dynamic array based on the Arr2 values and summarizes them into new lists.

datatable (Val:int, Arr1:dynamic, Arr2:dynamic)
[ 1, dynamic(['A1', 'A2', 'A3']),       dynamic([10, 30, 7]), 
  7, dynamic(['B1', 'B2', 'B5']),       dynamic([15, 11, 50]),
  3, dynamic(['C1', 'C2', 'C3', 'C4']), dynamic([6, 40, 20, 8])
] 
| mv-apply NewArr1=Arr1, NewArr2=Arr2 to typeof(long) on (
 top 2 by NewArr2
 | summarize NewArr1=make_list(NewArr1), NewArr2=make_list(NewArr2)
)

Output

Using with_itemindex for working with a subset of the array

The query results in a table with rows where the index is 3 or greater, including the index and element values from the original lists of even and odd numbers.

let _data =
    range x from 1 to 10 step 1
    | summarize l=make_list(x) by xMod2 = x % 2;
_data
| mv-apply with_itemindex=index element=l to typeof(long) on 
    (
    // here you have 'index' column
    where index >= 3
    )
| project index, element

Output

Using mutiple columns to join element of 2 arrays

The query combines elements from two dynamic arrays into a new concatenated format and then summarizes them into lists.

datatable (Val: int, Arr1: dynamic, Arr2: dynamic)
[
    1, dynamic(['A1', 'A2', 'A3']), dynamic(['B1', 'B2', 'B3']), 
    5, dynamic(['C1', 'C2']), dynamic(['D1', 'D2'])
] 
| mv-apply Arr1, Arr2 on (
    extend Out = strcat(Arr1, "_", Arr2)
    | summarize Arr1 = make_list(Arr1), Arr2 = make_list(Arr2), Out= make_list(Out)
    )

Output

Applying mv-apply to a property bag

This query dynamically removes properties from the packed values object based on the criteria that their values do not start with “555”. The final result contains the original columns with unwanted properties removed.

datatable(SourceNumber: string, TargetNumber: string, CharsCount: long)
[
    '555-555-1234', '555-555-1212', 46,
    '555-555-1212', '', int(null)
]
| extend values = pack_all()
| mv-apply removeProperties = values on 
    (
    mv-expand kind = array values
    | where values[1] !startswith "555"
    | summarize propsToRemove = make_set(values[0])
    )
| extend values = bag_remove_keys(values, propsToRemove)
| project-away propsToRemove

Output

Related content

• mv-expand operator

19 - mv-expand operator

Expands multi-value dynamic arrays or property bags into multiple records.

mv-expand can be described as the opposite of the aggregation operators that pack multiple values into a single dynamic-typed array or property bag, such as summarize … make-list() and make-series. Each element in the (scalar) array or property bag generates a new record in the output of the operator. All columns of the input that aren’t expanded are duplicated to all the records in the output.

Syntax

T |mv-expand [kind=(bag | array)] [with_itemindex= IndexColumnName] ColumnName [to typeof( Typename)] [, ColumnName …] [limit Rowlimit]

T |mv-expand [kind=(bag | array)] [Name =] ArrayExpression [to typeof(Typename)] [, [Name =] ArrayExpression [to typeof(Typename)] …] [limit Rowlimit]

Parameters

Returns

For each record in the input, the operator returns zero, one, or many records in the output, as determined in the following way:

1. Input columns that aren’t expanded appear in the output with their original value. If a single input record is expanded into multiple output records, the value is duplicated to all records.

2. For each ColumnName or ArrayExpression that is expanded, the number of output records is determined for each value as explained in modes of expansion. For each input record, the maximum number of output records is calculated. All arrays or property bags are expanded “in parallel” so that missing values (if any) are replaced by null values. Elements are expanded into rows in the order that they appear in the original array/bag.

3. If the dynamic value is null, then a single record is produced for that value (null). If the dynamic value is an empty array or property bag, no record is produced for that value. Otherwise, as many records are produced as there are elements in the dynamic value.

The expanded columns are of type dynamic, unless they’re explicitly typed by using the to typeof() clause.

Modes of expansion

Two modes of property bag expansions are supported:

• kind=bag or bagexpansion=bag: Property bags are expanded into single-entry property bags. This mode is the default mode.
• kind=array or bagexpansion=array: Property bags are expanded into two-element [key,value] array structures, allowing uniform access to keys and values. This mode also allows, for example, running a distinct-count aggregation over property names.

Examples

The examples in this section show how to use the syntax to help you get started.

Single column - array expansion

datatable (a: int, b: dynamic)
[
    1, dynamic([10, 20]),
    2, dynamic(['a', 'b'])
]
| mv-expand b

Output

Single column - bag expansion

A simple expansion of a single column:

datatable (a: int, b: dynamic)
[
    1, dynamic({"prop1": "a1", "prop2": "b1"}),
    2, dynamic({"prop1": "a2", "prop2": "b2"})
]
| mv-expand b

Output

Single column - bag expansion to key-value pairs

A simple bag expansion to key-value pairs:

datatable (a: int, b: dynamic)
[
    1, dynamic({"prop1": "a1", "prop2": "b1"}),
    2, dynamic({"prop1": "a2", "prop2": "b2"})
]
| mv-expand kind=array b 
| extend key = b[0], val=b[1]

Output

Zipped two columns

Expanding two columns will first ‘zip’ the applicable columns and then expand them:

datatable (a: int, b: dynamic, c: dynamic)[
    1, dynamic({"prop1": "a", "prop2": "b"}), dynamic([5, 4, 3])
]
| mv-expand b, c

Output

Cartesian product of two columns

If you want to get a Cartesian product of expanding two columns, expand one after the other:

datatable (a: int, b: dynamic, c: dynamic)
[
    1, dynamic({"prop1": "a", "prop2": "b"}), dynamic([5, 6])
]
| mv-expand b
| mv-expand c

Output

Convert output

To force the output of an mv-expand to a certain type (default is dynamic), use to typeof:

datatable (a: string, b: dynamic, c: dynamic)[
    "Constant", dynamic([1, 2, 3, 4]), dynamic([6, 7, 8, 9])
]
| mv-expand b, c to typeof(int)
| getschema 

Output

Notice column b is returned as dynamic while c is returned as int.

Using with_itemindex

Expansion of an array with with_itemindex:

range x from 1 to 4 step 1
| summarize x = make_list(x)
| mv-expand with_itemindex=Index x

Output

Related content

• mv-apply operator.
• For the opposite of the mv-expand operator, see summarize make_list().
• For expanding dynamic JSON objects into columns using property bag keys, see bag_unpack() plugin.

20 - parse operator

Evaluates a string expression and parses its value into one or more calculated columns. The calculated columns return null values for unsuccessfully parsed strings. If there’s no need to use rows where parsing doesn’t succeed, prefer using the parse-where operator.

Syntax

T | parse [ kind=kind [ flags=regexFlags ]] expression with [ * ] stringConstant columnName [: columnType] [ * ] , …

Parameters

Supported kind values

Regex mode

In regex mode, parse translates the pattern to a regex. Use regular expressions to do the matching and use numbered captured groups that are handled internally. For example:

parse kind=regex Col with * <regex1> var1:string <regex2> var2:long

In the parse statement, the regex internally generated by the parse is .*?<regex1>(.*?)<regex2>(\-\d+).

• * was translated to .*?.

• string was translated to .*?.

• long was translated to \-\d+.

Returns

The input table extended according to the list of columns that are provided to the operator.

Examples

The examples in this section show how to use the syntax to help you get started.

The parse operator provides a streamlined way to extend a table by using multiple extract applications on the same string expression. This result is useful, when the table has a string column that contains several values that you want to break into individual columns. For example, a column that’s produced by a developer trace ("printf"/"Console.WriteLine") statement.

Parse and extend results

In the following example, the column EventText of table Traces contains strings of the form Event: NotifySliceRelease (resourceName={0}, totalSlices={1}, sliceNumber={2}, lockTime={3}, releaseTime={4}, previousLockTime={5}). The operation extends the table with six columns: resourceName, totalSlices, sliceNumber, lockTime, releaseTime, and previousLockTime.

let Traces = datatable(EventText: string)
    [
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=02/17/2016 08:40:00, previousLockTime=02/17/2016 08:39:00)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces  
| parse EventText with * "resourceName=" resourceName ", totalSlices=" totalSlices: long * "sliceNumber=" sliceNumber: long * "lockTime=" lockTime ", releaseTime=" releaseTime: date "," * "previousLockTime=" previousLockTime: date ")" *  
| project resourceName, totalSlices, sliceNumber, lockTime, releaseTime, previousLockTime

Output

Extract email alias and DNS

In the following example, entries from the Contacts table are parsed to extract the alias and domain from an email address, and the domain from a website URL. The query returns the EmailAddress, EmailAlias, and WebsiteDomain columns, where the fullEmail column combines the parsed email aliases and domains.

let Leads=datatable(Contacts: string)
    [
    "Event: LeadContact (email=john@contosohotel.com, Website=https:contosohotel.com)",
	"Event: LeadContact (email=abi@fourthcoffee.com, Website=https:www.fourthcoffee.com)",
	"Event: LeadContact (email=nevena@treyresearch.com, Website=https:treyresearch.com)",
	"Event: LeadContact (email=faruk@tailspintoys.com, Website=https:tailspintoys.com)",
	"Event: LeadContact (email=ebere@relecloud.com, Website=https:relecloud.com)",
];
Leads
| parse Contacts with * "email=" alias:string "@" domain: string ", Website=https:" WebsiteDomain: string ")"
| project EmailAddress=strcat(alias, "@", domain), EmailAlias=alias, WebsiteDomain

Output

Regex mode

In the following example, regular expressions are used to parse and extract data from the EventText column. The extracted data is projected into new fields.

let Traces=datatable(EventText: string)
    [
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=02/17/2016 08:40:00, previousLockTime=02/17/2016 08:39:00)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces  
| parse kind=regex EventText with "(.*?)[a-zA-Z]*=" resourceName @", totalSlices=\s*\d+\s*.*?sliceNumber=" sliceNumber: long  ".*?(previous)?lockTime=" lockTime ".*?releaseTime=" releaseTime ".*?previousLockTime=" previousLockTime: date "\\)"  
| project resourceName, sliceNumber, lockTime, releaseTime, previousLockTime

Output

Regex mode with regex flags

In the following example resourceName is extracted.

let Traces=datatable(EventText: string)
    [
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=02/17/2016 08:40:00, previousLockTime=02/17/2016 08:39:00)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces
| parse kind=regex EventText with * "resourceName=" resourceName ',' *
| project resourceName

Output

If there are records where resourceName sometimes appears as lower-case and sometimes as upper-case, you might get nulls for some values.

The results in the previous example are unexpected, and include full event data since the default mode is greedy. To extract only resourceName, run the previous query with the non-greedy U, and disable case-sensitive i regex flags.

let Traces=datatable(EventText: string)
    [
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=23, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=02/17/2016 08:40:00, previousLockTime=02/17/2016 08:39:00)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
    "Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces
| parse kind=regex flags=Ui EventText with * "RESOURCENAME=" resourceName ',' *
| project resourceName

Output