GitHubContribute in GitHub: Edit online

!in~ operator

Filters a record set for data without a case-insensitive string.

The following table provides a comparison of the has operators:.

Operator Description Case-Sensitive Example (yields true)
in Equals to one of the elements Yes "abc" in ("123", "345", "abc")
!in Not equals to any of the elements Yes "bca" !in ("123", "345", "abc")
in~ Equals to any of the elements No "Abc" in~ ("123", "345", "abc")
!in~ Not equals to any of the elements No "bCa" !in~ ("123", "345", "ABC")

  • In tabular expressions, the first column of the result set is selected.
  • The expression list can produce up to 1,000,000 values.
  • Nested arrays are flattened into a single list of values. For example, x in (dynamic([1,[2,3]])) becomes x in (1,2,3).

For further information about other operators and to determine which operator is most appropriate for your query, see datatype string operators.

Case-insensitive operators are currently supported only for ASCII-text. For non-ASCII comparison, use the tolower() function.

Performance tips

Performance depends on the type of search and the structure of the data.

For faster results, use the case-sensitive version of an operator, for example, in, not in~.

If you're testing for the presence of a symbol or alphanumeric word that is bound by non-alphanumeric characters at the start or end of a field, for faster results use has or in.

Syntax

T | where col !in~ (list of scalar expressions)
T | where col !in~ (tabular expression)

Arguments

  • T - The tabular input whose records are to be filtered.
  • col - The column to filter.
  • list of expressions - A comma-separated list of tabular, scalar, or literal expressions.
  • tabular expression - A tabular expression that has a set of values. If the expression has multiple columns, the first column is used.

Returns

Rows in T for which the predicate is true.

Example

This example looks for log sources not provided in the allowed list.

events
| project data_source_name, name
| where data_source_name !in~("CiscoNAC", "Checkpoint", "CiscoASA") 
| limit 2

Results

data_source_name name
microsoftWindowsSource3 Activity Transfer
microsoftWindowsSource3 WinRM Protocol Handler Closed The Session