Osquery uses basic SQL commands to leverage a relational data-model to describe a device.

osquery

hi there! I am noob to osquery. Is there a ER (entity relationship) diagram for osquery schema, much like in traditional RDBMS? That will help understand how the tables are linked together and what info can be queried.

Hey <@U0177K6JVKJ> wrote this blog about the relationships, this might help you on your way. Working on the next more accurate version at the moment.
 “Untangling the Osquery:question: tables web:spider_web: using Jupyter Notebooks:notebook:” by Sevickson <https://link.medium.com/3sJ1mLrnS7|https://link.medium.com/3sJ1mLrnS7>

hey <@UFD00523D>! This is really cool! Thanks for sharing!

That’s really neat looking. But also a little misleading. osquery tables are (generally) light wrappers around underlying os apis. What a given column name means is table specific. And frustrating, some names are divergent that shouldn’t be, while some are the same that also shouldn’t be.

It makes me think that idea might be a good starting point to review this.

FWIW I generally approach this the other way — for a given piece of OS data, what _other_ data would want to join. And knowing that, is there a table for it?

<@U7QP20JQH> You have good points that I also stumbled upon as we speak I am working on a more correct approach by looking at the data and not the column name. In my blog I also have some improvements written.

And if you haven’t found it yet, the table schemas are translated into json for the web site. <https://github.com/osquery/osquery-site/blob/source/src/data/osquery_schema_versions> (updated with correct url)

If you come up with something neat, I’d be interesting to think about how to improve the web site’s schema browser. Or under take some larger name normalization

Screenshot_2020-06-26 8501f06c6b074afe99161274c2b73750 - Graphistry's Graph Explorer.png

By running Osquery on different kind of systems I get a baseline of possible connections dependent on OS. Attached is what I have till now. I can send you a link later on after I fixed some issues.

That sounds interesting. Like running it somewhere, and looking for table data correlations?

Very data-science. (As said, I come it the other way)

I’d absolutely love to see the update. And any thoughts/PRs about improving it all

Yes, writing a blog and hopefully will have it finished next week. Will surely give you a shout out when it is out. :wink: