find 运算符find operator

在一组表中查找与谓词匹配的行。Finds rows that match a predicate across a set of tables.

find 的作用域也可以跨数据库或跨群集。The scope of the find 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 (Table [, Table, ...])] where Predicate [project-smart | project ColumnName [:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)]] find [withsource=ColumnName] [in (Table [, Table, ...])] where Predicate [project-smart | project ColumnName [:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)]]

  • find Predicate [project-smart | project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...] [, pack(*)]] find Predicate [project-smart | project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...] [, pack(*)]]

参数Arguments

  • withsource=ColumnName:可选。withsource=ColumnName: Optional. 默认情况下,输出将包括一个名为 source_ 的列,其值指示哪个源表提供了每一行。By default, the output will include a column called source_ whose values indicate which source table has contributed each row. 如果指定,将使用 ColumnName,而不是 source_ 。If specified, ColumnName will be used instead of source_. 通配符匹配后,如果查询引用了多个数据库(包括默认数据库)中的表,则此列的值将具有使用数据库进行限定的表名。After wildcard matching, if the query references tables from more than one database (including the default database), the value of this column will have a table name qualified with the database. 同样,如果引用了多个群集,则值中将存在“群集”和“数据库”限定条件 。Similarly cluster and database qualifications will be present in the value if more than one cluster is referenced.
  • 谓词:针对输入表 Table [, Table, ...] 的列的 boolean 表达式 。对每个输入表中的每一行执行计算。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.
  • Table:可选。Table: Optional. 默认情况下,find 将在当前数据库的所有表中查找:By default, find will look in all the tables in the current database, for:
    • 表的名称,如 EventsThe name of a table, such as Events
    • 查询表达式,如 (Events | where id==42)A query expression, such as (Events | where id==42)
    • 使用通配符指定的一组表。A set of tables specified with a wildcard. 例如,E* 将构成数据库中所有名称以 E 开头的表的并集。For example, E* would form the union of all the tables in the database whose names begin with E.
  • project-smart | project:如果未指定,则默认情况下将使用 project-smartproject-smart | project: If not specified, project-smart will be used by default. 有关详细信息,请参阅输出架构详细信息For more information, see output-schema details.
  • withsource=ColumnName:可选。withsource=ColumnName: Optional. 默认情况下,输出将包括一个名为 source_ 的列,其值指示哪个源表提供了每一行。By default, the output will include a column called source_ whose values indicate which source table contributed each row. 如果指定,将使用 ColumnName,而不是 source_ 。If specified, ColumnName will be used instead of source_.
  • 谓词:针对输入表 Table [, Table, ...] 的列的 boolean 表达式 。对每个输入表中的每一行执行计算。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.
  • Table:可选。Table: Optional. 默认情况下 find 将在所有表中搜索:By default find will search all tables for:
    • 表的名称,如 EventsThe name of a table, such as Events
    • 查询表达式,如 (Events | where id==42)A query expression, such as (Events | where id==42)
    • 使用通配符指定的一组表。A set of tables specified with a wildcard. 例如,E* 将构成所有名称以 E 开头的表的并集。For example, E* would form the union of all the tables whose names begin with E.
  • project-smart | project:如果未指定,则默认情况下将使用 project-smartproject-smart | project: If not specified project-smart will be used by default. 有关详细信息,请参阅输出架构详细信息For more information, see output-schema details.

返回Returns

Predicate 为 true 的 Table [, Table, ...] 中的行的转换 。Transformation of rows in Table [, Table, ...] for which Predicate is true. 行根据输出架构进行转换。The rows are transformed according to the output schema.

输出架构Output schema

source_ 列source_ column

find 运算符输出始终包含具有源表名称的 source_ 列。The find operator output will always include a source_ column with the source table name. 可以使用 withsource 参数重命名该列。The column can be renamed using the withsource parameter.

结果列results columns

将筛除不包含谓词评估所用的任何列的源表。Source tables that don't contain any column used by the predicate evaluation, will be filtered out.

使用 project-smart 时,输出中会显示的列为:When using project-smart, the columns that will appear in the output will be:

  • 在谓词中显式显示的列。Columns that appear explicitly in the predicate.
  • 所有筛选的表的公共列。Columns that are common to all the filtered tables.

其余列将打包为属性包,并显示在其他 pack_ 列中。The rest of the columns will be packed into a property bag and will appear in an additional pack_ column. 谓词显式引用并显示在具有多种类型(每种类型在结果架构中将具有不同的列)的多个表中的列。A column that is referenced explicitly by the predicate and appears in multiple tables with multiple types, will have a different column in the result schema for each such type. 每个列名都基于原始列名和类型构造而成,并用下划线分隔。Each of the column names will be constructed from the original column name and type, separated by an underscore.

