Advanced Search query language

Data Lake Search Advanced Search query language allows you to write advanced, flexible queries for detections and events in your tenant using a custom syntax.

Anatomy of a query

An Advanced Search query has the following structure:

FROM <schema, schema> WHERE <condition> earliest=<time> latest=<time>

Example

FROM process earliest=-24h

FROM process, auth WHERE @user CONTAINS 'system' earliest=-12h

Search help

In Query Editor, click the Search Help icon to open the inline help that includes:

Build with Me section that helps you craft successful queries
Basic syntax
Available data types (schemas) and field names (context-aware as you type)
Operators
Examples

Tip

There is also a Getting Started section beneath the query prompt containing basic guidance.

Click Schema Library for a reference of all searchable schemas and fields.

Core concepts

Schemas

Schemas are a category of data (similar to a relational database tables). Examples: process, auth, netflow. Detections use the detection schema. For more details, see Schemas.
You can select one or more schemas with FROM.

Tip

You can omit FROM when using logical types. The engine targets relevant schemas automatically.

Time window

Adjust the time range at the top of the page. The default range is 24 hours.
Alternatively, add earliest and/or latest to bound the time range. See Time ranges

Filters (WHERE)

WHERE is optional and can include multiple conditions joined with AND/OR and parentheses ().
Strings use single quotes. To include single quotes inside a string, use the e'...' form and escape internal quotes:
```
FROM process WHERE commandline CONTAINS e'echo \'mimikatz\'' earliest=-24h
```

How fields are resolved

Event queriesDetection queriesCommon fields and logical types

Unqualified field names (for example, image_path) are matched against fields that exist in each selected schema.
In multi-schema queries (for example, FROM process, auth), the engine runs a subquery per schema and returns the union of results. A condition affects only schemas where that field exists.
You may also reference fields with a schema prefix for clarity (for example, process.commandline).
Find events that were used to generate a detection using detection.resource.id.

Note

Each event in Sophos XDR has a unique resource_id.

Supported operators:

IS NOT NULL
=
CONTAINS
IN

Example

WHERE detection.resource.id IS NOT NULL
WHERE detection.resource.id = 'alert://priv:event-filter:159778:1761693931622:f8ba5890-a5f9-5cbe-961d-d5031ee2beb1'
WHERE detection.resource.id IN ('<id1>', '<id2>', '<id2>')
WHERE detection.resource.id CONTAINS 'ba05c6fb'

Info

Sophos XDR employs rate limiting on events search to ensure consistent and optimum performance. This is an essential step to prevent the application from being overwhelmed by more requests than it can handle gracefully. Two kinds of rate limiting are employed:

Concurrent Running Query Unit Quotas: Restricts the number of query units that can simultaneously execute per tenant. Depending on the search being run, one or more query units could be executed. For example, running logical types searches would execute multiple query units. Presently, a maximum of 100 concurrent query units are allowed for each tenant.
Data Scan Limits: Manages the volume of data scanned during any query. Queries to the search database are restricted to a maximum scan limit of 10TB.

You can filter by detection fields (for example, title) or by event fields related to the detection.

When using event fields in a detection search, prefix with the event schema (for example, process.commandline).

Example

FROM detection WHERE title CONTAINS 'Powershell' earliest=-7d
FROM detection WHERE process.commandline CONTAINS 'powershell.exe' earliest=-7d

Common fields exist in multiple schemas (for example, sensor_type). Using them returns results wherever they exist.
Logical types (prefixed with @) expand to the appropriate fields across relevant schemas (for example, @ip, @user). For more information, see Logical types.
Example
```
sensor_type = 'ENDPOINT_SOPHOS'
@user CONTAINS 'system'
FROM netflow WHERE @ip = '10.0.0.1' earliest=-2h
```

For IP range matching with @ip and other IP address fields, see CIDR Notation.

Key rules and guardrails

You cannot query detections and events in the same query; run separate queries when needed.
For detection searches using event fields, always prefix the field with the event schema (for example, process.image_path).
Regular expressions differ by backend:
- Events use a Java-compatible regex engine. See the Java regex documentation for notation help.
- Detections use a Lucene-based regex engine. See the Elastic regex documentation for notation help.
Hostname is a special case that translates to host_id and supports only literal operators (=, !=, IN, !IN). Hostname matching is case-sensitive:
```
FROM auth WHERE hostname = 'Demo-PC01' earliest=-24h
```
The @raw Logical Type and the original_data field can access only the last 20 days of data. For longer periods, run in 20-day increments or narrow the time range.
Operators and values are case-insensitive, except for hostname translation as noted above.
Query length limits apply; expansions from logical types and special characters can cause short-looking queries to hit limits at execution.

