What's New in Osquery 4.2

Beyond the Changelog

This article goes beyond the Changelog, and demonstrates the use of new osquery features in context.

Osquery 4.2.0 also patches a security vulnerability that could allow a man-in-the-middle attack on the osquery TLS plugins.

SQL Functions

community_id_v1 (All Platforms)

Tables

ntfs_journal_events (Windows)
user_ssh_keys (Windows)
ssh_configs (Windows)
firefox_addons (Windows)
bitlocker_info (Windows)
windows_optional_features (Windows)
mdls (macOS)
docker_image_layers (macOS, Linux)
selinux_settings (Linux)
apparmor_profiles (Linux)
process_open_pipes (Linux)
chrome_extensions (All Platforms)

Configuration

enable_tables (All Platforms)

New SQL Function – `community_id_v1` – All Platforms

This function calculates the Community ID Hash of the provided network connection. As a function, it can be used with any data set in osquery that supplies the appropriate values. Using the hashed value, network connections in osquery can be linked to those recorded by network monitoring software.

Thank you to Security Onion Solutions for sponsoring Dactiv’s development of this new feature.

osquery> SELECT *, community_id_v1(local_address,remote_address,local_port,remote_port,protocol) AS community_id
    ...> FROM process_open_sockets
    ...> WHERE remote_address NOT IN ('', '0.0.0.0', '127.0.0.1', '::1') LIMIT 1;
           pid = 851
            fd = 21
        socket =
        family = 2
      protocol = 6
 local_address = 192.168.1.3
remote_address = 52.37.243.173
    local_port = 65465
   remote_port = 443
          path =
         state = ESTABLISHED
  community_id = 1:zzqPVx4eNedJ5tBhwJEitvVaq4w=

New Table – `ntfs_journal_events` – Windows

This table can be used to implement File Integrity Monitoring (FIM) with osquery on Windows.

Osquery configuration used:

{
  "options": {
    "enable_ntfs_event_publisher": true,
    "disable_events": false
  },
  "file_paths": {
    "user_folder": [
      "C:\\Users\\zachw\\*",
      "C:\\Users\\zachw\\"
    ]
  },
  "schedule": {
    "ntfs_events": {
      "interval": 10,
      "query": "SELECT * FROM ntfs_journal_events"
    }
  }
}

Logs resulting from edit of a file:

{
  "name": "ntfs_events",
  "hostIdentifier": "DESKTOP-US4MMSD",
  "calendarTime": "Thu Feb 20 05:28:10 2020 UTC",
  "unixTime": 1582176490,
  "epoch": 0,
  "counter": 0,
  "numerics": false,
  "columns": {
    "action": "FileOverwrite",
    "category": "user_folder",
    "drive_letter": "C",
    "file_attributes": "FILE_ATTRIBUTE_ARCHIVE",
    "node_ref_number": "2016110002000000000",
    "old_path": "",
    "parent_ref_number": "1006210001000000000",
    "partial": "0",
    "path": "C:\\Users\\zachw\\.bash_history",
    "record_timestamp": "1582176487",
    "record_usn": "000000005bbe99e0",
    "time": "1582176487"
  },
  "action": "added"
}
{
  "name": "ntfs_events",
  "hostIdentifier": "DESKTOP-US4MMSD",
  "calendarTime": "Thu Feb 20 05:28:10 2020 UTC",
  "unixTime": 1582176490,
  "epoch": 0,
  "counter": 0,
  "numerics": false,
  "columns": {
    "action": "FileWrite",
    "category": "user_folder",
    "drive_letter": "C",
    "file_attributes": "FILE_ATTRIBUTE_ARCHIVE",
    "node_ref_number": "2016110002000000000",
    "old_path": "",
    "parent_ref_number": "1006210001000000000",
    "partial": "0",
    "path": "C:\\Users\\zachw\\.bash_history",
    "record_timestamp": "1582176487",
    "record_usn": "000000005bbe99e0",
    "time": "1582176487"
  },
  "action": "added"
}
{
  "name": "ntfs_events",
  "hostIdentifier": "DESKTOP-US4MMSD",
  "calendarTime": "Thu Feb 20 05:28:10 2020 UTC",
  "unixTime": 1582176490,
  "epoch": 0,
  "counter": 0,
  "numerics": false,
  "columns": {
    "action": "FileOverwrite",
    "category": "user_folder",
    "drive_letter": "C",
    "file_attributes": "FILE_ATTRIBUTE_ARCHIVE",
    "node_ref_number": "2016110002000000000",
    "old_path": "",
    "parent_ref_number": "1006210001000000000",
    "partial": "0",
    "path": "C:\\Users\\zachw\\.bash_history",
    "record_timestamp": "1582176487",
    "record_usn": "000000005bbe9a38",
    "time": "1582176488"
  },
  "action": "added"
}
{
  "name": "ntfs_events",
  "hostIdentifier": "DESKTOP-US4MMSD",
  "calendarTime": "Thu Feb 20 05:28:10 2020 UTC",
  "unixTime": 1582176490,
  "epoch": 0,
  "counter": 0,
  "numerics": false,
  "columns": {
    "action": "FileWrite",
    "category": "user_folder",
    "drive_letter": "C",
    "file_attributes": "FILE_ATTRIBUTE_ARCHIVE",
    "node_ref_number": "2016110002000000000",
    "old_path": "",
    "parent_ref_number": "1006210001000000000",
    "partial": "0",
    "path": "C:\\Users\\zachw\\.bash_history",
    "record_timestamp": "1582176487",
    "record_usn": "000000005bbe9a38",
    "time": "1582176488"
  },
  "action": "added"
}

