Building atop Osquery. Compliance, monitoring, threat hunting and auditing.

Remote management of Osquery

This article assumes you are making use of Osquery’s remote API to manage and query numerous assets running the Osquery daemon.

Inventory management

Given Osquery’s broad (and ever-increasing) number of virtual tables, many of these tables used to build up a comprehensive view of your IT estate.

Information captured from systems using Osquery

System information

Osquery provides access to several tables relating to various aspects of system information. The following system queries will run across all platforms.

SELECT uuid, hardware_serial, hostname, cpu_subtype, cpu_brand, physical_memory, hardware_vendor, hardware_model FROM system_info;
SELECT name, version, build, platform FROM os_version;

Processes and process events

We published a previous post on this which covers collecting process information from the processes table and the corresponding application hashes as well as event information from process_events table. However, I want to cover a few neat tricks taking advantage of the SQLite engine used by OSquery.

WITH RECURSIVE rc(pid, parent, name) AS (
SELECT pid, parent, name FROM processes WHERE pid = 55334
UNION ALL
SELECT p.pid, p.parent, p.name FROM processes AS p, rc
WHERE p.pid = rc.parent
AND p.pid != 0
)
SELECT pid, parent, name FROM rc LIMIT 20;
Process tree recursion
4798: A user's local group membership was enumerated.

Installed Applications

Understanding whats installed across your IT estate is useful to identify licensing issues (Mixing installed applications with running processes you can also work out the amount of time spent per application), un-patched applications or applications that haven’t been authorized to be run.

Showing application information for Mac OS X
SELECT name, version, identifying_number FROM programs;
SELECT name, install_location FROM programs WHERE install_location NOT LIKE 'C:\Program Files%'
SELECT name, release, sha1 FROM rpm_packages;
SELECT name, version, source FROM deb_packages;
SELECT name, baseurl, enabled FROM yum_sources;
SELECT name, base_url FROM apt_sources;
Package vulnerability data
SELECT bundle_name FROM apps;

Security information event monitoring (SIEM)

Osquery offers several tables for collecting events for monitoring system security. There are a lot of neurosciences for collecting system events with Osquery and certian command line flags are required. Though before we look at the avaliable event tables I want to quickly look at the last table for both Linux and Mac OS X. Which in most use cases will be sufficient.

Using the Osquery last table to gather user logins

Linux and Mac OS X login information with Osquery

The last table in Osquery provides login and logout events for Linux and Mac OS X systems whilst providing the process id and the connecting host.

SELECT username, p.name AS process,
DATETIME(time, 'unixepoch') AS datetime, host FROM last AS l
LEFT JOIN processes AS p ON p.pid = l.pid
WHERE host <> '' AND host NOT LIKE ':pts%'
ORDER BY time DESC;
AND host NOT IN (':1', '127.0.0.1')
Using Osquery for EDR

System logs with Osquery

Osquery provides virtual tables for Linux syslogs syslog_events, Mac OS X Apple system logs asl and Windows event logs windows_events .

Osquery events

For the next set of tables, you’ll need to enable Osquery events with the command flag --disable_events=false . Osquery will dump logs into its local database. To prevent table bloat you can set the following flags,
--events_expiry=3600to expire events after a given time in seconds has passed and--events_max=50000to remove older events falling outside a given row limit. The flag--events_optimize=true can also be set as Osquery will create “removed” records for expired events as per Osquery’s diff engine. When a remote query is executed only “added” events will be returned.

Linux syslog

Osquery will read syslog events from a system pipe. Both rsyslog or syslog-ng can be configured to write to a system pipe in a format Osquery can read. Configuration steps can be found here. Once you’ve configured the syslog daemon you’ll need to set the additional command line flag --syslog_pipe_path=/etc/osquery/syslog_pipe . Once done you’ll be able to query the syslog_events table like so:

SELECT * FROM syslog_events LIMIT 5;

Apple system logs (ASL)

ASL does not require any additional configuration of Osquery. You can simply query your Apple system logs like so:

SELECT * FROM asl ORDER BY time LIMIT 5;

Windows event logs (WEL)

