Skip to main content

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 names first and last always 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

NameTypeRequiredDescription
PredicateboolYesThe 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

NameTypeRequiredDescription
ColumncolumnYesA column or nested path to keep, such as col or obj.key
Namecolumn nameNoThe name of the output column
ExpressionanyYesThe 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.key gives a column named key. 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

NameTypeRequiredDescription
ColumncolumnYesThe 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

NameTypeRequiredDescription
ColumncolumnYesThe 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 project to 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

NameTypeRequiredDescription
ColumncolumnYesThe 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

NameTypeRequiredDescription
NewNamecolumn nameYesThe new name
ColumncolumnYesThe 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 project or extend.
  • All renames apply at once, so project-rename a = b, b = a swaps 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

NameTypeRequiredDescription
CountlongYesThe largest number of rows to return, from 0.

Returns

Up to Count input rows, with the same columns.

Notes

  • Count must be a whole number written in the query, such as take 20.
  • Without sort, take returns 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

NameTypeRequiredDescription
Namecolumn nameYesThe name of the new column
ExpressionanyYesThe 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 later extend: 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

NameTypeRequiredDescription
Aggregationaggregation function callNoA call such as count() or dcount(col)
GroupExpressionbool, long, real, decimal, string, datetime, timespanNoThe value to group by. Rows with the same values form one group.
Namecolumn nameNoThe 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 for count().
  • With by and no input rows, the result has no rows.
  • With by and no aggregation, the result lists each distinct combination of the group values.