New Table – `user_ssh_keys` – Windows

Get information about the SSH keys in the default SSH configuration directory.

The table is now supported on all platforms.

osquery> SELECT usk.* FROM users JOIN user_ssh_keys usk USING (uid) LIMIT 1;
      uid = 1001
     path = C:\Users\zachw\.ssh\id_rsa
encrypted = 1

New Table – `ssh_configs` – Windows

Get information about the SSH configurations in the default SSH configuration directory.

The table is now supported on all platforms.

osquery> SELECT sc.* FROM users JOIN ssh_configs sc USING (uid);
            uid = 1001
          block = host *
         option = addkeystoagent yes
ssh_config_file = C:\Users\zachw\.ssh/config

New Table – `firefox_addons` – Windows

The table is now supported on all platforms.

osquery> SELECT fa.* FROM users JOIN firefox_addons fa USING (uid) LIMIT 1;
        uid = 1001
       name = DoH Roll-Out
 identifier = doh-rollout@mozilla.org
    creator = null
       type = extension
    version = 1.3.0
description = Mozilla add-on that supports the roll-out of DoH
 source_url = null
    visible = 1
     active = 1
   disabled = 0
 autoupdate = 1
     native =
   location = app-system-defaults
       path = C:\Program Files\Mozilla Firefox\browser\features\doh-rollout@mozilla.org.xpi

New Columns – `bitlocker_info` – Windows

version: The FVE metadata version of the drive.

percentage_encrypted: The percentage of the drive that is encrypted.

lock_status: The accessibility status of the drive from Windows.

osquery> SELECT * FROM bitlocker_info;
           device_id = \\?\Volume{fe05d1b3-0000-0000-0000-402400000000}\
        drive_letter = C:
persistent_volume_id =
   conversion_status = 0
   protection_status = 0
   encryption_method = None
             version = 0
percentage_encrypted = 0
         lock_status = 0

New Table – `windows_optional_features` – Windows

Provides information about the “optional features” enabled and disabled on a Windows device.

Dactiv’s Zach Wasserman enabled the (previously implemented) table by configuring it to be built with osquery.

osquery> SELECT * FROM windows_optional_features LIMIT 5;
     name = Printing-PrintToPDFServices-Features
  caption = Microsoft Print to PDF
    state = 1
statename = Enabled

     name = Printing-XPSServices-Features
  caption = Microsoft XPS Document Writer
    state = 1
statename = Enabled

     name = SearchEngine-Client-Package
  caption = Windows Search
    state = 1
statename = Enabled

     name = MSRDC-Infrastructure
  caption = Remote Differential Compression API Support
    state = 1
statename = Enabled

     name = TelnetClient
  caption = Telnet Client
    state = 2
statename = Disabled

New Table – `mdls` – macOS

Retrieve file metadata from Spotlight. Of special note is kMDItemWhereFroms which can give the download URL of a file.

osquery> SELECT * FROM mdls
    ...> WHERE path = '/Users/zwass/Downloads/osquery-4.1.2.pkg'
    ...> AND key IN ('kMDItemContentType', 'kMDItemKind', 'kMDItemWhereFroms');
 path = /Users/zwass/Downloads/osquery-4.1.2.pkg
  key = kMDItemContentType
