Hey! I was wondering why scheduled queries in “diff” mode are not stored in the database? diffs offer lower bandwidth and less updates, while still keeping track of the current state of the machine. why can’t we use them instead of snapshots? thanks 🙂

Morning Amit, Great question. From my understanding so far (still newish, so could be a little off here), but Fleet prefers to stream this to an external log destination. A good reason for this is efficiency and scalability. Reconstructing a full timeline of diffs each time is likely going to be more resource intensive than a single snapshot of data, right? When you're looking at the query results, you want to see the current/latest state. Not necessarily every step it took to get there? If we take the OS system information scheduled query. I want to see the current version straight away. There's also a line in our FAQ.

but the bulk of the data flows directly into a tool like Splunk or ElasticSearch. You can send that information to any of Fleet's supported log destinations.

Hopefully I understood your question correctly 🙂.

Good morning Gray 🙂, thanks for looking into this I think i’ll use an example to clarify what I mean: Say I want to know which certificates are installed on my machines. I can set up the following scheduled query to run every 12 hours:

SELECT * FROM certificates;

now I can set it to snapshot mode which will dump all the 1k+ certificates metadata information from each host (tens of thousands of hosts). This will use a lot of bandwidth while there are small, if any, changes to this data over time. On the other hand, if I collect this in diff mode, I get small pieces of updates whenever a new certificate is installed or removed. Instead of being dumped with the whole snapshot every 12h. I can add the diff to my DB, in which I’ll have the current state. So in essence, my DB data will store the current state/snapshot, while having endpoints sending only changes to the current state. This is leaner, resources-wise. What am I missing here? 🤔

Aha, I'm with you. And I'll be honest, I don't have the answer to that one for you. Someone smarter than me is going to have to chime in later today, I think! 😄

No worries 🙂 I appreciate the help I think it has to do with data integrity. It’s hard to build a synchronized system where you can trust that the local state and remote state are in sync. it’s totally possible but with the basic diff engine of osquery that is not full sync implementation. if you miss one diff message, the whole state is corrupted, etc

There's an interesting part here: https://osquery.readthedocs.io/en/stable/deployment/logging/#special-top-level-fields > To begin with, if the logs are stored in a log management system of some kind, it becomes difficult or impossible to identify which log results are from the initial run of the query, and which ones are differentials to the initial results.

That’s interesting! totally missed it. thank you