Searching Logs - ESQL

TEAM: Huntress Managed Security Information and Event Management (SIEM) 
ENVIRONMENT: Huntress Managed SIEM dashboard
SUMMARY: Learn how to search Huntress Managed SIEM logs using ES|QL, including basic syntax, operators, functions, field selections, and known limitations.

In this Article

Basic Search Syntax
Time Range and Search Results
Supported Operators
Supported Functions
Processing Operators
Query Limitations
Available Fields
Search the Raw Event
Missing Fields and NULL Values
Syslog Events
Example Syslog Queries

Basic Search Syntax

Huntress Managed SIEM supports a subset of Elastic ES|QL to help you construct targeted log searches without requiring deep expertise in the complete language. For full documentation on Elastic syntax, review Elastic's official ES|QL syntax documentation.

The basic structure of an ES|QL query starts with the log source and applies filters:

FROM logs
| WHERE [column_name] [operator] [value]

For example, the following query searches for Event ID 4624, which represents a successful account login:

FROM logs
| WHERE event.code == 4624

Use numeric values for numeric fields such as event.code. Values in the winlog.event_data scope are represented as strings in the ECS data and must be enclosed in double quotation marks:

FROM logs 
| WHERE winlog.event_data.TargetUserName == "john.doe"

You can chain multiple WHERE statements to filter on multiple criteria. Huntress combines these conditions so that returned events match all specified criteria:

FROM logs 
| WHERE winlog.event_data.TargetUserName == "john.doe" 
| WHERE event.code == 4624 
| KEEP event.code, winlog.event_data.TargetUserName

Use parentheses when combining AND, OR, or NOT conditions to ensure explicit evaluation logic:

FROM logs
| WHERE event.provider == "Microsoft-Windows-Security-Auditing"
| WHERE (winlog.event_data.LogonType == "7" OR winlog.event_data.LogonType == "10")

Comments

