KQL Reference
This page describes the operators, functions and types that AlphaSOC KQL Search supports. For how to write queries, see the KQL Guide.
For the rules that apply to every entry, such as casing, types and nulls, see KQL Engine Behavior.
Query structure
Statements
Right now, KQL Search supports only simple queries: one statement that reads
from one dataset. Statements such as let, more than one statement in a query,
and operators that combine datasets, such as join and union, are not
supported yet. To look at several datasets, run one query per dataset.
The statement starts with the dataset name, followed by a chain of pipe
operators, each after a |. Each operator takes the rows from the previous
step and passes its result on. You can end the query with one ;.
Evidences
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| take 20;
Comments
// starts a comment that runs to the end of the line.
// Lookups of the indicator from one device
Activity
| mv-expand ev = evidences // one row per observation
| where ev.query.hostname == "c2-tunnel.example"
| where ev.device.hostname == "workstation-7"
| take 20
Names
- Put a name that has spaces or special characters, or that is a reserved
word, in brackets:
['my col']or["my col"]. The namesfirstandlastalways need brackets. - Read a nested field with a dot (
obj.key) or with a text key in brackets (obj['key']).
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| project ['host name'] = ev.device.hostname, product = metadata['product'].name
| take 20
Pipe operators
Pipe operators take rows and return rows. Write each one after a |.
Filter and select
where
Keeps the rows for which a condition is true. Alias: filter.
Syntax
T | where Predicate
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Predicate | bool | Yes | The condition to test for each row. |
Returns
The input rows for which Predicate is true, with the same columns.
Example
Evidences
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example" and ev.query.type == "A"
| project time, hostname = ev.query.hostname
| take 20
project
Returns only the listed columns, and can rename or compute them.
Syntax
T | project Column [, ...]
T | project Name = Expression [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Column | column | Yes | A column or nested path to keep, such as col or obj.key |
Name | column name | No | The name of the output column |
Expression | any | Yes | The value of the output column |
Returns
The same rows, with one column per item in the order you list them.
Notes
- A nested path without a name gives a column named after its last part:
project obj.keygives a column namedkey. Two items with the same name cause an error, so name them:name = obj.key. - An item that is not a column or path needs a name:
name = tolower(col). - An item cannot use a column created by another item of the same
project.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| project time, host = ev.device.hostname, user = ev.actor.user.name, ev.query.type
| take 20
project-away
Removes the listed columns and keeps the rest.
Syntax
T | project-away Column [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Column | column | Yes | The name of a top-level column, such as raw_data |
Returns
The same rows, with every column except the listed ones, in their input order.
Notes
- Each column must be a top-level column, not a nested path.
Example
Events
| where device.hostname == "workstation-7"
| project-away raw_data, unmapped
| take 20
project-keep
Keeps only the listed columns.
Syntax
T | project-keep Column [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Column | column | Yes | The name of a top-level column |
Returns
The same rows, with only the listed columns.
Notes
- The columns keep their input order, not the order you list them in. Use
projectto set the order.
Example
Events
| where device.hostname == "workstation-7"
| project-keep time, query, raw_data
| take 20
project-reorder
Moves the listed columns to the front.
Syntax
T | project-reorder Column [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Column | column | Yes | The name of a top-level column |
Returns
The same rows and columns. The listed columns come first, in the order you list them, followed by the other columns in their input order.
Example
Events
| where device.hostname == "workstation-7"
| project-reorder raw_data, time
| take 20
project-rename
Renames columns.
Syntax
T | project-rename NewName = Column [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
NewName | column name | Yes | The new name |
Column | column | Yes | The name of the top-level column to rename |
Returns
The same rows and columns, with the listed columns renamed in place.
Notes
- The source must be a top-level column. To rename a nested path, use
projectorextend. - All renames apply at once, so
project-rename a = b, b = aswaps two names. - A new name must not match a column that stays.
Example
Events
| where device.hostname == "workstation-7"
| project-rename event_time = time, raw = raw_data
| take 20
take
Returns up to a given number of rows. Alias: limit.
Syntax
T | take Count
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Count | long | Yes | The largest number of rows to return, from 0. |
Returns
Up to Count input rows, with the same columns.
Notes
Countmust be a whole number written in the query, such astake 20.- Without
sort,takereturns any matching rows. See Row order.
Example
Evidences
| take 20
Compute columns
extend
Adds computed columns and keeps all input columns.
Syntax
T | extend Name = Expression [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Name | column name | Yes | The name of the new column |
Expression | any | Yes | The value of the column |
Returns
The same rows, with the input columns followed by the new columns. A Name
that matches an input column replaces that column.
Notes
- An item cannot use a column created by another item of the same
extend. Put the second column in a laterextend:extend a = ... | extend b = a * 2. - A nested path without a name, such as
extend obj.key, gives a column named after its last part (key).
Example
Events
| where device.hostname == "workstation-7"
| where class_uid == 4003
| extend hostname = tostring(query.hostname)
| extend length = strlen(hostname)
| project time, hostname, length
| take 20
Aggregate and sort
summarize
Groups rows and computes aggregation functions for each group.
Syntax
T | summarize [Name =] Aggregation [, ...] [by [Name =] GroupExpression [, ...]]
T | summarize by [Name =] GroupExpression [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Aggregation | aggregation function call | No | A call such as count() or dcount(col) |
GroupExpression | bool, long, real, decimal, string, datetime, timespan | No | The value to group by. Rows with the same values form one group. |
Name | column name | No | The name of the output column |
Returns
One row per group. The group columns come first, then one column per aggregation.
- Without
by, all rows form one group and the result is one row. With no input rows, that row holds the empty-group value of each aggregation, for example 0 forcount(). - With
byand no input rows, the result has no rows. - With
byand no aggregation, the result lists each distinct combination of the group values.
Notes
- Default column names:
count_forcount(), and the function name plus the last part of the column for other aggregations, for examplesum_portforsum(obj.port). A computed group value or aggregation argument needs a name:by bucket = (col / 100) * 100. - Each aggregation must be a call on its own. Put arithmetic on the result in
a later
extend:summarize c = count() | extend c2 = c * 2.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| summarize lookups = count(), first_seen = min(time) by host = ev.device.hostname
| sort by lookups desc
| take 20
count
Returns the number of input rows.
Syntax
T | count [as Name]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Name | column name | No | The name of the output column. The default is Count. |
Returns
One row with one long column, Count or Name. With no input rows, the
value is 0.
Example
Events
| where device.hostname == "workstation-7"
| where query.hostname == "c2-tunnel.example"
| count as lookups
sort
Sorts the rows by one or more values. Alias: order.
Syntax
T | sort by Expression [asc | desc] [nulls first | nulls last] [, ...]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Expression | bool, long, real, decimal, string, datetime, timespan | Yes | The value to sort by. Later values break ties of earlier ones. |
asc, desc | keyword | No | Ascending or descending order. The default is desc. |
nulls first, nulls last | keyword | No | Where null values go. The default is nulls last for desc and nulls first for asc. |
Returns
The same rows and columns, in sorted order.
Notes
- Use
sort by tolower(name)for an alphabetical order that ignores case.
Example
Events
| where query.hostname == "c2-tunnel.example"
| where device.hostname == "workstation-7"
| project time, origin = metadata.product.name, raw_data
| sort by time asc
| take 20
Arrays and objects
mv-expand
Turns each element of an array, or each key of an object, into its own row.
Alias: mvexpand.
Syntax
T | mv-expand [kind=(bag | array)] [with_itemindex=IndexName] [Name =] ArrayExpression [to typeof(Type)] [, ...] [limit Count]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
ArrayExpression | dynamic | Yes | The array or object to expand |
Name | column name | No | The name of the output column. Required when ArrayExpression is not a column or path. |
kind | bag or array | No | How object keys expand: bag (default) gives a one-key object per row, array gives a [key, value] array per row. bagexpansion is the same option. |
with_itemindex | column name | No | Adds a long column with the position of each element, starting at 0 |
Type | type name | No | Converts each element to this type: bool, long, real, decimal, string, datetime, timespan or dynamic. An element that does not convert gives null. |
Count | long | No | The largest number of rows made from one input row. Must be a whole number written in the query. |
Returns
One row per element. The other columns repeat on each row. The expanded column
holds the element, under its own name or Name, and the index column, when
you ask for one, is added at the end.
Notes
- Several expressions in one
mv-expandexpand side by side. The shorter arrays are padded with null. - An empty array or
dynamicobject gives no rows, so the input row is dropped. - A null value, or a value that is not an array or object, gives one row with that value.
- Keys of a
dynamicobject expand sorted by character code, so upper case comes first. A typed object gives one row per field of its type, in the type's order, including fields with no value.
Example
Evidences
| mv-expand ev = evidences
| mv-expand with_itemindex=i hash = ev.process.file.hashes
| where hash.value == "00112233445566778899aabbccddeeff00112233445566778899aabbccddeeff"
| project time, name = ev.process.file.name, i
| take 50
evaluate bag_unpack()
Turns the keys of an object column into columns.
Syntax
T | evaluate bag_unpack(Column [, Prefix [, ColumnsConflict [, IgnoredProperties]]]) [: (ColumnName:Type [, ...])]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Column | column | Yes | A top-level column that holds an object |
Prefix | string | No | Text added to the front of each new column name |
ColumnsConflict | string | No | What to do when a key matches an existing column: "error" (default), "replace_source" or "keep_source" |
IgnoredProperties | dynamic | No | A dynamic([...]) list of keys to skip |
| Output schema | ColumnName:Type | No | The columns to return and their types. Start the list with * to also keep the other input columns. |
Prefix, ColumnsConflict and IgnoredProperties must be written in the
query, and arguments are positional.
Returns
The same rows, with the other input columns followed by one column per key,
in alphabetical order. Column itself is removed. With an output schema, the
result has exactly the listed columns, after the other input columns when the
list starts with *.
Notes
Columnmust be a top-level column. Useprojectorextendto bring a nested object to the top level first.- On an untyped column, the output schema is required.
- With an output schema, a key that is missing gives null, or
""for astringcolumn. A value that does not convert to the column type gives null.
Example
Events
| where device.hostname == "workstation-7"
| where class_uid == 4003
| project time, query
| evaluate bag_unpack(query): (*, hostname:string, type:string)
| take 20
Metadata
getschema
Returns the columns and their types at this point of the query, instead of rows.
Syntax
T | getschema
Returns
One row per input column, with these columns:
| Column | Type | Description |
|---|---|---|
ColumnName | string | The column name |
ColumnOrdinal | long | The position of the column, starting at 0 |
DataType | string | The type as a .NET name, for example System.String |
ColumnType | string | The type as a KQL name, for example string |
Notes
- Nested object and array columns are shown as
dynamic.
Example
Activity
| mv-expand ev = evidences
| project time, host = ev.device.hostname, port = ev.dst_endpoint.port
| getschema
Visualization
render
Draws the result as a chart on the Visual tab of the console.
Syntax
T | render Visualization [with (Property = Value [, ...])]
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
Visualization | keyword | Yes | table, timechart, linechart, areachart, stackedareachart, barchart, columnchart, piechart, scatterchart or card |
Property | keyword | No | A chart property from the table below |
| Property | Value | Description |
|---|---|---|
title | text | The chart title |
xcolumn | column name | The column for the x-axis |
ycolumns | column names, separated by commas | The columns for the y-axis |
series | column names, separated by commas | The columns that split the values into series |
xtitle, ytitle | text | The axis titles |
xaxis, yaxis | linear or log | The axis scale |
legend | visible or hidden | Shows or hides the legend |
accumulate | true or false | Shows running totals |
kind | default, stacked, stacked100 or unstacked | How series are stacked |
ymin, ymax | number | The y-axis range |
xmin, xmax | number or datetime | The x-axis range |
Returns
The same rows and columns, drawn as a chart on the Visual tab of the console.
Notes
rendermust be the last operator of the query.- Property values must be written in the query: text, numbers, column names or keywords.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| where ev.device.hostname == "workstation-7"
| summarize lookups = count() by hour = todatetime(tostring((time / 3600000) * 3600))
| sort by hour asc
| render timechart with (title = "Lookups per hour", ycolumns = lookups)
Comparison and text operators
Both sides of a comparison must have the same type, or both be numbers. For case, type and null rules, see KQL Engine Behavior.
Equality and ordering
| Operator | Description | Operands | Result |
|---|---|---|---|
== | Equal | Two values of the same type | bool |
!=, <> | Not equal | Two values of the same type | bool |
<, <=, >, >= | Less than, at most, greater than, at least | Two values of the same type | bool |
Text comparisons are case-sensitive.
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| where ev.dst_endpoint.port >= 1024 and ev.query.type != "PTR"
| project time, ip = ev.dst_endpoint.ip, port = ev.dst_endpoint.port
| take 20
Text matching
| Operator | Description | Operands | Result |
|---|---|---|---|
contains | The left side contains the right side | A value, and a string | bool |
!contains, notcontains | The left side does not contain the right side | A value, and a string | bool |
Text matching is not case-sensitive and matches inside longer words. On an
array or object, it searches every element, key and value. A right side that
is not text never matches: n contains 5 matches nothing, while
n contains "5" works. A datetime or timespan left side never matches.
Activity
| mv-expand ev = evidences
| where ev.query.hostname contains "c2-tunnel"
| project time, hostname = ev.query.hostname, host = ev.device.hostname
| take 20
List membership
| Operator | Description | Operands | Result |
|---|---|---|---|
in | Equal to any value in the list | A value, and a list in parentheses of values, columns or arrays | bool |
!in | Not equal to any value in the list | Same as in | bool |
Text values are compared case-sensitively. An array in the list adds each of its elements.
Evidences
| mv-expand ev = evidences
| where ev.dst_endpoint.ip in ("203.0.113.5", "198.51.100.7")
| where ev.dst_endpoint.port !in (80, 443)
| project time, ip = ev.dst_endpoint.ip, port = ev.dst_endpoint.port
| take 20
Arithmetic and logical operators
Arithmetic operators
| Operator | Description | Operands | Result | Notes |
|---|---|---|---|---|
+ | Add | Two numbers; a datetime and a timespan; two timespan values | Number; datetime; timespan | |
- | Subtract | Two numbers; datetime - timespan; two datetime values; two timespan values | Number; datetime; timespan; timespan | datetime - datetime gives the time between them. |
* | Multiply | Two numbers; a timespan and a long or real | Number; timespan | |
/ | Divide | Two numbers; two timespan values; a timespan by a long or real | Number; real; timespan | |
% | Remainder | Two numbers; two timespan values | Number; timespan | The remainder of two whole numbers is never negative. |
- (one operand) | Negate | A number or a timespan | Same type; long for a bool | Write it directly before a name, call, value or ( ). |
+ (one operand) | No change | A number or a timespan | Same type |
For the result type of mixed numbers, division by zero and overflow, see KQL Engine Behavior. An untyped operand gives an untyped result.
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend delay_hours = (todatetime(time_dt) - datetime(2026-01-01)) / 1h
| extend hour = (time / 3600000) * 3600000
| project time, delay_hours, hour
| take 20
Logical operators
| Operator | Description | Operands | Result | Notes |
|---|---|---|---|---|
and | true when both sides are true | Two bool | bool | false when either side is false, even if the other is null. |
or | true when either side is true | Two bool | bool | true when either side is true, even if the other is null. |
not() | Reverses a condition | One bool | bool | A function, not an operator: write not(x). See not(). |
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
and (ev.query.type == "A" or ev.query.type == "AAAA")
and not(isempty(ev.actor.user.name))
| project time, hostname = ev.query.hostname, user = ev.actor.user.name
| take 20
Operator precedence
Operators are applied in this order, from first to last. Use parentheses to change the order.
- Function calls, field access (
.and[ ]) and parentheses - One-operand
-and+ contains,!contains*,/,%+,-<,<=,>,>===,!=,<>,in,!inandor
Because contains comes before arithmetic, n + 1 contains "5" is read as
n + (1 contains "5"). Add parentheses: (n + 1) contains "5".
Scalar functions
Scalar functions take values and return one value per row. Use them in
where, extend, project, sort by and summarize ... by.
Text functions
Text functions turn an argument of any type into text first, as tostring()
does, and read a null value as "", unless the entry says otherwise.
tolower()
Converts text to lower case.
Syntax
tolower(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The text to convert. |
Returns
string.
Example
Activity
| mv-expand ev = evidences
| where tolower(ev.query.hostname) == "c2-tunnel.example"
| take 20
toupper()
Converts text to upper case.
Syntax
toupper(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The text to convert. |
Returns
string.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend user = toupper(ev.actor.user.name)
| take 20
strlen()
Returns the number of characters in a text.
Syntax
strlen(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The text to measure. |
Returns
long.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| where strlen(ev.query.hostname) > 50
| project time, hostname = ev.query.hostname
| take 20
substring()
Returns part of a text.
Syntax
substring(Value, Start [, Length])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The source text |
Start | long or bool | Yes | The position of the first character, from 0. A negative value counts from the end. |
Length | long or bool | No | The number of characters. The default is the rest of the text. |
Returns
string. A Start past the end of the text gives "". A Length longer
than the rest of the text is cut to fit.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| project time, hostname = ev.query.hostname, first10 = substring(ev.query.hostname, 0, 10)
| take 20
indexof()
Returns the position of the first match of a text inside another text.
Syntax
indexof(Value, Lookup)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The text to search |
Lookup | any | Yes | The text to find |
Returns
long: the position of the first match, from 0, or -1 when there is no
match. A null Lookup gives null.
Notes
- The match is case-sensitive.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend dot = indexof(ev.query.hostname, ".")
| extend label = substring(ev.query.hostname, 0, dot)
| take 20
strcat()
Joins values into one text.
Syntax
strcat(Value [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | 1 to 64 values to join, in order |
Returns
string. A null value adds nothing.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend target = strcat(ev.dst_endpoint.ip, ":", ev.dst_endpoint.port)
| take 20
strcat_delim()
Joins values into one text, with a separator between them.
Syntax
strcat_delim(Delimiter, Value1, Value2 [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Delimiter | any | Yes | The text to put between the values |
Value | any | Yes | 2 to 64 values to join, in order |
Returns
string. A null value adds empty text.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| extend who = strcat_delim("/", ev.device.hostname, ev.actor.user.name)
| take 20
split()
Splits a text into an array of texts.
Syntax
split(Value, Delimiter)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The text to split |
Delimiter | string | Yes | The text to split on |
Returns
dynamic: an array of string values. An empty or null Delimiter gives
an array with the whole text.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend labels = split(ev.query.hostname, ".")
| extend depth = array_length(labels)
| take 20
replace_string()
Replaces every match of a text with another text.
Syntax
replace_string(Value, Lookup, Rewrite)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | string | Yes | The source text |
Lookup | string | Yes | The text to find |
Rewrite | string | Yes | The text to put in its place |
Returns
string. An empty Lookup returns Value unchanged.
Notes
- All three arguments must have the type
string. Wrap other values intostring(). - The match is case-sensitive.
Example
Events
| where device.hostname == "workstation-7"
| where class_uid == 4003
| extend name = replace_string(tostring(query.hostname), ".", "[.]")
| project time, name
| take 20
Conversion functions
Conversion functions take one value of any type. A value that cannot be converted gives null.
tostring()
Converts a value to text.
Syntax
tostring(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
string.
Notes
boolvalues becomeTrueandFalse.- A
realkeeps at least one decimal place:5.0becomes"5.0". - A
datetimebecomes ISO 8601 text in UTC, such as2026-01-02T03:04:05Z. - A
timespanbecomes clock text, such as01:30:00or1.02:00:00. - An array or object becomes JSON text. The keys of a
dynamicobject are sorted by character code. A typed nested object keeps the order of its fields.
Example
Events
| where device.hostname == "workstation-7"
| summarize events = count() by dst = tostring(dst_endpoint.ip)
| sort by events desc
| take 20
tolong()
Converts a value to a whole number. Alias: toint().
Syntax
tolong(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
long.
Notes
- Text must hold a whole number, such as
"42","-7"or"0x1A". Text such as"3.9"or"1e2"gives null. - A
realordecimalis cut toward zero:tolong(3.9)is 3. trueis 1 andfalseis 0.- A
datetime, atimespan, or a number outside thelongrange gives null.
Example
Events
| where device.hostname == "workstation-7"
| where tolong(dst_endpoint.port) == 53
| project time, ip = dst_endpoint.ip
| take 20
toreal()
Converts a value to a real number. Alias: todouble().
Syntax
toreal(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
real.
Notes
- Text must hold a number, such as
"1.5"or"1e3", or"nan","inf"or"infinity". trueis 1.0 andfalseis 0.0.- A
datetimeortimespangives null.
Example
Events
| where device.hostname == "workstation-7"
| extend port = toreal(dst_endpoint.port)
| project time, port
| take 20
todecimal()
Converts a value to a decimal number.
Syntax
todecimal(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
decimal.
Notes
- Text must hold a number. It is rounded to 34 significant digits.
trueis 1 andfalseis 0.
Example
Events
| where device.hostname == "workstation-7"
| extend port = todecimal(dst_endpoint.port)
| project time, port
| take 20
tobool()
Converts a value to true or false.
Syntax
tobool(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
bool.
Notes
- Text must be
"true"or"false"in any case, or a whole number. Other text, such as"yes", gives null. - A number other than 0 is
true, and 0 isfalse. - A
datetimeortimespangives null.
Example
Events
| where device.hostname == "workstation-7"
| where tobool(device.is_managed)
| project time, origin = metadata.product.name
| take 20
todatetime()
Converts a value to a datetime.
Syntax
todatetime(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
datetime.
Notes
- Text can be in any form a
datetime(...)literal accepts, such as"2026-01-02T03:04:05Z"or"2026-01-02 03:04". Text that holds a whole number is read as Unix seconds. - A number gives null. For Unix seconds in a number, convert to text first:
todatetime(tostring(n)).
Example
Activity
| where todatetime(time_dt) >= datetime(2026-01-01)
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| project time_dt, host = ev.device.hostname
| take 20
totimespan()
Converts a value to a timespan.
Syntax
totimespan(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to convert |
Returns
timespan.
Notes
- Text must be in clock form:
[days.]hh:mm[:ss[.fraction]], such as"01:30:00"or"1.02:00:00". Text with a unit, such as"1h", gives null. - A number gives null.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| where todatetime(time_dt) - datetime(2026-01-01) > totimespan("12:00:00")
| take 20
Conditional and null handling
iff()
Returns one of two values, based on a condition. Alias: iif().
Syntax
iff(Condition, Then, Else)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Condition | bool | Yes | The condition to test |
Then | any | Yes | The value when Condition is true |
Else | any | Yes | The value when Condition is false or null |
Returns
The type of Then and Else, or dynamic when either one is untyped. Typed
values must have the same type: iff(c, 1, 2.5) is an error, so write 1.0.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| extend user = iff(isempty(ev.actor.user.name), "unknown", ev.actor.user.name)
| project time, user
| take 20
case()
Returns the value of the first condition that is true.
Syntax
case(Condition1, Value1 [, Condition2, Value2, ...], Else)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Condition | bool | Yes | A condition to test, in order |
Value | any | Yes | The value when its condition is true |
Else | any | Yes | The value when no condition is true |
Returns
The type of the values, or dynamic when one of them is untyped.
Notes
- A null condition counts as
false. - Typed values must have the same type, as in
iff().
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend service = case(ev.dst_endpoint.port == 53, "dns", ev.dst_endpoint.port == 443, "https", "other")
| project time, service
| take 20
coalesce()
Returns the first value that is not null.
Syntax
coalesce(Value1, Value2 [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | Two or more values, tested in order |
Returns
The type of the values, or dynamic when one of them is untyped. Null when
every value is null.
Notes
- Typed values must have the same type, as in
iff(). - Empty text
""is not null, so it is returned. To skip empty text, useiff(isempty(a), b, a).
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| extend who = coalesce(ev.actor.user.name, ev.device.hostname)
| project time, who
| take 20
not()
Reverses a condition.
Syntax
not(Condition)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Condition | bool | Yes | The condition to reverse |
Returns
bool: true for false and false for true. A null condition gives
null.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| where not(ev.query.hostname contains "example.com")
| take 20
isnull()
Tests whether a value is null.
Syntax
isnull(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to test |
Returns
bool: true only for null.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| where isnull(ev.device.hostname)
| take 20
isnotnull()
Tests whether a value is not null.
Syntax
isnotnull(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to test |
Returns
bool: false only for null.
Example
Activity
| mv-expand ev = evidences
| where isnotnull(ev.actor.user.name) and ev.actor.user.name !in ("admin", "root")
| where ev.device.hostname == "workstation-7"
| take 20
isempty()
Tests whether a value is null or empty text.
Syntax
isempty(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to test |
Returns
bool: true for null and for "". Any other value, including an empty
array or object and the number 0, gives false.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| where isempty(ev.actor.user.name)
| take 20
isnotempty()
Tests whether a value is neither null nor empty text.
Syntax
isnotempty(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to test |
Returns
bool: false for null and for "", true for any other value.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| where isnotempty(ev.actor.user.name)
| project time, user = ev.actor.user.name
| take 20
Arrays and objects
The arguments marked dynamic take an array or object, such as an untyped
column, a typed nested field, or a dynamic(...) literal. A value of another type, such as a string column, causes an error.
pack_array()
Builds an array from values.
Syntax
pack_array(Value [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | One or more values, in order |
Returns
dynamic: an array of the values. A null value is kept as a null element.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend endpoint = pack_array(ev.dst_endpoint.ip, ev.dst_endpoint.port)
| take 20
bag_pack()
Builds an object from keys and values. Alias: pack().
Syntax
bag_pack(Key1, Value1 [, Key2, Value2, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Key | string | Yes | The key |
Value | any | Yes | The value of the key |
Returns
dynamic: an object. A pair with a null or empty key is left out. When a key
repeats, the last value is kept.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| extend source = bag_pack("host", ev.device.hostname, "user", ev.actor.user.name)
| project time, source
| take 20
bag_keys()
Returns the top-level keys of an object.
Syntax
bag_keys(Object)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Object | dynamic | Yes | The object |
Returns
dynamic: an array of the keys, sorted by character code. Null when Object
is not an object.
Example
Events
| where device.hostname == "workstation-7"
| extend keys = bag_keys(unmapped)
| project time, keys
| take 20
bag_merge()
Merges objects into one object.
Syntax
bag_merge(Object1, Object2 [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Object | dynamic | Yes | 2 to 64 objects to merge |
Returns
dynamic: an object with the keys of all objects. When a key is in more than
one object, the value from the first one is kept. Null when an argument is not
an object.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| extend context = bag_merge(ev.query, ev.device)
| project time, context
| take 20
bag_remove_keys()
Returns an object without the listed keys.
Syntax
bag_remove_keys(Object, Keys)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Object | dynamic | Yes | The object |
Keys | dynamic | Yes | An array of keys to remove, such as dynamic(["uid", "type_id"]) |
Returns
dynamic: the object without the listed keys. Null when Object is not an
object.
Notes
- A key that starts with
$is a path to a nested key, for example$.os.nameor$['os'].
Example
Events
| where device.hostname == "workstation-7"
| extend device_info = bag_remove_keys(device, dynamic(["uid", "$.os.build"]))
| project time, device_info
| take 20
array_length()
Returns the number of elements in an array.
Syntax
array_length(Array)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Array | any | Yes | The array |
Returns
long. Null when Array is not an array.
Example
Evidences
| mv-expand ev = evidences
| where ev.process.file.name == "invoice.exe"
| extend hashes = array_length(ev.process.file.hashes)
| project time, hashes
| take 20
array_concat()
Joins arrays into one array.
Syntax
array_concat(Array [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Array | dynamic | Yes | One or more arrays, in order |
Returns
dynamic: an array with the elements of all arrays. Null when an argument is
not an array.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend parts = array_concat(split(ev.query.hostname, "."), pack_array(ev.query.type))
| take 20
array_slice()
Returns part of an array.
Syntax
array_slice(Array, Start, End)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Array | dynamic | Yes | The array |
Start | long or bool | Yes | The position of the first element, from 0. A negative value counts from the end. |
End | long or bool | Yes | The position of the last element, included. A negative value counts from the end. |
Returns
dynamic: an array of the elements from Start to End. Positions outside
the array are cut to fit, and Start after End gives an empty array. Null
when Array is not an array.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend top_level = array_slice(split(ev.query.hostname, "."), -2, -1)
| take 20
array_index_of()
Returns the position of a value in an array.
Syntax
array_index_of(Array, Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Array | dynamic | Yes | The array to search |
Value | bool, long, real, decimal, string, datetime, timespan | Yes | The value to find |
Returns
long: the position of the first equal element, from 0, or -1 when there is
none. Null when Value is null or Array is not an array.
Notes
- An untyped
Valuecauses an error. Convert it first, for example withtostring().
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| where array_index_of(split(ev.query.hostname, "."), "c2-tunnel") >= 0
| take 20
zip()
Pairs the elements of arrays by position.
Syntax
zip(Array1, Array2 [, ...])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Array | dynamic | Yes | 2 to 16 arrays |
Returns
dynamic: an array of arrays. Element N holds element N of each input array.
The result is as long as the longest array, and shorter arrays are padded with
null. Null when an argument is not an array.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| extend labels = split(ev.query.hostname, ".")
| extend pairs = zip(labels, array_slice(labels, 1, -1))
| take 20
Type checks
gettype()
Returns the type name of a value.
Syntax
gettype(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to inspect |
Returns
string:
- For a typed value, its type:
bool,long,real,decimal,string,datetimeortimespan. A typed null still reports its type, sogettype(long(null))is"long". - For an untyped value, the type of the value:
null,bool,long,double,decimal,string,datetime,timespan,arrayordictionary(an object).
Example
Events
| where device.hostname == "workstation-7"
| summarize events = count() by port_type = gettype(dst_endpoint.port)
Aggregation functions
Aggregation functions compute one value from the rows of a group. Use them
only as items of summarize, each on its own: summarize n = count(). Put
arithmetic on the result in a later extend.
All aggregation functions except count() skip null values.
Count and distinct count
count()
Counts the rows of the group.
Syntax
count()
Arguments
None.
Returns
long. An empty group gives 0.
Example
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 20
dcount()
Counts the distinct values in the group.
Syntax
dcount(Value [, Accuracy])
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | bool, long, real, decimal, string, datetime, timespan | Yes | The value to count |
Accuracy | long | No | A whole number from 0 to 4, written in the query. It has no effect. |
Returns
long: the number of distinct non-null values. An empty group gives 0.
Notes
- An untyped
Valuecauses an error. Convert it first, for example withtostring(). - The count is exact.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| summarize hosts = dcount(ev.device.hostname), users = dcount(ev.actor.user.name)
Sum, average, minimum and maximum
sum()
Adds the values of the group.
Syntax
sum(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | long, bool, real, decimal, timespan or dynamic | Yes | The value to add |
Returns
Value type | Result type | Empty group or only nulls |
|---|---|---|
long or bool | long | 0 |
real | real | 0 |
decimal | decimal | 0 |
timespan | timespan | 00:00:00 |
dynamic | dynamic | null |
Notes
- For untyped values, text that holds a number is added as that number. Null when the values cannot be added together, for example text that is not a number, or arrays.
Example
Events
| where class_uid == 4001
| where device.hostname == "workstation-7"
| summarize bytes_out = sum(tolong(traffic.bytes_out)) by dst = tostring(dst_endpoint.ip)
| take 20
avg()
Returns the average of the values of the group.
Syntax
avg(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | long, bool, real, decimal, timespan or dynamic | Yes | The value to average |
Returns
Value type | Result type | Empty group or only nulls |
|---|---|---|
long, bool or real | real | NaN |
decimal | decimal | null |
timespan | timespan | null |
dynamic | dynamic | NaN |
Notes
- For untyped values, the rules of
sum()apply.
Example
Activity
| mv-expand ev = evidences
| where ev.device.hostname == "workstation-7"
| summarize avg_port = avg(ev.dst_endpoint.port), lookups = count()
min()
Returns the smallest value of the group.
Syntax
min(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to compare |
Returns
The type of Value. An empty group, or one with only nulls, gives null.
Notes
- For untyped values, null when the values cannot be compared with each other, for example a number and a text that is not a number.
Example
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| summarize first_seen = min(time), last_seen = max(time) by host = ev.device.hostname
| extend first_seen_dt = todatetime(tostring(first_seen / 1000))
max()
Returns the largest value of the group.
Syntax
max(Value)
Arguments
| Name | Type | Required | Description |
|---|---|---|---|
Value | any | Yes | The value to compare |
Returns
The type of Value. An empty group, or one with only nulls, gives null.
Notes
- For untyped values, the rule of
min()applies.
Example
Events
| where device.hostname == "workstation-7"
| where class_uid == 4003
| summarize last_seen = max(time) by hostname = tostring(query.hostname)
| sort by last_seen desc
| take 20
Types and literals
A literal is a value written in the query. The entries below show how to write each type, how values appear in results, and how to write a null value of that type. In results, the console JSON tab and exports show values as JSON.
Scalar types
bool
true or false.
- Literals:
true,false,bool(true) - In results:
trueorfalse.tostring()givesTrueorFalse. - Null:
bool(null)
long
A 64-bit whole number. Alias: int.
- Literals:
42,-7,0x1A,long(42),int(42) - In results: a JSON number, such as
42. - Null:
long(null)orint(null)
real
A floating-point number. Alias: double.
- Literals:
1.5,1e3,5.,real(2),double(2),real(nan),real(+inf),real(-inf). Write0.5, not.5. - In results: a JSON number, such as
1.5. A whole value is shown without a decimal point (5).NaNand infinity are shown as the text"NaN","Infinity"and"-Infinity". - Null:
real(null)ordouble(null)
decimal
An exact decimal number with up to 34 significant digits.
- Literals:
decimal(1.5),decimal(5),decimal(1e5),decimal(.5) - In results: JSON text, such as
"1.5". - Null:
decimal(null)
string
Text.
- Literals:
"text"or'text'. Use\for escapes, such as\",\\and\n. A verbatim string starts with@and has no escapes:@"C:\Windows\Temp". Strings written next to each other are joined:"ab" "cd"is"abcd". - In results: JSON text.
- Null:
tostring(long(null))
datetime
A point in time, in UTC.
- Literals:
datetime(2026-01-01),datetime(2026-01-01 10:00),datetime(2026-01-01T10:00:00Z),datetime("2026-01-01"). A whole number is read as Unix seconds:datetime(1767225600). - In results: ISO 8601 text in UTC, such as
"2026-01-02T03:04:05Z", with up to 7 decimal places of seconds. - Null:
datetime(null)
timespan
A length of time. Alias: time.
- Literals: a number with a unit:
2d,1h,1.5h,30m,10s,100ms,10microseconds,1tick, or with a long unit name, such as2hoursor3minutes. Alsotimespan(1.02:00:00)(1 day and 2 hours),timespan(2)(2 days) andtime(1h). Write a week as7d. - In results: clock text
[days.]hh:mm:ss[.fraction], such as"01:30:00"or"1.02:00:00". - Null:
timespan(null)ortime(null)
Dynamic type
dynamic
A JSON-like value: an array, an object, a number, text, true, false or
null.
- Literals:
dynamic(["a", "b"]),dynamic({"k": 1}),dynamic(5),dynamic("text"). In a literal, a whole number is alongand a number with a decimal point or exponent is areal. - In results: JSON. The keys of a
dynamicobject are sorted by character code. A typed nested object keeps the order of its fields. - Null:
dynamic(null) - Notes: Typed nested objects and arrays work wherever
dynamicis accepted. For howdynamicvalues behave, see Dynamic values.
Null values
Each type has a null value, written as a typed null such as long(null),
dynamic(null) or tostring(long(null)). A typed null keeps its type, so it
can be used where that type is required, for example as a branch of iff().
For how nulls behave in operators and functions, see
Nulls and empty text.
Activity
| mv-expand ev = evidences
| where ev.query.hostname == "c2-tunnel.example"
| extend port = iff(ev.dst_endpoint.port > 0, ev.dst_endpoint.port, long(null))
| where isnotnull(port)
| take 20
Type names
Use these names in to typeof(...) of mv-expand and in the output schema
of evaluate bag_unpack().
| Type | Names |
|---|---|
bool | bool, boolean |
long | long, int, int64 |
real | real, double |
decimal | decimal |
string | string |
datetime | datetime, date |
timespan | timespan, time |
dynamic | dynamic |