Query Your Device as a Relational Database With Osquery

Stop chaining commands start making SQL queries to fetch OS information

As dev or ops, we interact every day with operating systems (OS) that may differ. The command line allows us to retrieve information about the current state of the system. Finally, an operating system can be considered roughly and simplified as a database and the command line as a query to access what we want.

This is the concept that the osquery project pushes by providing a low-level and powerful endpoint that can be used for system analysis or monitoring

The osquery project logo

How Does Osquery Work

Osquery exposes the OS as a high-performance relational database. Tables represent OS abstract concepts such as users or processes for example. Afterward, you can retrieve information from them with SQL queries. Osquery performs real-time calls to the OS to serve you data:

In this article, we will explore the osquery possibilities. After installing it, we will get started to make some queries from a simple to a complex one. Then we will figure out the scheduled queries using the osqueryd daemon. It is useful to make system audits and record events in logs. Logs may be forwarded to a centralized logging system.

The communication interface is not limited only to the interpreter. We will use Golang to execute queries programmatically. In addition, we will extend osquery information by adding a new table in the endpoint.

Install Osquery

It is possible to install osquery on many OS:

Depending on the operating system you are using you can have extra information. The detailed installation below is for MacOSX but you can find another installation guide here.

$ brew install --cask osquery

The example configuration file needs to be copied:

$ sudo cp -p /var/osquery/osquery.example.conf /var/osquery/osquery.conf

Then let's start the osquery daemon:

$ sudo osqueryctl start

For Linux systems you can start osqueryd using systemd: sudo systemctl start osqueryd

Play With the Osquery Interpreter

Osquery provides an SQL interpreter with the osqueryi command:

$ osqueryi                                                               
Using a virtual database. Need help, type '.help'
osquery> .help
Welcome to the osquery shell. Please explore your OS!
You are connected to a transient 'in-memory' virtual database.

.all [TABLE]     Select all from a table
.bail ON|OFF     Stop after hitting an error
.connect PATH    Connect to an osquery extension socket
.disconnect      Disconnect from a connected extension socket
.echo ON|OFF     Turn command echo on or off
.exit            Exit this program
.features        List osquery's features and their statuses
.headers ON|OFF  Turn display of headers on or off
.help            Show this message
.mode MODE       Set output mode where MODE is one of:
                   csv      Comma-separated values
                   column   Left-aligned columns see .width
                   line     One value per line
                   list     Values delimited by .separator string
                   pretty   Pretty printed SQL results (default)
.nullvalue STR   Use STRING in place of NULL values
.print STR...    Print literal STRING
.quit            Exit this program
.schema [TABLE]  Show the CREATE statements
.separator STR   Change separator used by output mode
.socket          Show the local osquery extensions socket path
.show            Show the current values for various settings
.summary         Alias for the show meta command
.tables [TABLE]  List names of tables
.types [SQL]     Show result of getQueryColumns for the given query
.width [NUM1]+   Set column widths for "column" mode
.timer ON|OFF      Turn the CPU timer measurement on or off
osquery>

You can retrieve the list of tables with the .helpcommand or in the documentation:

osquery> .tables

Create First OS Queries With SQL

Retrieve user information

We are going to list all the existing users in the users table. It is possible to get the table schema:

osquery> .schema users
CREATE TABLE users(`uid` BIGINT, `gid` BIGINT, `uid_signed` BIGINT, `gid_signed` BIGINT, `username` TEXT, `description` TEXT, `directory` TEXT, `shell` TEXT, `uuid` TEXT, `type` TEXT HIDDEN, `is_hidden` INTEGER, `pid_with_namespace` INTEGER HIDDEN, PRIMARY KEY (`uid`, `username`, `uuid`, `pid_with_namespace`)) WITHOUT ROWID;

osquery> select * from users where username = 'gvincent';
+-----+-----+------------+------------+----------+-------------------+-----------------+----------+--------------------------------------+-----------+
| uid | gid | uid_signed | gid_signed | username | description       | directory       | shell    | uuid                                 | is_hidden |
+-----+-----+------------+------------+----------+-------------------+-----------------+----------+--------------------------------------+-----------+
| 501 | 20  | 501        | 20         | gvincent | Guillaume Vincent | /Users/gvincent | /bin/zsh | 90EF833A-727A-4635-81E7-AEAA7DCF0981 | 0         |
+-----+-----+------------+------------+----------+-------------------+-----------------+----------+--------------------------------------+-----------+

The current print is the pretty mode by default but it can be changed:

osquery> .mode line
osquery> select * from users where username = 'gvincent';
        uid = 501
        gid = 20
 uid_signed = 501
 gid_signed = 20
   username = gvincent
description = Guillaume Vincent
  directory = /Users/gvincent
      shell = /bin/zsh
       uuid = 90EF833A-727A-4635-81E7-AEAA7DCF0981
  is_hidden = 0

List running processes by users

Now we have made our first query we can further. We want to list the processes owned by a specific user. It is a joint query between the users and processes tables using the uid:

Relation between the users and processes tables

