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

Re:Osquery: An sqlite3 virtual table exposing operating syst... by woodruffw on Sunday February 25, 2024 @11:37AM (#39502200)

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

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by PenguinCoder on Sunday February 25, 2024 @12:26PM (#39502669)

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

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by mikermcneil on Sunday February 25, 2024 @05:43PM (#39505693)

What did you run into on Linux?

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by PenguinCoder on Wednesday February 28, 2024 @10:17PM (#39546093)

> Linux disk space usage reporting issues

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by mikermcneil on Thursday February 29, 2024 @10:22PM (#39558202)

In https://fleetdm.com/tables/mounts ?

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by neodymiumphish on Sunday February 25, 2024 @01:03PM (#39503096)

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

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by woodruffw on Sunday February 25, 2024 @01:10PM (#39503172)

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

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by EvanAnderson on Sunday February 25, 2024 @07:52PM (#39506557)

Alienvault's endpoint security tool thingamagic uses it too.

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by Myrmornis on Sunday February 25, 2024 @07:01PM (#39506276)

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
  }

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by woodruffw on Sunday February 25, 2024 @07:07PM (#39506325)

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

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by Myrmornis on Sunday February 25, 2024 @07:16PM (#39506378)

Thanks! I'll watch out for that one.

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by mikermcneil on Sunday February 25, 2024 @09:42PM (#39507116)

Was about to say "will fix ASAP", but double-plus since it's @nyanshak!

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by noahtaleran on Monday February 26, 2024 @02:17PM (#39515669)

Yo! We're working on the fix. You can track progress here: https://github.com/fleetdm/fleet/issues/17165

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by signa11 on Monday February 26, 2024 @12:35AM (#39507967)

> 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

[ Parent | on HN ]

Re:Osquery: An sqlite3 virtual table exposing operating syst... by MrBuddyCasino on Monday February 26, 2024 @03:41AM (#39508893)

Thanks, added.

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

[ Parent | on HN ]