Skip to main content

KQL Engine Behavior

These rules describe how AlphaSOC KQL Search handles names, types and values. They apply to every operator and function. To learn how to write a query, see the KQL Guide.

Casing​

  • Dataset names, column names and object keys are case-sensitive. Operator keywords and function names are not: where, WHERE and Where all work.
  • ==, !=, in and !in compare text with case. contains and !contains ignore case. To compare text in any case, use tolower().
  • Text sorts and compares by character code, so upper case comes before lower case: "B" < "a" is true. This applies to <, >, sort, min() and max().

Row order​

Rows come in no particular order unless the query sorts them. Only sort sets an order you can rely on. take without sort returns any matching rows, not the newest, and summarize does not put the largest groups first. Sort before you take:

Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| summarize lookups = count() by host = ev.device.hostname
| sort by lookups desc
| take 10

Types and conversions​

  • Numbers of different types work together in arithmetic and comparisons. The result takes the more precise type, in the order long, real, decimal.
  • Arithmetic on two whole numbers gives a whole number: 7 / 2 is 3. Write 7 / 2.0 for 3.5.
  • Literals are converted to the type of the column they are compared with.
  • Columns and computed values are not silently converted. They require explicit casts.
  • datetime values work with timespan values in arithmetic. Plain numbers are not converted to time values.
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend hours = (todatetime(time_dt) - datetime(2026-01-01)) / 1h
| project time_dt, hours
| take 20

Dynamic values​

A dynamic value is untyped: its type is known only when the query runs.

  • A path into a dynamic value, such as query.hostname, is also dynamic. A key that does not exist, or has the wrong case, gives null and no error. On typed fields, a wrong path is an error.
  • dynamic values are converted to the type of the value they are compared with.
  • Use casts such as tostring() or tolong() to give a dynamic value an explicit type, for example before you group, sort or count by it. See Conversion functions.
  • To read the elements of an array, use mv-expand. It turns each element into a row, and with_itemindex adds its position. Comparing a whole array with a value using == gives false, and != gives true for every row, so expand the array first and filter on the element.

This query finds the DNS lookups that resolved to one IP address. answers is an array, so mv-expand gives one row per answer, where keeps the answers with that address, and tostring() turns the dynamic values into text:

Events
| where class_uid == 4003
| mv-expand answer = answers
| where answer.rdata == "203.0.113.10"
| project time, host = tostring(device.hostname), hostname = tostring(query.hostname)
| take 20

Nulls and empty text​

Null means "no value". Any field can be null, and a field that is missing from a row is null too. Null is not the same as empty text "".

Find nulls and empty values

  • isnull(x) and isnotnull(x) test for null.
  • isempty(x) and isnotempty(x) treat null and "" the same. Use them when you do not care which one it is. x == "" does not match null.

Comparisons and filters

  • A null value is not equal to anything, so x != "a" also keeps the rows where x is null. To drop them, write isnotnull(x) and x != "a". The same applies to !in and !contains.

Calculations and functions

  • Arithmetic with null gives null.
  • Text functions such as strlen() treat null as "". Conversion functions such as tostring() keep it null.
  • Aggregation functions skip nulls. count() counts every row.

Write a null

null on its own is not a value. Write a typed null instead, such as long(null), datetime(null) or dynamic(null).

Impossible operations and overflow​

An operation with no valid result does not fail the query. It returns a value the result type can express, such as Infinity for a real, and null when the type has no such value. For example:

OperationResult
Division or % by 0null; Infinity, -Infinity or NaN for real division
Overflow, a result too large for its typenull; Infinity or -Infinity for a real
A conversion that fails, such as tolong("3.9")null