Arrays

Queries over array fields are flattened, so you can search for matches across any element using standard field.subfield notation:

FROM http WHERE http_response_headers.record.key = 'Authorization' AND http_response_headers.record.value = 'Bearer 1234'

Tip

The query above finds records that have an 'Authorization' header and a header with value 'Bearer 1234', but it does not guarantee these belong to the same header record. Matching on specific array indices is not supported.

Operators and booleans

Operators are case-insensitive and include: =, !=, >, >=, <, <=, CONTAINS, MATCHES, MATCHES_REGEX, IN, IS NULL, IS NOT.
Combine conditions with AND/OR/NOT. Use parentheses to ensure intended logic, especially in multi-condition queries.

See the table below for a list and description of Advanced Search query language operators and booleans:

Operator	Description
`= <literal>`	Case-insensitive exact match.
`!= <literal>`	Boolean NOT of equality.
`> <number>`	Numeric greater than.
`>= <number>`	Numeric greater or equal.
`< <number>`	Numeric less than.
`<= <number>`	Numeric less than or equal.
`CONTAINS <string>`	Case-insensitive substring match of string literal in field.
`<fieldname> !CONTAINS <string exclusion>`	Negation of case-insensitive substring match of string literal in field.
`MATCHES <string>`	Case-sensitive wildcard match of glob-style string pattern in field.
`<fieldname> !MATCHES <string exclusion>`	Negation of case-insensitive wildcard match of glob-style string pattern in field.
`MATCHES_REGEX <string>`	Case-insensitive regex match of string pattern in field.
`!MATCHES_REGEX <string>`	Negation of case-insensitive regex match of string pattern.
`IN <value list>`	Boolean expression evaluates true if field value is contained in value list.
`!IN <value list>`	Boolean expression evaluates true if field value is not contained in value list.
`IS NOT NULL`	Tests for the existence of field and that it is not null.
`IS NULL`	Returns true if the specified field does not exist or is null.

Info

All operators/reserved words are case insensitive (for example, AND, and, aNd are equivalent).

AGG, AGGREGATE, AND, AS, ASC,

AVG, BY, CONTAINS, COUNT,#

CARDINALITY, DESC, DESCENDANT,

EARLIEST, FALSE, FIELDS, FROM,

HEAD, IN, IS, INDEX,

LATEST, MATCHES, MATCHES_REGEX,

MAX, MIN, NOT, NOW, NULL, OF,

OR, SEARCH, SORT, SUM,

TAIL, TOP, TRUE, WHERE

CIDR Notation

Advanced Search query language supports CIDR notation for IP address fields when using the = (literal), MATCHES, or IN operators. Query language also supports CIDR notation with the negated forms of these operators, including !=, !MATCHES, and !IN.

At this time, CIDR notation is only supported for event queries. Detection queries do not support CIDR notation and a valid IP address must be used.

Examples

Query netflow events where the source address is within the IP range 192.168.2.0/24:

FROM netflow WHERE source_address = '192.168.2.0/24'

Query netflow events where the IP address is within an IPv6 CIDR range:

FROM netflow WHERE @ip IN ('2001:db8::/32')

Note

The following MATCHES query acts as a textual wildcard match and not a CIDR query:

FROM netflow WHERE @ip MATCHES '192.168.2*'

The following MATCHES query acts as a CIDR query:

FROM netflow WHERE @ip MATCHES '192.168.2.*.*'

IPv6 comparisons are string literal unless you use CIDR

When you use =, CONTAINS, or MATCHES with an IPv6 address, the query compares the value as a literal string. Equivalent IPv6 representations, including compressed and expanded forms, don't match unless they use the exact same formatting.

To match reliably across formats, use IPv6 CIDR notation. For a single address, use /128.

IPv6 address caveats

IPv6 addresses can be represented in multiple equivalent ways. For example, all of the following represent the same address:

2001:db8:abcd:1234:0:1:0:0

2001:db8:abcd:1234:0:1::

2001:db8:abcd:1234:0000:0001:0000:0000

When you search with the =, CONTAINS, or MATCHES operators, IPv6 addresses are compared as literal strings. Only the exact format used in the query will match.

To match an IPv6 address reliably regardless of its format, use CIDR notation. CIDR notation normalizes IPv6 addresses before comparison, so all equivalent representations match.

Use a /128 prefix length to match a single address:

FROM email WHERE @ip IN ('2001:db8:abcd:1234:0:1::/128')

Time ranges

Constrain results using relative or absolute time bounds.

Syntax:

