Using the Query Catalog
osquery makes it possible for queries to return a wealth of detailed data. While this provides a great deal of flexibility and power in creating queries to deliver on multiple use cases, it also adds a level of complexity and an expectation that the user has a deep knowledge of Operating System Internals, SQL, and osquery tables. The Query Catalog allows you to get started right away by providing a rich and deep collection of common and meaningful queries that are immediately ready for you to use.
The catalog includes several hundred stock queries to help you get started. These queries have been vetted and tested by the Threat Research for Endpoints team, and represent a robust collection. A few example queries are:
- Get System Inventory Information
- Is Critical Security Service Inactive
- Is FileVault Disabled
- List Firefox Addons
- List Installed Programs and Versions
- List Interfaces And Addresses
- List Logged In Users
- List Processes with Network Connections
- List Processes in Strange Locations
- List Processes with no file on disk
The implementation of these queries is visible, and allows you to customize and adapt these existing queries in order to support new use cases. The query catalog builds on the efforts of the osquery community to create reusable and high quality queries.
Viewing Catalog Queries
To open a Query Details Catalog page, click the desired query name link. Each query catalog page includes a description of the query, the query’s SQL statement, and the query’s Mitre tactics and techniques, if the query is a stock query. The figure below illustrates a query’s detailed page.
Finding Catalog Queries with Filter and Search
Orbital provides you with two methods for locating the desired query to use, filters and search.
Filters
Use the filters listed on the left side of the page to filter the display of catalog queries. The display will include all queries that have been grouped into the selected filter category, as shown in the figure below.
For example, select the New Queries filter to display only those queries that have been added to the Query Catalog within the last 90 days.
Search Catalog
Type the desired terms or phrases in the Search Catalog field at the top of the Catalog page to locate your desired query or queries. Search phrases can include partial words and variations on the query name. This will list all matching queries in the catalog.
You can also narrow down your search further by selecting one or more filters. In this instance, the search will be limited to the selected options as seen in the following sample, which shows the search results for the term user, with the Forensics filter set.
Note: Catalog queries that have been deprecated cannot be added to your query favorites list. Further, those queries that have been deprecated will be removed from your favorites list, if you have previously added it.
Editing a Custom Query
There may be times when you will need to edit a custom query that has been saved to the catalog. In order to edit a custom query:
-
Open the Catalog page.
-
Navigate to the desired query. This can be done by:
- Browsing to the desired query, using the Page Navigation Buttons, shown in the figure below.
or
- Using the Search Catalog field, shown in the figure below, to find the query.
-
Highlight the desired custom query.
-
Click the query’s action menu.
-
Select the Edit menu command.
This will display the Edit Query popup, as shown in the figure below.
-
Make the necessary changes to the query, using the Name, Description, OS, and Custom SQL interface elements.
-
Save the changes to the query by clicking Save.
Copy or Add the SQL to a New Query
The detailed Query Catalog page provides the query SQL statement.
- To copy the SQL, click the Copy icon above the SQL statement.
- To add the SQL to a query you’re building click the Add (
+) icon. Catalog queries are designed to be run independently, so you can only add one to run at a time.
Upload Queries
The Upload queries feature uses query packs to add new queries to Orbital’s Query Catalog. Query packs can contain one or more queries, allowing you to create groupings of similar queries. This feature allows you the flexibility of creating your queries on a local machine where they can be safely tested, without risking your operating environment. Once uploaded, these queries can be used by anyone in your organization.
Query packs are contained in a JSON file. The structure of the JSON file is outlined in the queryPackTemplate.json file. Refer to the Download Query Template section for more information on the file structure.
To use the feature:
-
Navigate to Orbital’s Catalog page.
-
Click Download query template. This will download the Query Template to your local computer. This step is optional, but having the template will help in defining the information and structure needed for the query packs.
-
Navigate to the downloaded template file.
-
Rename the edited template file to a name that describes the query or set of queries the file contains. Do not change the file extension.
-
Edit the template file to include one or more queries you wish to add to the Catalog. Refer to the Download Query Template section for more information on the query template structure.
- The query name that will appear in the Query Catalog page is taken from the Query Name field in the query pack file.
-
Save your changes.
-
Return to the Orbital Catalog page.
-
Click Upload queries. This will open a file navigation dialogue.
-
Navigate to your query pack file and select it.
-
Click Open. This will upload your query pack file to Orbital.
-
If the file is successfully uploaded and stored in the Catalog, you will receive the following status message.
-
If the file has been incorrectly formatted, you will receive the following error message.
-
If you have accidentally uploaded an existing query pack file, you will receive the following error message.
Once the query pack has been uploaded and successfully stored in the Catalog, it will be displayed on the Query Catalog List pane.
-
Download Query Template
Clicking on Download, then the Download query template link, shown in the figure below, allows you to download a template that you can use to create an osquery Query Pack. This query pack can contain one or more queries that can be uploaded to the Orbital Catalog, using the Upload queries feature. Refer to the Upload Queries section for more information on using the Upload Queries feature.
When you click the Download query template link, a file named queryPackTemplate.json will be downloaded to your computer. The structure of this file is shown in the figure below.
{
"queries": {
"Unique Title 1": {
"query": [
"Array of SQL statements to be executed.",
"At least one required."
],
"description": "Text description of what the query does. Required.",
"platform": [
"Array",
"of",
"compatible",
"platforms.",
"Optional."
],
"version": "Only run on osquery versions greater than or equal-to this version string. Optional."
},
"Unique Title 2 (etc)": {
"query": [
"SELECT a, b FROM z WHERE b = \"bee\"",
"SELECT a, c FROM z WHERE c = \"sea\""
],
"description": "Selects data based on letter puns.",
"platform": [
"windows",
"mac",
"linux"
],
"version": "2.4"
},
"Only Required Fields": {
"query": [
"required"
],
"description": "required"
}
}
}
Note: Orbital will accept a value populating the Version field; however, currently Orbital ignores this value.
Download Organization Queries
The Download organization queries link, shown in the figure below, allows you to download all of the queries, stored in the Orbital Catalog, that have been created by your organization.
Note: This download feature will not include any of the stock queries in the downloaded file. Stock queries are those queries that have been added to the Orbital Catalog by the Orbital development team.
When you click the Download organization queries link, a file named orgQueries.json will be downloaded to your computer. The structure of this file is shown in the figure below.
{
"queries": {
"Query Name": {
"created": "Date and Time Query Was Created",
"creator": {
"created": "Date and Time Query Was Created",
"id": "User ID Number",
"name": "Developer's Name"
},
"description": "The description of the query is typed in this field. This can include information such as
its purpose, what its target is, and so on",
"id": "org:This is the organization's ID number",
"organization": {
"id": "This is the organization's ID number",
"name": "This is the name of the organization"
},
"query": [
"This is the query's SQL statement;"
]
},
...
}