Learn Kusto Query Language – String Operators
Kusto Query Language (aka KQL) offers multiple query operators for searching string data types. When working with KQL we’re usually using many commands, whether its from functions through statement and operator.
The following blog-post describes how string terms are indexed, lists the string query operators, examples, and tips.
String Terms
Kusto indexes all columns, including columns of type string. Multiple indexes are built for such columns, depending on the actual data. These indexes aren’t directly exposed, but are used in queries with the string operators that have has as part of their name, such as has, !has, hasprefix, !hasprefix. The semantics of these operators are dictated by the way the column is encoded.
Instead of doing a plain substring match, these operators match terms.
The Term
Each string value is broken into maximal sequences of ASCII alphanumeric characters, and each of those sequences is made into a term. For example, in the following string, the terms are Kusto
Kusto builds a term index consisting of all terms that are four characters or more, and this index is used by has, !has, and so on. If the query looks for a term that is smaller than four characters, or uses a contains operator, Kusto will revert to scanning the values in the column if it can’t determine a match.
This method is much slower than looking up the term in the term index.
String operators
The String operators include common operators as described in the attached table, and on the example queries below
| Operator | Description | Case-Sensitive | Example (yieldstrue) |
|---|---|---|---|
== |
Equals | Yes | "aBc" == "aBc" |
!= |
Not equals | Yes | "abc" != "ABC" |
=~ |
Equals | No | "abc" =~ "ABC" |
!~ |
Not equals | No | "aBc" !~ "xyz" |
has |
RHS is a whole term in left-hand-side (LHS) | No | "North America" has "america" |
!has |
RHS isn’t a full term in LHS | No | "North America" !has "amer" |
has_cs |
RHS is a whole term in LHS | Yes | "North America" has_cs "America" |
!has_cs |
RHS isn’t a full term in LHS | Yes | "North America" !has_cs "amer" |
hasprefix |
RHS is a term prefix in LHS | No | "North America" hasprefix "ame" |
!hasprefix |
RHS isn’t a term prefix in LHS | No | "North America" !hasprefix "mer" |
hasprefix_cs |
RHS is a term prefix in LHS | Yes | "North America" hasprefix_cs "Ame" |
!hasprefix_cs |
RHS isn’t a term prefix in LHS | Yes | "North America" !hasprefix_cs "CA" |
hassuffix |
RHS is a term suffix in LHS | No | "North America" hassuffix "ica" |
!hassuffix |
RHS isn’t a term suffix in LHS | No | "North America" !hassuffix "americ" |
hassuffix_cs |
RHS is a term suffix in LHS | Yes | "North America" hassuffix_cs "ica" |
!hassuffix_cs |
RHS isn’t a term suffix in LHS | Yes | "North America" !hassuffix_cs "icA" |
contains |
RHS occurs as a subsequence of LHS | No | "FabriKam" contains "BRik" |
!contains |
RHS doesn’t occur in LHS | No | "Fabrikam" !contains "xyz" |
contains_cs |
RHS occurs as a subsequence of LHS | Yes | "FabriKam" contains_cs "Kam" |
!contains_cs |
RHS doesn’t occur in LHS | Yes | "Fabrikam" !contains_cs "Kam" |
startswith |
RHS is an initial subsequence of LHS | No | "Fabrikam" startswith "fab" |
!startswith |
RHS isn’t an initial subsequence of LHS | No | "Fabrikam" !startswith "kam" |
startswith_cs |
RHS is an initial subsequence of LHS | Yes | "Fabrikam" startswith_cs "Fab" |
!startswith_cs |
RHS isn’t an initial subsequence of LHS | Yes | "Fabrikam" !startswith_cs "fab" |
endswith |
RHS is a closing subsequence of LHS | No | "Fabrikam" endswith "Kam" |
!endswith |
RHS isn’t a closing subsequence of LHS | No | "Fabrikam" !endswith "brik" |
endswith_cs |
RHS is a closing subsequence of LHS | Yes | "Fabrikam" endswith "Kam" |
!endswith_cs |
RHS isn’t a closing subsequence of LHS | Yes | "Fabrikam" !endswith "brik" |
matches regex |
LHS contains a match for RHS | Yes | "Fabrikam" matches regex "b.*k" |
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 one of the elements | No | "abc" in~ ("123", "345", "ABC") |
!in~ |
Not equals to any of the elements | No | "bca" !in~ ("123", "345", "ABC") |
has_any |
Same as has but works on any of the elements |
No | "North America" has_any("south", "north") |
Operator “==”
The operator “==” means equals. This operator is used most of the time, and it’s used to look for specific values in a column.
For example, when running run a specific query based for AzureActivity with the value CallerIpAddress, than it provides a specific data
AzureActivity| where CallerIpAddress == “84.229.167.55”
Operator “!=”
The “!=” means “Not equals”. It’s will exclude a value or keyword from a column.
For example, if we run the following command we receive the following result:
AzureActivity| where CallerIpAddress != “84.229.167.55”
Operator “=~”
The “=~” is similar to the equals operator, but the difference is, that this operator doesn’t follow the case-sensitive queries.
For example, if we run the following command we will receive the same results
AzureActivity
| where CategoryValue =~ “Administrative”AzureActivity
| where CategoryValue =~ “AdministRativE” (not case-sensative)
Note: the first command and the second command will provide the same result
Operator “!has”
The “has” operator is mainly used to search for a specific value or keyword in a column
For example, running the following command will provide the results
AzureActivity| where Claims has”sts.windows.net”
Operator “!has”
The “!has” operator is meant to exclude a specific value or keyword in a column
AzureActivity| where Claims !has”sts.windows.net”
Operator “has_any”
The “has_any” is similar to the “contains” operator.
This example is from the Microsoft Threat Protection hunting dashboard, and if we run the following command it will provide all data that has any data with the specific code.
Operator “contains”
The “contains” operator means that it will look in the results to see if a part of a keyword or value exists in a column.
For example, If we run the following KQL query it will provide only data that contains FilePreviewed.
OfficeActivity| where Operation contains “FilePreviewed”
Operator “!contains”
The “!contains” operator does the opposite of what the “contains” operator does. It will exclude a part of a value or keyword in a column.
For example, when running the following command then it excludes the FilePreviewed.
OfficeActivity| where Operation !contains “FilePreviewed”
Operator “startswith”
The “startswith” operator says it already, but this operator will look at a value that starts with.
For example, when running the following command it will provide all data that start with Microsoft.
OfficeActivity| where UserAgent startswith “Microsoft”
Operation “!startswith”
The “!startswith” operator does the opposite of what the “startswith” operator does. This operator excludes a value that starts with <insert value>.
For example, when running the following command it provides all data that does not start with Microsoft.
Operator “endswith”
The “endswith” operator says it already, but this operator looks for a value that ends with <insert value>
For example, when running the following command it will provide all data that end with 7eb190308da2.
SigninLogs| where CorrelationId endswith “7eb190308da2”
Operator “!endswith”
The “!endswith” operator does the opposite of what the ends with the operator does. It will exclude all the values that end with <insert value>
SigninLogs
| where CorrelationId !endswith “7eb190308da2”
Operator “matches regex”
The “matches regex” operator is similar to the “contains” operator.
SecurityEvent| where EventSourceName matches regex “Windows-Security-Auditing”
Operator “in”
The “in” operator is used to filter on multiple values.
SecurityEvent| where EventID in (4671,4625,4688)
Operator “!in”
The “!in” operator does the opposite of what the “in” operator does. It excludes multiple values from returning to the results.
SecurityEvent
| where EventID !in (4671,4625,4688)
Operator “in~”
The “in~” operator is the same as the “in” operator, but the slight difference is that it doesn’t follow the case-sensitive rule.
SecurityEvent| where AccountType in~ (“uSER”)