使用 project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)] 时 :When using project ColumnName[:ColumnType] [, ColumnName[:ColumnType], ...][, pack(*)]:

  • 结果表将包含列表中指定的列。The result table will include the columns specified in the list. 如果源表不包含特定列,则相应行中的值将为 NULL。If a source table doesn't contain a certain column, the values in the corresponding rows will be null.
  • 使用 ColumnName 指定 ColumnType 时,“结果”中的此列将具有给定的类型,并且值将根据需要强制转换为该类型 。When specifying a ColumnType with a ColumnName, this column in the "result" will have the given type, and the values will be cast to that type if needed. 评估 Predicate 时,强制转换不会影响列类型。The casting won't have an effect on the column type when evaluating the Predicate.
  • 使用 pack(*) 时,其余列将打包为属性包,并显示在其他 pack_ 列中。When pack(*) is used, the rest of the columns will be packed into a property bag and will appear in an additional pack_ column.

pack_ 列pack_ column

此列将包含一个属性包,其中包含输出架构中未显示的所有列的数据。This column will contain a property bag with the data from all the columns that doesn't appear in the output schema. 源列名将用作属性名,列值将用作属性值。The source column name will serve as the property name and the column value will serve as the property value.

谓词语法Predicate syntax

find 运算符支持 * has 术语的替代语法,并且仅使用 term 即可在所有输入列中搜索术语 。The find operator supports an alternative syntax for the * has term, and using just term, will search a term across all input columns.

有关某些筛选函数的摘要,请参阅 where 运算符For a summary of some filtering functions, see where operator.

说明Notes

  • 如果 project 子句引用了出现在多个表中且具有多种类型的列,则在 project 子句中,类型必须遵循此列引用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
  • 如果某列出现在多个表中且具有多种类型,并且正在使用 project-smart,则 find 的结果中每种类型都会有一个对应的列,如 union 中所述If a column appears in multiple tables and has multiple types and project-smart is in use, there will be a corresponding column for each type in the find's result, as described in union
  • 当使用 project-smart 时,谓词、源表集或表架构中的更改可能会导致输出架构发生更改。When using project-smart, changes in the predicate, in the source tables set, or in the tables schema, may result in a change to the output schema. 如果需要常量结果架构,请改用 projectIf a constant result schema is needed, use project instead
  • find 作用域不能包括函数find scope can't include functions. 要在 find 作用域中包含函数,请使用 view 关键字定义 let 语句To include a function in the find scope, define a let statement with view keyword.

性能提示Performance tips

  • 使用,而不是表格表达式Use tables as opposed to tabular expressions. 如果使用表格表达式,则 find 运算符将回退到 union 查询,这可能会导致性能下降。If tabular expression, the find operator falls back to a union query that can result in degraded performance.
  • 如果列出现在多个表中并具有多种类型,并且是 project 子句的一部分,则在将表传递给 find 之前,最好将 ColumnType 添加到 project 子句,而不是修改表。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. 使用日期/时间列值或 ingestion_time()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 will fall back to union. 例如,请参阅将 find 用作 union 的案例示例For example, see examples of cases where find will act as a union.

示例Examples

在当前数据库的所有表中进行字词查找Term lookup across all tables in current database

以下查询从当前数据库的所有表中查找其中的任意列包含单词 Kusto 的所有行。The query finds all rows from all tables in the current database in which any column includes the word Kusto. 生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.

find "Kusto"

对当前数据库中与名称模式匹配的所有表进行字词查找Term lookup across all tables matching a name pattern in the current database

以下查询从当前数据库中名称以 K 开头的所有表中,查找其中的任意列包含单词 Kusto 的所有行。The query finds all rows from all tables in the current database whose name starts with K, and in which any column includes the word Kusto. 生成的记录根据输出架构进行转换。The resulting records are transformed according to the output schema.

find in (K*) where * has "Kusto"

对群集中所有数据库的所有表进行字词查找Term lookup across all tables in all databases in the cluster

以下查询从所有数据库的所有表中查找其中的任意列包含单词 Kusto 的所有行。The query finds all rows from all tables in all databases 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('*').*) "Kusto"

对群集中与名称模式匹配的所有表和数据库进行字词查找Term lookup across all tables and databases matching a name pattern in the cluster

以下查询从所有名称以 K 开头的表中和所有名称以 B 开头的数据库中,查找其中的任意列包含单词 Kusto 的所有行。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("B*").K*) where * has "Kusto"

在多个群集中进行字词查找Term lookup in several clusters

以下查询从所有名称以 K 开头的表中和所有名称以 B 开头的数据库中,查找其中的任意列包含单词 Kusto 的所有行。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

以下查询从所有表中查找其中的任意列包含单词 Kusto 的所有行。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"

find 输出结果的示例Examples of find output results

下面的示例演示如何针对两个表使用 find:EventsTable1 和 EventsTable2 。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:

EventsTable1EventsTable1

Session_IdSession_Id 级别Level EventTextEventText 版本Version
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 信息Information Some Text1Some Text1 v1.0.0v1.0.0
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 错误Error Some Text2Some Text2 v1.0.0v1.0.0
28b8e46e-3c31-43cf-83cb-48921c3986fc28b8e46e-3c31-43cf-83cb-48921c3986fc 错误Error Some Text3Some Text3 v1.0.1v1.0.1
8f057b11-3281-45c3-a856-05ebb18a3c598f057b11-3281-45c3-a856-05ebb18a3c59 信息Information Some Text4Some Text4 v1.1.0v1.1.0

