# ACKLOG Query Language Reference and Examples

> Complete reference for the ACKLOG SIEM search query language: search syntax, operators, the pipeline statements (where, eval, agg, timespan, sort, table, fieldformat, lookup, transaction and more), functions, and 100+ tested example queries for security scenarios such as brute force, password spraying, RDP, lateral movement, data exfiltration and web attacks. Or skip the syntax: the built-in AI query assistant writes, checks and explains ACKLOG queries from a plain-language request.

Canonical page: https://docs.acklog.com/query-language/  
Last updated: 2026-11-12

## Overview

The **ACKLOG Query Language** is the language you type into the search box of the ACKLOG SIEM search screen. A query first selects events, then passes them through a pipeline of statements separated by the pipe character `|`. Each statement transforms the result of the previous one: filter, calculate, aggregate, sort, format and present.

Syntax:

```text
<search part> | <statement> | <statement> | ...
```

```acklog
UserName=adm* SourceIP=*
| where SourceIP in [192.168.10.100, 10.16.8.10]
  | agg count by UserName
  | where count > 10
  | sort - count
```

Selects events whose UserName starts with "adm" and that have a SourceIP, keeps only two source addresses, counts events per user, keeps users with more than 10 events and lists the largest counts first.

### Key facts

- The **time range** is chosen with the time picker of the search screen. Queries do not contain a time clause.
- Keywords and statement names are **case-insensitive** (`agg` = `AGG`). Write field names and values as they appear in your logs.
- Field names in the examples (`EventID`, `TargetUserName`, `SourceIP`, `DestinationPort`, ...) are typical ACKLOG fields for Windows, firewall, proxy and web logs. Your field list depends on your log sources; the search screen lists the available fields and suggests them while you type.
- A query may span several lines. Lines that start with `//` are comments.
- The search box editor suggests statements, fields, functions and values as you type, shows inline documentation on hover and marks errors before you run the query. `Ctrl+I` formats a long query.
- Prefer plain language? The **Ask AI** button next to the search box writes the query for you. See [AI query assistant](#ai-query-assistant).

## Quick start

Ten queries that cover most daily work. Copy one, adjust the field names and run it.

```acklog
*
```

All events in the selected time range.

```acklog
EventID=4625
```

Windows failed logon events.

```acklog
EventID=4625
  | agg count by TargetUserName
  | sort - count
```

Failed logons per account, most failures first.

```acklog
EventID=4625 | latest 20
```

The 20 most recent failed logons.

```acklog
SourceIP=10.0.0.0/8 DestinationPort=[22, 3389]
```

SSH or RDP connections from the 10.x.x.x network.

```acklog
TargetUserName=*admin* | table EventTime, NodeName, EventID, TargetUserName
```

Events for accounts that contain "admin", shown as a table.

```acklog
NodeName=*
  | agg count by NodeName
  | timespan 1h
```

Event volume per log source per hour.

```acklog
SourceIP=*
  | agg count by SourceIP
  | iplocation SourceIP
  | size 20
```

Top 20 source IPs with country and city.

```acklog
Page=*
| where Page contains ["../", "etc/passwd"]
| table EventTime, SourceIP, Page
```

Web requests with path traversal patterns.

```acklog
SourceIP=* sentbyte=*
  | agg sum(sentbyte) as Sent by SourceIP
  | sort - Sent
  | size 10
  | fieldformat Sent=bytes(2)
```

Top 10 senders by volume, shown as KB/MB/GB.

## AI query assistant: describe it, get the query

You do not have to learn the syntax before you start. Click **Ask AI** next to the search box and describe what you want to find, the way you would ask a colleague. The query assistant writes the ACKLOG query, explains what it does, checks it, and puts it in the search box with one click. Every query on this page could just as well start as a sentence.

**You ask the AI query assistant:** "Show me source IPs with more than 20 failed logons in the last 24 hours"

**It writes:**

```acklog
EventID=4625 IpAddress=*
  | agg count as Failures by IpAddress
  | where Failures > 20
  | sort - Failures
```

The time range "last 24 hours" is applied to the time picker when you click **Use query**.

**You ask the AI query assistant:** "List users added to administrator groups in the last 7 days"

**It writes:**

```acklog
EventID=[4728, 4732, 4756]
| table EventTime, NodeName, SubjectUserName, MemberName, TargetUserName
```

**You ask the AI query assistant:** "Which internal hosts sent the most data? Show the totals in MB or GB"

**It writes:**

```acklog
SourceIP=10.0.0.0/8 sentbyte=*
  | agg sum(sentbyte) as Sent by SourceIP
  | sort - Sent
  | size 10
  | fieldformat Sent=bytes(2)
```

**You ask the AI query assistant:** "How do I count events per hour for each log source?"

**It writes:**

```acklog
NodeName=*
  | agg count by NodeName
  | timespan 1h
```

The assistant also answers how-to questions about the query language.

### How to use it

- Click **Ask AI** next to the search box.
- Describe what you want to find, or pick one of the suggested questions (logons, network, Windows events, query language).
- The assistant answers with one or more queries and a short explanation of each.
- Click **Use query**. The query goes into the search box and the time range is taken from your request, such as "last 24 hours" or "this week". Review it and run the search.

### Why you can trust the result

- **Checked before you see it.** Every generated query is run through ACKLOG's own search engine. If the check finds a problem, the assistant corrects the query automatically, and each answer shows whether its query passed the check.
- **Knows your environment.** The assistant writes queries with the field names of your own log sources, not generic ones.
- **Your logs stay private.** The assistant only writes queries. It never reads your log data or your search results.
- **English or Turkish.** Ask in either language; explanations come back in the language you write in.

### Fix with AI

When a query has an error, a **Fix with AI** button appears under the error message. It sends the query and the error to the assistant, which returns a corrected query and explains what was wrong. A quick way to learn the rules while you work.

### History

Your questions are kept in the **History** tab of the assistant. Search them, reuse a query, or ask the same question again.

> **Tip:** The AI query assistant is part of ACKLOG and is turned on by your ACKLOG administrator.

## Search part: selecting events

The search part is everything before the first `|`. It selects events from the index and is the fastest way to narrow the data, so put as much filtering here as possible.

### Free text and all events

```acklog
*
```

All events.

```acklog
login.aspx
```

Events that contain "login.aspx" in any field.

```acklog
"failed password"
```

Events that contain the exact phrase "failed password".

### Field conditions

| Operator | Meaning | Example |
| --- | --- | --- |
| `=`  `==`  `:` | equals (all three are the same) | `EventID=4625`  `EventID:4625` |
| `!=` | not equal | `EventID!=4624` |
| `>`  `>=`  `<`  `<=` | numeric comparison | `SourcePort>1024` |
| `=*` | the field exists (has any value) | `SourceIP=*` |

```acklog
Page=*
```

Events that have a Page field.

```acklog
SourcePort>1024
```

Source port greater than 1024.

```acklog
MemberName!="-"
```

MemberName is not "-".

### Wildcards

`*` matches any number of characters at the start, the end or both ends of a value.

```acklog
Page=log*
```

Page starts with "log".

```acklog
Page=*ogin.aspx
```

Page ends with "ogin.aspx".

```acklog
UserName=*admin*
```

UserName contains "admin".

> **Tip:** Leading wildcards (`*abc`) are slower than trailing ones (`abc*`) on large data sets. Prefer exact values or trailing wildcards when you can.

### AND, OR, NOT and parentheses

Terms separated by a space are combined with **AND**. Use `or`, `and` and `not` (any case) and parentheses for grouping.

```acklog
EventID=4625 TargetUserName=admin
```

Both conditions (implicit AND).

```acklog
Page=* AND (UserName=admin OR SourceIP=172.16.0.1)
```

Page exists and either the user is admin or the source is 172.16.0.1.

```acklog
EventID=4625 AND NOT TargetUserName=*$
```

Failed logons, excluding machine accounts (names ending with $).

```acklog
not EventID=4624
```

Every event except successful logons.

### Quotes: phrases and exact values

| Form | Use it for | Example |
| --- | --- | --- |
| no quotes | single words, numbers, IPs, wildcards | `UserName=admin` |
| double quotes `"..."` | phrases and values with spaces or special characters | `Message="access denied"` |
| single quotes `'...'` | an exact match of the whole field value | `TargetUserName='Administrator'` |

### Lists of values

A bracketed list matches any of the values (OR).

```acklog
EventID=[4624, 4625, 4644]
```

EventID is 4624, 4625 or 4644.

```acklog
DestinationPort=[22, 3389, 5985]
```

SSH, RDP or WinRM ports.

### IP subnets and ranges

```acklog
SourceIP=10.0.0.0/16
```

CIDR subnet.

```acklog
SourceIP=[10.10.1.1 TO 10.12.5.254]
```

An explicit IP range (inclusive).

### Predefined lists: {@ListName}

Values kept in an ACKLOG list (for example critical event IDs, VIP accounts or blocked IPs) can be used inside a value list with `{@ListName}`. The list is expanded when the query runs, so the query stays short and always uses the current list content.

```acklog
EventID=[4026, 4025, {@CriticalEventIDs}, 6012]
```

Literal values and the content of the CriticalEventIDs list.

### Comments and multi-line queries

```acklog
// failed logons per account
EventID=4625
| agg count by TargetUserName
| sort - count
```

A line that starts with // is ignored. Each pipe may start a new line.

## The pipeline and the two contexts

Statements after the search part run in order. A query has two contexts:

- **Search Context** — from the start of the query up to `agg`. Statements work on individual **events** (documents).
- **Aggregation Context** — after `agg`. Statements work on the **rows of the aggregation result table**. Column names here are the names produced by AGG (for example `count`, `dc(Page)` or a name given with `as`).

| Statement | Search Context | Aggregation Context | Purpose |
| --- | --- | --- | --- |
| [where](#where) | yes | yes | filter events / result rows |
| [eval](#eval) | yes | yes | add a calculated field / column |
| [agg](#agg) | yes (once) | — | aggregate; opens the Aggregation Context |
| [timespan](#timespan) | — | yes (once) | group by time buckets |
| [sort](#sort) | yes | yes | order results |
| [size](#size) | yes (once) | yes (once) | limit events / rows |
| [latest / earliest](#latest-earliest) | yes (once) | — | newest / oldest n events |
| [table](#table) | yes (once) | — | one row per event with chosen fields |
| [fields](#fields) | yes (once) | yes (once) | return only chosen fields / columns |
| [rename](#rename) | yes | yes (once, last) | rename fields / columns |
| [drop](#drop) | — | yes (once) | remove columns |
| [iplocation](#iplocation) | yes | yes | add country and city of an IP |
| [lookup](#lookup) | yes | yes | enrich from a CSV file |
| [transaction](#transaction) | yes (once) | — | group related events |
| [fieldformat](#fieldformat) | — | yes | display format of columns |

### Ordering rules

- `agg` can be used once per query. `table` cannot be combined with `agg`.
- `latest` and `earliest` cannot be combined with each other, with `size` or with `sort`.
- In the Search Context `table` and `fields` cannot be combined.
- In the Aggregation Context `fields` must come before `sort`, and `rename` must be the last statement.
- `drop` and `fieldformat` refer to column names as they are **before** `rename`.
- Aggregation results are sorted in descending order when no `sort` is given.

## Statement reference

### WHERE — filter events or result rows

Syntax:

```text
where <field> <= | == | != | > | >= | < | <=> <value>
where <field> in | notin [<value>, ...]
where <field> contains | notcontains [<value>, ...]
where <field> startswith | notstartswith [<value>, ...]
where <field> endswith | notendswith [<value>, ...]
where exists | notexists [<field>, ...]
```

In the Search Context WHERE filters events; after AGG it filters result rows, so it can test counts and calculated columns. Each WHERE holds one condition; chain several WHERE statements for more. Text comparisons are case-insensitive.

```acklog
UserName=* SourceIP=* | where SourceIP in [10.0.0.1, 10.0.0.2]
```

Keep events from two source addresses.

```acklog
EventID=4625 | where TargetUserName startswith [adm, svc]
```

Accounts whose name starts with "adm" or "svc".

```acklog
TargetUserName=*
| where TargetUserName endswith ["$"]
  | agg count by TargetUserName
```

Machine accounts only.

```acklog
EventID=4625
| where exists [IpAddress]
  | agg count by IpAddress
```

Only events that have an IpAddress field.

```acklog
UserName=* SourceIP=*
  | agg count by SourceIP
  | where count > 10
```

After AGG: keep source IPs with more than 10 events.

```acklog
SourceIP=*
  | agg count by SourceIP
  | where SourceIP notin [10.0.0.1, 10.0.0.2]
```

After AGG: exclude two rows.

> **Tip:** `contains`, `endswith` and their negations use wildcard matching and are expensive on large searches. Narrow the search part first.

### EVAL — calculated fields

Syntax:

```text
eval <newField>=<expression>
```

Creates one new field (Search Context) or column (Aggregation Context). Chain EVAL statements to create several. See [EVAL functions](#eval-functions) for every expression.

```acklog
DNSDomainName=* SourceIP=*
| eval DNSDomainNameLen=len(DNSDomainName)
| where DNSDomainNameLen > 100
  | agg count, values(DNSDomainName) by SourceIP
```

Very long DNS names per source (a DNS tunneling indicator).

```acklog
UserName=* SourceIP=*
| eval SourceCnt=concat(SourceIPCountryCode, "-", SourceIPCountryName)
  | agg count by SourceCnt
```

Combine two fields into one and count.

```acklog
EventID=[4624, 4625]
  | agg count(EventID) where EventID=4624 as SuccessfulLogons, count(EventID) where EventID=4625 as FailedLogons by TargetUserName
  | eval FailRate=div(FailedLogons, SuccessfulLogons)
  | sort - FailRate
```

After AGG: ratio of failed to successful logons per account.

### AGG — aggregate (count, sum, distinct count ...)

Syntax:

```text
agg <function> [where <condition>] [as <name>] [, ...] [by <field>, ...]
function: count | count(f) | sum(f) | avg(f) | min(f) | max(f) | minstr(f) | maxstr(f) | dc(f) | values(f) | var(f) | stdev(f)
```

Groups events by the BY fields and calculates one or more functions per group. AGG opens the Aggregation Context. Without AS, a column is named after the function as written: `count`, `dc(Page)`, `sum(SentByte)`. Each function may have its own WHERE; when more than one function uses WHERE, give each a name with AS. See [aggregation functions](#agg-functions).

```acklog
Taxonomy=Authentication.Fail SourceIP=*
  | agg count by SourceIP, UserName
  | sort - count
```

Failed authentications per source and user.

```acklog
NodeName=IIS* SourceIPCountryCode=* | agg count by SourceIPCountryCode
```

IIS requests per country.

```acklog
Page=* SourceIP=*
  | agg dc(Page) as DistinctCount, values(Page) by SourceIP
  | where DistinctCount > 200
```

Sources that requested more than 200 distinct pages (crawling / scanning).

```acklog
SentBytes=* SourceIP=*
  | agg count as NumberofLogs, sum(SentBytes) as TotalSentBytes by SourceIP
  | sort - TotalSentBytes
```

Several functions with names.

```acklog
Url=* SourceIP=*
  | agg count as TotalRequests, count where HttpStatus startswith [4, 5] as ErrorResponses by SourceIP
  | sort - ErrorResponses
```

Conditional count: all requests and error responses side by side.

```acklog
UserName=*
  | agg dc(DestinationIP) where DestinationPort in [3389, 22] as RemoteAccessTargets by UserName
  | where RemoteAccessTargets > 10
```

Users who reached more than 10 hosts over RDP or SSH.

```acklog
NodeName=trappot*
  | agg min(EventTime) where EventType="startup" as BootTime, min(EventTime) where EventType != "startup" as FirstAttackTime by NodeName
```

Two conditional minimums per honeypot.

```acklog
Taxonomy=Authentication.Fail SourceIP=*
  | agg count as FailedLogons, count where notexists [MFAToken, SmartcardId] as FailedLogonsWithoutMFA by SourceIP
  | sort - FailedLogonsWithoutMFA
```

Conditional count with an existence test.

### TIMESPAN — time buckets

Syntax:

```text
timespan <n><ms|s|m|h|d> [as <name>]
```

Splits the aggregation into time buckets and adds a time column (named `timespan` unless AS is given). Very short intervals over long time ranges produce many rows and slow responses.

```acklog
Method=* Page=* SourceIP=*
  | agg dc(Page) as DistinctCount by SourceIP
  | timespan 1h
```

Distinct pages per source per hour.

```acklog
EventID=4625
  | agg count by TargetUserName
  | timespan 15m
```

Failed logons per account in 15-minute buckets.

### SORT — order results

Syntax:

```text
sort [+|-] <field> [, ...]
```

`+` (default) is ascending, `-` is descending. After AGG, sort by any column, including unnamed ones such as `sum(SentByte)`.

```acklog
SourceIP=* DestinationIP=* SentBytes>100
| sort SourceIP, DestinationIP
| table SourceIP, DestinationIP, SentBytes
| size 100
```

Events sorted by two fields.

```acklog
SourceIP=* SentByte=*
  | agg sum(SentByte) by SourceIP
  | sort - sum(SentByte)
```

Sort by an unnamed aggregation column.

### SIZE — limit events or rows

Syntax:

```text
size <n>
```

```acklog
UserName=*admin* or User=*admin* | size 10
```

At most 10 events.

```acklog
(UserName=*admin* or User=*admin*) Hostname=*
  | agg count by Hostname
  | size 5
```

Top 5 rows of the aggregation.

### LATEST and EARLIEST — newest or oldest events

Syntax:

```text
latest [n]
earliest [n]
```

Return the n newest (LATEST) or oldest (EARLIEST) events by EventTime. n defaults to 1. Cannot be combined with SIZE or SORT.

```acklog
EventID=4625 | latest 20
```

The 20 most recent failed logons.

```acklog
SourceIP=172.70.10.11 NodeName=IIS_Server | earliest 5
```

The first 5 requests of a source in the time range.

```acklog
EventID=4625 | latest
```

Only the most recent event.

### TABLE — one row per event

Syntax:

```text
table <field> [as <name>] [, ...]
```

Shows events in the TABLE tab with the chosen fields as columns, in the given order. Cannot be combined with AGG or FIELDS.

```acklog
TargetUserName=* IpAddress=* EventID=4625
| table TargetUserName as USER_NAME, IpAddress, EventTime
```

Failed logons as a table with a renamed column.

### FIELDS — keep only some fields or columns

Syntax:

```text
fields <field> [, ...]
```

In the Search Context, returns only the listed fields, which makes large result sets faster to load. After AGG, keeps only the listed columns (names before RENAME; unknown names are ignored) and must come before SORT. Usable once in each context.

```acklog
SourceIP=* DestinationIP=* DestinationPort=*
| fields SourceIP, DestinationIP, DestinationPort
```

Return three fields only.

```acklog
DestinationPort=445 or SourcePort=445
  | agg sum(sentbyte) as sumsentbyte, sum(rcvdbyte) as sumrcvdbyte by UserName
  | eval TotalBytes=add(sumsentbyte, sumrcvdbyte)
  | fields UserName, TotalBytes
```

Keep only the final columns after a calculation.

### RENAME — rename fields or columns

Syntax:

```text
Search Context:       rename <field> as <name>
Aggregation Context:  rename <column> as <name> [, <column> as <name> ...]
```

In the Search Context RENAME copies one field under a new name (the original is kept); use one RENAME per field. After AGG it renames columns, may list several, can be used once and must be the last statement.

```acklog
srcip=* dstip=*
| rename srcip as Source_IP
| rename dstip as Destination_IP
| table Source_IP, Destination_IP
| size 1000
```

Two renames in the Search Context.

```acklog
TargetUserName=* EventID=4625
  | agg count by TargetUserName
  | rename TargetUserName as USER_NAME, count as FAILED_AUTH_COUNT
```

Rename result columns.

### DROP — remove columns

Syntax:

```text
drop <column> [, <column> ...]
```

Aggregation Context only, once. Typical use: remove helper columns after an EVAL. At least one column must remain.

```acklog
DestinationPort=445 or SourcePort=445
  | agg sum(sentbyte) as sumsentbyte, sum(rcvdbyte) as sumrcvdbyte by UserName, SourceIP
  | timespan 1h
  | eval TotalBytes=add(sumsentbyte, sumrcvdbyte)
  | sort - TotalBytes
  | drop sumsentbyte, sumrcvdbyte
```

Keep only the total.

### IPLOCATION — country and city of an IP

Syntax:

```text
iplocation <ipField> [, ...]
```

Adds `<field>Country` and `<field>City`. For example `IpAddress` gives `IpAddressCountry` and `IpAddressCity`.

```acklog
srcIp=* | iplocation srcIp
```

Location of each event.

```acklog
srcIp=*
  | agg count by srcIp
  | iplocation srcIp
```

Location of each aggregated source (faster: one lookup per row).

### LOOKUP — enrich from a CSV file

Syntax:

```text
lookup <file.csv> <lookupColumn> as <eventField> output <lookupColumn> as <newField>
```

Matches a field against a column of an uploaded CSV file and copies another column into the result. The file name must include its extension.

```acklog
EventID=*
  | agg count by EventID
  | lookup EventIDs.csv EventID as EventID output Explanation as EventDesc
```

Add a description to each event ID.

```acklog
EventID=[4728, 4732] MemberName!="-"
| lookup EventIDs.csv EventID as EventID output Explanation as EventDesc
| eval UserName=regex(MemberName, "(?i)cn=([^,]+)")
| table EventTime, EventID, EventDesc
```

Enrich, extract the user name from a DN, show as a table.

### TRANSACTION — group related events

Syntax:

```text
transaction <field> [, ...] [group_must_include=(<field>=[<value>, ...] [, ...])]
```

Groups events that share the same values of the listed fields into one transaction. `group_must_include` keeps only groups that contain the listed values. Most useful with TABLE.

```acklog
EventID=[4663, 4660] ObjectName!=$Recycle.Bin
| transaction SubjectLogonId, HandleId
| table EventTime, NodeName, ObjectName
```

Object access and deletion events of the same handle.

```acklog
EventID=[4663, 4660]
| transaction SubjectLogonId, HandleId group_must_include=(EventID=[4663, 4660])
| table EventTime, NodeName
```

Only handles that were both accessed and deleted.

### FIELDFORMAT — display format of columns

Syntax:

```text
fieldformat <column> = <formatFunction>([<argument>, ...]) [, <column> = <formatFunction>(...) ...]
```

Aggregation Context only. Changes how a column is **displayed**; the underlying numbers stay the same, so sorting and the GRAPH / MAP tabs still use real values. Column names are the names before RENAME. Several FIELDFORMAT statements can be chained, for example a number format followed by a suffix. A formatted column holds text, so do not use it in a later EVAL or WHERE. See [format functions](#format-functions).

```acklog
user=* SentByte=*
  | agg sum(SentByte) as SentByte by user
  | sort - SentByte
  | fieldformat SentByte=dots(0)
```

1234567 is shown as 1.234.567.

```acklog
SourceIP=* SentByte=*
  | agg sum(SentByte) by SourceIP
  | sort - sum(SentByte)
  | fieldformat sum(SentByte)=bytes(2)
```

Byte counts as KB, MB, GB.

```acklog
user=* SentByte=*
  | agg sum(SentByte) as Traffic by user
  | fieldformat Traffic=dots(2)
  | fieldformat Traffic=suffix(" bytes")
```

Number format plus a unit.

```acklog
NodeName=*
  | agg min(EventTime) as FirstSeen by NodeName
  | fieldformat FirstSeen=strftime("yyyy.MM.dd HH:mm:ss")
```

Epoch time as a readable date.

```acklog
user=* SentByte=*
  | agg sum(SentByte) as SentByte by user
  | sort - SentByte
  | fieldformat SentByte=dots(0)
  | rename SentByte as SENT_BYTES
```

FIELDFORMAT uses the name before RENAME.

## Aggregation functions

| Function | Returns | Notes |
| --- | --- | --- |
| `count` | number of events in the group |  |
| `count(f)` | number of events where f has a value |  |
| `dc(f)` | distinct count: number of different values of f | scanning, spraying and fan-out detection |
| `values(f)` | list of the distinct values of f | can time out on large data sets |
| `sum(f)` | sum | numeric fields |
| `avg(f)` | average | numeric fields |
| `min(f)`  `max(f)` | smallest / largest value | numeric and time fields |
| `minstr(f)`  `maxstr(f)` | alphabetically first / last value | text fields |
| `var(f)` | variance | numeric fields |
| `stdev(f)` | standard deviation | numeric fields |

```acklog
NodeID="*_*" | agg dc(SourceIP), count where exists [Password] by ModuleName
```

Unnamed columns are named dc(SourceIP) and count.

## EVAL functions

| Function | Description |
| --- | --- |
| `len(f)` | character length |
| `substr(f, start, end)` | part of a text; negative indexes count from the end |
| `lower(f)`  `upper(f)` | change case |
| `int(f)`  `str(f)` | convert to integer / text |
| `concat(f\|"text", ...)` | join fields and constants |
| `split(f, "delimiter", index)` | split and return the part at index (0-based) |
| `regex(f, "pattern")` | return the first capture group; `(?i)` makes it case-insensitive |
| `strftime(dateField, format)` | format a date; write the format without quotes and without spaces, e.g. `yyyy-MM-dd` |
| `add(a, b, ...[, decimals])` | sum of two or more operands |
| `sub(a, b[, decimals])` | a − b |
| `mul(a, b, ...[, decimals])` | product |
| `div(a, b[, decimals])` | a ÷ b; division by zero returns 99999999 |
| `calc(expression[, decimals])` | arithmetic with + − * /, unary minus and parentheses |
| `coalesce(f\|"text", f\|"text", ...)` | first argument that has a value |
| `case(cond => result, ... [, default => result])` | result of the first matching condition |

### Text functions

```acklog
NodeName=*
| eval N=upper(NodeName)
| eval L=len(NodeName)
| eval P=substr(NodeName, 0, 3)
| eval C=concat(NodeName, "-", L)
| table N, L, P, C
| size 3
```

upper, len, substr and concat.

```acklog
SourceIP=*
| eval Net=regex(SourceIP, "^([0-9]+[.][0-9]+)[.]")
  | agg count by Net
```

Extract the first two octets of an IP with a regular expression.

```acklog
UserName=*
| eval U=lower(UserName)
| eval Domain=split(UserName, "\\", 0)
| table EventTime, U, Domain
```

Lower case, and the domain part of DOMAIN\user.

### Date functions

```acklog
NodeName=*
| eval Hour=strftime(EventTime, HH)
  | agg count by Hour
  | sort Hour
```

Event count per hour of the day.

```acklog
NodeName=*
| eval Day=strftime(EventTime, yyyy-MM-dd)
  | agg count by Day
```

Event count per day.

### Arithmetic

```acklog
SourcePort=* DestinationPort=*
| eval S=sub(SourcePort, DestinationPort)
| eval M=mul(SourcePort, 2)
| eval A=add(SourcePort, DestinationPort)
| table S, M, A
| size 3
```

sub, mul and add.

```acklog
DestinationPort=445 or SourcePort=445
  | agg sum(sentbyte) as sumsentbyte, sum(rcvdbyte) as sumrcvdbyte by SourceIP
  | eval TotalKB=calc((sumsentbyte + sumrcvdbyte) / 1024, 0)
  | sort - TotalKB
```

calc with parentheses and decimal places.

### COALESCE

```acklog
UserName=*
| eval Actor=coalesce(UserName, SamAccountName, "UNKNOWN")
  | agg count by Actor
```

The first non-empty value, with a constant as the last fallback.

```acklog
NodeName=*
  | agg min(EventTime) where EventType="startup" as BootTime, min(EventTime) where EventType != "startup" as FirstEventTime by NodeName
  | eval RepresentativeTime=coalesce(BootTime, FirstEventTime)
```

COALESCE on aggregation columns.

### CASE

Conditions use these operators: `eq`, `ne`, `lt`, `le`, `gt`, `ge`, `in`, `notin`, `contains`, `notcontains`, `startswith`, `notstartswith`, `endswith`, `notendswith`. A result is a constant or `field(<name>)`. `default` must be last.

```acklog
EventID=[4624, 4625]
| eval Outcome=case(EventID eq 4624 => success, EventID eq 4625 => failure, default => other)
  | agg count by Outcome
```

Label events, then count the labels.

```acklog
HttpStatus=*
| eval Bucket=case(HttpStatus startswith [4, 5] => error, Action in [deny, drop, reset] => blocked, default => ok)
```

List operators inside CASE.

```acklog
SourcePort=*
| eval Direction=case(SourcePort gt field(DestinationPort) => "src-higher", SourcePort eq field(DestinationPort) => equal, default => "dst-higher")
```

Compare two fields with field().

```acklog
SourceIP=*
  | agg count by SourceIP
  | eval Level=case(count gt 1000 => high, count gt 100 => medium, default => low)
```

CASE on an aggregation column.

## FIELDFORMAT functions

| Function | Output example | Description |
| --- | --- | --- |
| `num([decimals][, "group"][, "decimal"])` | 1,234,567.89 | general number format with custom separators |
| `commas([decimals])` | 1,234,567.89 | comma groups, dot decimals |
| `dots([decimals])` | 1.234.567,89 | dot groups, comma decimals |
| `spaces([decimals])` | 1 234 567,89 | space groups, comma decimals |
| `percent([decimals])` | 12% | ratio × 100 with %; pairs with `div()` |
| `bytes([decimals][, 1024\|1000])` | 1.18 MB | scales to B, KB, MB, GB, TB, PB, EB |
| `duration([s\|ms])` | 01:02:03 | seconds (or ms) as HH:MM:SS, D+HH:MM:SS beyond a day |
| `strftime("format"[, s\|ms])` | 2026.01.31 14:05:00 | epoch value as a date |
| `printf("format")` | 1234.57 ms | C-style format with exactly one placeholder (%d %x %f %e %g %s) |
| `prefix("text")`  `suffix("text")` | $ 1.234 / 1.234 MB | add text before / after an already formatted value |

```acklog
EventID=[4624, 4625]
  | agg count(EventID) where EventID=4624 as SuccessfulLogons, count(EventID) where EventID=4625 as FailedLogons by TargetUserName
  | eval FailRate=div(FailedLogons, SuccessfulLogons)
  | fieldformat FailRate=percent(2)
  | sort - FailRate
```

A ratio shown as a percentage.

```acklog
user=* SentByte=*
  | agg sum(SentByte) as SentByte, count as Requests by user
  | fieldformat SentByte=dots(2), Requests=dots(0)
```

Two columns in one FIELDFORMAT.

```acklog
SourceIP=*
  | agg avg(ResponseTime) as AvgResponse by SourceIP
  | fieldformat AvgResponse=printf("%.2f ms")
```

printf with a unit.

## Scenario cookbook: security queries

Ready-to-use queries for common detection and threat hunting tasks. Event IDs are Windows Security log IDs; adjust field names to your log sources and thresholds to your environment.

### Authentication

#### Brute force: many failed logons from one source

**Goal:** Find source addresses with many failed logons.

```acklog
EventID=4625 IpAddress=*
  | agg count as Failures, dc(TargetUserName) as Users by IpAddress
  | where Failures > 20
  | sort - Failures
```

**Or ask the AI query assistant:** "Which source IPs had more than 20 failed logons, and how many accounts did each try?"

**How it works:** 4625 is a failed logon. Counting per IpAddress and keeping rows above a threshold shows the noisiest sources; Users shows how many accounts each tried.

#### Password spraying: one source, many accounts

**Goal:** Find sources that try a few passwords against many different accounts.

```acklog
EventID=4625 IpAddress=*
  | agg dc(TargetUserName) as DistinctUsers, count as Attempts by IpAddress
  | where DistinctUsers > 10
  | sort - DistinctUsers
```

**Or ask the AI query assistant:** "Find sources that tried to log on to more than 10 different accounts"

**How it works:** Spraying shows up as a high distinct count of accounts (dc) rather than a high count on one account.

#### Successful logon after repeated failures

**Goal:** Find account and source pairs that failed many times and then succeeded.

```acklog
EventID=[4624, 4625] IpAddress=*
  | agg count where EventID=4625 as Fail, count where EventID=4624 as Success by IpAddress, TargetUserName
  | where Fail > 10
  | where Success > 0
```

**Or ask the AI query assistant:** "Show accounts that logged on successfully after more than 10 failed attempts from the same IP"

**How it works:** Two conditional counts in one AGG give failures and successes side by side; the two WHERE statements keep pairs that match both conditions.

#### Failed logon rate per account

**Goal:** Rank accounts by the ratio of failed to successful logons.

```acklog
EventID=[4624, 4625]
  | agg count(EventID) where EventID=4624 as SuccessfulLogons, count(EventID) where EventID=4625 as FailedLogons by TargetUserName
  | eval FailRate=div(FailedLogons, SuccessfulLogons)
  | fieldformat FailRate=percent(2)
  | sort - FailRate
```

**Or ask the AI query assistant:** "Rank users by their failed logon rate as a percentage"

**How it works:** DIV calculates the ratio, PERCENT shows it as a percentage while SORT still uses the real number.

#### Failed logons over time

**Goal:** See when failed logons peak.

```acklog
EventID=4625
  | agg count by TargetUserName
  | timespan 15m
```

**Or ask the AI query assistant:** "Show failed logons per user in 15 minute intervals"

**How it works:** TIMESPAN buckets the counts into 15-minute intervals, which the GRAPH tab can plot.

#### RDP logons per account

**Goal:** List who logged on over Remote Desktop and from how many addresses.

```acklog
EventID=4624 LogonType=10
  | agg count, dc(IpAddress) as Sources by TargetUserName
  | sort - count
```

**Or ask the AI query assistant:** "Who logged on over RDP, and from how many different addresses?"

**How it works:** Logon type 10 (RemoteInteractive) is an RDP logon.

#### RDP logons with country and city

**Goal:** Spot RDP logons from unexpected countries.

```acklog
EventID=4624 LogonType=10 IpAddress=*
| iplocation IpAddress
| table EventTime, TargetUserName, IpAddress, IpAddressCountry, IpAddressCity
```

**Or ask the AI query assistant:** "List RDP logons with the country and city of the source IP"

**How it works:** IPLOCATION adds IpAddressCountry and IpAddressCity to each event.

#### Activity of admin and service accounts

**Goal:** Count failed logons for accounts whose names start with adm or svc.

```acklog
EventID=4625
| where TargetUserName startswith [adm, svc]
  | agg count by TargetUserName
  | sort - count
```

**Or ask the AI query assistant:** "Count failed logons for accounts starting with adm or svc"

**How it works:** STARTSWITH with a list matches any of the prefixes, case-insensitively.

### Accounts and audit

#### New user accounts

**Goal:** Who created which account, and where.

```acklog
EventID=4720
| table EventTime, SubjectUserName as CreatedBy, TargetUserName as NewAccount, NodeName
```

**Or ask the AI query assistant:** "Which user accounts were created, and by whom?"

**How it works:** 4720 is "a user account was created". TABLE with AS gives readable column names.

#### Members added to security groups

**Goal:** Track additions to global, local and universal security groups.

```acklog
EventID=[4728, 4732, 4756]
| table EventTime, NodeName, SubjectUserName, MemberName, TargetUserName
```

**Or ask the AI query assistant:** "Users added to security groups this week"

**How it works:** 4728, 4732 and 4756 are member-added events for the three group scopes; TargetUserName is the group.

#### Audit log cleared

**Goal:** Detect clearing of the Security or System event log.

```acklog
EventID=1102 or EventID=104 | table EventTime, NodeName, SubjectUserName
```

**Or ask the AI query assistant:** "Has anyone cleared the security or system event log?"

**How it works:** 1102 (Security log) and 104 (System log) are written when a log is cleared, a common anti-forensics step.

#### Who deleted which file

**Goal:** Correlate object access and delete events of the same file handle.

```acklog
EventID=[4663, 4660]
| transaction SubjectLogonId, HandleId group_must_include=(EventID=[4663, 4660])
| table EventTime, NodeName, ObjectName
```

**Or ask the AI query assistant:** "Who deleted which files?"

**How it works:** 4663 holds the file name, 4660 the deletion. TRANSACTION joins them by logon and handle, group_must_include keeps only handles that have both.

### Network

#### Lateral movement: fan-out over remote admin ports

**Goal:** Find sources that connect to many hosts over SSH, RDP, WinRM or SMB.

```acklog
DestinationPort=[22, 3389, 5985, 445] SourceIP=*
  | agg dc(DestinationIP) as Targets by SourceIP
  | where Targets > 20
  | sort - Targets
```

**Or ask the AI query assistant:** "Find hosts connecting to more than 20 machines over SSH, RDP, WinRM or SMB"

**How it works:** A workstation normally talks to few hosts on admin ports; a high distinct count of destinations is suspicious.

#### Port scan

**Goal:** Find source and destination pairs with many distinct destination ports.

```acklog
SourceIP=* DestinationPort=*
  | agg dc(DestinationPort) as Ports by SourceIP, DestinationIP
  | where Ports > 50
  | sort - Ports
  | size 20
```

**Or ask the AI query assistant:** "Detect port scans: sources hitting more than 50 ports on the same host"

**How it works:** Vertical scans touch many ports on one host; dc(DestinationPort) per pair exposes them.

#### Top talkers by volume

**Goal:** Which internal hosts sent the most data.

```acklog
SourceIP=10.0.0.0/8 sentbyte=*
  | agg sum(sentbyte) as Sent by SourceIP
  | sort - Sent
  | size 10
  | fieldformat Sent=bytes(2)
```

**Or ask the AI query assistant:** "Top 10 internal hosts by data sent, in MB or GB"

**How it works:** CIDR limits the search to internal sources; BYTES shows the totals as KB/MB/GB.

#### Possible data exfiltration: upload much larger than download

**Goal:** Find hosts whose sent bytes are far higher than received bytes.

```acklog
SourceIP=* sentbyte=* rcvdbyte=*
  | agg sum(sentbyte) as Up, sum(rcvdbyte) as Down by SourceIP
  | eval Ratio=div(Up, Down, 2)
  | where Ratio > 10
  | sort - Ratio
```

**Or ask the AI query assistant:** "Hosts that upload at least 10 times more than they download"

**How it works:** Normal browsing downloads more than it uploads. A high upload/download ratio is worth a look.

#### SMB traffic volume per host

**Goal:** Total SMB (445) traffic per source in KB.

```acklog
DestinationPort=445 or SourcePort=445
  | agg sum(sentbyte) as s, sum(rcvdbyte) as r by SourceIP
  | eval TotalKB=calc((s + r) / 1024, 0)
  | sort - TotalKB
  | drop s, r
  | size 10
```

**Or ask the AI query assistant:** "Total SMB traffic per host in KB, top 10"

**How it works:** CALC adds and converts in one expression; DROP removes the helper columns.

#### DNS tunneling: very long domain names

**Goal:** Find sources that resolve unusually long names.

```acklog
DNSDomainName=*
| eval Len=len(DNSDomainName)
| where Len > 60
  | agg count, dc(DNSDomainName) as Names by SourceIP
  | sort - Names
```

**Or ask the AI query assistant:** "Find sources querying unusually long domain names"

**How it works:** Tunneling tools encode data in sub-domains, producing long and unique names.

### Web

#### Web attack patterns in URLs

**Goal:** Requests with path traversal, SQL injection or XSS patterns.

```acklog
Page=*
| where Page contains ["../", "etc/passwd", "union select", "<script"]
| table EventTime, SourceIP, Method, Page, HttpStatus
```

**Or ask the AI query assistant:** "Show web requests that look like path traversal, SQL injection or XSS"

**How it works:** CONTAINS with a list matches any pattern, case-insensitively.

#### Sources with a high HTTP error rate

**Goal:** Find clients whose requests mostly fail (scanners, brute forcers).

```acklog
HttpStatus=* SourceIP=*
  | agg count as Total, count where HttpStatus startswith [4] as ClientErrors by SourceIP
  | eval ErrRate=div(ClientErrors, Total, 4)
  | where Total > 100
  | sort - ErrRate
  | fieldformat ErrRate=percent(1)
```

**Or ask the AI query assistant:** "Which clients get the highest rate of 4xx errors?"

**How it works:** A conditional count gives 4xx responses next to the total; DIV and PERCENT give the rate.

#### Crawling and content discovery

**Goal:** Sources that requested many distinct pages per hour.

```acklog
Method=* Page=* SourceIP=*
  | agg dc(Page) as DistinctCount by SourceIP
  | timespan 1h
```

**Or ask the AI query assistant:** "Sources requesting many distinct pages per hour"

**How it works:** Distinct pages per hour separates crawlers from normal users.

### Log source health

#### Event volume per log source per hour

**Goal:** Spot sources whose volume drops or spikes.

```acklog
NodeName=*
  | agg count by NodeName
  | timespan 1h
```

**Or ask the AI query assistant:** "Event count per log source per hour"

**How it works:** Plot it in the GRAPH tab; gaps mean a source stopped sending.

#### Last event time of each log source

**Goal:** Find silent log sources.

```acklog
NodeName=*
  | agg max(EventTime) as LastSeen by NodeName
  | sort LastSeen
  | fieldformat LastSeen=strftime("yyyy-MM-dd HH:mm:ss")
```

**Or ask the AI query assistant:** "When did each log source send its last event?"

**How it works:** Sorting ascending puts the sources that have been silent the longest at the top.

## Rules and common errors

The editor marks these problems while you type.

| Message / rule | Fix |
| --- | --- |
| Unknown statement | Check the spelling after `\|`. See the [statement list](#pipeline). |
| "x" cannot be used in the Aggregation Context | Move the statement before `agg`, or use the Aggregation Context alternative. |
| "table" and "agg" cannot be used together | Use either TABLE (rows per event) or AGG (grouped rows). |
| "latest" and "size"/"sort" cannot be used together | LATEST/EARLIEST already sort and limit. |
| "rename" must be the last statement in the Aggregation Context | Move RENAME to the end. |
| "fields" must be written before "sort" | After AGG, put FIELDS before SORT. |
| More than one aggregation function uses WHERE | Name each conditional function with `as`. |
| The column name is produced more than once | Give each function a distinct name with `as`. |
| Column does not exist in the aggregation results | DROP, FIELDS and FIELDFORMAT use the column names before RENAME. |
| At least one column must remain after DROP | Drop fewer columns. |
| Invalid duration | TIMESPAN takes `<number><ms\|s\|m\|h\|d>`, for example `1h` or `15m`. |
| The CSV file name must include its extension | Write `EventIDs.csv`, not `EventIDs`. |
| Unterminated quote / Unclosed bracket | Close every `"`, `'`, `(` and `[`. |

### Performance tips

- Filter as much as possible in the search part; it is evaluated by the index and is the fastest step.
- Use `field=*` to keep only events that have the fields you aggregate on.
- Prefer exact values and trailing wildcards (`abc*`) over leading wildcards and `contains`.
- `values()` and very short TIMESPAN intervals can produce very large results.
- In the Search Context, `fields` reduces the data sent to the browser.

### Deprecated: DERIVE

Older queries may contain `| derive Name=<expression>` after AGG. It still runs but is deprecated: use `| eval Name=<expression>` instead.

## Frequently asked questions

### Can AI write ACKLOG queries for me?

Yes. Click **Ask AI** next to the search box and describe what you want to find in plain language, for example "failed logons per user in the last 24 hours". The query assistant writes the ACKLOG query, explains it, checks it with the search engine and puts it in the search box with one click.

### Can I ask the ACKLOG query assistant in Turkish?

Yes. Write your request in English or Turkish; the explanation comes back in the language you write in. The query itself is always ACKLOG syntax.

### Does the AI query assistant read my logs?

No. The assistant only writes queries. It never reads your log data or your search results; you run the query yourself.

### How do I search for all events in ACKLOG?

Type `*` in the search box. The time range comes from the time picker next to the search box, not from the query.

### How do I count events per user, IP or host in ACKLOG?

Use the AGG statement: `EventID=4625 | agg count by TargetUserName`. Add `| sort - count` to see the largest values first.

### What is the difference between the search part and WHERE?

The search part (everything before the first `|`) selects events from the index and is the fastest filter. `| where` filters what the earlier steps produced; after `agg` it filters result rows, so it can filter on counts like `where count > 10`.

### How do I search for an exact value or a phrase?

Use double quotes for a phrase or a value with spaces: `"failed password"`. Use single quotes for an exact match of the whole field value: `TargetUserName='Administrator'`.

### How do I match several values for one field?

Use a bracketed list: `EventID=[4624, 4625, 4634]`. It means EventID=4624 OR EventID=4625 OR EventID=4634.

### How do I search an IP range or subnet?

Use CIDR notation `SourceIP=10.0.0.0/16` or an explicit range `SourceIP=[10.10.1.1 TO 10.12.5.254]`.

### How do I group results by time, such as per hour?

Add TIMESPAN after AGG: `NodeName=* | agg count by NodeName | timespan 1h`. Units are ms, s, m, h and d.

### How do I create a calculated field?

Use EVAL: `| eval NameLen=len(UserName)`. Each EVAL creates one field; chain several EVAL statements for more.

### How do I rename columns in the result table?

Use RENAME: `| agg count by TargetUserName | rename TargetUserName as USER, count as FAILS`. After AGG, RENAME must be the last statement.

### How do I show bytes as KB/MB/GB or numbers with thousands separators?

Use FIELDFORMAT after AGG: `| fieldformat Sent = bytes(2)` or `| fieldformat Total = dots(0)`. It changes only how values are displayed; sorting still uses the real numbers.

### Are ACKLOG query keywords case-sensitive?

No. `agg`, `AGG` and `Agg` are the same, as are `and`, `AND`, `or`, `OR`, `not`, `NOT`. Field names and values should be written as they appear in your logs.

### Can I add comments to a query?

Yes. A line that starts with `//` is a comment and is ignored when the query runs.