find operator

Applies to: ✅ Microsoft FabricAzure Data ExplorerAzure MonitorMicrosoft Sentinel

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

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"

Note

find operator is substantially less efficient than column-specific text filtering. Whenever the columns are known, we recommend using the where operator. find will not function well when the workspace contains large number of tables and columns and the data volume that is being scanned is high and the time range of the query is high.

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

Name Type Required Description
ColumnName string By default, the output will include a column called source_ whose values indicate which source table has contributed each row. 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.
Predicate bool ✔️ This boolean expression is evaluated for each row in each input table. For more information, see predicate-syntax details.
Tables string Zero or more comma-separated table references. By default, find will look in all the tables in the current database. You can use:
1. The name of a table, such as Events
2. A query expression, such as (Events | where id==42)
3. A set of tables specified with a wildcard. For example, E* would form the union of all the tables in the database whose names begin with E.
project-smart or project string If not specified, project-smart will be used by default. For more information, see output-schema details.
  • withsource=ColumnName: Optional. By default, the output will include a column called source_ whose values indicate which source table contributed each row. If specified, ColumnName will be used instead of source_.

  • 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.

  • Tables: Optional. Zero or more comma-separated table references. By default find will search all tables for:

    • The name of a table, such as Events
    • A query expression, such as (Events | where id==42)
    • A set of tables specified with a wildcard. For example, E* would form the union of all the tables whose names begin with E.
  • project-smart | project: If not specified project-smart will be used by default. For more information, see output-schema details.

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

Term lookup across all tables in current database

The query finds all rows from all tables in the current database in which any column includes the word Hernandez. The resulting records are transformed according to the output schema. The output includes rows from the Customers table and the SalesTable table of the ContosoSales database.

find "Hernandez"

Term lookup across all tables matching a name pattern in the current database

The query finds all rows from all tables in the current database whose name starts with C, and in which any column includes the word Hernandez. The resulting records are transformed according to the output schema. Now, the output only contains records from the Customers table.

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

Term lookup across all tables in all databases in the cluster

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('*').*) where * has "Kusto"

Term lookup across all tables and databases matching a name pattern in the cluster

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("S*").C*) where * has "Kusto"

Term lookup in several clusters

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

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"

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

Session_Id Level EventText Version
acbd207d-51aa-4df7-bfa7-be70eb68f04e Information Some Text1 v1.0.0
acbd207d-51aa-4df7-bfa7-be70eb68f04e Error Some Text2 v1.0.0
28b8e46e-3c31-43cf-83cb-48921c3986fc Error Some Text3 v1.0.1
8f057b11-3281-45c3-a856-05ebb18a3c59 Information Some Text4 v1.1.0

EventsTable2

Session_Id Level EventText EventName
f7d5f95f-f580-4ea6-830b-5776c8d64fdd Information Some Other Text1 Event1
acbd207d-51aa-4df7-bfa7-be70eb68f04e Information Some Other Text2 Event2
acbd207d-51aa-4df7-bfa7-be70eb68f04e Error Some Other Text3 Event3
15eaeab5-8576-4b58-8fc6-478f75d8fee4 Error Some Other Text4 Event4

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

source_ EventText Version EventName pack_
EventsTable1 Some Text2 v1.0.0 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error"}
EventsTable2 Some Other Text3 Event3 {"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

Output

source_ Session_Id EventText Version EventName
EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04e Some Text1 v1.0.0
EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04e Some Text2 v1.0.0
EventsTable2 f7d5f95f-f580-4ea6-830b-5776c8d64fdd Some Other Text1 Event1

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

source_ Session_Id Level EventText pack_
EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04e Information Some Text1 {"Version":"v1.0.0"}
EventsTable1 acbd207d-51aa-4df7-bfa7-be70eb68f04e Error Some Text2 {"Version":"v1.0.0"}
EventsTable2 acbd207d-51aa-4df7-bfa7-be70eb68f04e Information Some Other Text2 {"EventName":"Event2"}
EventsTable2 acbd207d-51aa-4df7-bfa7-be70eb68f04e Error Some Other Text3 {"EventName":"Event3"}

Return the results from each row as a property bag

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

Output

source_ pack_
EventsTable1 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Text1", "Version":"v1.0.0"}
EventsTable1 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Text2", "Version":"v1.0.0"}
EventsTable2 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Information", "EventText":"Some Other Text2", "EventName":"Event2"}
EventsTable2 {"Session_Id":"acbd207d-51aa-4df7-bfa7-be70eb68f04e", "Level":"Error", "EventText":"Some Other Text3", "EventName":"Event3"}

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)