ACKLOG Query Language Reference and Examples

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.

<search part> | <statement> | <statement> | ...

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

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.

Quick start

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

*

EventID=4625

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

EventID=4625 | latest 20

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

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

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

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

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

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

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.

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

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

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

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

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.

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

*

login.aspx

"failed password"

Field conditions

Page=*

SourcePort>1024

MemberName!="-"

Wildcards

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

Page=log*

Page=*ogin.aspx

UserName=*admin*

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.

EventID=4625 TargetUserName=admin

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

EventID=4625 AND NOT TargetUserName=*$

not EventID=4624

Quotes: phrases and exact values

Lists of values

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

EventID=[4624, 4625, 4644]

DestinationPort=[22, 3389, 5985]

IP subnets and ranges

SourceIP=10.0.0.0/16

SourceIP=[10.10.1.1 TO 10.12.5.254]

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.

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

Comments and multi-line queries

// failed logons per accountEventID=4625| agg count by TargetUserName| sort - count

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).

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

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.

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

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

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

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

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

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

EVAL — calculated fields

eval <newField>=<expression>

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

TIMESPAN — time buckets

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.

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

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

SORT — order results

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

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

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

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

SIZE — limit events or rows

size <n>

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

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

LATEST and EARLIEST — newest or oldest events

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.

EventID=4625 | latest 20

SourceIP=172.70.10.11 NodeName=IIS_Server | earliest 5

EventID=4625 | latest

TABLE — one row per event

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.

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

FIELDS — keep only some fields or columns

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.

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

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

RENAME — rename fields or columns

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.

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

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

DROP — remove columns

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

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

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

IPLOCATION — country and city of an IP

iplocation <ipField> [, ...]

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

srcIp=* | iplocation srcIp

srcIp=*  | agg count by srcIp  | iplocation srcIp

LOOKUP — enrich from a CSV file

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.

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

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

TRANSACTION — group related events

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.

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

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

FIELDFORMAT — display format of columns

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.

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

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

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

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

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

Aggregation functions

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

EVAL functions

Text functions

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

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

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

Date functions

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

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

Arithmetic

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

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

COALESCE

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

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

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.

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

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

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

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

FIELDFORMAT functions

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

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

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

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

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

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

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

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

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

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

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

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

Accounts and audit

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

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

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

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

Network

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

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

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

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

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

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

Web

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

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)

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

Log source health

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

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

Rules and common errors

The editor marks these problems while you type.

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