search operator
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Searches a text pattern in multiple tables and columns.
Note
If you know the specific tables and columns you want to search, it's more performant to use the union and where operators. The search operator can be slow when searching across a large number of tables and columns.
Syntax
[T |] search [kind= CaseSensitivity ] [in (TableSources)] SearchPredicate
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | string |
The tabular data source to be searched over, such as a table name, a union operator, or the results of a tabular query. Cannot appear together with TableSources. | |
| CaseSensitivity | string |
A flag that controls the behavior of all string scalar operators, such as has, with respect to case sensitivity. Valid values are default, case_insensitive, case_sensitive. The options default and case_insensitive are synonymous, since the default behavior is case insensitive. |
|
| TableSources | string |
A comma-separated list of "wildcarded" table names to take part in the search. The list has the same syntax as the list of the union operator. Cannot appear together with TabularSource. | |
| SearchPredicate | string |
✔️ | A boolean expression to be evaluated for every record in the input. If it returns true, the record is outputted. See Search predicate syntax. |
Search predicate syntax
The SearchPredicate allows you to search for specific terms in all columns of a table. The operator that will be applied to a search term depends on the presence and placement of a wildcard asterisk (*) in the term, as shown in the following table.
| Literal | Operator |
|---|---|
billg |
has |
*billg |
hassuffix |
billg* |
hasprefix |
*billg* |
contains |
bi*lg |
matches regex |
You can also restrict the search to a specific column, look for an exact match instead of a term match, or search by regular expression. The syntax for each of these cases is shown in the following table.
| Syntax | Explanation |
|---|---|
ColumnName:StringLiteral |
This syntax can be used to restrict the search to a specific column. The default behavior is to search all columns. |
ColumnName==StringLiteral |
This syntax can be used to search for exact matches of a column against a string value. The default behavior is to look for a term-match. |
Column matches regex StringLiteral |
This syntax indicates regular expression matching, in which StringLiteral is the regex pattern. |
Use boolean expressions to combine conditions and create more complex searches. For example, "error" and x==123 would result in a search for records that have the term error in any columns and the value 123 in the x column.
Note
If both TabularSource and TableSources are omitted, the search is carried over all unrestricted tables and views of the database in scope.
Search predicate syntax examples
| # | Syntax | Meaning (equivalent where) |
Comments |
|---|---|---|---|
| 1 | search "err" |
where * has "err" |
|
| 2 | search in (T1,T2,A*) "err" |
union T1,T2,A* | where * has "err" |
|
| 3 | search col:"err" |
where col has "err" |
|
| 4 | search col=="err" |
where col=="err" |
|
| 5 | search "err*" |
where * hasprefix "err" |
|
| 6 | search "*err" |
where * hassuffix "err" |
|
| 7 | search "*err*" |
where * contains "err" |
|
| 8 | search "Lab*PC" |
where * matches regex @"\bLab.*PC\b" |
|
| 9 | search * |
where 0==0 |
|
| 10 | search col matches regex "..." |
where col matches regex "..." |
|
| 11 | search kind=case_sensitive |
All string comparisons are case-sensitive | |
| 12 | search "abc" and ("def" or "hij") |
where * has "abc" and (* has "def" or * has hij") |
|
| 13 | search "err" or (A>a and A<b) |
where * has "err" or (A>a and A<b) |
Remarks
Unlike the find operator, the search operator does not support the following:
withsource=: The output will always include a column called$tableof typestringwhose value is the table name from which each record was retrieved (or some system-generated name if the source isn't a table but a composite expression).project=,project-smart: The output schema is equivalent toproject-smartoutput schema.
Examples
Global term search
Search for a term over all unrestricted tables and views of the database in scope.
search "Green"
The output contains records from the Customers, Products, and SalesTable tables. The Customers records shows all customers with the last name "Green", and the Products and SalesTable records shows products with some mention of "Green".
Conditional global term search
Search for records that match both terms over all unrestricted tables and views of the database in scope.
search "Green" and ("Deluxe" or "Proseware")
Search a specific table
Search only in the Customers table.
search in (Products) "Green"
Case-sensitive search
Search for records that match both case-sensitive terms over all unrestricted tables and views of the database in scope.
search kind=case_sensitive "blue"
Search specific columns
Search for a term in the "FirstName" and "LastName" columns over all unrestricted tables and views of the database in scope.
search FirstName:"Aaron" or LastName:"Hughes"
Limit search by timestamp
Search for a term over all unrestricted tables and views of the database in scope if the term appears in a record with a date greater than the given date.
search "Hughes" and DateKey > datetime('2009-01-01')
Performance Tips
| # | Tip | Prefer | Over |
|---|---|---|---|
| 1 | Prefer to use a single search operator over several consecutive search operators |
search "billg" and ("steveb" or "satyan") |
search "billg" | search "steveb" or "satyan" |
| 2 | Prefer to filter inside the search operator |
search "billg" and "steveb" |
search * | where * has "billg" and * has "steveb" |