Log Analytics query language recommendations for Summarise and Join operators

This update describes Azure Log Analytics and Application Insights query language syntax recommendations for Summarise and Join operators. Existing syntax is still supported, but we strongly recommend that you modify your query syntax where applicable in saved searches and alerts, to avoid result ambiguity.

For questions about these recommendations, contact us.

Hourly auto-binning in the Summarise operator

Currently, data aggregated by datetime key is automatically grouped into hourly bins. In this example, the TimeGenerated column used in the Summarise operator has been automatically rounded to hourly bins:

SecurityEvent

| where TimeGenerated > ago (1d)

| where EventID == 4625

| summarise count() by TimeGenerated

Here are the query results:

TimeGenerated [UTC]	count_
04/02/2018T09:00:00.000 04/02/2018T10:00:00.000 04/02/2018T11:00:00.000 04/02/2018T12:00:00.000 04/02/2018T13:00:00.000 04/02/2018T14:00:00.000 04/02/2018T15:00:00.000 04/02/2018T16:00:00.000 04/02/2018T17:00:00.000 04/02/2018T18:00:00.000	1,205 1,268 1,234 1,173 1,007 1,042 1,041 1,049 1,090 1,113

 

Recommended actions:

• If you’re not relying on the auto-binning feature, either in saved searches or in alerts (for example, summarise by TimeGenerated):
  • No action is required.
• If you’re not aware of the auto-binning feature, your queries might return results with unintended hourly aggregation:
  • Verify whether you’re using aggregating data by datetime key (for example, summarise by TimeGenerated). If so, correct the queries to use explicit binning – for example, summarise by bin(TimeGenerated, 1h).
• If you’re aware of the auto-binning feature and rely on it:
  • Correct the queries to use explicit binning – for example, summarise by bin(TimeGenerated, 1h).

Equality expressions in the Join operator

Currently, the language syntax allows the use of ambiguous equality expressions as Join attributes (for example, key1 == key2) that yield undesired results. We recommend that you change ambiguous equality expressions to avoid this behaviour.

Here’s an example of an ambiguous Join expression (summing the daily session duration per computer):

SecurityEvent

| where EventID == 4624

| join kind= inner (

    SecurityEvent

    | where EventID == 4634

    ) on TargetLogonId == TargetLogonId

| extend Duration = LogoffTime - LogonTime

| summarise sum(Duration) by bin(LogonTime, 1d), Computer

 

Recommended actions:

• If you don’t use equality expressions in Join operators, either in saved searches or in alerts:
  • No action is required.
• If you use equality expressions in Join operators:
  • Correct the Join operator syntax.
  • Correct your queries. The column names should be qualified with the applicable owner table denoted by $left and $right notations. Examples:

| join … on $left.Col1 == $right.Col2

| join … on X (equivalent to: | join … on $left.X == $right.X)