Using queries

Queries represent the cornerstone concept in the Endpoint Agent solution. In a nutshell, queries are the method by which information is extracted from the endpoints for value generation. The Endpoint Agent solution, which is ultimately implemented as a wrapper of Osquery, utilizes this method to retrieve a wide range of highly heterogeneous data sets exposed as database tables utilizing a common, structured way.

Queries in the Osquery solution follow an SQL-like syntax that provides the familiarity to perform any data extraction process in an easy way, but also leverages the capabilities of the language for advanced operations such as table joins.

Please refer to the official Osquery documentation for more details on queries as well as the exposed data schemas provided by default with the solution.

The Queries section

Queries are essentially defined as an SQL command that, upon execution, will return a table-based data structure containing its results. 

There is no limitation in terms of the number of queries supported by an Endpoint Agent installation. Furthermore, queries can be written and executed "on the fly" for investigation purposes. It is also important to note that individual queries will not be executed regularly, nor will their results be uploaded to Devo, unless they are included as part of a pack. As such, the queries section in the EA Manager is used for:

• On-demand queries execution to assist in investigations, forensic analysis, etc.

• Queries definition and refinement prior to their inclusion in a pack.

• Packs definition.

To access the queries management section within the Endpoint Agent Manager, click on the queries button in the left-hand side panel of the UI. The following screen will be displayed:

Open

The complete list of options and functionalities provided to the user is detailed as follows:

• Queries list (1): Provides the full list of queries defined in the EA Manager, organized in terms of name, description, author and last modification date. Clicking on any row in the list provides extra visibility on the implementation details of the selected queries, displayed on the right-hand side pane of the UI as described in the following bullet point. Furthermore, use the Filter queries component in the upper part of the section to filter out the list using the query name field.

• Query description (2): Detailed information of individual queries includes:
  a. Query name: textual identifier and short description.
  b. SQL: syntax in SQL language of the query.
  c. Description: textual information for the query.
  d. Packs: list of queries packs the selected query belongs to. Note the listed packs are provided as hyperlinks, which allows users to access directly the definition of the pack under the Packs section.

• Create new query (3): Clicking this button opens up the queries editing interface that assists users in the creation of a new query.

• Edit or run query (4): Clicking this button opens the editing interface for edition and/or execution of a particular query. As a shortcut function, it is possible to go directly to the query editor / running interface by double-clicking on any query row in the list.

Creating or editing queries

As described before, either by clicking on the Create new query or the Edit or run query buttons in the previous interface will lead the user to a new section in which queries can be created, tested and saved for later use. 

The creation/editing interface looks as follows:

• Query definition and description fields (1): Queries are defined as SQL commands, utilizing Osquery’s exposed schema to retrieve different sets of data from the endpoint. Please refer to the official Osquery documentation for a reference of the different options and commands available for queries definition.

There are multiple helpers available in the SQL input to assist in the creation of new queries:

- Use the documentation element (3) in the right-hand side panel of the UI for quick access to the definition of the different tables included in the Osquery schema. To do so, simply click on the down-pointing arrow of the selector and select the table you are interested in getting additional information from. Alternatively, you can search by table names by clicking on the text in the same component and typing its identifier.

- Text inputs in the SQL input are predictive, supporting table names, table fields, operations and filtering clauses. Matching values will automatically be listed as you start typing and editing the SQL command:

Open

Use the query title and query description to provide a textual identifier, as well as a long description for the newly created query. It it is not necessary to assign these values to edit and execute a query, only to save them for future usage or refinement.

• Query execution section (2): This section allows for the execution of the query under definition or editing, as well as the visualization of the returned results. With this function, it is possible to cover both the testing and refinement of new queries that can be potentially incorporated into packs for automatic execution and the execution of on-demand queries for ad-hoc analysis (e.g., threats detection). To run the query, select the targeted endpoints in the fleet and click the run button.

Selection of the targets supports multiple methods: manual (by introducing the IP address, hostname, etc.) in the input box, or selecting groups of endpoints defined by by label or operating system.

• Documentation (3): As explained above, this section provides a reference guide for the entire Osquery data schema. There are two important sets of information provided per table in the schema:
  a. OS Availability: list of supported operating systems for the selected table.
  b. Columns: full description of the selected table, including the list of all returned value columns with their type.

Execution results

Once a valid query is introduced and a list of targets defined, click on the Run button to initiate the execution of the query. After some seconds, in which the Endpoint Agent Manager contacts the list of targeted endpoints to execute the query and gathers all individual results, the following elements will be displayed on screen:

• Execution summary (1): this information element is updated in realtime with the progress of the query execution, including a notification of the execution elapsed time. Once all results are received, a summary of the query execution is provided in terms of the number of endpoints that successfully provided a valid response to the request out of the total list of registered ones. Note that the total number includes those endpoints that might be in offline status or MIA.

• Query results (2): the result of any query run will always come in the form of a table, where the columns and rows composition will depend of the query itself. Only valid results will be displayed per execution, meaning that the result of a query might be empty or the addition of multiple individual contributions, one per each answering endpoint.

• Exporting button (3): clicking on this button will generate a .csv (comma separated values) file that will be downloaded immediately, and which will contain the results of the query in that format.

• Full screen mode button (4): click on this icon to toggle between full screen mode for the results section.

• Column names / filtering bar (5): the first row in the table displays the name for each column in the table. It is possible to introduce values in the fields to filter out the results of a query to those registries that match the introduced text in the column.

Error results

If a query results in an error, a new section appears describing the cause of the error. In the example below, we ran a windows_events query against a Linux server. Since the table does not exist in Linux servers, it returns the below error.