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
Activityreads theActivitydataset.mv-expand evidencesturns each observation in theevidencesarray into its own row.wherekeeps the rows that looked upc2-tunnel.example.projectkeeps the time and names two new columns,hostanduser.sort by time descputs the newest rows first.take 50returns the first 50 rows.
A few rules apply to every query:
- A query reads one dataset and is one statement.
letstatements andjoinare not supported yet: write values inline, for example in aninlist, 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-expandturns each element of an array into its own row. Name the result to keep paths short:mv-expand ev = evidences.- In
EvidencesandActivity, expandevidencesbefore you filter on its fields. Arrays inside it, such as file hashes, need their ownmv-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, bwith 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)andmax(time)insummarize. - To get results faster when a query matches many rows, end it with
take N. The query stops reading once it has N rows, unlesssortorsummarizecomes beforetake. - To group values into buckets, use
(x / size) * size. For example,by hour = (time / 3600000) * 3600000groups by hour.