Log Analytics query language recommendations for Summarize and Join operators

This update describes Azure Log Analytics and Application Insights query language syntax recommendations for Summarize 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 Summarize operator

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

SecurityEvent

| where TimeGenerated > ago(1d)

| where EventID == 4625

| summarize count() by TimeGenerated

Here are the query results:

TimeGenerated [UTC]	count_
2018-02-04T09:00:00.000 2018-02-04T10:00:00.000 2018-02-04T11:00:00.000 2018-02-04T12:00:00.000 2018-02-04T13:00:00.000 2018-02-04T14:00:00.000 2018-02-04T15:00:00.000 2018-02-04T16:00:00.000 2018-02-04T17:00:00.000 2018-02-04T18: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, summarize 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, summarize by TimeGenerated). If so, correct the queries to use explicit binning—for example, summarize 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, summarize 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 behavior.

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

| summarize 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)