Osquery: An sqlite3 virtual table exposing operating system data to SQL

by signa11 • 2 years ago

300 99 2 years ago

jeffbee2• 2 years ago

Osquery: 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.

signa112• 2 years ago

if possible, may you please elaborate, that would be so useful. thank you !

VWWHFSfQ• 2 years ago

You 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.

jeffbee2• 2 years ago

I 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.

formerly_proven• 2 years ago

OS 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), 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.

iJohnDoe• 2 years ago

Or 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• 2 years ago

Everyone 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.

anotherhue• 2 years ago

Haha, 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.

w0de07• 2 years ago

The 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.

mikermcneil17• 2 years ago

Feel 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• 2 years ago

https://osquery.readthedocs.io/en/latest/deployment/performa...

Seems pretty straightforward as a start.

wkat42427• 2 years ago

I 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).

w0de07• 2 years ago

Deploy 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).

wkat42427• 2 years ago

I 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.

bolle• 2 years ago

VMware Carbon Black uses osquery for the ‘Cloud Audit and Remediation’ part of the software.

mikermcneil17• 2 years ago

likely the origin of the `carbon_black_info` table https://fleetdm.com/tables/carbon_black_info

lolinder• 2 years ago

Definitely 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• 2 years ago

D. Richard Hipp pronounces it Ess-Queue-El Ite.

quickslowdown2• 2 years ago

When 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.

samatman• 2 years ago

"like a mineral"

o11c• 2 years ago

"Sequel" is explicitly wrong; it was a predecessor of SQL, not a pronunciation of it.

usrusr• 2 years ago

Wouldn't that make SQL a sequel to Sequel? Sorry, I feel like I'm annoying on purpose, I shouldn't do this.

Reading up on the history parts in the Wikipedia article, the German language version presents it as if SQL was the Oracle (Relational Software Inc) implementation of IBM's Sequel, the English page says the name was shortened due to trademark issues, before mentioning any implementation outside IBM. That would make Sequel and SQL the same thing, just rebranded early on. It also lists a clever (b)acronym and two other reasons for why they called it sequel before shortening, so it seems like there's a decent amount of mystery around the name. Almost as if it happened before the invention of writing.

kwk1• 2 years ago

It was a superficial renaming initiated by a trademark conflict, that hardly warrants a "predecessor" designation.

paulddraper• 2 years ago

https://dictionary.cambridge.org/us/dictionary/english/homon...

Karellen• 2 years ago

But also, Microsoft's "SQL Server" is officially pronounced "Sequel Server", so a lot of devs who grew up working on Windows systems will pronounce "SQL" as "sequel". For them, it's not entirely wrong.

euroderf• 2 years ago

Can't it just be pronounced "sclite", rhyming with (e.g.) "sprite" ?

And when you apply FUSE (or FUSE-T or NFS or WebDAV) to its archive format "sqlar", getting a file system called "sqlarfs", then pronounce it as "sclarfs", rhyming with snarfs/scarfs/barfs.

woodruffw4• 2 years ago

osquery 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.)

PenguinCoder2• 2 years ago

Agreed with that first statement. Some of the things osquery does especially with regards to Linux disk space usage, is frustrating to deal with.

mikermcneil17• 2 years ago

What did you run into on Linux?

neodymiumphish• 2 years ago

It’s also the base layer for Sophos’ EDR platform.

woodruffw4• 2 years ago

It's the base layer for a lot of EDRs :-)

EvanAnderson• 2 years ago

Alienvault's endpoint security tool thingamagic uses it too.

Myrmornis2• 2 years ago

I don't know about production, but I use it on my MacOS laptop to list all listening sockets with the associated command line. Should I be concerned that this sort of thing has correctness bugs? (Can anyone give a competing non-osquery implementation on MacOS?)

  sockets() {
    (osqueryi --list --separator ',' | column -t -s ',') <<EOF
  SELECT s.local_port, p.cmdline
  FROM process_open_sockets AS s
  INNER JOIN processes AS p
  ON s.pid = p.pid
  WHERE s.state = 'LISTEN'
  ORDER BY p.cmdline;
  EOF
  }