Windows event logging requires no additional system configuration. Just add --windows_event_channels=System,Application,Setup,Security to your Osquery flags file. Be aware that applying all these channels to a server either ingesting logs from other servers or an Active Domain controller will result in a huge amount of data being both written locally and sent back to your Osquery server.

SELECT * FROM windows_events WHERE source IN ('Security') AND eventid IN (4625, 4634, 4647, 4672) LIMIT 5;

Network events

Osquery provides several tables to access network connections across Windows, Linux and Mac OS X. There are really cool uses for network event data, not least what you’d do with standard netflow logs. However, Osquery can provide a little more context such as the originating application for the network event which can be married against existing netflow logs or for EDR incidents as shown in the screenshot below.

Query to grab network events
SELECT processes.pid, processes.name, remote_address, remote_port FROM process_open_sockets LEFT JOIN processes ON processes.pid = process_open_sockets.pid WHERE remote_address <> '' AND remote_address != '::' AND remote_address != '127.0.0.1' AND remote_address != '0.0.0.0' AND remote_port = 443 LIMIT 10;
SELECT action, pid, local_address, local_port, remote_address, remote_port, family, protocol, path, time AS timestamp 
FROM socket_events
WHERE remote_address <> "" AND remote_port != 0 AND pid > 0;
SELECT DISTINCT "CONNECT" AS action, pid, local_address, local_port, remote_address, remote_port, family, protocol, path, NULL AS timestamp
FROM process_open_sockets
WHERE remote_address <> "" AND remote_port != 0 AND pid > 0;

Geo-location with WiFi surveys

Ever wondered where your Mac OS X devices are? Well, Osquery has two really interesting tables for you. Firstly, the wifi_status table will let you see what WiFi networks a device is connected to. A router will actually broadcast its country_code which can be used to finding the rough location of a device. This data is useful for comparing this against Google G-suite logins or other single-sign-on (SSO) providers to verify authentication requests.

Data loss protection (USB device monitoring)

A really cool application we built using Osquery was to monitor for USB devices being plugged into a machine and then monitoring for files being either copied to or from that USB device using File integrity monitoring (FIM). This application of Osquery can somewhat provide a basic data loss protection.

Monitoring for USB device events

File integrity monitoring (FIM)

Firstly, file integrity monitoring (FIM) uses inotify (Linux) and FSEvents(Mac OS X) to monitor files and directories for changes. As files/directories are written, read and deleted events are created. Osquery will also provide (where appropriate) a hash of the file as well. Which can be used to identify sensitive files such as key-chains. Osquery can update its FIM configuration on the fly via the remote API. Wildcards for directories can also be used.

# osq.conf
{
"options": {
},
"file_paths": {
"homes": [
"/Volumes/%%"
]
}
}
sudo osqueryi --disable_audit=false --verbose --disable_events=false --config_path ./osq.conf

USB devices

There are two tables we need for this next part disk_events and mounts this will help us find newly inserted USB devices and most importantly their mount point so that we know what files are being copied to which device.

SELECT action, DATETIME(time, 'unixepoch') AS datetime, vendor, mounts.path FROM disk_events LEFT JOIN mounts ON mounts.device = disk_events.device;
SELECT action, uid, SUBSTR(target_path, 18) AS path, SUBSTR(md5, 0, 8) AS hash, time FROM file_events WHERE sha1 <> '' AND target_path NOT LIKE '%DS_Store';

Compliance

Well done for getting this far and whilst compliance isn’t the sexiest of security topics Osquery does make compliance a breeze. In the screenshot below each cube represents a compliance check executed against a collection of systems. Red being zero or little compliance against the tested systems and green representing most or all systems comply.

Screenshot showing compliance checks for several CIS frameworks

Windows registry

Osquery provides access to the Windows registry via the registry table. In Windows you can use the regedit.exe tool to inspect your systems configuration as well as check that executed queries are working correctly. This can also be used to check the vast majority of Group Policy objects (GPOs) within the Windows ecosystem.

SELECT
COUNT(*) AS passed
FROM
registry
WHERE
key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\GPExtensions\{D76B9641-3288-4f75-942D-087DE603E3EA}'
AND name = 'DllName';

Mac OS X Plist