Annotate your queries using line comments (//) or block comments (/* ... */) to document search intent:

FROM logs
// only successful logins
| WHERE event.code == 4624
| KEEP /* the fields we care about */ winlog.event_data.TargetUserName

Quoting Field Names

Field names are written unquoted by default. If a field name contains special characters that the parser reads as syntax, enclose the field name in backticks:

FROM logs
| WHERE `event.code` == 4624

Time Range and Search Results

Manage search timeframes and result volume directly within the dashboard controls.

The default search time range is the previous hour. To search a larger or custom time window, modify the Time Range picker in the Huntress Managed SIEM dashboard.

Huntress initially returns the first 100 log entries for a query. Select Load More at the bottom of the results table to retrieve additional records.

Supported Operators

Use comparison, logical, and pattern-matching operators within WHERE clauses to narrow down query results.

FROM logs 
| WHERE event.code == 4624

FROM logs 
| WHERE event.code != 4624

FROM logs 
| WHERE event.code > 1

FROM logs 
| WHERE event.code >= 3

FROM logs 
| WHERE event.code < 3

FROM logs 
| WHERE event.code <= 7

FROM logs 
| WHERE message LIKE "permissions%" 
AND event.provider == 
"Microsoft-Windows-Security-Auditing"

FROM logs 
| WHERE event.code != “4106” 
OR message LIKE "permission%"

FROM logs 
| WHERE NOT (event.code == 4624)

FROM logs 
| WHERE winlog.event_data.TargetUserName 
LIKE "%Admin%"

FROM logs 
| WHERE event.provider 
NOT LIKE "%powershell%"

FROM logs 
| WHERE process.name RLIKE ".*cmd\\.exe"

FROM logs 
| WHERE process.name NOT RLIKE ".*svchost\\.exe"

FROM logs 
| WHERE "192.168"
IN source.ip

FROM logs 
| WHERE event.code NOT IN (4624, 4625)

FROM logs 
| WHERE event.code BETWEEN 4600 AND 4700

FROM logs 
| WHERE event.code NOT BETWEEN 4600 AND 4700

FROM logs 
| WHERE source.ip IS NULL 
| KEEP source.ip

FROM logs 
| WHERE host.os.name IS NOT NULL 
| KEEP host.os.name

Comparing Against the Current Time

Calculate relative time ranges within WHERE clauses using the NOW() function.

NOW() evaluates to the query's execution timestamp. Subtract or add fixed time intervals to filter logs within a wider dashboard window:

FROM logs
| WHERE @timestamp > NOW() - 1 hour

Supported interval units include second, minute, hour, day, and week (singular or plural). month and year are not supported due to variable lengths.

Supported Functions

Functions allow you to transform, analyze, and manipulate field values within queries. Function support depends on the clause where it is invoked.

Functions Supported in WHERE and EVAL

These scalar functions transform string, numeric, or hash values across filtering and evaluation steps.

Functions Supported in WHERE Only

These Boolean functions evaluate conditions directly within WHERE clauses.

Negate any of these functions using NOT (for example, WHERE NOT CONTAINS(message, "powershell")).

Functions Supported in EVAL Only

Use these functions within EVAL clauses to derive new attributes or evaluate conditional expressions.

Arithmetic in EVAL

Perform mathematical operations on numeric fields during log processing.

EVAL supports standard arithmetic operators (+, -, *, /, %) with parentheses for grouping:

FROM logs
| EVAL total_bytes = source.bytes + destination.bytes
| EVAL ratio = (source.bytes + 1) / 1.5
| KEEP total_bytes, ratio

Converting Types in EVAL

Explicitly cast field data types to prepare values for calculations or display.

Convert field types using cast functions or the :: shortcut notation. Supported data types include ip, long, datetime, string, and double:

FROM logs
| EVAL code_text = TO_STRING(event.code)
| EVAL also_code_text = event.code::string
| KEEP code_text, also_code_text

Available cast functions are TO_IP, TO_LONG, TO_DATETIME, TO_STRING, and TO_DOUBLE.

Processing Operators

Processing operators structure query pipelines to filter, calculate, aggregate, and format dataset results.

WHERE

Filter raw log events prior to pipeline transformations.

Chain multiple WHERE clauses or combine conditions using logical operators:

FROM logs
| WHERE winlog.event_data.TargetUserName == "john.doe"
| WHERE event.code == 4624
| WHERE winlog.event_data.LogonType == "2"
| KEEP event.code, winlog.event_data.TargetUserName, winlog.event_data.LogonType

EVAL

Create calculated fields or modify existing values within the pipeline.

Calculate intermediate fields before aggregation:

FROM logs
| EVAL total_bytes = source.bytes + destination.bytes
| STATS total = SUM(total_bytes)

Normalize string values before counting unique entries:

FROM logs
| EVAL normalized_user = TO_LOWER(user.name)
| STATS unique_users = COUNT_DISTINCT(normalized_user)

Define multiple fields in one EVAL statement, separated by commas:

FROM logs
| EVAL total_bytes = source.bytes + destination.bytes, total_kb = total_bytes / 1024
| KEEP total_kb

Filter on newly created EVAL fields in subsequent WHERE statements:

FROM logs
| EVAL total_bytes = source.bytes + destination.bytes
| WHERE total_bytes > 100000

Transform aggregated rows by placing EVAL after STATS:

FROM logs
| STATS log_count = COUNT(*) BY user.name
| EVAL high_volume = log_count > 100

STATS

Summarize log entries into aggregated dataset groups.

Alias result fields using alias = before the function or AS after it:

FROM logs
| STATS event_count = COUNT(*) BY user.name

FROM logs
| STATS COUNT(*) AS event_count BY user.name

COUNT

Count total events per unique user:

FROM logs
| WHERE user.name != ""
| STATS count = COUNT(*) BY user.name

COUNT DISTINCT

Count unique event codes per user:

FROM logs
| WHERE user.name != ""
| STATS unique_event_codes = COUNT_DISTINCT(event.code) BY user.name

SUM

Sum numeric values across log records:

FROM logs
| WHERE event.code == 4104
| STATS total_messages = SUM(winlog.event_data.MessageTotal)

MIN and MAX

Extract boundary values across log groups:

FROM logs
| WHERE event.code == 4104
| STATS minimum_messages = MIN(winlog.event_data.MessageTotal), maximum_messages = MAX(winlog.event_data.MessageTotal)

VALUES

Collect distinct values into a list for each group:

FROM logs
| WHERE event.code == 4624
| STATS accounts = VALUES(winlog.event_data.TargetUserName) BY host.name

AVG, PERCENTILE, MEDIAN, and TOP

Calculate distribution statistics across numeric fields:

FROM logs
| STATS average_bytes = AVG(source.bytes)
| STATS p95_bytes = PERCENTILE(source.bytes, 95)

MEDIAN(field) returns the median value. TOP(field, limit, "order") returns top or bottom records (for example, TOP(source.bytes, 3, "desc")).

Grouping by Time with DATE_TRUNC

Bucket log events into time histograms using DATE_TRUNC:

FROM logs
| WHERE event.code == 4625
| STATS failures = COUNT(*) BY DATE_TRUNC(1 hour, @timestamp)

Supported intervals are 1 second, 1 minute, 1 hour, 1 day, and 1 week.

Filtering Aggregated Results

Place WHERE after STATS to filter aggregated output rows:

FROM logs
| STATS failures = COUNT(*) BY user.name
| WHERE failures > 100

Re-aggregating with a Second STATS

Chain aggregate operations to summarize grouped datasets further:

FROM logs
| STATS events = COUNT(*) BY user.name, host.name
| STATS busiest_user_events = MAX(events) BY host.name

SORT

Reorder aggregated rows in queries using STATS:

FROM logs
| STATS failures = COUNT(*) BY user.name
| SORT failures DESC
| LIMIT 10

Use ASC for ascending order and DESC for descending. Separate multiple columns with commas (for example, SORT failures DESC, user.name ASC).

KEEP

Specify explicit output columns returned in the search results table:

FROM logs
| WHERE winlog.event_data.TargetUserName == "john.doe"
| KEEP event.code, winlog.event_data.TargetUserName

DROP

Exclude specific columns from the default result view:

FROM logs
| WHERE event.code == 4104
| DROP event.provider, message

RENAME

Alias column titles to shorter or clearer names:

FROM logs
| WHERE event.code == 4624
| RENAME winlog.event_data.TargetUserName AS target_user
| KEEP event.code, target_user

Multiple field mappings can be assigned in a single statement:

FROM logs
| RENAME target_user = winlog.event_data.TargetUserName, source_ip = winlog.event_data.IpAddress
| KEEP target_user, source_ip

LIMIT

Restrict the maximum number of returned records:

FROM logs
| WHERE event.code == 4624
| LIMIT 10

MV_EXPAND

Expand multi-value array entries into individual result rows:

FROM logs
| WHERE event.code == 4624
| STATS accounts = VALUES(winlog.event_data.TargetUserName) BY host.name
| MV_EXPAND accounts

Query Limitations

Review unsupported query constructs to prevent syntax execution errors.

The following syntax constructs are currently unsupported in Huntress Managed SIEM:

• Expressions nested directly inside aggregation arguments (for example, SUM(a + b)). Use EVAL to create intermediate fields first.

• Per-aggregation filters (for example, STATS c = COUNT(*) WHERE a == 1 BY b). Filter before STATS or apply WHERE after STATS.

• BUCKET functions. Use DATE_TRUNC in BY clauses instead.

• SORT ... NULLS FIRST or SORT ... NULLS LAST.

• SORT on raw log searches without STATS.

• Unassigned EVAL statements. Always assign output field names.

• Unsupported multi-value functions (MV_COUNT, MV_DEDUPE, MV_SORT, etc.).

• Full-text search directives (MATCH, QSTR, KQL, TERM, :).

• Date manipulation functions other than DATE_TRUNC and DATE_DIFF.

• Advanced string functions (TRIM, SPLIT, LOCATE, REVERSE, etc.).

• Advanced math functions (POW, SQRT, LOG10, trigonometric operations).

• Type conversions outside standard cast functions (TO_INTEGER, TO_BOOLEAN, TO_VERSION).

• Complex aggregations (STD_DEV, WEIGHTED_AVG, SAMPLE).

• Parsing commands (GROK, DISSECT, RENDER).

• Join and pipeline controls (ENRICH, LOOKUP JOIN, FORK, INLINE STATS, ST_* spatial functions).

Available Fields

Log schema details vary depending on the event provider and source environment.

To view available fields for an event, select View on the event row in the results table. The inspector displays both parsed ECS fields and raw payload structures.

All XML elements from Windows Event Log <EventData> tags are extracted under the winlog.event_data scope.

Search the Raw Event

Query unparsed log payloads stored in event.original when searching for non-materialized fields.

Every log entry preserves full unparsed text within event.original. Query event.original using substring functions like CONTAINS:

FROM logs
| WHERE CONTAINS(event.original, "Invoke-Mimikatz")
| KEEP event.original
| LIMIT 5

Use LIKE or RLIKE for regex pattern matching against raw payloads:

FROM logs
| WHERE event.original RLIKE "-enc(odedcommand)? [A-Za-z0-9+/=]{100,}"
| KEEP event.original

Missing Fields and NULL Values

Understand how unmaterialized field values are handled in log datasets.

Missing fields may return ClickHouse default zero values rather than NULL:

• Strings: "" (empty string)

• Numbers: 0

• Timestamps: Epoch zero timestamp

Because missing attributes do not reliably return NULL, null-checking functions like IS NULL, COALESCE, and LEAST may not trigger as expected. Check explicitly against zero-values (for example, WHERE user.name != "").

Syslog Events

Huntress continuously expands parsing capabilities for syslog log formats across network security appliances.

When a parser is active for a syslog source, critical attributes populate into the Huntress ECS schema under event.provider.

Filter specifically for syslog events using event.capability:

FROM logs
| WHERE event.capability == "syslog"

Example Syslog Queries

Use these pre-built queries for common investigation scenarios.

Successful RDP Logins

Query successful remote desktop logins while filtering out loopback and broadcast traffic:

• IpAddress: Originating IP address of the authentication request.

• TargetUserName: User account authenticating via RDP.

• WorkstationName: Originating client computer name (when available).

FROM logs
| WHERE event.provider == "Microsoft-Windows-Security-Auditing"
| WHERE event.code == 4624
| WHERE (winlog.event_data.LogonType == "7" OR winlog.event_data.LogonType == "10")
| WHERE winlog.event_data.IpAddress NOT IN ("-", "127.0.0.1", "0.0.0.0")
| KEEP winlog.event_data.IpAddress, winlog.event_data.TargetUserName, winlog.event_data.WorkstationName
| LIMIT 5

User Creation

Identify newly created user accounts and responsible actor credentials:

• SubjectUserName: Account that created the new user.

• TargetUserName: Newly created user account.

• TargetDomainName: Domain where the user creation occurred.

FROM logs
| WHERE event.code == 4720
| KEEP winlog.event_data.SubjectDomainName, winlog.event_data.SubjectUserName, winlog.event_data.TargetUserName, winlog.event_data.TargetDomainName

Failed Logins by User

Identify top accounts experiencing failed authentication attempts:

FROM logs
| WHERE event.code == 4625
| STATS failures = COUNT(*) BY winlog.event_data.TargetUserName
| WHERE failures > 5
| SORT failures DESC
| LIMIT 10

Firewall Queries

Filter SonicWall SSL VPN IP assignments and login events:

FROM logs
| WHERE sonicwall.m == "1079"
| WHERE message != "destination for 255.255.255.255 is not allowed by access control"
| KEEP sonicwall.m, message

Review successful SonicWall VPN user authentications:

FROM logs
| WHERE sonicwall.m == "1080"
| KEEP sonicwall.m, message, sonicwall.note, source.ip

For more details on SonicWall log fields, refer to the SonicWall Log Events Reference Guide.