Queries

Queries are the means by which Orbital searches for malicious activity on an organization's endpoints. To accomplish this, Orbital uses osquery as its query engine and SQL as its query language. osquery uses the relational data model to represent an endpoint's entire operating system as a high-performance relational database, thereby allowing you to write SQL queries to explore operating systems and device data.

Orbital makes use of osquery’s stock tables in addition to Orbital-specific tables. The results returned through Orbital can be sent to other applications, such as Secure Endpoint™, Secure Malware Analytics™, and Cisco Threat Response™. Additionally, results can be stored in remote data stores (RDS), such as Amazon S3™, Microsoft’s Azure™, and Splunk™.

Queries are built and executed using the Orbital Builder, located on Orbital's Investigate page.

SQL in Orbital

Orbital was developed to support SQL users of every level, from novice to expert; however, you do not need to know how to write SQL statements in order to use Orbital effectively.

SELECT Statement

One of the most common SQL statements is the SELECT statement, which is used to retrieve information stored in a relational database.

The SQL SELECT statement selects data using clauses, such as FROM and WHERE, to specify the desired criteria or parameters to return.

The most basic SELECT statement syntax looks as follows:

SELECT * FROM table_name;

where:

  • The asterisk (*) is used as a wildcard.

  • White space is ignored.

  • SQL statements are not case-sensitive; however, there's an industry standard of putting the commands into all caps.

For example:

SELECT column1, column2 FROM table1, table2 WHERE column2='value';

In this example, column1 and column2 are the field names of the table from which you want to select data from.

To select all fields available in the table, use the following syntax:

SELECT * FROM table1;

Types of Orbital Queries

Orbital provides two types of queries, custom queries and scheduled queries. These query types are defined in the subsections below.

Custom Queries

Note: Custom queries are also know as live queries or as probes.

The point of an custom query is to allow you to iterate and test quickly. Custom queries are queries that are run immediately and will have the results returned as soon as they are available from the node. These results are listed in the [Results pane](../the orbital builder/#results_pane); however, they can also be viewed on the Results page.

Additionally, they only return one set of results for any endpoints that meet the query's criteria. This means that if a targeted endpoint is offline, it will not be queried and there will be no results returned for that endpoint.

Scheduled Queries

Scheduled queries are those queries that been assigned both a window of time in which it must execute and a frequency of when it will execute. For example, a query is given an execution window of 24 hours and a frequency of 15 minutes. This means that the query will actively run and collect results for a period of 24 hours after its execution has been started and that it will attempt contact each relevant endpoint and run the query every 15 minutes.

Scheduled queries are most useful for developing a picture of a node's history over the query's window of time.

Refer to Schedule Orbital Queries for a more complete description of what scheduled queries are.

Query Prefixes

Orbital can make use of query prefixes to specify the endpoints that the query will run against, whether the they are specifically identified or match a set of criteria. Query prefixes can be grouped into three categories, Special, Dynamic, or Static.

Special Prefixes

This prefix category currently contains only one prefix, the all prefix. If you type all into the Endpoints field of the Builder, it will force Orbital to search across all endpoints in the organization. Additionally, all cannot be used in conjunction with other prefixes, nor can it be used with the allowos filter.

The all Prefix

The all prefix is used when defining which endpoints will be included in a query. Using this prefix will force Orbital to run the query against all of an organization's endpoints.

The prefix can be used in conjunction with the Operating System Filter () to narrow down the number of endpoints that the query will be run against. For example, if you set the the Operating System Filter to include only Mac endpoints, the all prefix will run the query across all macOS-based endpoints in an organization.

Caution: Care should be taken when using this prefix, as it has the potential to seriously impact Orbital's performance.

Dynamic Prefixes

This prefix category consists of those prefixes where a set of search criteria has been specified. Providing any criteria for one or more of these dynamic prefixes will cause the query to be run against all endpoints that match the criteria specified by the prefix. Unlike static prefixes, any endpoints that match the prefix criteria, but are not online, will be included in the query once they have reconnected, and will continue to be included until the query expires.

Static Prefixes

This prefix category consists of those prefixes that directly specify one or more known endpoints to run the query against. These prefixes can directly specify the endpoint or endpoints to query, or they can contain a wildcard (%) to specify a group of endpoints.

Note: All prefixes that use the wildcard character (%) will be treated as a dynamic prefix.

Any static prefixes that either refer to unknown endpoints or endpoints that are not connected at the time the query has begun will have the specified endpoints dropped from the query. If an endpoint has been dropped from the query, it will not be queried if it reconnects to the network, during the query's execution.

Refer to the Query API's Prefix Table for more information on Orbital's query prefixes.

The allowos Filter and the Orbital Query Catalog

allowos is a filter that is used to specify which operating system must be running on an endpoint before the query will run against it.

The allowos filter can be used with queries stored in Orbital's SQL Query Catalog. Because allowos influences the endpoints that will run the query, it is defined along with the endpoints, either by typing the value in the Endpoints field or defining it in either of the Operating System Filter popup(s), shown in the figure below.

or

The SQL Query Catalog contains queries that should only be executed on endpoints running a specific operating system. For example, a query that searches through the Windows registry cannot be run on endpoints using macOS or Linux.

The allowos filter returns only the endpoints that are running the desired operating system. For example, if you run the query Apple System Log (ASL) System Events Monitoring, Orbital will run it against all endpoints specified in the Endpoints field, irrespective of OS. Since some endpoints queried may be running Windows or Linux, specifying that you only want macOS (allowos:mac) endpoints returned is a way of ensuring that you will receive accurate results.

Query Success

A query will be considered successful provided that at least one of the following conditions is met.

  • The query contains the all special prefix, a dynamic prefix, or at least one resolvable static prefix.

  • At least one static or dynamic selector is able to be resolved if some or all of a query's static prefixes fail to resolve to known endpoints.

  • While queryid (for linked queries) is a dynamic prefix, each prefix value must refer to a known query at the time of the request or the request will fail.

More Info