What can we expect to see?

Osquery 4.9.0 is currently in pre-release, so let’s have a quick look at some of the new additions that we are hopefully in store for.

As always, for the complete list of changes, check out the osquery changelog and osquery.io

New features

For Windows users who don’t have a good alternative to osquery’s recommendation to logrotate, osquery 4.9.0 brings a basic logrotate feature for the --logger_plugin=filesystem filesystem plugin.

This feature is disabled by default because it will delete logs when rotation limits are exceeded, and also so as to avoid any possible conflicts if there is another logrotate function enabled. …

Human readable timestamps

Unix timestamps can be confusing for even the smartest Time Lord.

If you are anything like me, and unix timestamps leave you thinking about the mysterious numbers in Lost, you’re going to want to convert them into something more human friendly. Running your timestamp through any number of online converters is one way to go, but it’s a clunky process.

Connect network monitoring with endpoint monitoring.

This article was originally written by Zach Wasserman

Interested in correlating events from network monitoring tools to host activity? Support for Community ID hashing in osquery allows osquery’s endpoint instrumentation to be easily correlated with that of network monitors such as Zeek. Similar strategies can be used to correlate osquery logs with those from other tools that support Community ID. This includes Arkime (formerly Moloch), Suricata, and more.

Community ID

Community ID is a hash of the network connection parameters that allows a connection to be matched between monitoring solutions that support the hash.

To generate a Community ID, a hash is…

Using Elasticsearch and Kibana to visualize osquery performance

This article was originally written by Zach Wasserman

This article serves as a guide to building an osquery performance dashboard with Elasticsearch and Kibana.

Our goal is to build a dashboard like the one pictured below:

Rich process trees on macOS, Linux, and Windows

This article was originally written by Zach Wasserman

Using advanced SQL syntax, it is possible to generate process trees in osquery similar to those generated by the pstree utility. With osquery, the generated trees can be extended to include additional information that can aid analysis.

Below is the basic structure of the query:

WITH target_procs AS (
SELECT * FROM processes WHERE name = 'osqueryd'
WITH recursive parent_proc AS (
SELECT * FROM target_procs
SELECT p.* FROM processes p JOIN parent_proc pp ON p.pid = pp.parent
WHERE pp.pid != pp.parent …

A simple query for IP-Geolocation

This article was originally written by Zach Wasserman

In the event of an emergency or public safety concern, osquery can be easily used to identify employees the direct vicinity, so that teams can push warnings or safety precautions to their staff.

This simple strategy for obtaining the location of an osquery device utilizes the ipapi.co API to retrieve the IP geolocation of the device. Note that the device must be able to connect to the internet over HTTP, and the calculated location may be skewed by VPN, proxies, etc.


SELECT JSON_EXTRACT(result, '$.ip') AS ip,
JSON_EXTRACT(result, '$.city') AS city,

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:

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

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

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

Mike Thomas

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