What’s new in osquery 4.7.0

For the full list of changes, check out the osquery changelog.

New features

concat and concat_ws sql functions

If you’ve ever found yourself wanting while concatenating strings in osquery, tying columns together has never felt better, thanks to the introduction of concat and concat_ws functions.

Constructing simple, unique keys based on columns can now be achieved more cleanly. For example, let’s compare:

Using the usual concat operator ||returns NULL

osquery> SELECT 'hello' || NULL || 'world';
'hello' || NULL || 'world' =

Using coalesce everywhere is just messy:

osquery> SELECT coalesce('hello', '') || coalesce(NULL, '') || coalesce('world', '');
coalesce('hello', '') || coalesce(NULL, '') || coalesce('world', '') = helloworld

Hello concat!

osquery> SELECT concat('hello', NULL, 'world');
concat('hello', NULL, 'world') = helloworld

(sourced from https://github.com/osquery/osquery/pull/6927 )

Table changes

computer_name in windows_eventlog (Windows)

The addition of the computer_name column means that you can now parse out the computer field for Windows Eventlogs. This is useful because it enables you to identify the hostname of the system on which an event was generated.

docker_image_history (macOS and Linux)

With the introduction of the docker_image_history table, we’re able to pull historic information about when and how a Docker image was created.

docker_image_history example output. Sourced from https://github.com/osquery/osquery/pull/6884.

filevault_status in disk_encryption (macOS)

This column is an important update to the disk_encryption table. By default, Apple’s T2 chip encrypts drives at rest, so even with Filevault disabled, querying the disk_encryption table would correctly return the volume as being encrypted — where in fact, the drive could still be accessible to someone with physical access to the device.

The addition of the new filevault_status column enables osquery to more accurately observe the encryption status of devices.

filevault_status in disk_encryption example output.

location_service (macOS)

location_service reports the enabled/disabled status of macOS Location Services. This is handy for those who, you guessed it, need to know the current status of Location Services.

location_services example output, sourced from https://github.com/osquery/osquery/pull/6826.

shellbags (Windows)

Juicy one here. This new table brings Windows Shellbag support to osquery. Windows Shellbags are primarily used to improve user experience, but can also be critical during forensic investigation.

The shellbags table adds support for parsing several shellitems and FAT timestamps that show when a directory was created, modified, and accessed.

shellbags example output, sourced from https://github.com/osquery/osquery/pull/6949.

Due to the complexity of Shellbags, the table does not currently support, or only partially supports, the following shellitems:

  • optical disc
  • variable (partial support)
  • mtp (partial support)
  • user property view data (partial support)

While the main value of the shellbags table is reconstructing directories accessed, support for FTP servers connected to via Windows Explorer, ZIP files opened, and network shares browsed to via Windows Explorer is not currently included — but still, some awesome functionality to build upon.

system_extensions (macOS)

The addition of the system_extensions table is welcomed as Apple continues efforts to make macOS a more reliable and secure platform by deprecating kernel extensions.

The system_extensions table gets the list of extensions installed, and the associated apps from which they are referenced, from the system extension database /Library/SystemExtensions/db.plist.

systemd_units (Linux)

For the Linux community, the systemd_units table is back in the fray, and makes it to release thanks to the successful implementation of D-Bus — an interprocess communication system that allows users to interact directly with systemd in order to extract information about startup items.

The systemd_units table has a column for all the many types of systemd units, the most common of which being; service, socket, and device, and allows osquery to report back for each.

systemd_units example output, sourced from https://github.com/osquery/osquery/pull/6593.

ycloud_instance_metadata (all platforms)

Introduction of a new VM metadata table for Yandex.Cloud.

A great addition for anyone who uses Yandex Cloud for their compute.

Yandex Cloud logo.

So there we have it. Osquery 4.7.0 brings with it a host (🥁) of new changes, under the hood improvements, and bug fixes. Along with updated documentation, and improvements and strengthening to the build.

For the complete list of changes, check out the osquery changelog.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store