6 ms·
Osquery: An sqlite3 virtual table exposing operating system data to SQL
- jeffbee 3y agoOsquery: a privileged daemon written in an unsafe language that allows your junior sysadmins to take down all your machines at once or cause mystery performance blips that someone else has to diagnose for a year before they figure it out. First-person testimony.
- signa11 3y agoif possible, may you please elaborate, that would be so useful. thank you !
- VWWHFSfQ 3y agoYou should explain what you're talking about if you're going to publicly trash somebody else's project > allows your junior sysadmins to take down all your machines at once or cause mystery performance blips that someone else has to diagnose for a year Sounds like you have extremely poor administrative management of your computer systems.
- jeffbee 3y agoI don't feel like I need to be nice to Facebook. The raison d'etre of this thing is to allow interactive ad hoc exploration of large-scale systems. It is, in other words, a thoroughly bad idea.
- convolvatron 3y agothere may be problems with the implementation, or its usage, but I don't see why that's fundamentally a bad idea at all
- mikermcneil 3y ago(used in production directly or via major EDRs by much of silicon valley, governments, and the F1000) Contributions: https://github.com/osquery/osquery https://github.com/osquery/osquery
- mikermcneil 3y agoosquery after Meta: https://devops.com/the-linux-foundation-breathes-new-life-into-osquery/ https://devops.com/the-linux-foundation-breathes-new-life-in...
- deleted 3y ago[deleted]
- formerly_proven 3y agoOS state introspection can get expensive quick and often has a performance impact beyond the process doing the sampling, because you're locking tons of kernel structures to read from them. Case in point, a simple "select * from processes" takes a solid 3 seconds of kernel time on my laptop. Now you might say, "well that's clearly a dumb idea because osquery certainly relies on vtab's colUsed field to avoid querying all sorts of expensive stuff when it doesn't have to so you really should only query what you need" and that's of course 100% true. But it's also a senior developer thought. Easy to see how an inexperienced person might make mistakes like this with any one of the dozens or hundreds of tables offered by osquery and cause performance issues. In terms of security, well it is clearly a kitchen sink project (there's a prometheus client in there, for example: https://osquery.io/schema/5.11.0/#prometheus_metrics https://osquery.io/schema/5.11.0/#prometheus_metrics), so there's a huge breadth of interfaces it talks to and files controlled by all sorts of people it parses, and the default does seem to be privileged usage, which is the general ballpark where AV engines and their highly dubious track record live.
- mikermcneil 3y agoSome example queries: https://fleetdm.com/queries https://fleetdm.com/queries The formula osquery uses to report performance: https://github.com/fleetdm/fleet/issues/16123 https://github.com/fleetdm/fleet/issues/16123
- iJohnDoe 3y agoOr they are a righteous developer who likes to trash people outside of development. It was a popular fad for a while, which worked both ways, such as, “Developers think they are experts in everything but couldn’t figure out how to plug in a computer.”
- foobiekr 3y agoEveryone who has experienced osquery in production when mandated from above and used by less qualified admins knew exactly what he was getting at and the simple truth of it.
- w0de0 3y agoI’ve used it. At large enterprises with sophisticated deployments. I disagree.
- dherder 3y agoosquery has a huge span of use cases and has deep roots in system native apis, checkout a recent development in file access monitoring with osquery: https://material.security/blog/protecting-the-security-and-privacy-of-our-customers https://material.security/blog/protecting-the-security-and-p....
- mikermcneil 3y agoHaving paved roads helps: https://github.com/fleetdm/fleet https://github.com/fleetdm/fleet
- anotherhue 3y agoHaha, yeah I've seen this. Highly tuned production server? No match for a junior security admin whose only tool is a hammer. It really really messes with windows defender too.
- w0de0 3y agoThe senior sysadmins neglected to configure osquery’s client performance safeguards and apply properly scoped access (observer mode!) on the server. Systems administration is the art of protecting everyone with less access from footguns while still and especially enabling their use of effective tools. First-person testimony.
- mikermcneil 3y agoFeel your pain! This was the idea behind query performance "bubbles" in Fleet. (Uses the osquery performance metadata to indicate whether performance impact of a query on a host is "minimal", "considerable", or "excessive") Next step is "clairvoyance", where we predict perf based on the SQL when you're in the query editor
- WhatIsDukkha 3y agohttps://osquery.readthedocs.io/en/latest/deployment/performance-safety/ https://osquery.readthedocs.io/en/latest/deployment/performa... Seems pretty straightforward as a start.
- wkat4242 3y agoI like osquery. Airwatch the Endpoint management system was working to incorporate this. It was pretty good. Unfortunately they were acquired by VMware which tried to maneuver the product into pretty unrelated VDI tech (Horizon). And of course VMware is completely in the shitcan now with everything being discontinued. We moved to Intune in the end. Which was not better by the way (nobody buys Microsoft because they're great, just because of the network effect). But it does have better long term outlook (no pun intended). But it was a good product while it lasted. The osquery integration was really useful for custom scripts. AirWatch had linux support 4 years ago even though Intune is only starting with that (and it hardly works).
- w0de0 3y agoDeploy your own, stand-alone osquery instance - it’s open source. Airwatch/Workspace One was a terrible product even before the acquisitions. You may want to try FleetDM, an MDM product that deeply integrates osquery (an intersecting set of people are responsible for the two).
- wkat4242 3y agoI don't agree, I really liked airwatch and we managed tens of thousands of mobiles with it. It had its issues but every product does. The problem was that when we had to move to Intune some features we used in airwatch weren't even supported yet! It really made me laugh when Gartner put Intune in their magical Quadrant but not airwatch as Intune wasn't even feature complete at that point for basic mobile uses. I'm sure those Gartner guys just talk to the sales suits but don't actually try the products. But now we're stuck with Intune due to decisions made at top level.
- w0de0 3y agoWhen I used it, I was managing mostly Macs - and I never used it pre-vmware. This probably encouraged my flippancy! Is there even an established, reliable alternative to intune for Windows? I don’t know.
- lolinder 3y agoDefinitely a tangent, but it's always interesting to see which of {a,an} people use for SQL and derivatives. It never occurred to me until today to pronounce "sqlite" as anything other than "sequelite".
- simonw 3y agoD. Richard Hipp pronounces it Ess-Queue-El Ite.
- quickslowdown 3y agoWhen I have to say SQLite out loud, my brain decides in the moment between "ess-cue-lite" and "see-cue-lite." I have no idea what criteria/heuristics I use in that moment to decide which way to pronounce it, but for whatever reason that's how it works for me. In my head I feel like I say "ess-cue-lite" pretty much every time.
- chrisweekly 3y agohuh, I get the "ess-cue-lite" but not (at all) the "see-cue-lite" -- for me, the "see" sound only makes sense in the context of "sequel" (see-kwell-ite), which is how I instinctively tend to pronounce it.
- quickslowdown 3y agoTo confound you further, I say "sequel" and not "ess-cue-ell" for SQL
- smaudet 3y agoI think the former pronunciation is more correct, but... I see the point of the later if you are prone to verbalize acronyms: SQL => Verbalized Acronym: "See-Quell" vs Structured Query Language: "Ess-Queue-Ell" So then See-Cue-lite is a concatenation of the verbalization with the "lite". Whereas Ess-Cue-Lite is a concatenation of the acronym pronunciation with the "lite". This probably solve the former question of why some people say both and don't understand why. If the conversation/person is saying "SeeQuel" and "Sass" or "less" etc. then "See-Cue-Lite" fits that dialect. In a dialog with a lot of acronyms e.g. SQS, VPN, AWS, etc. you are probably more likely to go the acronym route i.e. "Ess-Cue-Lite". The term itself is a bastardization of an acronym and the "lite" term, I'd argue there's no correct pronunciation.
- woodruffw 3y agoosquery is a cool project, with a lot of outstanding issues. It has a great deal of technical debt, including performance and security debts that don't receive adequate attention. It also has a huge user community around it, but only a handful of active recurring contributors and companies actually funding development on it (and, even then, the bulk of the development is feature work rather than debt burndown). (Source: I worked on osquery's core and tables for 2-3 years for various clients, and my company employs several of the current maintainers.)
- PenguinCoder 3y agoAgreed with that first statement. Some of the things osquery does especially with regards to Linux disk space usage, is frustrating to deal with.
- mikermcneil 3y agoWhat did you run into on Linux?
- PenguinCoder 3y ago> Linux disk space usage reporting issues
- mikermcneil 3y agoIn https://fleetdm.com/tables/mounts https://fleetdm.com/tables/mounts ?
- neodymiumphish 3y agoIt’s also the base layer for Sophos’ EDR platform.
- woodruffw 3y agoIt's the base layer for a lot of EDRs :-)
- lopkeny12ko 3y agoHuh, I thought osquery was a Facebook open source project. Surprised to see it under the Linux Foundation now. I wonder if Facebook still uses osquery in its current form or if they re-forked their own project to evolve separately in-house.
- woodruffw 3y agoI don't know the exact dates, but Facebook turned osquery over to the community back in 2019 or 2020, after a period of uncertainly over (reasonable!) conflicting development interests[1]. It became a LF hosted project sometime after that. (I don't know whether they still use it in-house.) [1]: https://blog.trailofbits.com/2019/04/18/announcing-the-community-oriented-osquery-fork-osql/ https://blog.trailofbits.com/2019/04/18/announcing-the-commu...
- mikermcneil 3y agoHistory (tldr): https://fleetdm.com/handbook/company#history https://fleetdm.com/handbook/company#history There’s also a podcast episode on it with two of the creators: "The story behind the creation of osquery" https://fleetdm.com/podcasts/the-future-of-device-management-ep1 https://fleetdm.com/podcasts/the-future-of-device-management...
- ciupicri 3y agoFrom https://osquery.io/blog/osquery-foundation https://osquery.io/blog/osquery-foundation > 5 years ago > Today we are excited to announce the creation of the osquery foundation. Please read the Linux Foundation's official announcement. We have created an osquery/foundation repository on GitHub to host the technical charter. > The official repository has been renamed from facebook/osquery to osquery/osquery.
- gooseyard 3y agoHave wondered for years whether Akamai's Query system (https://www.usenix.org/legacy/events/lisa10/tech/full_papers/Cohen.pdf https://www.usenix.org/legacy/events/lisa10/tech/full_papers..., sadly never open sourced) had any influence on the creation of osquery. They are quite different architecturally but functionally similar.
- mikermcneil 3y agoStarted as people sending scripts back and forth, like The Postal Service https://www.linkedin.com/in/ramosjohnny/ https://www.linkedin.com/in/ramosjohnny/ https://www.linkedin.com/in/marpaia/ https://www.linkedin.com/in/marpaia/
- ddm379 3y ago(Opens GitHub issue) Do another album and add a stop in Tampa.
- infogulch 3y agoSqlite is embedded, so how do you actually build it into your app? Is there a C file somewhere, an amalgamation published? I haven't been able to find it, but surely it exists. I'd like to build a Go binary with mattn/go-sqlite3 with osquery vtables included.
- w0de0 3y agohttps://github.com/osquery/osquery/tree/master/osquery/sql https://github.com/osquery/osquery/tree/master/osquery/sql
- deleted 3y ago[deleted]
- tehlike 3y agois a shared library you can embed into your app. or you can use it in a server/client setting through some other project, but probably not you wanted
- mikermcneil 3y agoruns as a daemon, communicates over http
- mikermcneil 3y agoThey did an amazing feat of engineering. Mike Arpaia's brainchild, co-created by my cofounder zwass at Fleet, and massively improved by seph (@directionless), ted reed, alessandro, sharvil, and many others: https://github.com/osquery/osquery/graphs/contributors https://github.com/osquery/osquery/graphs/contributors
- deleted 3y ago[deleted]
- archsurface 3y agoI like the idea, but the queries seem to be longer than the shell commands, so the only advantage is not having to know the shell commands, and I'm not convinced you want people who don't know the system mucking about on the system.
- riku_iki 3y agoits also useful when you are building some telemetry agent, since you don't need to parse output of each individual command
- mikermcneil 3y agome and my brother-in-law Eric built this table viewer: https://fleetdm.com/tables https://fleetdm.com/tables (it's got an extra layer of metadata with examples. Supports markdown in the yaml for most fields. Contributions welcome!!)
- DinaCoder99 3y ago[dead]
- tonymet 3y agoI’ve been working on “life query” which is like osquery but all your life indicators: social, finance, health, business, productivity.
- russell_h 3y agoIs it usable? Depending on exactly what it can access I might be interested to try it.
- tonymet 3y agoNot at all because the data sources are collected with scraping tech. I like your enthusiasm let me think of how to better scale the product. Is this something you’ve been interested in for a while?
- dotancohen 3y agoNot GP but it sounds very interesting to me.
- 29athrowaway 3y agoAnsible is my prefered way to do this.
- mikermcneil 3y agoyou should chat w/ AB! https://www.linkedin.com/in/abubakar-yousafzai-b7213659/ https://www.linkedin.com/in/abubakar-yousafzai-b7213659/
- deleted 3y ago[deleted]
- debarshri 3y agoFew projects in the same realm that you should also checkout - [1] Steampipe (https://steampipe.io/ https://steampipe.io/) [2] InfraSQL (https://iasql.com/ https://iasql.com/)
- michelpp 3y agosystem_stat is another one specific to PostgreSQL: https://github.com/EnterpriseDB/system_stats https://github.com/EnterpriseDB/system_stats
- mdaniel 3y agobe mindful of its AGPLv3 https://github.com/turbot/steampipe/blob/v0.21.8/LICENSE https://github.com/turbot/steampipe/blob/v0.21.8/LICENSE (AFAIK v0.4.3 is the last MIT release https://github.com/turbot/steampipe/blob/v0.4.3/LICENSE https://github.com/turbot/steampipe/blob/v0.4.3/LICENSE ) and the actual providers are Apache 2 <https://github.com/turbot/steampipe-plugin-aws/blob/v0.131.0/LICENSE https://github.com/turbot/steampipe-plugin-aws/blob/v0.131.0...> (but I don't know if provider drift makes them compatible with 0.4 or not) iasql seems to be AWS only, but good for them for taking this on: https://github.com/alantech/iasql/blob/v0.1.9/src/services/canonical-iam-policy.ts https://github.com/alantech/iasql/blob/v0.1.9/src/services/c... My heart goes out to anyone having to touch AWS IAM canonicalization
- getvictor 3y agoI started fixing some outstanding osquery issues recently. There are a lot of outstanding issues on GitHub that I haven't gone through. What's an issue preventing you from deploying osquery in your org?
- worewood 3y agoThis sounds like having a complete toolbox and saying "you know what would be cool? Using a drill to drive a nail into wood" and start banging the nail with it
- Cloudef 3y agoSqlite virtual tables are nice. I made one for my db that decompresses strings from my other table on the fly (supports dictionaries for short data)
- mikermcneil 3y agoNice! “ACT” for anyone searching (automatic table construction)
- deleted 3y ago[deleted]
- binsquare 3y agoWould love to see cloud embrace something similar to this, imds and cloud trails come to mind as a poorer implementation
- yevpats 3y agoCheck out CloudQuery (https://github.com/cloudquery/cloudquery https://github.com/cloudquery/cloudquery) for the cloud version :) (Maintainer here)
- gromneer 3y agoAre there enough tables and documentation to train a model off of?
- Already__Taken 3y agoit's on my work laptop which means Cisco shove it in something. just an anecdata