Notes

  • Default column names: count_ for count(), and the function name plus the last part of the column for other aggregations, for example sum_port for sum(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

NameTypeRequiredDescription
Namecolumn nameNoThe 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

NameTypeRequiredDescription
Expressionbool, long, real, decimal, string, datetime, timespanYesThe value to sort by. Later values break ties of earlier ones.
asc, desckeywordNoAscending or descending order. The default is desc.
nulls first, nulls lastkeywordNoWhere 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

NameTypeRequiredDescription
ArrayExpressiondynamicYesThe array or object to expand
Namecolumn nameNoThe name of the output column. Required when ArrayExpression is not a column or path.
kindbag or arrayNoHow 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_itemindexcolumn nameNoAdds a long column with the position of each element, starting at 0
Typetype nameNoConverts each element to this type: bool, long, real, decimal, string, datetime, timespan or dynamic. An element that does not convert gives null.
CountlongNoThe 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-expand expand side by side. The shorter arrays are padded with null.
  • An empty array or dynamic object 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 dynamic object 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

NameTypeRequiredDescription
ColumncolumnYesA top-level column that holds an object
PrefixstringNoText added to the front of each new column name
ColumnsConflictstringNoWhat to do when a key matches an existing column: "error" (default), "replace_source" or "keep_source"
IgnoredPropertiesdynamicNoA dynamic([...]) list of keys to skip
Output schemaColumnName:TypeNoThe 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

  • Column must be a top-level column. Use project or extend to 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 a string column. 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:

ColumnTypeDescription
ColumnNamestringThe column name
ColumnOrdinallongThe position of the column, starting at 0
DataTypestringThe type as a .NET name, for example System.String
ColumnTypestringThe 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

NameTypeRequiredDescription
VisualizationkeywordYestable, timechart, linechart, areachart, stackedareachart, barchart, columnchart, piechart, scatterchart or card
PropertykeywordNoA chart property from the table below
PropertyValueDescription
titletextThe chart title
xcolumncolumn nameThe column for the x-axis
ycolumnscolumn names, separated by commasThe columns for the y-axis
seriescolumn names, separated by commasThe columns that split the values into series
xtitle, ytitletextThe axis titles
xaxis, yaxislinear or logThe axis scale
legendvisible or hiddenShows or hides the legend
accumulatetrue or falseShows running totals
kinddefault, stacked, stacked100 or unstackedHow series are stacked
ymin, ymaxnumberThe y-axis range
xmin, xmaxnumber or datetimeThe x-axis range

Returns

The same rows and columns, drawn as a chart on the Visual tab of the console.

Notes

  • render must 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​

OperatorDescriptionOperandsResult
==EqualTwo values of the same typebool
!=, <>Not equalTwo values of the same typebool
<, <=, >, >=Less than, at most, greater than, at leastTwo values of the same typebool

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​

OperatorDescriptionOperandsResult
containsThe left side contains the right sideA value, and a stringbool
!contains, notcontainsThe left side does not contain the right sideA value, and a stringbool

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​

OperatorDescriptionOperandsResult
inEqual to any value in the listA value, and a list in parentheses of values, columns or arraysbool
!inNot equal to any value in the listSame as inbool

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​

OperatorDescriptionOperandsResultNotes
+AddTwo numbers; a datetime and a timespan; two timespan valuesNumber; datetime; timespan
-SubtractTwo numbers; datetime - timespan; two datetime values; two timespan valuesNumber; datetime; timespan; timespandatetime - datetime gives the time between them.
*MultiplyTwo numbers; a timespan and a long or realNumber; timespan
/DivideTwo numbers; two timespan values; a timespan by a long or realNumber; real; timespan
%RemainderTwo numbers; two timespan valuesNumber; timespanThe remainder of two whole numbers is never negative.
- (one operand)NegateA number or a timespanSame type; long for a boolWrite it directly before a name, call, value or ( ).
+ (one operand)No changeA number or a timespanSame 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​

OperatorDescriptionOperandsResultNotes
andtrue when both sides are trueTwo boolboolfalse when either side is false, even if the other is null.
ortrue when either side is trueTwo boolbooltrue when either side is true, even if the other is null.
not()Reverses a conditionOne boolboolA 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.

  1. Function calls, field access (. and [ ]) and parentheses
  2. One-operand - and +
  3. contains, !contains
  4. *, /, %
  5. +, -
  6. <, <=, >, >=
  7. ==, !=, <>, in, !in
  8. and
  9. or

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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe source text
Startlong or boolYesThe position of the first character, from 0. A negative value counts from the end.
Lengthlong or boolNoThe 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

NameTypeRequiredDescription
ValueanyYesThe text to search
LookupanyYesThe 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

NameTypeRequiredDescription
ValueanyYes1 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

NameTypeRequiredDescription
DelimiteranyYesThe text to put between the values
ValueanyYes2 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

NameTypeRequiredDescription
ValueanyYesThe text to split
DelimiterstringYesThe 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

NameTypeRequiredDescription
ValuestringYesThe source text
LookupstringYesThe text to find
RewritestringYesThe 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 in tostring().
  • 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

NameTypeRequiredDescription
ValueanyYesThe value to convert

Returns

string.

Notes

  • bool values become True and False.
  • A real keeps at least one decimal place: 5.0 becomes "5.0".
  • A datetime becomes ISO 8601 text in UTC, such as 2026-01-02T03:04:05Z.
  • A timespan becomes clock text, such as 01:30:00 or 1.02:00:00.
  • An array or object becomes JSON text. The keys of a dynamic object 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

NameTypeRequiredDescription
ValueanyYesThe 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 real or decimal is cut toward zero: tolong(3.9) is 3.
  • true is 1 and false is 0.
  • A datetime, a timespan, or a number outside the long range 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

NameTypeRequiredDescription
ValueanyYesThe value to convert

Returns

real.

Notes

  • Text must hold a number, such as "1.5" or "1e3", or "nan", "inf" or "infinity".
  • true is 1.0 and false is 0.0.
  • A datetime or timespan gives 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

NameTypeRequiredDescription
ValueanyYesThe value to convert

Returns

decimal.

Notes

  • Text must hold a number. It is rounded to 34 significant digits.
  • true is 1 and false is 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

NameTypeRequiredDescription
ValueanyYesThe 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 is false.
  • A datetime or timespan gives 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ConditionboolYesThe condition to test
ThenanyYesThe value when Condition is true
ElseanyYesThe 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

NameTypeRequiredDescription
ConditionboolYesA condition to test, in order
ValueanyYesThe value when its condition is true
ElseanyYesThe 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

NameTypeRequiredDescription
ValueanyYesTwo 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, use iff(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

NameTypeRequiredDescription
ConditionboolYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesOne 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

NameTypeRequiredDescription
KeystringYesThe key
ValueanyYesThe 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

NameTypeRequiredDescription
ObjectdynamicYesThe 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

NameTypeRequiredDescription
ObjectdynamicYes2 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

NameTypeRequiredDescription
ObjectdynamicYesThe object
KeysdynamicYesAn 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.name or $['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

NameTypeRequiredDescription
ArrayanyYesThe 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

NameTypeRequiredDescription
ArraydynamicYesOne 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

NameTypeRequiredDescription
ArraydynamicYesThe array
Startlong or boolYesThe position of the first element, from 0. A negative value counts from the end.
Endlong or boolYesThe 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

NameTypeRequiredDescription
ArraydynamicYesThe array to search
Valuebool, long, real, decimal, string, datetime, timespanYesThe 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 Value causes an error. Convert it first, for example with tostring().

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

NameTypeRequiredDescription
ArraydynamicYes2 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

NameTypeRequiredDescription
ValueanyYesThe value to inspect

Returns

string:

  • For a typed value, its type: bool, long, real, decimal, string, datetime or timespan. A typed null still reports its type, so gettype(long(null)) is "long".
  • For an untyped value, the type of the value: null, bool, long, double, decimal, string, datetime, timespan, array or dictionary (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

NameTypeRequiredDescription
Valuebool, long, real, decimal, string, datetime, timespanYesThe value to count
AccuracylongNoA 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 Value causes an error. Convert it first, for example with tostring().
  • 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

NameTypeRequiredDescription
Valuelong, bool, real, decimal, timespan or dynamicYesThe value to add

Returns

Value typeResult typeEmpty group or only nulls
long or boollong0
realreal0
decimaldecimal0
timespantimespan00:00:00
dynamicdynamicnull

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

NameTypeRequiredDescription
Valuelong, bool, real, decimal, timespan or dynamicYesThe value to average

Returns

Value typeResult typeEmpty group or only nulls
long, bool or realrealNaN
decimaldecimalnull
timespantimespannull
dynamicdynamicNaN

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

NameTypeRequiredDescription
ValueanyYesThe 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

NameTypeRequiredDescription
ValueanyYesThe 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: true or false. tostring() gives True or False.
  • 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) or int(null)

real​

A floating-point number. Alias: double.

  • Literals: 1.5, 1e3, 5., real(2), double(2), real(nan), real(+inf), real(-inf). Write 0.5, not .5.
  • In results: a JSON number, such as 1.5. A whole value is shown without a decimal point (5). NaN and infinity are shown as the text "NaN", "Infinity" and "-Infinity".
  • Null: real(null) or double(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 as 2hours or 3minutes. Also timespan(1.02:00:00) (1 day and 2 hours), timespan(2) (2 days) and time(1h). Write a week as 7d.
  • In results: clock text [days.]hh:mm:ss[.fraction], such as "01:30:00" or "1.02:00:00".
  • Null: timespan(null) or time(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 a long and a number with a decimal point or exponent is a real.
  • In results: JSON. The keys of a dynamic object 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 dynamic is accepted. For how dynamic values 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().

TypeNames
boolbool, boolean
longlong, int, int64
realreal, double
decimaldecimal
stringstring
datetimedatetime, date
timespantimespan, time
dynamicdynamic