Title
#general
c

CK Kee

07/05/2020, 8:43 AM
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.
SK

SK

07/05/2020, 3:13 PM
Hey @CK Kee 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 tables web🕸️ using Jupyter Notebooks📓” by Sevickson https://link.medium.com/3sJ1mLrnS7
c

CK Kee

07/06/2020, 3:53 AM
hey @SK! This is really cool! Thanks for sharing!
s

seph

07/06/2020, 6:03 PM
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.
6:03 PM
It makes me think that idea might be a good starting point to review this.
6:04 PM
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?
SK

SK

07/06/2020, 6:04 PM
@seph 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.
s

seph

07/06/2020, 6:05 PM
How are you looking at the data?
6:06 PM
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)
6:07 PM
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
SK

SK

07/06/2020, 6:08 PM
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.
s

seph

07/06/2020, 6:09 PM
That sounds interesting. Like running it somewhere, and looking for table data correlations?
6:09 PM
Very data-science. (As said, I come it the other way)
6:12 PM
I’d absolutely love to see the update. And any thoughts/PRs about improving it all
SK

SK

07/06/2020, 6:14 PM
Yes, writing a blog and hopefully will have it finished next week. Will surely give you a shout out when it is out. 😉