## Hamelin Instructions

I am going to teach you a new query language, it’s called Hamelin. Its main purpose is to support SIEM use-cases. In some ways, it is similar to SQL, PRQL, Elastic Query Language, Sumo Logic Query Language or Splunk Query Language, though it has some distinct properties. It’s a pipe language, which processes data and allows to separate the commands using pipes. Throughout this guide, I will include some examples in <example></example> tags.

Hamelin query consists out of one or more commands separated by pipe ("|"). It is very important to memorize the commands, as each pipe command must start with either of these. Following are the only commands supported:

- `FROM` is the beginning of every Hamelin query. Every query must pull data from _somewhere._ One exception is that Hamelin can be used to calculate some expression value too, e.g. <example>SET x = 8 * 2</example>.
- `SELECT` allows to specify which columns are selected: <example>SELECT <col1>, <col2>, <col3> = <val></example> drops all columns except columns 1 and 2, and also defines a 3rd column to a new, specific value. It’s optional and by default, SELECT * is implied (which selects all columns).
- `WHERE` filters the rows that flow through it based on applying a boolean condition to each of them.
- `SET <col> = <val>` defines a new column value, or shadows an existing value.
- `DROP <col1>, <col2>` removes col1 and col2 from the projection downstream and in the final result set.
- `DEF <tab1> = <commands>;` associates the tab1 with specified commands, so tab1 can be reused later
- `AGG <function1, function2, ...> BY <column1, column2, ...>` aggregates and groups by the data accordingly
- `WINDOW` applies aggregation or window functions over sliding windows of data.
- `MATCH` correlates multiple event patterns together. Supports an optional `AGG` clause for aggregating over matched rows.
- `SORT <columns> <optional order>` sorts the rows using specified criteria (and `ASC` or `DESC` order, `ASC` being default)
- `LIMIT <limit>` limits the output rows
- `UNNEST <expr>` lifts a struct or array of structs into the parent or enclosing result set. The source column is consumed when the expression is a simple column reference.
- `EXPLODE <expr or assignment_clause>` expand array fields into separate rows.
- `TRIMSTRINGS <n>` byte-caps every string-typed column to at most `n` bytes, recursing through structs and arrays. The argument must be a non-negative integer constant. Truncation lands on a valid UTF-8 boundary (partial codepoints are dropped). NULL values stay NULL.

It is extremely important to start each pipe section using one of the commands and use pipe ("|") to separate further commands, e.g.
<example>
FROM example | WHERE name = "John Smith" | SELECT name, address
</example>

For example, to select columns "c1" and "c2" from table "A", one needs to write:

<example>
FROM A
| SELECT c1, c2
</example>

To add a filter for "c1=3" condition:

<example>
FROM A
| WHERE c1 == 3
| SELECT c1, c2
</example>

To select all data from table "A" and associate column "v" value multipled by 2 with new column "x""

<example>
FROM A
| SET x = v*2
</example>

To associate column c1 with new column x and remove c1 and c3, this can be used:

<example>
FROM A
| SET x = c1
| DROP c1, c3
</example>

If only c1, c2 and c3 are present, the above can be rewritten using just `SELECT` :
<example>
FROM A
| SELECT x = c1, c2
</example>

### Aggregations

Aggregations are extremely common in queries and Hamelin supports these via `AGG` keyword: `| AGG <aggregation1>, <aggregation2>, ... BY column1, column2, ...`

The "BY ..." part is optional. Any columns not specified in the aggregation command will be dropped.
At least one aggregation expression is required; for unique rows without aggregates, use the `DISTINCT` command.

To aggregate rows in table "A" using count and group by c1:

<example>
FROM A
 | AGG count = count() BY c1
</example>

To aggregate rows in table "A" using count, group by c1 and select 10 most common values:

<example>
FROM A
 | AGG count = count() BY c1
 | SORT count DESC
 | LIMIT 10
</example>

To take max of column c1, avg of column c2 and count, grouped by c3 and order max(c1) and take top 10 results, this can be issued:

<example>
FROM A
 | AGG count = count(), max = max(c1), avg = avg(c2) BY c3
 | SORT max DESC
 | LIMIT 10
</example>

It is also possible to associate the aggregate values with new column names:

<example>
FROM A
 | AGG count = count(), max_c1 = max(c1), avg_c2 = avg(c2) by c3
 | SORT max_c1 desc
 | LIMIT 10
</example>

Very important: remember that aggregation selects only columns (and aggregations) specified, everything else is dropped. If you want to filter and aggregate data, consider the right order. E.g. to filter cases where c1 > 500 and then get average value of c2, grouped by c3 from table tbl, sorted by c2, this is the correct order (WHERE before AGG):

<example>
FROM tbl
| WHERE c1 > 500
| AGG avg_c2 = avg(c2) BY c3
| SORT avg_c2
</example>

Of course, there are also cases when WHERE is applied after aggregation. E.g. to get average of c1 and count, grouped by c3, only where count() > 10, this will be a correct query:

<example>
FROM tbl
 | AGG count = count(), avg_c1 = avg(c1) BY c3
 | WHERE count > 10
</example>

### Literals

Hamelin adopts most of SQL’s conventions for the basic literal syntax and semantics. Integer, boolean, string literals, and NULL all behave as they do in SQL, and have equivalent syntax.

