Azure Monitor 日志查询中的联接Joins in Azure Monitor log queries

备注

在完成本课程之前,应先完成 Azure Monitor Log Analytics 入门Azure Monitor 日志查询You should complete Get started with Azure Monitor Log Analytics and Azure Monitor log queries before completing this lesson.

备注

可以在自己的 Log Analytics 环境中完成此练习,也可以使用我们的演示环境,其中包含大量样本数据。You can work through this exercise in your own Log Analytics environment, or you can use our Demo environment, which includes plenty of sample data.

利用联接,可在同一查询中分析来自多个表的数据。Joins allow you to analyze data from multiple tables, in the same query. 它们通过匹配指定列的值来合并两个数据集的行。They merge the rows of two data sets by matching values of specified columns.

SecurityEvent 
| where EventID == 4624     // sign-in events
| project Computer, Account, TargetLogonId, LogonTime=TimeGenerated
| join kind= inner (
    SecurityEvent 
    | where EventID == 4634     // sign-out events
    | project TargetLogonId, LogoffTime=TimeGenerated
) on TargetLogonId
| extend Duration = LogoffTime-LogonTime
| project-away TargetLogonId1 
| top 10 by Duration desc

在本例中,第一个数据集筛选所有登录事件。In this example, the first dataset filters for all sign-in events. 这与筛选所有注销事件的第二个数据集进行联接。This is joined with a second dataset that filters for all sign-out events. 投影列包括计算机、帐户、TargetLogonId 和 TimeGenerated。The projected columns are Computer, Account, TargetLogonId, and TimeGenerated. 数据集由共享列 TargetLogonId 进行关联。The datasets are correlated by a shared column, TargetLogonId. 输出实为具有登录时间和注销时间的记录,每个关联对应一条记录。The output is a single record per correlation, which has both the sign-in and sign-out time.

如果这两个数据集具有名称相同的列,则右侧数据集的列将附加索引号,因此本例中,结果显示为 TargetLogonId 具有左侧表的值,而 TargetLogonId1 具有右侧表的值。If both datasets have columns with the same names, the columns of the right-side dataset would be given an index number, so in this example the results would show TargetLogonId with values from the left-side table and TargetLogonId1 with values from the right-side table. 在此情况下,已使用 project-away 运算符删除第二个 TargetLogonId1In this case, the second TargetLogonId1 column was removed by using the project-away operator.

备注

为提高性能,请使用 project 运算符仅保留已联接的数据集的相关列。To improve performance, keep only the relevant columns of the joined data-sets, using the project operator.

请使用以下语法来联接两个数据集,所联接的键在两个表之间具有不同的名称:Use the following syntax to join two datasets and the joined key has a different name between the two tables:

Table1
| join ( Table2 ) 
on $left.key1 == $right.key2

查找表Lookup Tables

联接的常见用法是通过 datatable 静态映射值,这有助于将结果转换为更易呈现的形式。A common use of joins is using static mapping of values using datatable that can help in transforming the results into more presentable way. 例如,使用每个事件 ID 的事件名称来丰富安全事件数据。For example, to enrich the security event data with the event name for each event ID.

let DimTable = datatable(EventID:int, eventName:string)
  [
    4625, "Account activity",
    4688, "Process action",
    4634, "Account activity",
    4658, "The handle to an object was closed",
    4656, "A handle to an object was requested",
    4690, "An attempt was made to duplicate a handle to an object",
    4663, "An attempt was made to access an object",
    5061, "Cryptographic operation",
    5058, "Key file operation"
  ];
SecurityEvent
| join kind = inner
 DimTable on EventID
| summarize count() by eventName

与数据表联接

联接类型Join kinds

使用 kind 参数来指定联接类型。Specify the type of join with the kind argument. 如下表所述,每种类型在给定表的记录之间执行不同的匹配。Each type performs a different match between the records of the given tables as described in the following table.

联接类型Join type 说明Description
inneruniqueinnerunique 这是默认的联接模式。This is the default join mode. 首先找到左表中匹配列的值,并删除重复值。First the values of the matched column on the left table are found, and duplicate values are removed. 然后将该组唯一值集与右表匹配。Then the set of unique values is matched against the right table.
内部inner 结果中只包括在两个表中匹配的记录。Only matching records in both tables are included in the results.
leftouterleftouter 结果中包含左表中的所有记录和右表中的匹配记录。All records in the left table and matching records in the right table are included in the results. 不匹配的输出属性包含 null。Unmatched output properties contain nulls.
leftantileftanti 结果中包含左表中没有右表匹配项的记录。Records from the left side that do not have matches from the right are included in the results. 结果表只包含左表中的列。The results table has only columns from the left table.
leftsemileftsemi 结果中包含左表中具有右表匹配项的记录。Records from the left side that have matches from the right are included in the results. 结果表只包含左表中的列。The results table has only columns from the left table.

最佳实践Best practices

要实现最佳性能,请注意以下几点:Consider the following points for optimal performance:

  • 在每个表上使用时间筛选器,减少为联接计算得出的最小记录量。Use a time filter on each table to reduce the records that must be evaluated for the join.
  • 使用 whereproject 可减少联接前输入表中的行数和列数。Use where and project to reduce the numbers of rows and columns in the input tables before the join.
  • 如果某个表始终小于另一个表,则将其用作联接的左侧。If one table is always smaller than the other, use it as the left side of the join.

后续步骤Next steps

参阅有关使用 Azure Monitor 日志查询的其他课:See other lessons for using Azure Monitor log queries: