find operator

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Finds rows that match a predicate across a set of tables.

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 (Tables)] where Predicate [project-smart | project ColumnName[: ColumnType , ... ] [, pack_all()]]

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

Learn more about syntax conventions.

Parameters

Returns

Transformation of rows in Table [, Table, ...] for which Predicate is true. The rows are transformed according to the output schema.

Output schema

source_ column

The find operator output will always include a source_ column with the source table name. 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.

When you use 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.

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.

When using project ColumnName[: ColumnType , ... ] [, pack_all()]:

• The result table will include the columns specified in the list. If a source table doesn't contain a certain column, the values in the corresponding rows will be null.
• 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. The casting won't have an effect on the column type when evaluating the Predicate.
• When pack_all() is used, all the columns, including the projected columns, are packed into a property bag and appear in an additional column, by default 'column1'. In the property bag, the source column name serves as the property name and the column's value serves as the property value.

Predicate syntax

The find operator supports an alternative syntax for the * has term, and using just term, will search a term across all input columns.

For a summary of some filtering functions, see where operator.

Notes

• 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
• 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
• When you use 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. If a constant result schema is needed, use project instead
• find scope can't include functions. To include a function in the find scope, define a let statement with view keyword.

Performance tips

• Use tables as opposed to tabular expressions. If tabular expression, the find operator falls back to a union query that can result in degraded performance.
• 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. 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. For example, see examples of cases where find will act as a union.

Examples

find "Hernandez"

find in (C*) where * has "Hernandez"

find in (database('*').*) where * has "Kusto"

find in (database("S*").C*) where * has "Kusto"

find in (cluster("cluster1").database("B*").K*, cluster("cluster2").database("C*".*))
where * has "Kusto"

find "Kusto"

Examples of find output results

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:

EventsTable1

EventsTable2

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_all()

Output

Search in common and uncommon columns

find Version == 'v1.0.0' or EventName == 'Event1' project Session_Id, EventText, Version, EventName

Output

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'

Output

Return the results from each row as a property bag

find Session_Id == 'acbd207d-51aa-4df7-bfa7-be70eb68f04e' project pack_all()

Output

Examples of cases where find will act as union

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)

• The following query will be executed as union.

find in (Table1, Table2) where ProcessId == 1001

The output result schema will be (Level:string, Timestamp, ProcessId_string, ProcessId_int).

• 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 

The output result schema will be (Level:string, Timestamp, ProcessId_string)