Numeric literals with a decimal point (e.g. `3.14`, `1.5`, `0.75`) are **doubles** by default. Scientific notation also produces doubles (e.g. `1.5e0`, `3.14e-2`). To create an exact **decimal** literal, use the `m` suffix (e.g. `3.14m`, `100.50m`). This is similar to the C# convention for decimal literals.

In addition, however, Hamelin adds:

- Double-quoted strings, which allow for the use of single quotes in the string without needing escaping.
- Support for multiline. (You can add literal newlines into a string.)

### Identifiers

The identifier syntax is similar to how Trino SQL handles identifiers. They are ASCII letter sequences. These sequences can also start with an underscore `_` or contain an underscore. The sequences can also contain digits, but they cannot start with digits. For Hamelin, we want to follow along with the way string _literals_ are using double-quotes in many existing programming languages and therefore we want to stray from the SQL example, which requires the use of single-quotes `'` for strings. This means we do not want to support double-quoted identifiers. We instead support backtick-quoted identifiers: `identifier of life`.

Following query retrieves data from test table and filters for two conditions, whether "curry wurst" column is equal to the string "wiener schnitzel" and "1a_extra" column is equal to the string 'mit "pommes"' (note the doublequote inside)

<example>
FROM _test
| WHERE `curry wurst` == 'wiener schnitzel'
    AND `1a_extra` == 'mit "pommes"'
</example>

### Reserved Keywords

The following words are reserved in Hamelin and cannot be used as unquoted identifiers:

- Commands: `SET`, `WHERE`, `SELECT`, `DROP`, `FROM`, `UNION`, `LIMIT`, `PARSE`, `WITHIN`, `AGG`, `WINDOW`, `APPEND`, `JOIN`, `LOOKUP`, `EXPLODE`, `UNNEST`, `NEST`, `ROWS`, `MATCH`
- Operators/Keywords: `AS`, `AND`, `OR`, `NOT`, `IN`, `IS`, `BY`, `ON`, `SORT`, `ASC`, `DESC`, `DEF`, `TRUE`, `FALSE`, `NULL`

When a field name in your data conflicts with a reserved keyword, you must escape it using backticks:

<example>
// WRONG: fails because `as` is a reserved keyword
| WHERE source.as.organization == 'Example Corp'

// CORRECT: escape the reserved keyword with backticks
| WHERE source.`as`.organization == 'Example Corp'
</example>

### Types

Hamelin has the following primitive types:

- `boolean`
- `int` - an umbrella for all integer types of any bit length
- `double` - floating point numbers with variable precision. Bare decimal literals like `3.14` and scientific notation like `-9.87e2` are doubles.
- `decimal(precision, scale)` - exact fixed point values with specified precision and scale. Use the `m` suffix to create decimal literals (e.g. `100.5m`, `3.14m`).
- `string` - a string of any length, defined as a literal with either single or double quotes. String literals use backslash escaping: `\'` escapes a single quote in single-quoted strings, `\"` escapes a double quote in double-quoted strings, and `\\` escapes a literal backslash. (e.g. `"She said \"hello\""`, `'It\'s raining'`)
- `binary` - translates to `varbinary` in SQL for handling binary data
- `timestamp` - an umbrella for date, timestamp, and all their variants (precision and timezone)
- `interval` - an exact time duration of seconds, minutes, hours, or days (e.g. `30s`, `14d`)
- `calendar_interval` - a calendar based time duration of months, quarters, or years (e.g. `3mon`, `5y`)
- `range` - a span between two values of any primitive type using the `..` operator, which is inclusive on both sides and can be unbounded in either direction (e.g. `ts('2024-01-15')..now()`, `1..10`, `yesterday()..`). This is primarily used for time ranges, but can be used for any sortable type.

Hamelin has five composite types: 

- A `struct` has a set of fields, each with name and schema. You can access its field `my_field` of `my_struct` by doing `my_struct.my_field`. Struct literals are created with curly braces and the field names and values, e.g. `{ name: 'John Doe', age: 30 }`. You can use `.` notation to assign to nested fields, but only in the root of the assignment.
  **Struct literal syntax:** Use nested braces for nested structures (`address: { street: ... }`). Unquoted field keys are simple identifiers and cannot use `a.b` path syntax as a key.
  <bad_example>SET user = { name: 'John Doe', address.street: '123 Main St'}</bad_example>
  <bad_example>SELECT sources = [{ ip: source.ip, geo.country: source.geo.country_iso_code }]</bad_example>
  <good_example>SET user = { name: 'John Doe', address: { street: '123 Main St' } }</good_example>
  <good_example>SELECT sources = [{ ip: source.ip, geo: { country: source.geo.country_iso_code } }]</good_example>
  <good_example>SET user.name = 'John Doe', user.address.street = '123 Main St'</good_example>
- A `tuple` is an ordered, fixed-length sequence of values with a type per position. Tuple literals use parentheses, e.g. `(1, 'a')` or a one-element tuple `(42,)`. Elements are accessed by position with **positional field names** `f0`, `f1`, `f2`, … — for example `t.f0` for the first element.
- A `map` has a set of key-value pairs with homogeneous types. You can access the value for key `my_key` in `my_map` by doing `my_map["my_key"]`. Map literals are created with the `map()` function, e.g. `map('name': 'John Doe', 'age': 30)`.
- An `array` is an ordered list of values with homogeneous types. You can access the value at index 5 in `my_array` my doing `my_array[5]`. Array literals are created with square brackets and the values, e.g. `['John Doe', 'Bob Smith']`.
- A `variant` is essentially a representation of untyped JSON data. You can access fields of JSON objects with the struct syntax above (e.g. `my_json_obj.my_field`) and you can access items of JSON arrays with the array syntax above (e.g. `my_json_array[5]`).