woodruffw4• 2 years ago

There's at least one open data quality issue for `process_open_sockets` on macOS[1]. It's a few years old however and, if you aren't seeing that casting error, you probably aren't hitting it. But that's a good example of the kind of debt that's been built up over time.

(In terms of general purpose/flexible tooling, I'm not aware of a close replacement for osquery.)

[1] https://github.com/osquery/osquery/issues/6319

signa112• 2 years ago

Can anyone give a competing non-osquery implementation on MacOS?

shouldn't lsof with some awk munging get you there ?

edit-01:

for example

    lsof -iTCP -sTCP:LISTEN -n -P | awk '{print $1, substr($0, index($0,$9))}'

might be useful enough ? fwiw, it does give me something useful on my machine

lopkeny12ko• 2 years ago

Huh, 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.

woodruffw4• 2 years ago

I 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-commu...

mikermcneil17• 2 years ago

History (tldr): 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...

ciupicri• 2 years ago

From 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• 2 years ago

Have wondered for years whether Akamai's Query system (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.

mikermcneil17• 2 years ago

Started as people sending scripts back and forth, like The Postal Service

https://www.linkedin.com/in/ramosjohnny/ https://www.linkedin.com/in/marpaia/

ddm379• 2 years ago

(Opens GitHub issue)

Do another album and add a stop in Tampa.

infogulch• 2 years ago

Sqlite 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.

w0de07• 2 years ago

https://github.com/osquery/osquery/tree/master/osquery/sql

tehlike• 2 years ago

is 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

mikermcneil17• 2 years ago

runs as a daemon, communicates over http

mikermcneil17• 2 years ago

They 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

archsurface• 2 years ago

I 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• 2 years ago

its also useful when you are building some telemetry agent, since you don't need to parse output of each individual command

mikermcneil17• 2 years ago

me and my brother-in-law Eric built this table viewer: https://fleetdm.com/tables

(it's got an extra layer of metadata with examples. Supports markdown in the yaml for most fields. Contributions welcome!!)

tonymet2• 2 years ago

I’ve been working on “life query” which is like osquery but all your life indicators: social, finance, health, business, productivity.

russell_h• 2 years ago

Is it usable? Depending on exactly what it can access I might be interested to try it.

tonymet2• 2 years ago

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

29athrowaway• 2 years ago

Ansible is my prefered way to do this.

mikermcneil17• 2 years ago

you should chat w/ AB! https://www.linkedin.com/in/abubakar-yousafzai-b7213659/

debarshri• 2 years ago

Few projects in the same realm that you should also checkout -

[1] Steampipe (https://steampipe.io/)

[2] InfraSQL (https://iasql.com/)

michelpp• 2 years ago

system_stat is another one specific to PostgreSQL:

https://github.com/EnterpriseDB/system_stats

mdaniel2• 2 years ago

be mindful of its AGPLv3 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 ) and the actual providers are Apache 2 <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/c... My heart goes out to anyone having to touch AWS IAM canonicalization

getvictor• 2 years ago

I 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• 2 years ago

This 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• 2 years ago

Sqlite 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)

mikermcneil17• 2 years ago

Nice! “ACT” for anyone searching (automatic table construction)

binsquare• 2 years ago

Would love to see cloud embrace something similar to this, imds and cloud trails come to mind as a poorer implementation

yevpats• 2 years ago

Check out CloudQuery (https://github.com/cloudquery/cloudquery) for the cloud version :) (Maintainer here)

gromneer• 2 years ago

Are there enough tables and documentation to train a model off of?

Already__Taken• 2 years ago

it's on my work laptop which means Cisco shove it in something. just an anecdata

Osquery: An sqlite3 virtual table exposing operating system data to SQL

Related Stories