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.

Fleet 3.13 is now available on GitHub and Docker Hub! 3.13 introduces improved performance of the additional queries feature, improvements to the fleetctl preview experience, and more.

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

Improved performance of the additional queries feature

The additional queries feature in Fleet allows you to add host data to the response payload of the /hosts Fleet API endpoint. This feature is helpful when you want to grab specific host information straight from the Fleet API instead of your logging destination. …

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…

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:

Fleet 3.12 is now available! 3.12 offers enhanced host vitals to see which queries are run on which devices, the ability to “refetch” host vitals to get authoritative answers about your devices on-demand, and several awesome contributions from the Fleet community.

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

Which queries apply to a host

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'
)
SELECT *
FROM (
 WITH recursive parent_proc AS (
 SELECT * FROM target_procs
 UNION ALL
 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.

Query:

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

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…