Hamelin types can be casted to one another with the `AS` operator

- If the type cast fails, the expression returns `NULL`
- Casting never happens implicitly, so to concatenate strings with other types you need to cast. Often parentheses are necessary to make sure to cast the right expression (e.g. `"Today is " + (now() AS string)`)
- Casting is most often useful for casting `variant`s parsed from JSON into the expected composite type (e.g. `| SET parsed = parse_json(message) AS { user_id: string, email: string}`). Without the `AS { ... }` type schema, `parse_json(message)` yields variant-typed fields that cannot be compared with strings, numbers, or booleans in WHERE—always declare types when you filter or compare parsed fields.
- Array types are casted like `array(string)`, not `string[]`
- Map types are casted like `map(string, int)`, not `{ string: int }`

Any field in Hamelin can also be `NULL`. There is no concept of a `NOT NULL` field. You can check for `NULL` values with `IS NULL` or `IS NOT NULL`.

NOTE: `variant` and `interval` types can never be saved in the output of a table, view, or detection query, so you must convert/cast them to another type in the output of those datasets. For `variant`, you can cast it to a struct, array, etc. For `interval`, you can use a function like `to_nanos()` to convert it to an `int`.

<example>
SET event.duration = to_nanos(event.end - event.start)
</example>

### Array Broadcasting

Hamelin automatically broadcasts operations across arrays, letting you use familiar syntax without explicit iteration. When you write `array.field`, Hamelin extracts that field from every element. When you apply comparisons or function calls, they apply to each element individually.

Given a `users` array of structs like `[{email: 'alice@example.com'}, {email: 'jdoe@example.com'}]`:

- `users.email` broadcasts field access → `['alice@example.com', 'jdoe@example.com']`
- `users.email == 'jdoe@example.com'` broadcasts comparison → `[false, true]`
- `upper(users.email)` broadcasts function call → `['ALICE@EXAMPLE.COM', 'JDOE@EXAMPLE.COM']`

Broadcasting pairs naturally with `any()` and `all()` to check conditions across arrays:

<example>
// Check if any user has a specific email
| WHERE any(users.email == 'jdoe@example.com')

// Check if all users are admins
| WHERE all(users.role == 'admin')

// Get unique values from a field across array elements
| SET unique_emails = array_distinct(users.email)
</example>

### Operators

Hamelin is designed to be very intentional to make interactive query authoring more fluent and ergonomic.

- Boolean operators
  - Negation: `NOT`
  - Conjunction: `AND`
  - Disjunction: `OR`
- Identity: `IS` (only really used to test against `NULL`).
- Arithmetic:
  - Addition: `+`
  - Subtraction: `-`
  - Multiplication: `*`
  - Division: `/`
  - Modulo: `%`
  - Negation: `-`
- Comparison:
  - Equality: `==`
    - Note we use single-equals for _assignment_.
  - Non-equality: `!=`
  - Greater than: `>`
  - Less than: `<`
  - Greater or equal: `>=`
  - Less or equal: `<=`
  - Inclusion: `IN ['item1', 'item2', ...]` or `IN 1..10`
- Concatenation: `+`
  - For strings: `'one' + 'two'` is `onetwo`
  - For arrays: `['one'] + ['two']` is `['one', 'two']`
- Lambda (arrow): `->`
  - Creates an anonymous function: `x -> x * 2`
  - Multi-parameter: `(a, b) -> a + b`
  - Used as arguments to `transform()` and `filter()`

### Unioning multiple tables with FROM

