Osquery: An sqlite3 virtual table exposing operating system data to SQL
osquery.io
osquery.io
(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.)
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
}(In terms of general purpose/flexible tooling, I'm not aware of a close replacement for osquery.)
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))}'
alias ports="lsof -iTCP -sTCP:LISTEN -n -P | awk '{print \$1, substr(\$0, index(\$0,\$9))}'"[1] Steampipe (https://steampipe.io/)
[2] InfraSQL (https://iasql.com/)
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
What's an issue preventing you from deploying osquery in your org?
First-person testimony.
> 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.
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.
Contributions: https://github.com/osquery/osquery
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.
The formula osquery uses to report performance: https://github.com/fleetdm/fleet/issues/16123
It really really messes with windows defender too.
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.
Next step is "clairvoyance", where we predict perf based on the SQL when you're in the query editor
Seems pretty straightforward as a start.
In my head I feel like I say "ess-cue-lite" pretty much every time.
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.
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.
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.
(I don't know whether they still use it in-house.)
[1]: https://blog.trailofbits.com/2019/04/18/announcing-the-commu...
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...
> 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.
I'd like to build a Go binary with mattn/go-sqlite3 with osquery vtables included.
or you can use it in a server/client setting through some other project, but probably not you wanted
https://www.linkedin.com/in/ramosjohnny/ https://www.linkedin.com/in/marpaia/
Do another album and add a stop in Tampa.
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).
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).
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.
Is there even an established, reliable alternative to intune for Windows? I don’t know.
For Mac JAMF is pretty much the gold standard and I tried to get it but the leadership preferred a "single pane of glass" sadly.
For Windows we always just used SCCM and even now we're only in hybrid mode with most functions in traditional management.
For example: any single Jamf admin can type a bash script into a text box which will then be automatically executed on every machine as root.
You can restrict this ability using the built-in (granular) access controls.
However, the market wasn’t always asking for it. Most Mac management is done by a sole individual at the org, usually not a large team where everyone can review everyone’s changes. This is steadily changing, but there are still tons of people who do clickops in Jamf because it’s what they understand and have the bandwidth to do.
I think the windows guys have everything more proceduralised. But the Mac work is more of a one man show. I still don't think they have an approvals process though. They mainly still use SCCM and I don't think that has approvals built in.
And yeah things get tested in a separate environment before they're deployed in live. But as there's just one person doing both on Mac there's not much point to an approvals process. Which is also the person that gets to deal with any mistakes so there generally aren't any :)
And it's not a bad thing either, the Mac side is usually much quicker to adopt new features. Both because there's not that many and because Mac users are always interested in new OS versions whereas Windows users generally prefer to stay on what they know.
Jamf’s script feature is agent based, just like Airwatch’s, not an implementation of the MDM protocol.
In terms of the overal tooling I have to disagree. SCCM is way more powerful than anythng Apple has to offer.
In terms of actual MDM "Modern Management" for Windows (Intune), yes that's only in its infancy but it's because most of their customers still use SCCM for most tasks. It's a bit chicken-and-egg.
But Apple's MDM is not great. The password profile is extremely simplified, not able to handle any complexity (example: In our AD passwords must contain special characters and numbers if they are shorter than 10 characters but don't need to when longer because we want to stimulate passphrases). Also, as far as I know (I don't work in this scope anymore) it still has no MDM profile to mandate the user installing updates in a timely manner. You can delay them but not force the user to install them. This stuff must be handled with scripting. The MDM app deployment is also very hit and miss which is why most MDMs do it through their own agent. It works fine when using the mac app store but most apps are not on there and usually there is a need for a customised package anyway.
And on the topic of customised packages, having to go through Apple's notarisation is really annoying. We should be able to just deploy our own signing keys to the machines that we own, and deploy to those machines whatever we want that's signed with our internal key without having to get Apple's OK on it. Sometimes the notarisation service refuses to work for some reason (happens especially with package installers combining code and signing keys from 2 different vendors) and I need to obfuscate the embedded packages to make it work.
So no, in terms of MDM I think Apple is not great for enterprise usecases. If you're a small all-apple shop and you can align everything with Apple's requirements then you may fare better but we don't. Less than a percent of our systems are macs.
> are often much better at code driven workflows than Windows admins.
Yes but Apple does shoot us in the foot sometimes by changing stuff around. I have to say that PowerShell is much more consistent in this manner.
I still prefer Mac but I have to say the enterprise management tooling is just way better on Windows. Apple doesn't really seem to care about enterprise users at all.
Another point is that terrible federated apple ID system that to this day still requires the UPN to be equal to the email address. In our environment this is different for a reason and there is no way it's going to get changed just to satisfy an Apple requirement.
As the other reply said, this is something you need to enable to only the most expert admins, and have processes to test it properly before you deploy it to the entire population.
FWIW our antimalware solution has the same capability.
But JAMF is the 'gold standard' because they are the only ones that really focus on Mac management. The used to be called Casper Suite, perhaps that rings more of a bell (I think JAMF is a pretty poor name but anyway)
Unfortunately we can't use that because we can't use Apple federated accounts (they still require the UPN and email to be the same which we can't comply with) and because our IDP isn't supported.
This is the problem in enterprise, where you're often stuck with a relatively small amount of Macs (in our case less than half a percent) and thus the macs have to comply with the rest of the environment. Because the environment isn't going to be adapted to Apple's requirements. We're stuck with the requirement to limit user admin rights, to have security proxies (Zscaler), and many other things that don't work seamlessly on a Mac. Like the built-in password restrictions MDM profile, this is way too basic to fully encompass our security policy (it seems more like a mobile one shoehorned onto a Mac). So we need a lot of workarounds to meet our security policy.
For this JAMF works really well because it has lots of workarounds for enterprise setups that aren't done "the Apple way".
If you're free from legacy constraints the more modern solutions work great but in our environment we don't have that luxury, sadly. There's just too many strings attached from the security side.
You can sometimes script your way around the issues but every major macOS update will break things and JAMF is pretty good at figuring all this stuff out.
But yes I should have mentioned that context.
(it's got an extra layer of metadata with examples. Supports markdown in the yaml for most fields. Contributions welcome!!)