value = com.apple.installer-package-archive

 path = /Users/zwass/Downloads/osquery-4.1.2.pkg
  key = kMDItemKind
value = Installer package

 path = /Users/zwass/Downloads/osquery-4.1.2.pkg
  key = kMDItemWhereFroms
value = https://pkg.osquery.io/darwin/osquery-4.1.2.pkg,https://osquery.io/downloads/official/4.1.2

New Table – `docker_image_layers` – macOS, Linux

This table retrieves metadata about the layers that make up a Docker image.

osquery> SELECT * FROM docker_images JOIN docker_image_layers USING (id);
         id = 44d36d2c2374b240abcf5da2130abf49938b8fb49446df6eec028718520332ef
    created = 1580584735
 size_bytes = 98205111
       tags = redis:latest
   layer_id = 488dfecc21b1bc607e09368d2791cb784cf8c4ec5c05d2952b045b3e0f8cc01e
layer_order = 1

         id = 44d36d2c2374b240abcf5da2130abf49938b8fb49446df6eec028718520332ef
    created = 1580584735
 size_bytes = 98205111
       tags = redis:latest
   layer_id = 6d35c327901c03885dc2646367382a9cf90b77e07fb5bc1347905eed9a9f464b
layer_order = 2

         id = 44d36d2c2374b240abcf5da2130abf49938b8fb49446df6eec028718520332ef
    created = 1580584735
 size_bytes = 98205111
       tags = redis:latest
   layer_id = 35e1c7a160662fc0b9a97d6cd893978b6a531d81bede805e47fcae1a06ebe751
layer_order = 3

         id = 44d36d2c2374b240abcf5da2130abf49938b8fb49446df6eec028718520332ef
    created = 1580584735
 size_bytes = 98205111
       tags = redis:latest
   layer_id = 9f839e56c43407961d93dcb97372ffbe5abf7b313f0db9d4328770b1e5caaeab
layer_order = 4

         id = 44d36d2c2374b240abcf5da2130abf49938b8fb49446df6eec028718520332ef
    created = 1580584735
 size_bytes = 98205111
       tags = redis:latest
   layer_id = d7344f36256ca9a11964e67a4b16fb51e147c69bf4f7b9f3e150f5d13c64f781
layer_order = 5

         id = 44d36d2c2374b240abcf5da2130abf49938b8fb49446df6eec028718520332ef
    created = 1580584735
 size_bytes = 98205111
       tags = redis:latest
   layer_id = 0233556febffa2d711e3f9d34f3daaab58063987b910232728bbea71ba5906bb
layer_order = 6

New Table – `selinux_settings` – Linux

This table exposes the SELinux configuration on a Linux machine. If SELinux is not enabled, no results are returned.

osquery> SELECT * FROM selinux_settings LIMIT 10;
+----------+------------------------------+-------+
| scope    | key                          | value |
+----------+------------------------------+-------+
|          | checkreqprot                 | 0     |
|          | deny_unknown                 | 0     |
|          | enforce                      | 0     |
|          | mls                          | 1     |
|          | policyvers                   | 31    |
|          | reject_unknown               | 0     |
| booleans | abrt_anon_write              | off   |
| booleans | abrt_handle_event            | off   |
| booleans | abrt_upload_watch_anon_write | on    |
| booleans | antivirus_can_scan_system    | off   |
+----------+------------------------------+-------+

New Table – `apparmor_profiles` – Linux

Retrieves the AppArmor configurations on a Linux host.

osquery> SELECT * FROM apparmor_profiles LIMIT 2;
  path = /usr/sbin/tcpdump
  name = /usr/sbin/tcpdump
attach = /usr/sbin/tcpdump
  mode = enforce
  sha1 = 2c1ebd44f7fd403a6167c0e9a27e6eee1b5a314e

  path = /usr/lib/snapd/snap-confine
  name = /usr/lib/snapd/snap-confine
attach = /usr/lib/snapd/snap-confine
  mode = enforce
  sha1 = 0a9ed1d878417122c6f2d60c0c80bedf845ecef6

New Table – `process_open_pipes` – Linux

Retrieves metadata about the Unix Pipes opened by running processes.

osquery> SELECT pid, path, cmdline, pop.* FROM processes JOIN process_open_pipes pop USING (pid) LIMIT 3;
         pid = 1
        path = /lib/systemd/systemd
     cmdline = /sbin/init
         pid = 1
          fd = 58
        mode = rw
       inode = 266
        type = named
 partner_pid =
  partner_fd =
