https://github.com/osquery/osquery logo
#general
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.
s

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!
😉 1
👍 1
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.
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?
s

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?
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
s

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?
✔️ 1
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
s

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. 😉
2 Views