Using queries

Introduction

Queries represent the cornerstone concept in the Endpoint Agent solution. In a nutshell, queries are the method by means of 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 retrieve a wide range of highly heterogeneous data sets exposed as database tables utilizing a common, structured way.

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

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.

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, and so forth.

• Query 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 are:

• 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 the list using the query name field.

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

Clicking on a query opens its query details view, displaying details and allowing you to edit them as follows:

Open

• Query description (1): Detailed information of individual queries include:

  • Query name: Textual identifier and short description.

  • SQL: Syntax in SQL language of the query.

  • Description: Textual information for the query.

• Tables (2): This panel can be utilized as a reference to review the different tables existing in the query data schema, as well as all columns included in each table.

• Save or Save as new (3): Clicking on the Save button will let you save changes made in the query. Save as new will save the query changes as a new query.

• Run query (4).

Creating or editing queries

As described before, clicking on the Create new query button in the previous interface will lead the user to a new section in which queries can be created, tested, and saved for later usage.

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. 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 Tables element (2) 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 want to get mroe information on. 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:
  
  

• Tables (2): As explained above, this section provides a reference guide for the entire Osquery data schema. There are two important pieces of information provided per table in the schema:

  • OS Availability: List of supported operating systems for the selected table.

  • 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, click on the Run Query button. A list of targets will be displayed and you can define on which host the query is executed. Click 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’s 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 composition of the columns and rows depends on 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 endpoint that responds.

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

• Column names / filtering bar (4): 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 macOs server. Since the table does not exist in macOS servers, it returns the below error.