Transitioning to Advanced Search query language

If you're familiar with SQL queries in Sophos Central Live Discover, this guide helps you translate common query patterns to the Data Lake Search Advanced Search query language in Sophos XDR.

Overview of changes

The following table summarizes what has changed between Sophos Central Live Discover and Sophos XDR Advanced Search query language.

Navigating Data Lake Search

In Sophos XDR, Data Lake Search is the primary interface for querying detections and events in your tenant. To access it, go to Security Operations > Data Lake Search.

Data Lake Search offers three main search modes: Query Editor, Query Builder, and AI Search. It also includes a Schema Library and Data Exports.

Query Editor

With Query Editor, write advanced, flexible queries using a custom syntax. For full documentation, see Advanced Search query language.

Query Editor features the following capabilities:

• Operators for string matching, regex, wildcards, and CIDR notation.
• Inline help, schema library, and query examples.
• Save and add searches to cases.
• Column customization and result filtering in the UI.
• Support for logical types (like @ip, @user, and @host) to simplify cross-schema searches. For more information, see Logical types.

Query Builder

Query Builder offers a visual, guided interface for constructing queries without writing code. For full documentation, see Query Builder.

Query Builder features the following capabilities:

• Select detections or events and choose event types, such as process, auth, and netflow.
• Add multiple criteria and use AND/OR logic.
• Support for nested queries and logical types.
• Save and add searches to cases.

AI Search

Use natural language to generate queries with AI Search, making it easier to retrieve relevant data without knowing the query syntax. For full documentation, see AI Search.

Schema Library

Schema Library provides a tabular reference of searchable schemas, fields, and logical types in Sophos XDR. For full documentation, see Schema Library.

Data Exports

Click the Actions menu above a query language results table to export the full search results or a selected subset. You can check the status of the export and download the file on the Data Exports page. For more information, see Data Exports.

Differences between SQL and query language

SQL and the Advanced Search query language may look similar because they use familiar elements, such as FROM, WHERE, and quoted strings. However, the underlying models are very different. Advanced Search query language uses a filter over a fixed, normalized event schema rather than a general-purpose relational query engine.

This table shows SQL concepts you are already familiar with and the query language equivalent.

Logical types

Logical types are special fields that map to field names under the appropriate data schemas for a given field category, so you don't have to remember and specify each individual field name for every schema. They're denoted with the @ prefix.

The table below summarizes the field mappings of each logical type. For full field mappings, see Logical types.

Example queries using logical types

FROM netflow, http WHERE @ip = '203.0.113.44' earliest=-7d | aggregate count BY destination_port

@user CONTAINS 'admin' AND @domain MATCHES '*.ru' earliest=-24h

FROM process WHERE @hash = 'd41d8cd98f00b204e9800998ecf8427e' AND @path CONTAINS 'temp' earliest=-30d | sort event_time_usec DESC | head 25

Special case: hostname

hostname behaves differently from all other logical types in Advanced Search query language in the following ways:

• It silently translates to the underlying host_id field.
• It supports only literal operators, such as =, !=, IN, !IN. It doesn't support CONTAINS, MATCHES, or MATCHES_REGEX.
• Hostname matching is case sensitive, unlike query language's usual case-insensitive default.

FROM auth WHERE hostname = 'Demo-PC01' earliest=-24h

Scenarios and example queries

Each of the following scenarios walks you through the original SQL you might have used, how compatible it is with Advanced Search query language, the best query language equivalent, what still has to happen on the client side, and a verification checklist.

Scenario 1: Log4j process hunt

The original SQL

SELECT meta_hostname AS ep_name, name, cmdline, path, query_name, sophos_pid, pid
FROM xdr_data
WHERE query_name IN ('running_processes_linux_events', 'running_processes_osx_events', 'running_processes_windows_sophos')
AND LOWER(cmdline) LIKE '%log4j%'

Partially compatible with query language

This SQL query can be translated to Advanced Search query language, but some functionality requires modification or can't be reproduced exactly.

The filter logic in this SQL query is fully expressible because the cmdline CONTAINS 'log4j' condition maps cleanly. However, the following caveats apply:

• Caveat A: The three query_name values (running_processes_linux_events, running_processes_osx_events, and running_processes_windows_sophos) aren't part of any documented query_name to FROM <schema> mapping. Mapping all three to FROM process is a reasonable inference because they're OS-specific "list running processes" snapshot sources, but this mapping isn't confirmed. Verify it against the live Sophos XDR event schema before relying on it.
• Caveat B: Query language doesn't support column selection or aliasing such as AS ep_name. Column selection occurs on the client side after retrieving the full result set, so this is expected behavior rather than a compatibility gap.

