Dec 6th, 2021: [en] Using OSQuery in Elastic Stack

[aravindputrevu]

What is Osquery?

Osquery is a Opensource project developed from Facebook. As the name suggests, Osquery helps in querying the Operating System resources using SQL. It exposes the underlying Operating system concepts like processes, files, network like SQL tables.

You can write a simple SQL query to see the actively running processes in your system.

> SELECT pid, name, path FROM processes;

The same SQL query can be scheduled to run periodically and observe the changes in the system. This makes it easy to observe low-level operating system metrics.

How it works?

Osquery is compatible with most Linux-based distributions, windows, and mac. Osquery can be downloaded from here.

Osquery has two components:

• osqueryd

  The daemon service runs on your host operating system. osqueryd schedules and executes SQL queries on the host system. It gives the current state of the system when the query runs on the host. The daemon uses operating system eventing APIs to record changes.

  Queries executed by the daemon are logged in JSON format and reflect the state of the host system. Logs generated exported for analysis at a later point.

• osqueryi

  It is the interactive shell through which you can execute SQL queries. It is a standalone and doesn't communicate with other Osquery daemons running elsewhere. osqueryd is the binary; when you run osqueryd with -S flag, it operates as a shell to execute commands. You could also rename osqueryd as osqueryi to operate in an interactive mode.

  

To talk to osqueryd, you can integrate the SDK from the tool of your choice.

Using Osquery in Elastic Stack

Once you install the Osquery in your respective host system using instructions from the official docs. You need to spin up a Elasticsearch, Kibana & Fleet Server for the osqueryd result logs to be streamed.

There are a couple of ways to communicate with osqueryd and visualize the data in Elastic Stack.

• Filebeat + OSQquery module

  This method is relatively simple. First, install the Filebeat using the quickstart guide and enable the osquery module to see the results in an out of the box dashboard.

• Elastic Agent + Osquery Integration

  With Elastic Agent, you can run live queries on the host system and schedule queries to run periodically.

  

  Steps to setup:

  1. Use the integrations UI under Management to add Osquery Log Collection and Osquery manager integrations.
  

  Osquery log collection integration collects and stores result logs written by `osqueryd`. All the events stored in ES via the log collection integration follow [ECS](https://github.com/elastic/ecs) format.
        
  Osquery manager lets you schedule queries via osquery config packs, run live queries, and visualize the data in Kibana lens or discover. 
  

  

  Add Osquery integrations to a policy. Later the policy will be assigned to the Elastic Agent. 
  

  2. Install Elastic Agent on the host system where osqueryd is running.
  3. Enable and enroll the Elastic agent into Fleet. Fleet manages all the Elastic Agents installed on host systems.

Once you enable the integration to run a live query, you could head over to the metrics app in the Kibana, click on the machine you want to run the query.

Results collected from the scheduled queries can be visualized in the "Kibana lens" & "discover" from the Osquery app under the management tab. In addition, you can add such custom osquery packs from here.

Feel free to comment on this post for further questions!