earliest=[+-]<delta><unit>[@unit]
latest=[+-]<delta><unit>[@unit]
Units: s (sec), m (min), h (hour), d (day), w (week), mo (month), y (year)
@unit truncates to the boundary of that unit.

Behavior:

If only latest is provided, earliest defaults to the preceding 24 hours.

Example

FROM process WHERE image_path CONTAINS 'powershell.exe' earliest =-24h

FROM netflow WHERE source_address = '10.0.0.1' earliest =-1d@d

FROM process WHERE image_path CONTAINS 'powershell.exe' earliest ='2019-06-01T00:00:00' latest ='2019-06-30T00:00:00'

Functions

Searches can be further qualified by piping results into additional functions.

search | functions

When the query searches across multiple data types, the function operates across each data type independently.

Function	Description
`sort`	Sort results by specified fields and order. Single event type: sort by any field. Multiple event types: only `event_time_usec` and `ingest_time_usec` are supported. For detections sorting is not supported.
`head`	Return the first N results from each event type in search order.
`tail`	Return the last N results from each event type. `tail` reverses the order of the results before returning the last N.
`tolower`	Normalize a string field to lowercase in `BY` grouping clauses to enable case-insensitive aggregations and consistent grouping regardless of original casing.
`toupper`	Normalize a string field to uppercase in `BY` grouping clauses to enable case-insensitive aggregations and consistent grouping regardless of original casing.
`fields`	API-only. Include or exclude selected fields in results (acts like a column selector). By default, all fields except `original_data` are returned. Selecting only required fields can improve performance.

Info

The fields function is available only via the API and is not available in the Sophos XDR UI.

Example

Limit results per data type:

FROM process, auth WHERE @user CONTAINS 'admin' earliest =-24h | head 5

Sort within a single event type:

FROM dnsquery WHERE query_name MATCHES '*.sophos.com' earliest =-30d | sort query_name DESC

Sort within multiple event types (time fields only):

FROM auth, process earliest =-1d | sort event_time_usec ASC

Sort with time truncation:

FROM netflow WHERE source_address = '10.0.0.1' earliest =-1d@d | sort source_address ASC

Count auth events grouped by user, domain, and hostname with case normalization:
```
FROM auth | count by tolower(user_name), domain tolower(hostname)
```
Count auth events grouped by domain with uppercase normalization:
```
FROM auth | count by toupper(domain)
```

Select fields (API only):

FROM dnsquery WHERE @domain MATCHES '*.sophos.com' earliest =-30d | fields query_name

Aggregations

aggregate allows users to group the results of their query and perform listed operations on those results:

An aggregate query can take the following form:

<search> | aggregate <func>(<field>)(, <func>(<field>))* [by <field>(, <field>)* | by <int><unit>]

Operator	Description
`sum`	Calculate the sum of a field for every row returned by the query.
`min`	Find the smallest value of a field.
`max`	Find the largest value of a field.
`avg`	Find the average value of a field for every row returned by the query.
`count`	Count the number of rows which have a field. If no field is specified, all rows are counted.
`cardinality`	Count the number of rows which have a distinct, non-null value for a field.
`(aggregate) by`	Group or aggregate the results by the values of the field specified and show a count for each value.

by

The by clause can either specify an optional field list or a time duration where the results will be grouped by the specified field(s) or time unit:

<search> | aggregate <aggregation>(, <aggregation>)? by <field>

<search> | aggregate <aggregation>(, <aggregation>)? by <int> <unit>

where unit is:

s — second
m — minute
h — hour
d — day

Example

FROM process WHERE commandline CONTAINS 'powershell' earliest =-24h | aggregate count(username) BY username

FROM auth WHERE source_user_name = 'bob' earliest =-3d | aggregate min(event_time_usec), max(event_time_usec)

FROM dnsquery earliest =-1d | aggregate count BY 1h

Warning

Aggregation is supported for event queries only. In multi-event queries, aggregation runs per event type. Aggregating on logical types is not supported.

Example

Common query patterns

Single schema by field:

FROM process WHERE image_path CONTAINS 'powershell.exe' earliest=-24h

Multiple schemas with a common field:

FROM process, auth WHERE sensor_type = 'ENDPOINT_SOPHOS' earliest=-1h

No FROM (schema-agnostic via logical types)
```
@user CONTAINS 'system'
```

Detection by detection field:

FROM detection WHERE title CONTAINS 'Powershell' earliest=-7d

Detection by related event field:

FROM detection WHERE process.commandline CONTAINS 'powershell.exe' earliest=-7d

Logical type within a schema:

FROM netflow WHERE @ip = '10.0.0.1' earliest=-2h