Best query language equivalent

FROM process WHERE commandline CONTAINS 'log4j' earliest=-30d

Note the following details about this query:

• CONTAINS is a case-insensitive substring match, so the original LOWER(cmdline) LIKE '%log4j%' collapses to commandline CONTAINS 'log4j' with no LOWER() needed.
• Instead of the former cmdline name in SQL, the query language field name commandline is the canonical field name across process, auth, filemod, apicall, and threadinjection schemas. It's also reachable via the @command logical type using commandline.
• The three query_name IN (...) values are dropped from WHERE and folded into FROM process, due to query_name selecting the event source, not a content filter. See Caveat A for more information.
• The original query has no time bound applied. In query language, there's no default time window when neither earliest nor latest is given — an explicit window (such as earliest=-30d in the example) must be chosen deliberately, not assumed.

Client-side requirements

• Column projection and renaming (such as meta_hostname AS ep_name, name, path, query_name, sophos_pid, and pid). This must be done after fetching results.
• If the "collapse three query_names into FROM process" inference from Caveat A turns out wrong, the OS-specific split may need to be reconstructed client-side by filtering the results on an OS or platform field.

Verify the query

Follow these steps to verify that the new query is correctly prepared:

1. Confirm running_processes_{linux,osx,windows_sophos} all correspond to Sophos XDR's process event type and verify against live schema.
2. Confirm commandline is the correct field name (not cmdline) for this event type.
3. Choose and apply an explicit earliest or latest window, as the original has none.
4. Confirm case-insensitive CONTAINS matches original LOWER(...) LIKE '%...%' semantics.
5. Reconstruct the seven selected or aliased columns client-side from returned rows.
6. Paginate through results if the result count is large.

Scenario 2: Malware/PUA score triage

Original SQL

SELECT ARRAY_JOIN(ARRAY_AGG(DISTINCT meta_hostname), CHR(10)) AS ep_list,
       COUNT(DISTINCT meta_hostname) AS ep_count,
       DATE_FORMAT(FROM_UNIXTIME(MIN(time)), '%Y-%m-%dT%H:%i:%SZ') AS first_seen,
       DATE_FORMAT(FROM_UNIXTIME(MAX(time)), '%Y-%m-%dT%H:%i:%SZ') AS last_seen,
       sha256, path,
       CASE WHEN ml_score >= 30 THEN 'Malicious : ' || CAST(ml_score AS varchar)
            WHEN ml_score >= 27 THEN 'High suspicion : ' || CAST(ml_score AS varchar)
            WHEN ml_score >= 24 THEN 'Medium suspicion : ' || CAST(ml_score AS varchar)
            WHEN ml_score >= 20 THEN 'Low suspicion : ' || CAST(ml_score AS varchar)
            ELSE '' END AS ml_suspicion,
       CASE WHEN pua_score > 20 THEN 'Suspect PUA : ' || CAST(pua_score AS varchar) ELSE '' END AS pua_suspicion,
       local_rep, global_rep,
       ARRAY_JOIN(ARRAY_AGG(DISTINCT sophos_pid), CHR(10)) AS sophos_pid_list,
       ARRAY_JOIN(ARRAY_AGG(DISTINCT parent_sophos_pid), CHR(10)) AS parent_sophos_pid_list
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos' AND (ml_score >= 20 OR pua_score > 20) AND (local_rep < 91)
GROUP BY sha256, path, local_rep, global_rep, ml_score, pua_score
ORDER BY ml_score DESC, last_seen DESC

Partially compatible with query language

This SQL query can be translated to Advanced Search query language, but some functionality requires modification or can't be reproduced exactly.

The row-level WHERE filter (ml_score >= 20 OR pua_score > 20, local_rep < 91) is fully expressible, but the following caveats apply:

• Caveat A: running_processes_windows_sophos isn't in any documented mapping. Inferring FROM process for a single-OS variant is more defensible than the three-way collapse in Scenario 1, but it's still unconfirmed. For more details about this caveat, see Scenario 1: Log4j process hunt.
• Caveat B: Everything after the WHERE — GROUP BY (both ARRAY_AGG/ARRAY_JOIN host/pid roll-ups, MIN/MAX(time), DATE_FORMAT(FROM_UNIXTIME(...)), both CASE expressions, and ORDER BY on derived or aggregated columns) has no direct equivalent.

Best query language equivalent

FROM process WHERE (sophos_endpoint_process_info.malware_score >= 20 OR sophos_endpoint_process_info.pua_score > 20) AND sophos_endpoint_process_info.local_reputation < 91 earliest=-30d | aggregate count BY program_hash.sha256

