Skip to content

Getting started

Overview

This guide takes you through a few simple steps to start using the new XDR Query API in Sophos Fusion.

Read the announcement about Sophos XDR here: https://community.sophos.com/intercept-x-endpoint/b/blog/posts/sophos-xdr-and-edr-4-0-now-available

All our APIs are offered as RESTful HTTP endpoints over the public internet. We use standard authentication, JSON requests and responses and standard HTTP verbs. All communication is over HTTPS.

This guide tells you how to run a query against the Sophos Data Lake in Sophos Fusion, monitor its status, and retrieve results when the query run has completed.

Pre-requisites

You must have a set of API credentials (service principal) to be able to call the XDR Query API. For more information refer to the appropriate quick start guide:

  • Sophos Partners: Read the Partner Getting Started guide first.
  • Enterprise customers: If you use Sophos Enterprise to manage multiple tenants, read the Organization Getting Started guide first.
  • Other customers: Read the Tenant Getting Started guide first.

You can make the API calls in the next few sections using cURL. Follow the instructions on cURL's website to install this tool.

Each cUrl command in the rest of this guide assumes you will replace the following variables:

  • <tenant-id>: The ID of the tenant you want to query
  • <jwt>: The JWT access token returned when the IDP authenticate the service principal.
  • <data-region>: The regional API host in the data geography where the tenant data is located.

To understand these terms you must read the appropriate Getting Started guide.

Step 1 - Run a query

First create a file named query.sql containing the query text:

-- running_processes_windows_sophos
SELECT
   -- Device ID DETAILS
   meta_hostname, meta_ip_address,
   -- Query Details
   query_name, cmdline, file_size, gid, global_rep,
   global_rep_data, local_rep, local_rep_data, ml_score, ml_score_data,
   name, parent, parent_name, parent_path, parent_sophos_pid,
   path, pid, pua_score, sha1, sha256,
   sophos_pid, time, uid, username,
   -- Decoration
   meta_boot_time, meta_eid, meta_endpoint_type,
   meta_ip_mask, meta_mac_address, meta_os_name, meta_os_platform, meta_os_type,
   meta_os_version, meta_public_ip, meta_query_pack_version, meta_username,
   --- Generic
   calendar_time, counter, epoch, host_identifier, numerics
   osquery_action, unix_time,
   -- Data Lake
   customer_id, endpoint_id, upload_size
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'

When you are familiar with our data schema you can write your own queries.