osquery> .mode pretty
osquery> select u.username, p.pid, p.start_time, p.name, p.path, p.state
...> from processes as p, users as u
...> where p.uid = u.uid and u.username = 'gvincent'
...> order by p.start_time;

An UNIX timestamp can be formatted to a human-readable date with the datetime function: datetime(start_time, 'unixepoch')

Repeat Actions With Scheduled Queries

"osqueryd is the host monitoring daemon that allows you to schedule queries and record OS state changes. The daemon aggregates query results over time and generates logs, which indicate state change according to each query. The daemon also uses OS eventing APIs to record monitored file and directory changes, hardware events, network events, and more." https://osquery.readthedocs.io/en/1.8.2/introduction/using-osqueryd/#logging-and-reporting

We are going to configure a scheduled query for USB device monitoring. USB devices represent a data breach. They may be used to exfiltrate sensible data or contain malicious files compromising the networks and systems.

The scheduled query is added in the osquery configuration file in /var/osquery/osquery.conf:

{
  "usb_devices": {
    "query": "select vendor, model from usb_devices",
    "interval": 60
  }
}

We restart the daemon:

$ sudo osqueryctl restart

Events are recorded in /var/log/osquery.

Programmatical Example With Golang

Download the Golang osquery-go package:

$ go get github.com/osquery/osquery-go

The main.gofile below connects to the local osquery socket and list information about a user:

package main

import (
  "fmt"
  "github.com/osquery/osquery-go"
  "log"
  "time"
)

func main() {
  socketPath := "/var/osquery/osquery.em"
  client, err := osquery.NewClient(socketPath, 10*time.Second)
  if err != nil {
    log.Fatalf("Error creating Thrift client: %v", err)
  }
  defer client.Close()

  resp, err := client.Query("select * from users where username = 'gvincent'")
  if err != nil {
    log.Fatalf("Error communicating with osqueryd: %v", err)
  }
  if resp.Status.Code != 0 {
    log.Fatalf("osqueryd returned error: %s", resp.Status.Code)
  }
  fmt.Printf("Got results:\n%v\n", resp.Response)
}

$ sudo go run main.go
Got results:
[map[description:Guillaume Vincent directory:/Users/gvincent gid:20 gid_signed:20 is_hidden:0 shell:/bin/zsh uid:501 uid_signed:501 username:gvincent uuid:90EF833A-727A-4635-81E7-AEAA7DCF0981]]

Add New Tables To Extend Possibilities

You can complete the information in osquery. The Golang code below shows how to add a new table:

package main

import (
  "context"
  "log"
  "os"
  "flag"

  "github.com/osquery/osquery-go"
  "github.com/osquery/osquery-go/plugin/table"
)

func main() {
  socket := flag.String("socket", "", "Path to osquery socket file")
  flag.Parse()
  if *socket == "" {
    log.Fatalf(`Usage: %s --socket SOCKET_PATH`, os.Args[0])
  }

  server, err := osquery.NewExtensionManagerServer("foobar", *socket)
  if err != nil {
    log.Fatalf("Error creating extension: %s\n", err)
  }

  // Create and register a new table plugin with the server.
  // table.NewPlugin requires the table plugin name,
  // a slice of Columns and a Generate function.
  server.RegisterPlugin(table.NewPlugin("foobar", FoobarColumns(), FoobarGenerate))
  if err := server.Run(); err != nil {
    log.Fatalln(err)
  }
}

// FoobarColumns returns the columns that our table will return.
func FoobarColumns() []table.ColumnDefinition {
  return []table.ColumnDefinition{
    table.TextColumn("foo"),
    table.TextColumn("baz"),
  }
}

// FoobarGenerate will be called whenever the table is queried. It should return
// a full table scan.
func FoobarGenerate(ctx context.Context, queryContext table.QueryContext) ([]map[string]string, error) {
  return []map[string]string{
    {
      "foo": "bar",
      "baz": "baz",
    },
    {
      "foo": "bar",
      "baz": "baz",
    },
  }, nil
}

For the example here we return directly a string map. But we could execute an action and return its result. This would be a record in the new table.

To test this new table, you have to launch osqueryi with a flag like this:

$ osqueryi --nodisable_extensions

At the same time you have to specify the UNIX socket as an argument to the program:

$ go run ./my_table_plugin.go --socket /Users/gvincent/.osquery/shell.em

Back to the interpreter:

Using a virtual database. Need help, type '.help'
osquery> select * from foobar;
+-----+-----+
| foo | baz |
+-----+-----+
| bar | baz |
| bar | baz |
+-----+-----+

Conclusion

After a tour of osquery, we saw that it offers an endpoint compatible with multiple OS. Instead of chaining commands, you just need to make joins in SQL queries. This allows better maintainability and it is possible to make advanced information extractions.

The scheduled queries allow to automate queries and to log events on the system. Only the change of state is recorded. It is interesting to make audits in particular to verify the integrity of a system. Of course, other uses are possible and imagination is the limit.

It is also an extensible tool using a programming language. We have seen how to execute queries with Golang and how to add a new table to enrich the endpoint.

#Programming