Note the following details about this query:

• >=, >, <, OR, and parentheses all map directly.
• | aggregate count BY program_hash.sha256 gives the count-per-hash grouping and is the only piece of the original GROUP BY that has a direct equivalent.
• The original query has no time bound applied. In query language, there's no default time window when neither earliest nor latest is given — an explicit window (such as earliest=-30d in the example) must be chosen deliberately, not assumed.

Client-side requirements

• The host/pid roll-ups (ARRAY_AGG(DISTINCT meta_hostname), sophos_pid, parent_sophos_pid, ARRAY_JOIN(..., CHR(10))) need per-group unique-value collection and a newline join.
• COUNT(DISTINCT meta_hostname) needs a client-side count of the unique-hostname set per group (which is distinct from the plain row-count the | aggregate count pipe gives).
• MIN(time)/MAX(time), FROM_UNIXTIME, and DATE_FORMAT(..., '%Y-%m-%dT%H:%i:%SZ') require epoch > ISO 8601 formatting on the client side. Note that min/max aggregates do exist in query language, but they're not combinable with count BY in the same aggregate pipe. Only that combination, not the aggregate's availability, is the actual gap. For more details, see Differences between SQL and query language.
• No conditional-labeling pipe command exists for either CASE expression (ml_suspicion four-tier label and pua_suspicion label). Reimplement as plain if/elif per row or group after fetch.

Verify the query

Follow these steps to verify that the new query is correctly prepared:

1. Confirm running_processes_windows_sophos > FROM process mapping against the live schema.
2. Choose an explicit earliest/latest window.
3. Reconcile the informal field names used above (ml_score, pua_score, local_rep, sha256) with the corrected, namespaced field names in the "Best QL equivalent" query (sophos_endpoint_process_info.*, program_hash.sha256).
4. Reimplement both CASE label ladders exactly (score-threshold boundaries: 20/24/27/30 for ml_suspicion, 20 for pua_suspicion) client-side.
5. Reimplement the multi-field ARRAY_AGG/ARRAY_JOIN roll-ups client-side, preserving DISTINCT.
6. Confirm whether the final sort (ml_score DESC, last_seen DESC) can be pushed server-side via a second | sort pipe on the derived count column, rather than assuming client-side sort is required.

Scenario 3: CALDERA/MITRE lookup table hunt

Original SQL

-- Hunt for CALDERA IOCS
-- VARIABLE $$Any Field String Match$$ / $$Device Name$$ / $$MITRE ATT&CK ID$$ / $$Tactic$$ / $$Technique$$ STRING
WITH Detection_Counts AS (
  WITH Detections AS (
    WITH Mitre_map (ID, Tactic, Technique, SubTechnique, Mitre_Link, Method, Condition, SubCondition, Refrence) AS (
      SELECT 'T1002','exfiltration','Compress staged directory','Data Compressed',
             'https://attack.mitre.org/techniques/T1002','Process_Cmd','%powershell%','%Compress-Archive%',
             'Compress a directory on the file system'
      UNION ALL
      SELECT 'T1003','Process Memory-access','Leverage Procdump','Memory Dump',
             'https://attack.mitre.org/techniques/T1003','Process_Cmd','%powershell%','%procdump%',
             'Dump a processes memory'
      -- ... ~150 total UNION ALL SELECT rows mapping MITRE technique IDs to
      -- Process_Cmd LIKE-pattern conditions (name/cmdline patterns), omitted here for brevity ...
    )
    SELECT xdr_data.meta_hostname Device_Name, xdr_data.meta_os_type Device_Type, xdr_data.unix_time Date_Time,
           map.ID Mitre_ID, map.Tactic, map.Technique, map.SubTechnique,
           map.condition || ' + ' || map.subcondition Hunt_Rule, map.Mitre_Link, map.Refrence,
           xdr_data.username User_Name, xdr_data.name Process_Name, xdr_data.cmdline Cmd_Line,
           xdr_data.sophos_PID SophosPID, xdr_data.path Path_Name
    FROM xdr_data
    JOIN Mitre_map map
      ON LOWER(xdr_data.name) LIKE LOWER(map.condition) AND LOWER(xdr_data.cmdline) LIKE LOWER(map.SubCondition)
    WHERE LOWER(xdr_data.meta_hostname) LIKE LOWER('%$$Device Name$$%')
      AND LOWER(map.Tactic) LIKE LOWER('%$$Tactic$$%')
      AND LOWER(map.Technique) LIKE LOWER('%$$Technique$$%')
      AND LOWER(map.ID) LIKE LOWER('%$$MITRE ATT&CK ID$$%')
      AND map.Method = 'Process_Cmd'
  )
  SELECT Mitre_ID, Tactic, Technique, COUNT(Device_Name) Instances, Process_Name, Cmd_Line,
         array_join(array_agg(DISTINCT User_Name), CHR(10)) User_List, Hunt_Rule,
         array_join(array_agg(DISTINCT Device_Name), CHR(10)) Device_LIST,
         array_join(array_agg(DISTINCT Device_Type), CHR(10)) Device_Type,
         MIN(Date_Time) First_Seen, MAX(Date_Time) LAST_Seen, SubTechnique,
         array_join(array_agg(DISTINCT SophosPID), CHR(10)) SophosPID_LIST,
         array_join(array_agg(DISTINCT Path_Name), CHR(10)) Path_Name_LIST, MITRE_Link, Refrence
  FROM Detections
  GROUP BY Mitre_ID, Tactic, Technique, Process_Name, Cmd_Line, Hunt_Rule, SubTechnique, User_Name, MITRE_Link, Refrence
)
SELECT * FROM Detection_Counts WHERE LOWER(...) LIKE '%$$Any Field String Match$$%' ORDER BY Instances DESC