Osquery can parse Apple plist flies (used to hold both system and application configuration information) via the plist table. As an example, we’re going to take the “CIS 1.5 Enable system data files and security update installs” compliance check. Which checks to ensure critical updates are automatically installed.

SELECT
CASE WHEN SUM(passed) = 2 THEN 1 ELSE 0 END AS passed
FROM (
SELECT key, value AS passed
FROM plist
WHERE path = '/Library/Preferences/com.apple.SoftwareUpdate.plist'
AND (
key = 'ConfigDataInstall'
OR key = 'CriticalUpdateInstall'
)
);

Linux & Mac OS X System controls

Osquery can use system_controls provide access to sysctl names, values, and settings information. As an example, we’re going to create a compliance rule for the “CIS 3.1.1 Ensure IP forwarding is disabled” rule.

SELECT COUNT(*) AS passed 
FROM system_controls
WHERE name = 'net.ipv4.ip_forward'
AND current_value = 0 AND config_value = 0;

Augeas

One of Osquery’s lesser-known tables but very cool nevertheless, augeas will automatically parse known file types. In this example we’re going to check that the sshd_config file is configured to useProtocol 2 for SSH communication as per: “CIS 5.2.2 Ensure SSH Protocol is set to 2”

SELECT COUNT(*) AS passed FROM augeas WHERE path = '/etc/ssh/sshd_config' AND label = 'Protocol' AND value = '2';

Carves

There are some file formats that augeas cannot parse. Osquery carves table lets you fetch files from the system which you can then parse remotely. For example we can fetch all the config files from /etc/ . To make use of the carves table, you need to add a few other command-line arguments. We’ll cover this in more detail in a future blog post.

SELECT * FROM carves WHERE path LIKE '/etc/%' AND carve=1;

A few honourable mentions

As an example, of a few other Osquery tables you can use. The Mac OS X alf table lets you check the configuration of OS Xs application layer firewall (ALF) service details. The following query will ensure as per “CIS 2.6.4 Enable Firewall Stealth Mode” that stealth mode is enabled.

SELECT stealth_enabled AS passed FROM alf;
SELECT CASE WHEN COUNT(*) = SUM(encrypted) THEN 1 ELSE 0 END AS passed 
FROM user_ssh_keys;

Closing

Lastly, I just wanted to cover a few of the things that we’ve been building atop all of this data to help automate security operations. You can really build quite a comprehensive view of your systems and cybersecurity posture. Helping grade risks to your IT estate and group those risks to understand areas of improvement. At Zercurity the results we’re interpenetrating, can not only be weighted be also add a cost of implementation added to help identify quick wins when improving your cybersecurity maturity as well as monitoring that progress week on week to provide reporting.

Workflows & User-focused security (USF)

We’re also using workflows, heavily building off of AWS Lambda functions and AWS state machines to hook Osquery events. Triggering actions whether that be an email notification, JIRA ticket, slack message or any other third party integration. The possibilities here are endless. We’ve also been using Osquery to identify system users. Mapping this data against Active Directory and Google G-suite to push compliance and other system configuration alerts back onto users. Making them aware both of the risks to their system and corperate policies but also how to fix it. In the example above around USB devices. A user can be notified of a new device that’s been plugged in. They can then either acknowledge this new device as their own or escalate the ticket. We can also check to ensure its encrypted and if not provide instructions on how to correctly setup the device.

Automated recommendations and maturity tracking

We’ve also been working to have Zercurity automatically raise issues and track changes providing a complete audit trail. Moreover, Zercurity can find common issues across systems and raise this as a company-wide security goal which can be actioned based on the level of effort required. We’ve been providing these bi-weekly objectives to help companies improve their security posture whilst ensuring coverage of the major cybersecurity themes.

Conclusion

Osquery is the Swiss army knife for cybersecurity teams. There is a whole lot you can do. Osquery can perform as an EDR, Compliance tool, audit system and let you query machines to both preempt threats and find them. A very modern and powerful tool for IT administrations and security teams.

Footnote

Zercurity is a cybersecurity platform aimed to help companies understand their cybersecurity posture and help them improve their cybersecurity hygiene proactively through remediation advice and user-focused security recommendations to find out more visit us at https://www.zercurity.com.

--

--

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