Last month’s osquery release brought with it some exciting additions. Let’s jump in and have a quick look at some of the new features and table changes that were introduced.
For the full list of changes, check out the osquery changelog.
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
Constructing simple, unique keys based on columns can now be achieved more cleanly. For example, let’s compare:
Using the usual concat operator
osquery> SELECT 'hello' || NULL || 'world';
'hello' || NULL || 'world' =
coalesce everywhere is just messy:
osquery> SELECT coalesce('hello', '') || coalesce(NULL, '') || coalesce('world', '');
coalesce('hello', '') || coalesce(NULL, '') || coalesce('world', '') = helloworld
osquery> SELECT concat('hello', NULL, 'world');
concat('hello', NULL, 'world') = helloworld
(sourced from https://github.com/osquery/osquery/pull/6927 )
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.
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.
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.
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.
shellbags table adds support for parsing several shellitems and FAT timestamps that show when a directory was created, modified, and accessed.
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.
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.
system_extensions table gets the list of extensions installed, and the associated apps from which they are referenced, from the system extension
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.
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.
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.
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.