Osquery

edit

Osquery is an open source tool that lets you query operating systems like a database, providing you with visibility into your infrastructure and operating systems. Using basic SQL commands, you can ask questions about devices, such as servers, Docker containers, and computers running Linux, macOS, or Windows. The extensive schema helps with a variety of use cases, including vulnerability detection, compliance monitoring, incident investigations, and more.

With Osquery in Kibana, you can:

  • Run live queries for one or more agents
  • Schedule query packs to capture changes to OS state over time
  • View a history of past queries and their results
  • Save queries and build a library of queries for specific use cases

Osquery in Kibana is powered by the Osquery Manager integration. For information on how to set up Osquery Manager, refer to Manage the integration.

Required privileges

edit

To use Osquery Manager, you must be assigned to a role with the following privileges:

  • Read privileges for the logs-osquery_manager.result* index.
  • Kibana privileges for Osquery Manager. The All privilege enables you to run, schedule, and save queries. Read enables you to view live and scheduled query results, but you cannot run live queries or edit.

Run live queries

edit

To inspect hosts, run a query against one or more agents or policies, then view the results.

  1. Open the main menu, and then click Osquery.
  2. In the Live queries view, click New live query.
  3. Select one or more agents or groups to query. Start typing in the search field, and you’ll get suggestions for agents by name, ID, platform, and policy.
  4. Enter a query or select a query from your saved queries.

    Select saved query dropdown name showing query name and description
  5. (Optional) Expand the Advanced section to view or set mapped ECS fields included in the results from the live query.
  6. Click Submit.
  7. Review the results in a table, or navigate to Discover to dive deeper into the response, or to the drag-and-drop Lens editor to create visualizations.
  8. To view more information about the request, such as failures, open the Status tab.
  9. To save the query for future use, click Save for later and define the ID, description, and other details.

View or rerun previous live queries

edit

The Live queries history section on the Live queries tab shows a log of queries run over the last 30 days. Each query has the following options:

  • Click Right-pointing triangle to rerun a query.
  • Click Table icon to view the query results and status.

    Results of OSquery

Schedule queries with packs

edit

Create packs to organize sets of queries. For example, you might create one pack that checks for IT compliance-type issues, and another pack that monitors for evidence of malware. You can schedule packs to run for one or more agent policies. When scheduled, queries in the pack are run at the set intervals for all agents in those policies. Scheduling packs is optional.

  1. Open the Packs tab.
  2. Click Add pack to create a new pack, or click the name of an existing pack, then Edit to add queries to an existing pack.
  3. Provide the following fields:

    • The name of the pack.
    • A short description of the pack.
    • The agent policies where this pack should run. If no agent policies are set, then the pack is not scheduled.
  4. Add queries to schedule:

    • To add a query to the pack, click Add query, and then either add a saved query or enter a new query. Each query must include a unique query ID and the interval at which it should run. Optionally, set the minimum Osquery version and platform, or map ECS fields. When you add a saved query to a pack, this adds a copy of the query. A connection is not maintained between saved queries and packs.
    • To upload queries from a .conf query pack, drag the pack to the drop zone under the query table. To explore the community packs that Osquery publishes, click Example packs.
  5. Click Save pack. The queries run when the policy receives the update.

View status of scheduled packs

edit
  1. Open the Packs tab.
  2. Click a pack name to view the status.

    Details include the last time each query ran, how many results were returned, and the number of agents the query ran against. If there are errors, expand the row to view the details, including an option to view more information in the Logs.

    Shows queries in the pack and details about each query
  3. View scheduled query results in Discover or the drag-and-drop Lens editor.

Save queries

edit

You can save queries in two ways:

  • After running a live query, click the Save for later link.
  • From the Saved queries tab, click Add saved query.

Once you save a query, you can only edit it from the Saved queries tab:

  1. Go to Saved queries, and then click Add saved query or the edit icon.
  2. Provide the following fields:

    • The unique identifier (required).
    • A brief description.
    • The SQL query (required). Osquery supports multi-line queries.
    • The ECS fields to populate when the query is run (optional). These fields are also copied in when you add this query to a pack.
    • The defaults to set when you add the query to a pack.

      • The frequency to run the query.
      • The minimum version of Osquery required to run the query.
      • The operating system required to run the query. For information about supported platforms per table, refer to the Osquery schema.
  3. Click Test configuration to test the query and any mapped fields:

    • From the Test query panel, select agents or groups to test the query, then click Submit to run a live query. Result columns with the mapping icon are mapped. Hover over the icon to see the mapped ECS field.
  4. Click Save or Update.

Prebuilt Elastic packs and queries

edit

Osquery Manager includes a set of prebuilt Osquery packs and saved queries that can help you get started using the integration.

Prebuilt queries

edit