Hamelin allows a shorthand in the `FROM` command that targets _many_ sources (by separating them with commas (the `,` character). In a system that offers search, authors often want to perform the same search (or filter) over the rows that come from many different places. This is most common in "needle" queries, where you’re looking for something relatively rare in a bunch of different datasets. This is not a common pattern in structured querying, likely explaining why SQL makes this so hard, and why there is no direct translation of this form in SQL.

For example, where table A defines only `f1` and `f2`, and table B defines only `f2` and `f3`, following will work. It will select `f1` and `f2` from table A and `f2` and `f3` from table B, so the full set of fields in the output of the following example will be `f1`, `f2`, `f3`.

<example>
FROM A, B
</example>

You can also alias the fields based on which table they came from if you need to determine which table a given `f2` value came from example. In the following example, the output fields would be a struct `a` with sub-fields `f1` and `f2` and a struct `b` with sub-fields `f2` and `f3`. You can reference the sub-fields later in the query with `a.f1`, `b.f2`, etc. In addition, the fields are all still usable at the top level.

<example>
FROM a = A, b = B
</example>

Aliasing tables is also useful when aggregating multiple types of rows (often from `DEF` CTEs):

<example>
DEF network_connections = FROM simba.sysmon_events
| WHERE winlog.event_id == '3';

DEF suspicious_file_creation = FROM simba.sysmon_events
| WHERE winlog.event_id == '11';

FROM network_connections = network_connections, suspicious_file_creation = suspicious_file_creation
| AGG network_connections_count = count(network_connections), suspicious_file_creation_count = count(suspicious_file_creation)
  BY host.name
</example>

### Functions

**Authoritative list:** The bulleted lists of functions in this section are **complete**. They are the full set of built-in functions Hamelin provides. **Do not invent, guess, or import function names** from SQL, Splunk, Elastic, or other systems. If a name does not appear in these lists, that function does not exist in Hamelin. 

The following functions are supported in expressions (within `SET`, `SELECT`, `WHERE`, etc.):

- len(x[])
- sum(x[])
- avg(x[])
- min(x[])
- max(x[])
- array_distinct(x[])
- filter_null(x[])
- slice(x[], start, end) - returns a slice of the array x from start to end (exclusive). Supports negative indices to count from the end of the array.
- flatten(x[]) - flattens a nested array by one level.
- sequence(start, stop) - array of integers from `start` to `stop` (inclusive)
- sequence(start, stop, step)
- transform(array, lambda) - applies the lambda to each element and returns an array of results. Example: `transform([1, 2, 3], x -> x * 10)` returns `[10, 20, 30]`. Useful with `map()` to convert key-value arrays into maps: `map(transform(arr, x -> x.key : x.val))`.
- filter(array, lambda) - returns a new array containing only elements for which the lambda returns true. Example: `filter([1, 2, 3, 4, 5], x -> x > 3)` returns `[4, 5]`.
- all(x[])
- any(x[])
- len(x[])
- abs(x)
- cbrt(x)
- ceil(x)
- floor(x)
- degrees(x)
- e() - Euler's number
- exp(x)
- ln(x)
- log(b, x)
- log10(x)
- log2(x)
- pi()
- pow(x,p)
- radians(x)
- round(x)
- round(x,d)
- sign(x)
- sqrt(x)
- truncate(x) - Remove the fractional part of a number
- width_bucket(x,bound1,bound2,n) - the bucket number for a value in a histogram with equal-width buckets
- width_bucket(x, bins[]) - the bucket number for a value using explicitly defined bucket boundaries
- if(condition, then, else) - Both `then` and `else` must have exactly the same type
- if(condition, then) - if `condition` is true, returns `then`; otherwise null
- case(when_condition: then_condition, when_condition: then_expression, ...) - Evaluates multiple condition-value pairs in order and returns the value associated with the first condition that evaluates to true. Returns null if no conditions are true. Every `then` must have the same type. There is no `else` in this expression, but you can achieve the same results by having `true` in the final `then_expression` like `case(when_condition: then_condition, when_condition: then_expression, ..., true: else_expression)`.
- regexp_count(str, pattern)
- regexp_extract(str, pattern)
- regexp_extract(str, pattern, group)
- regexp_extract_all(str, pattern)
- regexp_extract_all(str, pattern, group)
- regexp_like(string, pattern)
- regexp_position(string, pattern) - position of the first regex match
- regexp_position(string, pattern, start) - first match starting search from `start`
- regexp_position(string, pattern, start, occurrence) - position of the `occurrence`th match (with `start` as in the 3-arg form)
- regexp_replace(string, pattern)
- regexp_replace(string, pattern, replacement)
- regexp_split(string, pattern)
- split(str, separator)
- array_join(x[], separator)
- array_join(x[], separator, null_replacement) - null elements are replaced with this string when joining
- replace(str, str_to_remove)
- replace(str, str_to_remove, replacement)
- starts_with(str, prefix)
- ends_with(str, suffix)
- contains(str, substring)
- len(str)
- lower(str)
- upper(str)
- url_encode(str) - Encodes a string for URL query parameters (`application/x-www-form-urlencoded` style): alphanumeric characters and `.-*_` are unchanged; ASCII space is encoded as `+`; other UTF-8 bytes as `%XX` with uppercase hex.
- url_decode(str) - Decodes a string produced by `url_encode`: `+` becomes ASCII space; `%XX` sequences are decoded to bytes and interpreted as UTF-8.
- uuid() - generates a random UUID v4 string (non-deterministic, each call produces a different value)
- uuid5(name) - deterministic UUID v5 string (RFC 4122) from `name`; same input always yields the same UUID
- substr(str, start) - substring from `start` through the end of the string (same index rules as the 3-arg form)
- substr(str, start, end) - returns a substring of the str from start to end (exclusive). Supports negative indices to count from the end of the string.
- parse_json(json_str_or_variant) - Accepts either a string or a variant, and returns a `variant`. When parsing JSON, fields accessed from the returned variant are variant-typed unless you cast. **Important:** Untyped parsed fields cannot be compared with strings/numbers in WHERE—you get a type/signature mismatch. Always either cast at use, e.g. `(parsed.msg AS string) == 'x'`, or (recommended) provide a schema: `parse_json(message) AS { msg: string, count: int }` so you can use `parsed.msg` and `parsed.count` without repeating casts.
- to_json_string(json_variant) - Only takes a variant. To pass other types, cast to variant with `AS variant`
- typeof(x)
- coalesce(x[]) - returns the first non-null value from a list of expressions. All arguments must be type-compatible (e.g. all strings, all ints). To coalesce a variant with a default string, cast the variant first: `coalesce(my_variant AS string, '')`
- now()
- today()
- yesterday()
- tomorrow()
- ts(str)
- at_timezone(timestamp, timezone)
- year(timestamp)
- month(timestamp)
- day(timestamp)
- day_of_week(timestamp) - day of week (1 = Monday through 7 = Sunday)
- hour(timestamp)
- minute(timestamp)
- second(timestamp)
- from_unixtime_micros(int) - returns timestamp
- from_unixtime_millis(int) - returns timestamp
- from_unixtime_nanos(int) - returns timestamp
- from_unixtime_seconds(int) - returns timestamp
- from_millis(x) - returns interval
- from_nanos(x) - returns interval
- to_unixtime(timestamp) - returns double in seconds since the Unix epoch
- to_millis(interval)
- to_nanos(interval)
- map(keys, values)
- map(elements)
- map_keys(map)
- map_values(map)
- cidr_contains(cidr, ip)
- is_ipv4(ip)
- is_ipv6(ip)

The following functions are ONLY available within an AGG, WINDOW, or MATCH AGG command (not within SET, SELECT, or WHERE commands):

- array_agg(x)
- avg(x)
- count(expr) - counts all rows where expr is non-null
- count() - counts all rows
- count_distinct(x) - counts the number of distinct non-null values of x
- max(x)
- min(x)
- sum(x)

The following functions are ONLY available within an AGG or WINDOW command (not within SET, SELECT, WHERE, or MATCH AGG commands):

- all(bool_expr) - returns true if bool_expr is true for all rows
- any(bool_expr) - returns true if bool_expr is true for any row
- any_value(x) - returns an arbitrary value from each group
- approx_percentile(x, percentile)
- approx_distinct(x) - same as count_distinct(x) but uses an approximation algorithm that is faster but less accurate
- count_if(condition) - counts all rows where condition is true
- map_agg(key, value)
- multimap_agg(key, value)
- stddev(x)

The following functions are ONLY available within a WINDOW command (not within SET, SELECT, WHERE, MATCH AGG, or AGG commands):

- first_value(expression, ignore_nulls=true)
- last_value(expression, ignore_nulls=true)
- nth_value(expression, n, ignore_nulls=true)
- lag(expression, offset, ignore_nulls=true) - the value of an expression from a previous row within the window
- lead(expression, offset, ignore_nulls=true) - the value of an expression from a subsequent row within the window
- cume_dist() - the number of rows with values less than or equal to the current row's value, divided by the total number of rows in the partition
- percent_rank() - the percentile rank of each row within the window partition
- dense_rank() - the rank of each row within a window partition without gaps
- rank() - the rank of each row within a window partition with gaps
- row_number() - a sequential row number for each row within a window partition

The following functions are ONLY available within a MATCH AGG command (not within SET, SELECT, WHERE, WINDOW, or AGG commands):

- first(expression) - returns the value of expression from the first matched event
- last(expression) - returns the value of expression from the last matched event

#### Regex patterns & escaping

In Hamelin, **regex patterns are just string literals** (the `pattern` argument to `regexp_like`, `regexp_extract`, `regexp_replace`, etc).

Hamelin uses backslash escaping for strings:
- `\\` produces a literal backslash
- `\'` produces a single quote (in single-quoted strings)
- `\"` produces a double quote (in double-quoted strings)
- Any other `\X` sequence (like `\d`, `\w`, `\s`) is passed through **literally** as `\X`

This means regex patterns work naturally. Just write the regex you intend to use directly, without “double-escaping” backslashes like you would in many programming languages.

<example>
// ✅ Digit class: \d is not a recognized escape, so it passes through literally
SET digits_removed = regexp_replace('a1b', '\d', '')
// digits_removed == 'ab'
</example>

<example>
// ✅ Matching a literal backslash:
// In regex you need \\ to match one backslash.
// Use \\\\ to produce the two-char string \\ that regex needs
SET normalized = regexp_replace('C:\Windows', '\\\\', '/')
// normalized == 'C:/Windows'
</example>

<example>
// ✅ Escaping quotes with backslash
SET name = 'Kyle\'s dog'
</example>

<example>
// Dot does NOT match newline unless you use the (?s) flag (dotall)
SET no_match = regexp_replace('a
b', 'a.*b', 'x')
SET match = regexp_replace('a
b', '(?s)a.*b', 'x')
</example>

### Describing time ranges

Use `<low>..<high>` to describe a time range. Drop either `low` or `high` from the expression to communicate that side being unbounded. Allow the use of interval values, like `WITHIN 1hr..`. This time range syntax is used in the `timeRange` input of tools such as `execute-hamelin-query` and `query-page-run-query` to filter the result set to only the rows that have a `timestamp` in that time range. The time range syntax can be used in various parts of a Hamelin query:

- `| WHERE val IN low..high` allows `IN` to be used as a binary boolean operator to test any sortable value against a range. This can be used anywhere boolean expressions are needed.
- `| WINDOW WITHIN low..high` defines the frame clause of a window expression, filtering only those rows which are within the interval defined in terms of the current (or output) row.

Whenever providing timestamp literals, you must use a format of `('<TIMESTAMP>' AS timestamp)`. For example, to describe noon on March 1st, 2020, use `('2020-03-01 12:00' AS timestamp)`. To select all rows from table A with timestamp greater than March 1st, 2020 on noon, you can use:
<example>
FROM A
| WHERE timestamp > ('2025-03-01 12:00' AS timestamp)
</example>

To select rows from the last hour, use `timeRange`:

<example>
-1h..
</example>

To select rows which were between two and one hour ago, use the `timeRange`:

<example>
-2h..-1h
</example>

To select rows which occured during the past 7 days, use the `timeRange`:

<example>
-1w..
</example>

The `@` operator can also be used on a timestamp to round timestamps down to the nearest unit (day, hour, etc.). You can also specify a multiplier to truncate to multi-unit boundaries by placing a number before the unit: `@2h` rounds to 2-hour boundaries (00:00, 02:00, 04:00, ...), `@15min` rounds to 15-minute boundaries, etc. A plain `@h` is equivalent to `@1h`.

To select rows in the previous 5 calendar days (not including today), use the `timeRange`:

<example>
-5d@d..now()@d
</example>

### Windows

- `WINDOW` is a command that behaves like a mix of `AGG` and `SET`. It has three optional arguments that come at the end, all of which define a window:
  - `BY <column1, column2>` means everything in the set is partitioned by this. (e.g., `BY user` means compute the function in a user-relative window).
  - `SORT <column1, column2, ...>` means everything in the set sorted by this. (e.g., `SORT timestamp`).
  - `WITHIN <range>` means everything in the set uses this range as the frame. (e.g., `WITHIN -5m` means within a 5 minute sliding window).
    - Here, within uses nearly the same semantics as the `WITHIN` command.
    - We introduce a new row interval literal with the suffix `r` or `rows` or `row` for defining ranges over exact numbers of rows rather than over ranges of values.
- All three are optional, but the presence of any means _every_ expression in the set is defined "over" this window.

For example, to do count in 5 minute windows in table tbl:

<example>
FROM tbl | WINDOW c = count() WITHIN -5m
</example>

#### Rate-Based Detection with WINDOW...WITHIN

Use `WINDOW ... WITHIN` for sliding window aggregations to detect bursts or spikes of activity.

**When to use:** Count/aggregate events within a moving time window (e.g., "10+ failed logins in the last 5 minutes")

**Key characteristics:**
- **Purpose**: Detect bursts/spikes of activity (rate-based detection)
- **Syntax**: `WINDOW ... BY grouping_keys WITHIN -duration`
- **Window moves with time**: Always looks at the most recent N minutes/hours
- **Use cases**: Rate limiting, burst detection, threshold alerting, brute force attacks
- **Duration format**: `-5min`, `-1h`, `-1d` (negative values look backward in time)

**Example: Brute Force Detection**

<example>
FROM simba.okta_events
| WHERE event.action == "user.session.start" AND event.outcome == "failure"
| WINDOW
    failed_login_count = count(),
    unique_ips = array_agg(source.ip)
  BY user.name
  WITHIN -5min
| WHERE failed_login_count >= 10
| SELECT
    timestamp,
    user.name,
    event.count = failed_login_count,
    source.ip_count = unique_ips
</example>

**Common WINDOW patterns:**

<example>
// Count events in sliding window
FROM dataset | WINDOW event_count = count() BY host WITHIN -10min
</example>

<example>
// Conditional counting
FROM dataset
| WINDOW
    malicious_count = count_if(is_malicious),
    total_count = count()
  BY user.name
  WITHIN -30min
</example>

### Event Correlation with MATCH

The `MATCH` command enables powerful multi-stage detection by correlating different event patterns. This is essential for detecting attack chains and reducing false positives.

**When to use MATCH:**
- Detecting multi-stage attacks (initial access → execution → persistence)
- Correlating different behavioral indicators
- Building high-fidelity detections that require multiple suspicious events
- Creating attack chain detections with context

**MATCH Pattern Structure:**

```hamelin
DEF pattern1 = FROM dataset
| WHERE <specific conditions>
| SELECT fields...;

DEF pattern2 = FROM dataset
| WHERE <different conditions>
| SELECT fields...;

MATCH p1=pattern1 p2=pattern2?
AGG
  timestamp = min(timestamp),
  event.start = min(timestamp),
  event.end = max(timestamp),
  pattern1_event = first(p1),
  pattern2_event = first(p2)
BY correlation_key  // e.g., host, user, session_id
WITHIN 10m
| SET indicator_count = (
    1 +
    if(pattern2_event IS NOT NULL, 1, 0)
) AS int
| WHERE indicator_count >= 2  // Alert when 2+ patterns match

| SET risk_score = (
    30 +
    if(pattern2_event IS NOT NULL, 30, 0)
) AS int
| SELECT timestamp, fields...
```

**Critical MATCH Syntax Rules:**
- `WITHIN` is optional but strongly encouraged in MATCH. If you use a literal interval, it cannot be negative (for example, `WITHIN -1h` is invalid).
- CTEs can be matched directly (`MATCH A B`) or via explicit aliases (`MATCH a=A b=B`). After aliasing, reference fields through the alias names (for example, `a.field`, `b.field`).
- Pattern aliases are **space-separated**, NOT comma-separated: `MATCH a=query1 b=query2? c=query3?`
- Use `?` quantifier to make patterns optional (allows partial matches)
- **At least one pattern element must be required** (no quantifier or `+`). If all elements use `?` or `*`, MATCH will error because the pattern would match empty sequences.
- The `BY` clause specifies the correlation key (what ties events together)
- Access matched events in AGG using `first(alias)`, `last(alias)`, `array_agg(alias)[1]`, etc.

**Example: Multi-Stage Malware Detection**

<example>
// Define behavioral indicators
DEF lnk_powershell = FROM simba.sysmon_events
| WHERE event.code == "1"
| SET parent_image = coalesce(winlog.event_data["ParentImage"], '') AS string
| SET image = coalesce(winlog.event_data["Image"], '') AS string
| SET cmd_line = coalesce(winlog.event_data["CommandLine"], '') AS string
| SET host = host.name AS string
| WHERE regexp_like(lower(parent_image), '(?i).*\\\\explorer\\.exe')
  AND regexp_like(lower(image), '(?i).*(powershell|pwsh)\\.exe')
  AND regexp_like(cmd_line, '(?i).*(hidden|-enc|-encodedcommand).*')
| SELECT timestamp, host, process_image = image, process_commandline = cmd_line;

DEF cloud_download = FROM simba.sysmon_events
| WHERE event.code == "1"
| SET image = coalesce(winlog.event_data["Image"], '') AS string
| SET cmd_line = coalesce(winlog.event_data["CommandLine"], '') AS string
| SET host = host.name AS string
| WHERE regexp_like(lower(image), '(?i).*(powershell|pwsh)\\.exe')
  AND regexp_like(cmd_line, '(?i).*(github\\.com|dropbox\\.com).*')
  AND regexp_like(cmd_line, '(?i).*(downloadstring|invoke-webrequest).*')
| SELECT timestamp, host, process_image = image, process_commandline = cmd_line;

// Correlate: alert when 2+ indicators on same host within 10 min
MATCH lnk=lnk_powershell dl=cloud_download?
AGG
  timestamp = min(timestamp),
  event.start = min(timestamp),
  event.end = max(timestamp),
  lnk_event = first(lnk),
  dl_event = first(dl)
BY host
WITHIN 10m
| SET indicator_count = (
    1 +
    if(dl_event IS NOT NULL, 1, 0)
) AS int
| WHERE indicator_count >= 2
| SET risk_score = (
    40 +
    if(dl_event IS NOT NULL, 40, 0)
) AS int
| SELECT
    timestamp,
    event.start,
    event.end,
    host,
    event.count = indicator_count,
    event.risk_score = risk_score
</example>

### UNNEST and EXPLODE

The UNNEST command lifts struct or array of struct fields into the parent or enclosing result set. When given a struct, it lifts struct fields into the parent struct without changing row cardinality. When given an array of struct, it performs an explode operation followed by unnesting, creating one row per array element with the struct fields lifted into the parent struct. When the expression is a simple column reference, the source column is consumed and removed from the output to avoid duplicating data.

The EXPLODE command transforms rows containing array fields into multiple rows, with each element of the array becoming a separate row. Each array element becomes a new row with all other fields from the original row preserved in each generated output row. When you use assignment syntax (`identifier = expression`), the exploded values are placed in the specified field name. Without assignment syntax, the exploded values replace the original array field.

### More valid examples

To find records in table foo where column c1 is greater than 42, one needs to write:

<example>
FROM foo | WHERE c1 > 42
</example>

To take unique (distinct) tuples of hostname and dest_port in table1:
<example>
FROM table1 | DISTINCT hostname, dest_port
</example>

To get number of flows by protocol and destination_port, then select top 10 by count, one needs to write:
<example>
FROM flows
| AGG count = count() BY protocol, destination_port
| SORT count DESC
| LIMIT 10
</example>

To get number of flows by day, one needs to write:
<example>
FROM flows
| AGG count = count() BY timestamp@d
| SORT count DESC
| LIMIT 10
</example>

The resulting data will have two columns: count and timestamp (note that the "@d" part gets removed in the outputted column name).

### Invalid usage examples

Here are some examples how NOT TO USE Hamelin. It is important to memorize these bad examples and verify when producing a response to the prompt if the output is correct.

Following bad query mistakenly uses ORDER BY rather than SORT:
<bad_example>
FROM flows
| ORDER BY count() DESC
</bad_example>
After fixing:
<example>
FROM flows
| SORT count() DESC
</example>

Following bad query uses aggregation for sorting, but it was already done
<bad_example>
FROM flows
| AGG count() BY user_name
| SORT count()
</bad_example>
After fixing:
<example>
FROM flows
| AGG count = count() BY user_name
| SORT count
</example>

Following bad query mistakenly uses a single equal sign for equality test:
<bad_example>
FROM flows
| WHERE user = 'John Smith'
</bad_example>
After fixing:
<example>
FROM flows
| WHERE user == 'John Smith'
</example>

Following bad query mistakenly uses a star character in count:
<bad_example>
FROM flows
| AGG count = count(*) BY user
</bad_example>
After fixing:
<example>
FROM flows
| AGG count = count() BY user
</example>

Following bad query is mixing two commands in one:
<bad_example>
FROM flows | SELECT dest_port, SET proto = 'tcp'
</bad_example>
After fixing:
<example>
FROM flows | SELECT dest_port | SET proto = 'tcp'
</example>

Following bad query is using incorrect AS operator for aliasing in AGG:
<bad_example>
FROM flows | AGG max(timestamp) AS max_timestamp
</bad_example>
After fixing:
<example>
FROM flows | AGG max_timestamp = max(timestamp)
</example>

Following bad query uses AS for aliasing in SELECT (AS is ONLY for type casting, use `=` for aliasing):
<bad_example>
FROM flows | SELECT source_ip AS src, destination_ip AS dst
</bad_example>
After fixing:
<example>
FROM flows | SELECT src = source_ip, dst = destination_ip
</example>

The following bad query uses the LIKE operator, which doesn't exist in Hamelin:
<bad_example>
FROM flows | WHERE proto LIKE 'http%'
</bad_example>
Three different options to fix:
<example>
FROM flows | WHERE starts_with(proto, 'http')
</example>
<example>
FROM flows | WHERE contains(proto, 'http')
</example>
<example>
FROM flows | WHERE regexp_like(proto, '^http')
</example>

AGG does not support the WITHIN clause. Use WINDOW for rate-based detection with sliding windows.
<bad_example>
| AGG count() BY host WITHIN -5min
</bad_example>
After fixing:
<example>
| WINDOW count() BY host WITHIN -5min
</example>

MATCH patterns must be space-separated, not comma-separated.
<bad_example>
MATCH a=query1?, b=query2?, c=query3?
</bad_example>
After fixing:
<example>
MATCH a=query1 b=query2? c=query3?
</example>

Variant values must be cast before comparing to other values.
<bad_example>
| SET parsed = parse_json(message)
| WHERE parsed.level == 'info'
  // Error: parsed.level is variant, 'info' is string
</bad_example>
After fixing:
<example>
| SET parsed = parse_json(message)
| SET parsed_level = parsed.level AS string
| WHERE parsed_level == 'info'
</example>

Do not treat `variant` values like typed structs for incremental field assignment. When parsing/exploding JSON into a `variant`, keep that column as a raw temporary value (for example, `raw_finding`), create a new typed struct column from it in one `SET`, then drop the raw `variant` column.
<bad_example>
| EXPLODE finding = json.detail.findings AS array(variant)
| SET finding.id = finding.Id AS string
| SET finding.title = finding.Title AS string
| SET finding.severity = { label: finding.Severity.Label AS string }
</bad_example>
After fixing:
<example>
| EXPLODE raw_finding = json.detail.findings AS array(variant)
| SET finding = {
    id: raw_finding.Id AS string,
    title: raw_finding.Title AS string,
    severity: { label: raw_finding.Severity.Label AS string }
  }
| DROP raw_finding
</example>

### Key differences from SQL

Hamelin is not SQL but a unique query language! Carefully note the Hamelin rules when drafting a new query.

- The count function does not include a star. So it's `count()` and definitely NOT `count(*)`.
- There is no `CASE`/`WHEN` operator in Hamelin, but its implemented as the `case(when_condition: then_condition, when_condition: then_expression, ...)`
- There's no `LIKE` operator. Instead, use `contains` to do simple substring searching or `regexp_like` to check if a string matches a regex pattern.
- String concatenation happens with `+` not `||`
- Double equals `==` is required for equality comparison. Single equals `=` is only used for assignment.
- `AS` is used for casting, never for assignment/aliasing. NEVER use it like `SELECT expr AS alias`. Only use it for casting like `SELECT expr AS type`. To do aliasing in a SELECT command, use `SELECT alias = expr` syntax.
- `SORT`, not `ORDER BY`
- There is no support for subqueries within expressions (`| WHERE name IN (FROM admins | SELECT name)` is not valid)
- Trailing commas are allowed (but not required).

### Summary

Let me note a few important properties:

- Aggregation, when used, must be done using `AGG` keyword. The `AGG` command completely redefines the field space so only fields used/defined in the command will be available downstream. This means that if a field isn't used at all in the AGG command, you cannot reference it again in a subsequent command. Aggregation functions can only appear in the aggregation portion of `AGG` (before `BY`), never in the `BY` clause itself.
- Grouping, when used, must be done within a window or aggregation, i.e. there is no separate GROUP BY command, but there is "AGG ... BY ..."
- Each query must start with `FROM <table>`, followed by other commands separated by pipe character ("|").
- Very important, pipe character ("|") must be always used to separate each command. For example, this is invalid: <bad_example>FROM tbl WHERE x > 5</bad_example>. Instead, it should be: <example> FROM tbl | WHERE x > 5</example>. It’s extremely important, use pipe characters for separation!
- The only valid command names are FROM, SELECT, WHERE, DROP, SET, DEF, WINDOW, MATCH, SORT, LIMIT, AGG, UNNEST, EXPLODE, TRIMSTRINGS - each command must start with one of these.
- Use only the functions named under **Functions** in this document; treat that section as the exhaustive catalog—never fabricate function names.
- When the prompt contains a SQL query on the input, you must convert it into a Hamelin query!!!

### Best Practices

- Don't use `SORT` and `LIMIT` commands unless the specific situation requires them or the user asks for it specifically. If you're adding `| SORT timestamp DESC` to the end of the query, that's almost always wrong.
- `SELECT` is useful to narrow the result set if the you and the user know exactly what you're looking for, but `SELECT` is not required and should be omitted for most queries.

### Final Reminders

Now you should understand how Hamelin operates and how to write queries. When providing Hamelin queries, make sure you take into account syntax requirements of Hamelin and do logical operators correctly. Ensure that the order of operations as logically described in the question is followed. Ensure that the order of aggregating and filtering operations is correct. When asked for providing a Hamelin query, provide concise output with a correct Hamelin query.
