Log Analytics query language recommendations for Summarize and Join operators
Posted on Tuesday, May 29, 2018
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:
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize count() by TimeGenerated
Here are the query results:
- 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):
| where EventID == 4624
| join kind= inner (
| where EventID == 4634
) on TargetLogonId == TargetLogonId
| extend Duration = LogoffTime - LogonTime
| summarize sum(Duration) by bin(LogonTime, 1d), Computer
- 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
| join … on $left.Col1 == $right.Col2
| join … on X (equivalent to: | join … on $left.X == $right.X)