partner_mode =

         pid = 1239
        path = /lib/systemd/systemd
     cmdline = (sd-pam)
         pid = 1239
          fd = 6
        mode = w
       inode = 19822
        type = anonymous
 partner_pid =
  partner_fd =
partner_mode =

         pid = 1959
        path = /usr/sbin/sshd
     cmdline = sshd: vagrant [priv]
         pid = 1959
          fd = 7
        mode = w
       inode = 527
        type = named
 partner_pid = 738
  partner_fd = 19
partner_mode = r

New Columns – `chrome_extensions` – All Platforms

optional_permissions: The permissions optionally required by the extension.

osquery> SELECT * FROM chrome_extensions;
...
                 uid = 501
                name = LastPass: Free Password Manager
             profile = Default Profile
          identifier = hdokiejnpimakedhajhdlcegeplioahd
             version = 4.41.0.4
         description = LastPass, an award-winning password manager, saves your passwords and gives you secure access from every computer and mobile device.
              locale = en_US
          update_url = https://clients2.google.com/service/update2/crx
              author = LastPass
          persistent = 1
                path = /Users/zwass/Library/Application Support/Google/Chrome/Default/Extensions/hdokiejnpimakedhajhdlcegeplioahd/4.41.0.4_0/
         permissions = tabs, idle, notifications, contextMenus, unlimitedStorage, webRequest, webNavigation, webRequestBlocking, http://*/*, https://*/*, chrome://favicon/*
optional_permissions = cookies, nativeMessaging, privacy, history
...

New Configuration – `enable_tables` – All Platforms

This new flag allows an osquery administrator to enable only a whitelist of tables. This can be useful in a context in which only a limited set of tables should be exposed to users (typically with live queries).

$ osqueryi --enable_tables 'time,osquery_info'
Using a virtual database. Need help, type '.help'
osquery> SELECT timezone FROM time;
+----------+
| timezone |
+----------+
| UTC      |
+----------+
osquery> SELECT version FROM osquery_info;
+---------+
| version |
+---------+
| 4.2.0   |
+---------+
osquery> SELECT * FROM processes;
Error: no such table: processes
osquery> SELECT * FROM interface_addresses;
Error: no such table: interface_addresses

Work with the experts to craft your open-source security strategy. Contact us to arrange a consultation.

Announcement

What's New in Osquery 4.2

Contents

New SQL Function – `community_id_v1` – All Platforms

New Table – `ntfs_journal_events` – Windows

New Table – `user_ssh_keys` – Windows

New Table – `ssh_configs` – Windows

New Table – `firefox_addons` – Windows

New Columns – `bitlocker_info` – Windows

New Table – `windows_optional_features` – Windows

New Table – `mdls` – macOS

New Table – `docker_image_layers` – macOS, Linux

New Table – `selinux_settings` – Linux

New Table – `apparmor_profiles` – Linux

New Table – `process_open_pipes` – Linux

New Columns – `chrome_extensions` – All Platforms

New Configuration – `enable_tables` – All Platforms

Announcement

Contents

New SQL Function – community_id_v1 – All Platforms

New Table – ntfs_journal_events – Windows

New Table – user_ssh_keys – Windows

New Table – ssh_configs – Windows

New Table – firefox_addons – Windows

New Columns – bitlocker_info – Windows

New Table – windows_optional_features – Windows

New Table – mdls – macOS

New Table – docker_image_layers – macOS, Linux

New Table – selinux_settings – Linux

New Table – apparmor_profiles – Linux

New Table – process_open_pipes – Linux

New Columns – chrome_extensions – All Platforms

New Configuration – enable_tables – All Platforms

Related Content

New SQL Function – `community_id_v1` – All Platforms

New Table – `ntfs_journal_events` – Windows

New Table – `user_ssh_keys` – Windows

New Table – `ssh_configs` – Windows

New Table – `firefox_addons` – Windows

New Columns – `bitlocker_info` – Windows

New Table – `windows_optional_features` – Windows

New Table – `mdls` – macOS

New Table – `docker_image_layers` – macOS, Linux

New Table – `selinux_settings` – Linux

New Table – `apparmor_profiles` – Linux

New Table – `process_open_pipes` – Linux

New Columns – `chrome_extensions` – All Platforms

New Configuration – `enable_tables` – All Platforms