Not compatible with query language

The original SQL doesn't reduce to Advanced Search query language in any useful sense for the following reasons:

• No safe FROM inference exists. Unlike Scenarios 1 and 2, this query doesn't restrict xdr_data by any query_name at all — it implicitly assumes a process-execution-shaped row (it has name, cmdline, sophos_PID, path, username) but gives no basis to pick a single Sophos XDR event type. Guessing FROM process here would be much less certain than in Scenarios 1 and 2, since the original author deliberately left it unscoped.
• The ~150-row literal Mitre_map lookup table has no representation. Query language is a live-query filter language against event data. It has no construct for an inline literal-value virtual table (no UNION ALL of literal rows, no WITH).
• The JOIN ... ON LOWER(name) LIKE LOWER(map.condition) AND LOWER(cmdline) LIKE LOWER(map.subcondition) pattern-match join has no equivalent. In principle, since query language supports AND, OR, and parentheses, one could try to flatten all ~150 lookup rows into one giant "OR-of-AND" pairs boolean expression ((name CONTAINS 'x1' AND commandline CONTAINS 'y1') OR (name CONTAINS 'x2' AND commandline CONTAINS 'y2') OR ...). This isn't recommended and not attempted here. There's no documented limit on WHERE clause size or complexity, so whether the platform would even accept a 150-clause expression is unverified, and it would hardcode the entire MITRE lookup table into the query string rather than treating it as data, which would be brittle and against the spirit of the migration. The recommended approach is the hybrid one: a broad fetch plus client-side matching.
• All of GROUP BY, COUNT, both ARRAY_AGG/array_join roll-ups, MIN/MAX(Date_Time), and the final ORDER BY Instances DESC are not expressible. See Scenario 2: Malware/PUA score triage for more details.
• The $$Tactic$$/$$Technique$$/$$MITRE ATT&CK ID$$ bind variables filter the lookup table, not event fields. They can never become query language WHERE clauses. Instead, they filter the client-side lookup structure before or after the match step.

Best query language equivalent

FROM process earliest=-7d

This is a broad, best-guess, time-bounded fetch of process events. The FROM process choice itself is an unconfirmed inference. Nothing more from the original SQL is expressible.

Client-side requirements

• Load the ~150-row Mitre_map table as a plain data structure (a list of records, or a small CSV or JSON file shipped alongside the query) instead of a SQL literal table.
• Translate each row's SQL LIKE pattern (%powershell%, %procdump%, etc.) into a substring/glob/regex check, and for each fetched event, check name/cmdline against condition/subcondition for every lookup row. This reimplements the JOIN ... ON LIKE as a nested loop, or as a vectorized merge if performance matters.
• Reimplement Device Name/Tactic/Technique/MITRE ATT&CK ID bind-variable filtering against the lookup table and fetched device metadata on the client side.
• Reimplement GROUP BY, COUNT , all ARRAY_AGG/array_join roll-ups (users, devices, device types, PIDs, paths), and MIN/MAX(Date_Time) on the client side.
• Reimplement the final Any Field String Match free-text filter and ORDER BY Instances DESC on the client-side aggregated result.

Verify the query

Follow these steps to verify that the new query is correctly prepared:

