Skip to main content

KQL Guide

This guide shows you how to write KQL queries in AlphaSOC KQL Search: how a query is built and examples for common tasks that you can copy and change. For the rules every query follows, see KQL Engine Behavior. For the full list of operators, functions and types, see KQL Reference. For where to run queries and which datasets exist, see KQL Search.

AlphaSOC runs a subset of KQL. A query that uses something outside it is rejected with an error that points at the unsupported part.

How a query works​

A query starts with one dataset name, then a chain of operators, each after a |. Each operator takes the rows from the previous step and passes its result on, so the order of the steps matters.

Activity
| mv-expand evidences
| where evidences.query.hostname == "c2-tunnel.example"
| project time, host = evidences.device.hostname, user = evidences.actor.user.name
| sort by time desc
| take 50
  • Activity reads the Activity dataset.
  • mv-expand evidences turns each observation in the evidences array into its own row.
  • where keeps the rows that looked up c2-tunnel.example.
  • project keeps the time and names two new columns, host and user.
  • sort by time desc puts the newest rows first.
  • take 50 returns the first 50 rows.

A few rules apply to every query:

  • A query reads one dataset and is one statement. let statements and join are not supported yet: write values inline, for example in an in list, and run one query per dataset.
  • The time range comes from the time picker, not from the query. See Time range.
  • // starts a comment that runs to the end of the line.
  • Names with special characters or reserved words go in brackets: ['my col'].

Common tasks​

The examples follow one investigation: was the indicator c2-tunnel.example seen (Evidences), who interacted with it (Activity), and what the device workstation-7 did (Events). These datasets hold large volumes of telemetry, so filter to an indicator, device or user first, and keep only the columns you need.

See a sample of rows​

take (alias limit) returns up to N rows.

Evidences
| take 20

Filter rows​

where (alias filter) keeps the rows where the condition is true. Combine conditions with and, or and not(...). in (...) matches any value in a list.

Exact matches with == and in are the fastest filters, because on the main fields, such as hostnames, IPs, ports, URLs, user agents, hashes, and device and user fields, they can often use an index. contains and functions such as tolower() read more data.

Evidences
| mv-expand evidences
| where evidences.dst_endpoint.ip in ("203.0.113.5", "198.51.100.7")
| project time, ip = evidences.dst_endpoint.ip, port = evidences.dst_endpoint.port
| take 20

Work with nested fields and arrays​

  • Read a nested field with a dot (metadata.product.name) or with brackets (metadata['product']).
  • mv-expand turns each element of an array into its own row. Name the result to keep paths short: mv-expand ev = evidences.
  • In Evidences and Activity, expand evidences before you filter on its fields. Arrays inside it, such as file hashes, need their own mv-expand.
Evidences
| mv-expand evidences
| mv-expand hash = evidences.process.file.hashes
| where hash.value == "00112233445566778899aabbccddeeff00112233445566778899aabbccddeeff"
| project time, name = evidences.process.file.name
| take 50

Choose and rename columns​

project keeps the listed columns and computed ones (name = expression). project-away drops columns, project-keep keeps them, project-rename new = old renames a column, and project-reorder moves columns to the front.

A nested path becomes a column named after its last part: project evidences.device.ip gives a column named ip. Two paths with the same last part clash, so give them names.

Activity
| mv-expand evidences
| where evidences.device.hostname == "workstation-7"
| project time, src = evidences.device.ip, dst = evidences.dst_endpoint.ip
| take 20

Compute new columns​

extend adds columns and keeps the rest. Use iff(), case(), strcat() and the other scalar functions. To use a new column in another expression, define it in an earlier extend.

Events
| where class_uid == 4003
| where query.hostname == "c2-tunnel.example"
| extend host = tostring(device.hostname)
| extend is_server = iff(host contains "server", "yes", "no")
| project time, host, is_server
| take 20

Count and group​

count returns the number of rows. summarize groups rows by the columns after by and computes aggregates: count(), sum(), avg(), min(), max() and dcount().

  • Name computed group columns: by host = evidences.device.hostname.
  • summarize by a, b with no aggregate lists each distinct pair.
Activity
| mv-expand evidences
| where evidences.query.hostname == "c2-tunnel.example"
| summarize lookups = count() by host = evidences.device.hostname, user = evidences.actor.user.name
| sort by lookups desc
| take 20

Sort and keep the top rows​

sort by (alias order by) sorts by one or more columns. The default order is descending; add asc for ascending. For the top N rows, sort and then take: sort by c desc | take 10. sort reads every matching row before it returns any, so filter first.

This query shows the first events in which workstation-7 looked up the indicator:

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

Filter and group by time​

The datasets follow the OCSF schema, so time is a long that holds Unix milliseconds, not a datetime. In Findings, time, start_time and end_time hold Unix seconds. time_dt holds the same time as text. To compare it with a datetime, convert it with todatetime(time_dt).

Compare time with Unix milliseconds for an exact cut inside the time range:

Activity
| where time >= 1767229200000 and time < 1767236400000
| mv-expand evidences
| where evidences.query.hostname == "c2-tunnel.example"
| project time_dt, host = evidences.device.hostname
| take 50

To group by time, use whole-number division of time. This query counts the lookups of the indicator from one device per hour:

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

Chart results​

Add render as the last operator to draw a chart on the Visual tab. The supported charts are table, timechart, linechart, areachart, stackedareachart, barchart, columnchart, piechart, scatterchart and card. Filter and summarize before you chart.

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

Inspect columns​

getschema lists the columns and their types at that point of the query.

Events
| getschema

Tips​

If you know KQL from other tools, these are the ways to do common things here:

  • To match a value in any case, compare tolower(x) with lower-case text.
  • To list distinct values, use summarize by ....
  • To find the first and last time a value was seen, use min(time) and max(time) in summarize.
  • To get results faster when a query matches many rows, end it with take N. The query stops reading once it has N rows, unless sort or summarize comes before take.
  • To group values into buckets, use (x / size) * size. For example, by hour = (time / 3600000) * 3600000 groups by hour.