What log destination are people using to make scheduled queries practical? The default host information is fine, but we really want to capitalize on the power of osquery, and I feel at a loss when trying to turn the logs into anything useful... how are people processing the logs and creating dashboards and whatnot? It seems to be this big knowledge gap that I'm tripping over myself trying to figure out.

Hi @Shawn Maddock -From our Premium user base, we most frequently see logs being piped to Snowflake, Splunk, Kafka, and Elastic. A few folks also just going straight from Firehose-> S3 -> either a PowerBI or Tableau

@Shawn Maddock the freedom from "data entanglement" is one of the great differentiators of Fleet. As Zay mentioned, you can pipe the query results data pretty much anywhere via the logging plugins that are supported (https://fleetdm.com/docs/using-fleet/log-destinations). Other options include direct API based data extraction, for which you'd have to develop a middleware layer to do some form of ETL on the data stream you're interested in. The REST API docs can be found here. If you opt to go that route, leveraging the automation webhooks are very useful. A really really simple dashboard with plotly and flask. Also, check out piping data to Splunk. I also know @tlark has written a great blog post on this topic as well.

We’re already pulling data via the API into our documentation platform every 20 minutes, but that’s limited to what Fleet is already aggregating. I don’t have a problem getting the logs to somewhere, it’s more a question of what do I do with the logs to turn it into queryable data like we have with the API. I’ve used Splunk in the past, it’s great but expensive. I asked last year about the Grafana family of products but didn’t seem like anyone here had gone that route. Sorry I’m not being succinct, like I said I’m struggling to even know what to ask.

Ah, thanks for the additional context, that helps! Checkout this endpoint.

WHOT.

also, on the grafana topic, I know @Mathias Palmersheim had crafted a cool project to merge data coming from the API and scheduled queries into a single grafana dashboard.

The logs are retrievable via the API???

It's not the logs, but any query result you build with automations enabled. The only limitation right now is that we store up to 1000 records only.

Ahh… so with 500 endpoints, that’s the two most recent queries only

yup

I knew my question was a little out of scope of Fleet, which is why I asked here and not through support. I’d love to see and maybe be able to build off @Mathias Palmersheim ‘s project.

https://gitlab.com/shiftsystems/fleet-ansible/-/blob/main/roles/fleet-server/files/fleet-yeet.py and https://grafana.com/grafana/dashboards/18690-fleet-dm/

for making the data actionable, sharable, and extendable we use our product (disclaimer: I work for snowflake) to build data models. From there those data views in Snowflake can be joined to any other tables/views like you would in any relational database. I wrote a basics of data modeling in a more recent blog post here: https://t-lark.github.io/posts/osquery-data-in-snowflake/ I just got done data modeling the process events table for macOS which I will be writing another blog post on in the future when I have time

We write result and status logs to disk on fleet servers, then ship them over to an ELK instance using filebeat -> redis -> logstash -> profit.

ope I see someone already linked that one

Yeah we were looking at maybe using Kafka as a data “lake” intermediary to feed Loki but that seemed overkill. Our internal automation system is functioning how you’re describing: we have our internal data model for storage, and it maps any external source’s model to ours. So far we’re only interacting with MySQL, Redis, and a few flavors of APIs. Interacting with logs is next

my only advice is to get maximum return off of your fleet/osquery data ship the data to a spot where you can join it to security data, HR data, systems data, and so forth because the return on that is so much more higher than not having that level of data. For example, I oftentimes use the following data sets in almost all of my models: • HR data (think employee directory data) • Security agent telemetry (last successful data submission) • MDM data telemetry (for sys, and ops data) • ServiceNow Asset data (what human owns what device and what is that device function - tester, primary, secondary, loaner, demo system, conference room, etc) • osquery data (all the sweet data you get from osquery) that way you can do things like filter contingent workers vs FTEs, the sales intern versus a sr principal cloud engineer (who has access keys to the kingdom), leave of absence folks (the systems aren't patched because they are on LOA), asset type/function (digital signage mac minis are low priority and low risk vs end user computers), we also track things like app usage and revoke licenses for cost savings on apps that are expensive and not being used, etc

It’s a tough problem to solve. When I was in the GRC space, I wrote about it here: https://www.linkedin.com/pulse/using-acl-robotics-galvanize-harmonize-disparate-asset-dave-herder?utm_source=share&utm_medium=member_ios&utm_campaign=share_via

We’re an MSP so we don’t typically have access to HR data beyond what we provide via IAM, but all that other data we are currently ingesting… except for osquery 😅

another advantage to Snowflake for a MSP model would be you could securely share that data back to your customers via Snowflake and not give them any direct host access, then upsale the MSP contract for that premium service

Idk if any of our customers would want the raw data, but we do share it back in a UI. And my boss is pretty set on FOSS Loki/Prometheus/Grafana if it is feasible, which it seems like it is.

gotcha well this would be shared modeled data not raw but I feel ya on the FOSS best of luck my friend and hope to see a blog post or conference talk in the future of what you build

Thank you again!

np this is what communities are for

If I can give my two cents on this discussion: Currently my Fleet deployment uses the filesystem as the destination for the result logs and I have a fluentbit that reads the log file from a tail and rotates this file every defined period and sends these logs to a Loki instance where we can query through Grafana to create alerts and other visualizations. As I use Fleet from a security perspective, I try to focus the detection part on the queries themselves, I have used a lot of the queries available in osquery-defense-kit for this.

Ooh, thanks @Saulo Guilhermino ! Why fluentbit over promtail?

This happened because before we didn't use grafana to store the logs, it was all an ELK stack. So our SRE team decided to switch to the Grafana stack and it turned out that we only needed to change a line or two in the fluentbit configuration, so we went the path of least effort 😅

No that is a wise decision! I had not considered using osquery on detection (even though Fleet has built in ticketing integration 🤦‍♂️ )… will definitely be exploring the repo you linked. Thank you!

No problem! The points raised by other community members in this thread were also very enlightening for me

@Shawn Maddock to clarify, the Fleet query report limit of 1000 rows is per query. reports only store the latest data until that limit is reached, so at 500 hosts, you should consistently have the latests host data if the query returns a single row per host.

So how many reports are accessible?

there is no limit on amount of reports, but I would keep an eye on MySQL performance. Depending on frequency and amount of queries, that could be a lot of write activity.

I guess I need to revisit automation queries vs. "normal" logged queries

query reports are a fairly new feature, and to note they only support snapshot queries

Back at this, trying to get the latest osquery results displaying in a Grafana table panel of devices. The two obvious paths I see without using a slick intermediary like Snowflake or Splunk is to either ingest snapshot query logs directly into Loki, or write a custom parser to parse the query logs into a MySQL database. The Loki path has fewer custom parts, I'd just have to learn LogQL, the MySQL path would be vastly more efficient but require custom dev. 🤔

That's what I do ☝️

Which, the Loki or the MySQL?

The loki path

we have another team here that dumps osquery logs locally, and then uses filebeat to ship them as logs to a logstash server

I only care about the most recent result for each device, not time-series which is what Grafana Labs loves. But I want to cache the latest result in case a device is offline when a scheduled query runs.

or dump it into Elastic and just use ELK

I was thinking of logstash as a slick intermediary like Splunk. Something that had a better understanding of logs and deltas.

I haven't touched MySQL in a good solid 6 ish years but it can store native JSON data now

MySQL, MS SQL, PostgreSQL, whatevs, not picky

the only thing I would say, is think about the end state of the data, and if you want to build that data up front or you want to do it post ingest down stream. Meaning, I keep my osquery queries simple enough in FleetDM and do all the heavy lifting ETL side post ingest. This allows me to keep osquery query libraries functionally simpler and with less joins/cross joins on the client side

Good point. I guess there are three points of ETL... osquery, osquery logs->data store, and data store->grafana. All three have some sort of query language.

yeah I did not really come to that realization until I was starting to build a complex query in fleetDM and then decided to not do that and just transform it downstream in the ETL tool itself. Then I realized I can just do this in general and keep the compute costs lower on the endpoint with functionally simpler queries

Are the endpoint compute costs noticeable?

not yet for us, but lets just say I work with lots of smart "tech detectives" that will monitor the process and not like it if compute spikes

ah

and on end user devices we cannot really predict what the end user will do

Good call... we also use JumpCloud, who push their own flavor of osquery to endpoints, and definitely ran into that spike when they had a poor performing query

yeah and if you have a good data tool you are shipping osquery results too, you can keep the osquery side on the simpler/lighter side and do the heavy compute data modeling and such on the data infra side

I think if it was just me collecting data I'd fully go your route of minimal osqueries and heavy server-side compute, but thinking about having team members who are more code-phobic contributing, it'd be easier for them to only have to learn one query lang (osquery)... but that has risks... good things to think about and I have enough to take to my boss

I didn't really even think of this until I first started to craft some complex osquery queries then I was like let me just

select * from osquery-table-here

and then do all the data modeling and transformation downstream

That's definitely pushing me in the custom data store direction, though, because fleet-wide snapshots of

*

queries is gonna be so much storage

yeah it is what we do because we are a data company. So we have "cheat codes" turned on I guess

⬆️ ⬆️ ⬇️ ⬇️ ⬅️ ➡️ ⬅️ ➡️ B A Start

hahaha the contra data code