EventsTable2EventsTable2

Session_IdSession_Id 级别Level EventTextEventText EventNameEventName
f7d5f95f-f580-4ea6-830b-5776c8d64fddf7d5f95f-f580-4ea6-830b-5776c8d64fdd 信息Information Some Other Text1Some Other Text1 Event1Event1
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 信息Information Some Other Text2Some Other Text2 Event2Event2
acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 错误Error Some Other Text3Some Other Text3 Event3Event3
15eaeab5-8576-4b58-8fc6-478f75d8fee415eaeab5-8576-4b58-8fc6-478f75d8fee4 错误Error Some Other Text4Some Other Text4 Event4Event4

搜索公共列、投影通用列和非通用列,然后打包其余列Search in common columns, project common and uncommon columns, and pack the rest

find in (EventsTable1, EventsTable2) 
     where Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' and Level == 'Error' 
     project EventText, Version, EventName, pack(*)
source_source_ EventTextEventText 版本Version EventNameEventName pack_pack_
EventsTable1EventsTable1 Some Text2Some Text2 v1.0.0v1.0.0 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}
EventsTable2EventsTable2 Some Other Text3Some Other Text3 Event3Event3 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}

搜索公共列和非公共列Search in common and uncommon columns

find Version == 'v1.0.0' or EventName == 'Event1' project Session_Id, EventText, Version, EventName
source_source_ Session_IdSession_Id EventTextEventText 版本Version EventNameEventName
EventsTable1EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e Some Text1Some Text1 v1.0.0v1.0.0
EventsTable1EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e Some Text2Some Text2 v1.0.0v1.0.0
EventsTable2EventsTable2 f7d5f95f-f580-4ea6-830b-5776c8d64fddf7d5f95f-f580-4ea6-830b-5776c8d64fdd Some Other Text1Some Other Text1 Event1Event1

注意:在实践中,将使用 Version == 'v1.0.0' 谓词筛选 EventsTable1 行,使用 EventName == 'Event1' 谓词筛选 EventsTable2 行 。Note: in practice, EventsTable1 rows will be filtered with Version == 'v1.0.0' predicate and EventsTable2 rows will be filtered with EventName == 'Event1' predicate.

使用缩写表示法在当前数据库的所有表中进行搜索Use abbreviated notation to search across all tables in the current database

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e'
source_source_ Session_IdSession_Id 级别Level EventTextEventText pack_pack_
EventsTable1EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 信息Information Some Text1Some Text1 {"Version":"v1.0.0"}{"Version":"v1.0.0"}
EventsTable1EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 错误Error Some Text2Some Text2 {"Version":"v1.0.0"}{"Version":"v1.0.0"}
EventsTable2EventsTable2 acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 信息Information Some Other Text2Some Other Text2 {"EventName":"Event2"}{"EventName":"Event2"}
EventsTable2EventsTable2 acbd207d-51aa-4df7-bfa7-be70eb68f04eacbd207d-51aa-4df7-bfa7-be70eb68f04e 错误Error Some Other Text3Some Other Text3 {"EventName":"Event3"}{"EventName":"Event3"}

以属性包的形式返回每一行的结果Return the results from each row as a property bag

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' project pack(*)
source_source_ pack_pack_
EventsTable1EventsTable1 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Text1", "Version":"v1.0.0"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Text1", "Version":"v1.0.0"}
EventsTable1EventsTable1 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Text2", "Version":"v1.0.0"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Text2", "Version":"v1.0.0"}
EventsTable2EventsTable2 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Other Text2", "EventName":"Event2"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Other Text2", "EventName":"Event2"}
EventsTable2EventsTable2 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Other Text3", "EventName":"Event3"}{"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Other Text3", "EventName":"Event3"}

find 用作 union 的案例示例Examples of cases where find will act as union

使用非表格表达式作为 find 操作数Using a non-tabular expression as find operand

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

假设我们已通过运行以下内容创建了两个表:Assume we've created two tables by running:

.create tables 
  Table1 (Level:string, Timestamp:datetime, ProcessId:string),
  Table2 (Level:string, Timestamp:datetime, ProcessId:int64)
  • 下面的查询将作为 union 执行。The following query will be executed as union.
find in (Table1, Table2) where ProcessId == 1001

输出结果架构将为 (Level:string, Timestamp, ProcessId_string, ProcessId_int)。The output result schema will be (Level:string, Timestamp, ProcessId_string, ProcessId_int).

  • 以下查询也将作为 union 执行,但将产生不同的结果模式。The following query will also be executed as union, but will produce a different result schema.
find in (Table1, Table2) where ProcessId == 1001 project Level, Timestamp, ProcessId:string 

输出结果架构将为 (Level:string, Timestamp, ProcessId_string)The output result schema will be (Level:string, Timestamp, ProcessId_string)