Kusto KQL - Part 3A - Scalars and String Predicates

Diving into Scalars & String Predicates in KQL

Now that we’ve covered how to view table schemas, perform basic searches, and filter logs by time, it’s time to go deeper into scalar operations and string matching in Kusto Query Language (KQL), key building blocks for powerful filtering.

Types of KQL Statements

• Tabular Expressions – Return result sets (e.g., rows/columns from tables).

• Scalar Expressions – Return single values used in filters, projections, calculations.

What Are Scalars?

In KQL, a scalar value is a single data value (e.g., string, number, datetime, boolean) used in evaluations and comparisons. Scalar expressions return a single value per row, making them essential when using the where clause for filtering data.

Using Scalars for String Filtering

A common task is searching for values like usernames, IP addresses, URLs, hostnames, file paths, etc. You typically use string predicates within the where clause to compare a string column to a search term, which returns a boolean (true or false) to determine whether each row should be included.

| where DeviceName == "PC-01"
| where UserName has "admin"
| where FileName contains "mimikatz"

Core String Operators in KQL

Operator	Description	Case Sensitivity
==	Exact match	Case-sensitive
! =	Not equal	Case-sensitive
has	Matches whole word or term	Case-insensitive (default)
has_any	Matches any of a list of whoe words or terms	("term1", "term2", "term3")
has_all	Matches all of a list of whoe words or terms	("term1", "term2", "term3")
contains	Matches any substring, partial matches allowed	Case-insensitive (default)
startswith	Checks if string begins with a value	Case-insensitive
endswith	Checks if string ends with a value	Case-insensitive
matches regex	Pattern matching with regular expressions	Case-sensitive

Tip: Most of these have case-sensitive versions using cs suffix:e.g., hascs, contains_cs, startswith_cs, etc.

Special Notes on Case Sensitivity & Negation

• Prefixing with ! negates a condition:

FileName !has "setup"

• The not() function is a scalar function that reverses a condition:

not(FileName has "rundll32")

Operators with ~ are often case-insensitive versions of case-sensitive ones.

Understanding has, has_any, and has_all

has

Searches for a single indexed term. Fast and efficient, but doesn’t match substrings.

FileName has "mimikatz"

• Matches: C:\Tools\mimikatz.exe

• Doesn’t match: amimikatz.dll or mimikatz12.exe

has_any

Searches for any one of multiple indexed terms in a string column.

FolderPath has_any ("temp", "public", "downloads")

•  Match if any term is present.

has_all

All listed terms must be present for a match.

FolderPath has_all ("windows", "system32", "drivers")

• Match only if all terms appear in the field.

in Operator for Matching Against a List

Use in to check if a string value equals any value in a list (not for substrings).

FileName in ("rundll32", "powershell", "dllhost")

Case-sensitive by default. No substring matching, must be an exact match.

contains vs has

Feature	has	contains
Match Type	Whole word/term	Substring
Performance	Index-based (faster)	Full scan (slower)
Case	Case-insensitive (default)	Case-insensitive (default)
Flexibility	Low (exact terms only)	High (partial matches)
Indexed Match?	Yes	No

FileName contains "imikat"  // Matches mimikatz.dll

Use contains when:

• Looking for partial matches or typos

• Dealing with non-indexed, arbitrary strings

Best Practices & Tips

• Use has or has_any for performance-critical queries when searching whole terms

• Use contains for flexible string matching, especially for partials

• Avoid searching with terms under 3 characters with has/has_any – they’ll default to full scans

• Be deliberate about case-sensitivity to avoid missed matches

• Use getschema to identify available string fields for searching