What’s new in osquery 4.7.0
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.
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', '') = helloworldHello 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.
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 (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.
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.
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.
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.