1. Determine the correct Sophos XDR event type for unscoped process-execution rows before doing anything else. There's no safe inference available here, unlike Scenarios 1 and 2.
2. Port the full ~150-row MITRE technique lookup table faithfully into a client-side data structure. Verify that none of the 150 rows is lost or altered in translation.
3. Convert every SQL LIKE pattern in the lookup table to correct matching semantics (convert % to substring wildcard).
4. Confirm performance of a client-side nested match (rows x 150 patterns) is acceptable, or vectorize.
5. Reimplement all aggregation and roll-up columns, and confirm that the output shape matches what downstream consumers of this hunt query expect.
6. Confirm with a security or detection engineering stakeholder whether this hunt logic should instead become a native Sophos XDR detector or alert rule, rather than an ad-hoc query at all.

Verification checklist

As the person hand-writing and running a query, walk through this checklist before you trust the results:

• Schemas are chosen correctly. You're querying the schema(s) that actually contain the fields you need, documented in the Schema Library.
• Time window is respected. Event queries are ≤ 31 days. If your old query spanned longer, split it into multiple runs.
• LIKE to CONTAINS/MATCHES/MATCHES_REGEX is chosen deliberately. These functions aren't interchangeable. CONTAINS is a substring, MATCHES is a case-sensitive glob, and MATCHES_REGEX is regex. The dialect differs between event and detection.
• No join is assumed. If your old query correlated two tables, confirm you actually need a union of independent results (which is acceptable), as opposed to a true correlation (which isn't possible as it needs client-side joining after fetching both schemas separately).
• Aggregation is checked against the real pipe syntax (| aggregate ... BY ...). Don't assume aggregation must be done on the client-side. Refer to Differences between SQL and query language.
• String-list building and CASE labeling are routed on the client side. These functions have no query language equivalent.
• hostname is treated as a special case. hostname is the one field that's case sensitive and only supports =/!=/IN/!IN. See Special case: hostname.
• Reserved words avoided as bare field or value names. Quote reserved words as string literals if needed.
• Detections and events not mixed into one query. Always run detections and events into separate queries.

Common pitfalls

Avoid the following pitfalls and misconceptions:

• Query language isn't SQL. Don't assume operator or function names map directly just because the surrounding syntax looks similar.
• hostname is a special case. It silently translates to the underlying host_id field, is case sensitive, and only supports literal operators (no CONTAINS/MATCHES).
• IPv6 literal comparisons are string comparisons. For example, 2001:db8::1 and its fully-expanded form won't match each other with =/CONTAINS/MATCHES. Use CIDR notation (such as /128 for a single address) to match reliably across representations.
• Regex dialect depends on what you're querying. The system uses Java regex for events, Lucene regex for detections. A pattern that works on one may not work on the other.
• @raw and original_data are limited to the last 20 days. If your overall query's time range exceeds that window, the query is rejected outright, not silently modified or truncated.
• Query length limits are rare but exist. The system automatically expands logical types and special characters. A short-looking query can still hit a length limit at execution, though this is uncommon.
• Aggregation is event-only. Aggregation isn't supported on detection queries or logical types (@user, etc.). It only applies to concrete schema fields.
• The UI caps displayed results at 10,000 rows. This is separate from the 100,000-row-per-event-type export cap referenced in Navigating Data Lake Search. If you need to see more than 10,000 rows directly in the results table, narrow your query or use export.
• The source data you're migrating from may itself be mislabeled. Don't assume every query you're reorienting is the SQL dialect its label claims.
• Field names differ. Sophos Central and Sophos XDR use different field naming conventions. Always cross-reference the CQL logical types and event schemas.
• Severity scales differ. Sophos Central detections use a 1-10 integer scale. Sophos XDR alerts use a 0.0-1.0 float plus an enum (CRITICAL/HIGH/MEDIUM/LOW/INFO).

Additional examples for reference

Below are some concrete query language examples for quick reference.

FROM process EARLIEST=-24h
FROM process WHERE image_path CONTAINS 'powershell.exe' EARLIEST=-24h
FROM process, auth EARLIEST=-12h

SELECT * FROM xdr_data
WHERE query_name = 'sophos_process_activity'
AND name LIKE '%powershell%'
AND meta_hostname = 'WORKSTATION01'
AND time > '2024-01-01T00:00:00.000Z'

# KEEP: existing OAuth token acquisition
token = get_sophos_token(client_id, client_secret)

# NEW: discover Taegis environment
whoami = requests.get(
    "https://api.central.sophos.com/whoami/v1",
    headers={"Authorization": f"Bearer {token}"},
)
taegis_url = whoami.json()["apiHosts"]["taegis"]

# NEW: initialize SDK with discovered URL and existing token
from taegis_sdk_python import GraphQLService

service = GraphQLService()
with service(url=taegis_url, access_token=token):
    # ... Taegis SDK calls here
    pass