A set of saved queries are included with the integration and available to run as a live query. Note the following about the prebuilt queries:

  • The queries are not editable.
  • Several of the queries include default ECS mappings to standardize the results.
  • The prebuilt Elastic queries all follow the same naming convention and identify what type of information is being queried, what operating system it supports if it’s limited to one or more, and that these are Elastic queries. For example, firewall_rules_windows_elastic.

Prebuilt packs

edit

The prebuilt Osquery packs are included with the integration and can be optionally loaded. Once added, you can then activate and schedule the packs.

You can modify the scheduled agent policies for a prebuilt pack, but you cannot edit queries in the pack. To edit the queries, you must first create a copy of the pack.

For information about the prebuilt packs that are available, refer to Prebuilt packs reference.

Load and activate prebuilt Elastic packs

edit
  1. Go to Packs, and then click Load Elastic prebuilt packs.

    This option is only available if new or updated prebuilt packs are available.

  2. For each pack that you want to schedule:

    • Enable the option to make the pack Active.
    • Click the pack name, then Edit.
    • Update the Scheduled agent policies to specify the policies where this pack should run.
  3. Click Update pack.

Copy prebuilt Elastic packs

edit

To modify queries in prebuilt packs, you must first make a copy of the pack.

  1. Go to Stack ManagementSaved Objects.
  2. Search for the Osquery packs you want to modify by name.
  3. Select the checkboxes of the packs to export.
  4. Click Export x objects.
  5. Click Import.
  6. Select the import option Create new objects with random IDs, then click Import to import the pack. This creates a copy of the pack that you can edit.

Map result fields to ECS

edit

When you save queries or add queries to a pack, you can optionally map Osquery results or static values to fields in the Elastic Common Schema (ECS). This standardizes your Osquery data for use across detections, machine learning, and any other areas that rely on ECS-compliant data. When the query is run, the results include the original osquery.<fields> and the mapped ECS fields. For example, if you update a query to map osquery.name to user.name, the query results include both fields.

  1. Edit saved queries or queries in a pack to map fields:

    • For Saved queries: Open the Saved queries tab, and then click the edit icon for the query that you want to map.
    • For packs: Open the Packs tab, edit a pack, and then click the edit icon for the query that you want to map.
  2. In the ECS mapping section, select an ECS field to map.
  3. In the Value column, use the dropdown on the left to choose what type of value to map to the ECS field:

    • Osquery value: Select an Osquery field. The fields available are based on the SQL query entered, and only include fields that the query returns. When the query runs, the ECS field is set dynamically to the value of the Osquery field selected.
    • Static value: Enter a static value. When the query runs, the ECS field is set to the value entered. For example, static fields can be used to apply tags or your preferred event.category to the query results.
  4. Map more fields, as needed. To remove any mapped rows, click the delete icon.
  5. Save your changes.
  • Some ECS fields are restricted and cannot be mapped. These are not available in the ECS dropdown.
  • Some ECS fields are restricted to a set of allowed values, like event.category. Use the ECS Field Reference for help when mapping fields.
  • Osquery date fields have a variety of data types (including integer, text, or bigint). When mapping an Osquery date field to an ECS date field, you might need to use SQL operators in the query to get an Elasticsearch-compatible date type.

Extended tables for Kubernetes queries

edit

In addition to the Osquery schema, the Elastic-provided version of Osquery also includes the following tables to support Kubernetes containers. These can be queried with live or scheduled queries.

  • host_users
  • host_groups
  • host_processes

When querying these tables, the expectation is that the /etc/passwd, /etc/group, and /proc are available in the container under /hostfs as: /hostfs/etc/passwd, /hostfs/etc/group, and /hostfs/proc. For information about the fields available in these tables, see the exported fields reference.

Osquery status

edit

A query can have the following status:

Successful

The query successfully completed.

Failed

The query encountered a problem, such as an issue with the query or the agent was disconnected, and might have failed.

Not yet responded

The query has not been sent to the agent.

Expired

The action request timed out. The agent may be offline.

If an agent is offline, the request status remains pending as Kibana retries the request. By default, a query request times out after one minute. An action timeout error is returned when the query does not complete within that interval.

Osquery results

edit

When you run live or scheduled queries, the results are automatically stored in an Elasticsearch index, so that you can search, analyze, and visualize this data in Kibana. For a list of the Osquery fields that can be returned in query results, refer to exported fields. Query results can also include ECS fields, if the query has a defined ECS mapping.

Osquery responses include the following information:

  • Everything prefaced with osquery. is part of the query response. These fields are not mapped to ECS by default.
  • Results include some ECS fields by default, such as host.* and agent.*, which provide information about the host that was queried.
  • For live queries, the action_data.query is the query that was sent.
  • For scheduled queries in a pack, the action_id has the format pack_<pack-name>_<query-ID>. You can use this information to look up the query that was run.
  • By default, all query results are snapshot logs that represent a point in time with a set of results, with no differentials.
  • Osquery data is stored in the logs-osquery_manager.result-<namespace> datastream, and the result row data is under the osquery property in the document.