Proper use of JOIN to return osquery data for users

This article was originally written by Zach Wasserman

Many an osquery user has encountered a situation like the following:

$ osqueryi
Using a virtual database. Need help, type '.help'
osquery> SELECT uid, name FROM chrome_extensions LIMIT 3;
+-----+--------------------------------------------+
| uid | name                                       |
+-----+--------------------------------------------+
| 501 | Slides…

No sooner had we posted our write-up last month on osquery 4.7.0’s new features than osquery 4.8.0 pre-release was launched. Now that it’s released officially, let’s take a quick look at what’s been added.

For Orbit users, osquery 4.8.0 is now available in the edge channel.

If you would like to see the full list of updates, be sure to check the osquery changelog.

Bug fixes

Osquery 4.8.0 welcomes a handful of bug fixes. A couple worth noting here are:

• Handle events optimization edge cases. A bug was fixed in the events optimization code for when events tables had multiple subscribers.
• Guard…

Fleet 3.11.0 is now available and more powerful, with improved performance, support for software inventory, and enhancements to running common queries.

Let’s jump into the highlights:

• Improved performance
• Software inventory
• Running common queries

For the complete summary of changes and release binaries check out the release notes on GitHub.

Improved performance

Fleet is utilized by organizations with up to hundreds of thousands of endpoints. As a result, we’re constantly looking for areas to improve performance. Changes introduced in Fleet 3.11.0 reduce the MySQL CPU usage by ~33%.

These performance improvements are the result of batching the updates of the last time a…

What is Orbit?

Orbit is an osquery runtime and auto-updater. Orbit eases the deployment of osquery connected with a Fleet server, and is a (near) drop-in replacement for osquery in a variety of deployment scenarios — with or without the use of Fleet.

For documentation on Orbit beta, check out: https://github.com/fleetdm/orbit

Why use Orbit as part of your deployment strategy?

In a production environment, it’s not always trivial to deploy software to your servers laptops, and workstations. …

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' ||…

Fleet 3.10.0 is now available and more powerful, with the beta for agent auto-updates to better manage osquery updates on your hosts, Identity Provider-Initiated Single Sign-On for increased login flexibility, and improved server logging.

Let’s jump into the highlights:

• Agent auto-updates beta
• Identity Provider-initiated Single Sign-On
• Improved server logging

For the complete summary of changes and release binaries check out the release notes on GitHub.

Agent auto-updates beta

Updating the osquery version on your hosts helps reveal new capabilities and information from your fleet, but managing updates can be challenging. …

By now, you’ve no doubt already heard of Microsoft’s big email hack.

While attackers initially flew largely under the radar via an unknown vulnerability in the email software, the folks at Volexity observed a handful of post exploitation activities and tools that operators used to gain a foothold — one such tool being ProcDump, which attackers were observed using to dump LSASS process memory.

As a possible detection method using osquery and Fleet, check out this query from Recon InfoSec that looks for systems that accepted the ProcDump EULA. …

We’re psyched to announce the release of Fleet 3.9.0 which includes a configurable host identifier, fresh upgrades to the UI, and more!

Let’s dig into the highlights:

• Configurable host identifier
• Upgrades to the UI

For the complete summary of changes and release binaries check out the release notes on GitHub.

Configurable host identifier

Fleet 3.9.0 focuses on stability and responding to feedback from customers and the osquery community. Recently, several Fleet users encountered a scenario in which hosts using the same identifiers would cause enrollment issues with Fleet.

To help solve this enrollment issue, we’ve introduced a configurable host identifier.

By default, Fleet’s…