To run this query, you need to send a JSON request to the Sophos Data Lake Query API. The JSON standard requires newlines (\n and \r) and double-quotes (") to be escaped in field values. The request must look like this:

{
    "adHocQuery": {
        "template": "<... escaped SQL query here ...>"
    }
}

In python, you can read the SQL from "query.sql" and create the JSON request in a file named "request.json" using this command:

$ python -c 'import json; print(json.dumps(dict(adHocQuery=dict(template=open("query.sql").read().strip())),indent=2))' > request.json

The command above has been tested on macOS and will probably work on Linux without modification. If you are on Windows or don't have access to python or another programming language, you can copy and paste the following request JSON below into a file named 'request.json'

{
  "adHocQuery": {
      "template": "-- running_processes_windows_sophos\nSELECT\n   -- Device ID DETAILS\n   meta_hostname, meta_ip_address,\n   -- Query Details\n   query_name, cmdline, file_size, gid, global_rep,\n   global_rep_data, local_rep, local_rep_data, ml_score, ml_score_data,\n   name, parent, parent_name, parent_path, parent_sophos_pid,\n   path, pid, pua_score, sha1, sha256,\n   sophos_pid, time, uid, username,\n   -- Decoration\n   meta_boot_time, meta_eid, meta_endpoint_type,\n   meta_ip_mask, meta_mac_address, meta_os_name, meta_os_platform, meta_os_type,\n   meta_os_version, meta_public_ip, meta_query_pack_version, meta_username,\n   --- Generic\n   calendar_time, counter, epoch, host_identifier, numerics\n   osquery_action, unix_time,\n   -- Data Lake\n   customer_id, endpoint_id, upload_size\nFROM xdr_data\nWHERE query_name = 'running_processes_windows_sophos'"
    }
}

Now run this query:

curl -XPOST -H 'Content-Type: application/json' \
            -H 'Authorization: Bearer <jwt>' \
            -H 'X-Tenant-ID: <tenant-id>' \
            --data @request.json  \
                   <data-region>/xdr-query/v1/queries/runs

The file request.json must be in the local directory for this to work. A successful response looks like this:

{
    "id": "bb0cbb9b-a189-495b-9cc9-7027df7110d6",
    "createdAt": "2020-11-02T12:51:09.933Z",
    "expiresAt": "2020-11-02T14:51:09.933Z",
    "result": "notAvailable",
    "status": "pending"
}

Step 2 - Monitor query run status

Run the following command, replacing <run-id> with the id from the response in previous step.

curl -XGET -H 'Content-Type: application/json' \
           -H 'Authorization: Bearer <jwt>' \
           -H 'X-Tenant-ID: <tenant-id>' \
                  <data-region>/xdr-query/v1/queries/runs/<run-id>

Repeat this command until the status changes to finished.

{
    "id": "bb0cbb9b-a189-495b-9cc9-7027df7110d6",
    "createdAt": "2020-11-02T12:51:09.933Z",
    "expiresAt": "2020-11-02T14:51:09.933Z",
    "finishedAt": "2020-11-02T12:51:12.512Z",
    "result": "succeeded",
    "status": "finished"
}

Leave a few seconds between API calls to avoid being rate limited. If you're rate limited your API calls fail with a HTTP 429 response status (Too Many Requests).

Step 3 - Fetch the results

Use this command to fetch the results:

curl -XGET -H 'Content-Type: application/json' \
           -H 'Authorization: Bearer <jwt>' \
           -H 'X-Tenant-ID: <tenant-id>' \
                  <data-region>/xdr-query/v1/queries/runs/:runId/results?maxSize=1000

A successful response looks like this:

{
    "items": [
        {
            "calendar_time": "2020-10-24T10:22:26Z",
            "endpoint_id": "9029242c-5359-490e-98c1-157aee62958c",
            "host_identifier": "Victim5-Win10",
            "ingestion_timestamp": "2020-10-25T02:16:46.73Z",
            "message_identifier": "067a42adcf84f1ec41ee8e4f53b4101ec20d60e0158da42e183db4321165e9c5",
            "query_name": "open_sockets",
            "query_source": "xdr_only",
            "schema_version": "1",
            "upload_size": 948,

            // ... Other fields in the result row
        },
        {
            "calendar_time": "2020-10-23T22:15:03Z",
            "endpoint_id": "d3d54811-0666-404f-8427-ce87e79d9b37",
            "host_identifier": "Victim1-EDR",
            "ingestion_timestamp": "2020-10-23T23:29:43.605Z",
            "message_identifier": "04dcf2936472887af347616bab0318b0b0fb137182fbf407dc21ebf614e79991",
            "query_name": "running_processes_windows_sophos",
            "query_source": "xdr_only",
            "schema_version": "1",
            "upload_size": 2311,

            // ... Other fields in the result row
        },

        // ... Other row objects in the result
    ]
    "metadata": {
        "columns": [
            {
                "name": "calendar_time",
                "type": "timestamp"
            },
            {
                "name": "endpoint_id",
                "type": "string"
            },
            {
                "name": "host_identifier",
                "type": "string"
            },
            {
                "name": "ingestion_timestamp",
                "type": "timestamp"
            },
            {
                "name": "message_identifier",
                "type": "string"
            },
            {
                "name": "query_name",
                "type": "string"
            },
            {
                "name": "query_source",
                "type": "string"
            },
            {
                "name": "schema_version",
                "type": "string"
            },
            {
                "name": "upload_size",
                "type": "bigint"
            },

            // ... Other columns in the metadata
        ]
    }
}

The fields in the response above are sorted alphabetically for illustration only. If you omit maxSize, this API returns up to 500000 results, with a maximum of 2000 items per page. To get more specific results, make the focus of the query narrower. Add the page query parameter to fetch a specific page of results or pageFromKey to fetch the next page. The value of page must be between 1 and 250, inclusive.

Additional resources

Here are some resources to bookmark:

  • There are some things you need to know about error handling, pagination, partial responses and other aspects of calling RESTful APIs that are specific to Sophos APIs. You can read about them here.
  • Read the API reference.
  • Use this postman collection to call the API.
  • Use the data schema reference when writing XDR queries.
  • See our community forum for useful Live Discover and XDR queries.

Conclusion

You can now call the Sophos Data Lake Query API to issue a query, monitor its status, and fetch the results. You can also do other operations such as cancelling a query and fetching the original query request.