Edit or create queries
You can edit a pre-prepared Live Discover query or create your own query.
The query is written in osquery, which uses basic Structured Query Language (SQL) commands. You must be familiar with osquery or SQL to edit the query.
For help with osquery, see osquery schema.
You also need to check the Sophos schema for the data sources you want to include in your query, for example Sophos email data or Sophos Cloud Optix data. See Data Lake schema.
To edit or create a query, do as follows:
- Go to Threat Analysis Center and click Live Discover.
In Live Discover, turn on Designer Mode (if it isn't already on). This lets you edit or create queries.
In the Query section, do one of the following:
- To edit a query, go into a category and select the query you want. Then click Edit.
- To create a query, click Create new query.
In the editing screen, build your query as described in the steps that follow. The steps are the same whether you're editing or creating a query.
- Enter a name, category and description for the query.
Select a source to query:
- Data Lake. This gives results for endpoint data in the Data Lake, and data from other Sophos products you have set up to send data to the Data Lake, for example Sophos Cloud Optix or Sophos Email.
- Live Endpoint. This only gives results for endpoints that are connected. If you selected Live Endpoint, select the operating systems to include.
In the SQL box, enter the new query or enter the changes that you want to make to the existing query.
A query must contain at least 15 characters to run on the selected devices.
For information about the tables and data available, see osquery schema.
You can add a variable to the query and assign a value to it. You can then use the value, for example in a conditional statement. To do this, do as follows:
- Expand the variable editor.
- Click + Add variable.
Enter a name for the variable.
You can include spaces in the name but not dollar symbols.
Specify the variable type and the value that you want to use when the query runs.
- In the SQL box, enter the SQL variable name, including the dollar symbols, where you want to use the variable.
For example, if you enter
File pathfor the variable name, SQL variable name becomes
$$File path$$in the SQL box:
SELECT * FROM processes WHERE filepath = $$File path$$
If you're setting up a Live Endpoint query, open Device selector and select the devices to query.
You don't need to select devices for a Data Lake query. All devices are included automatically.
Optional: If you're setting up a Data Lake query, click the arrow to open Select a Time Period and select the period to query.
This option isn't a schedule. It specifies how much past data the query runs on, not how often it runs.
Click Save. The query is saved to the category that you specified.