Queries

Queries are the means by which Orbital searches for malicious activity on an organization's devices. 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 device'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;

Use Enter to enter the code editor and Esc to exit.

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

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 devices that meet the query's criteria. This means that if a targeted device is offline, it will not be queried and there will be no results returned for that device.

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 device 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 devices 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 Devices field of the Builder, it will force Orbital to search across all devices 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 devices will be included in a query. Using this prefix will force Orbital to run the query against all of an organization's devices.

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

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 devices that match the criteria specified by the prefix. Unlike static prefixes, any devices 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 devices to run the query against. These prefixes can directly specify the device or devices to query, or they can contain a wildcard (%) to specify a group of devices.

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

Any static prefixes that either refer to unknown devices or devices that are not connected at the time the query has begun will have the specified devices dropped from the query. If a device 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 a device 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 devices that will run the query, it is defined along with the devices, either by typing the value in the Devices 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 devices running a specific operating system. For example, a query that searches through the Windows registry cannot be run on devices using macOS or Linux.

The allowos filter returns only the devices 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 devices specified in the Devices field, irrespective of OS. Since some devices queried may be running Windows or Linux, specifying that you only want macOS (allowos:mac) devices 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 devices.

  • 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