Introspecting your machine with osquery

texas linux fest 2025

Ian Littman / @ian@phpc.social / @ian.im / @iansltx

osquery: query systems with SQL(ite)

installable via apt/yum/tarball

let's get the sha1 of all open files <= 100KiB

SELECT path, sha1 FROM hash WHERE path IN (
  SELECT path FROM process_open_files
  JOIN file USING (path) WHERE file.size <= 1024 * 100
);

https://ian.im/txlf25

Why and when?

Why? Facebook Sysadmins and security engineers had some problems
- Needed to collect telemetry from tons of sources
- Existing tools were particularly scarce on macOS and Linux
- Most existing tools hide internals
Timeline
- 2014: osquery is built as a Facebook project
- 2016: Windows support is added (still serviceable but WIP)
- 2019: Joined The Linux Foundation

https://ian.im/txlf25

Key Tenets

Write SQL, not procedural code
- osquery pulls and parses requisite data
- SQLite (embedded in osquery) queries data via virtual tables
- If you know how to write SQL, you can figure out how to use osquery
Reliable and performant enough to run on corporate machines and in prod
- Worker process does the heavy lifting
- Watchdog process kills/respawns the worker if resource limits are exceeded
- Heavy queries are denylisted
- Some tables require WHERE clauses because e.g. hashing files is expensive
Dual-licensed open-source GPLv2 + Apache 2.0

https://ian.im/txlf25

270+ tables built in

https://ian.im/txlf25

Hardware

```
acpi_tables
```
```
battery
```
```
block_devices
```
```
chassis_info
```
```
connected_displays
```
```
cpu_info
```
```
cpuid
```
```
device_firmware
```
```
fan_speed_sensors
```
```
hardware_events
```

```
ibridge_info
```
```
intel_me_info
```
```
memory_devices
```
```
nvram
```
```
pci_devices
```
```
power_sensors
```
```
temperature_sensors
```
```
usb_devices
```
```
video_info
```
```
drivers (Windows)
```

https://ian.im/txlf25

software*

```
apps (macOS)
```
```
apt_sources
```
```
chocolatey_packages
```
```
deb_packages
```
```
gatekeeper
```
```
gatekeeper_approved_apps
```
```
homebrew_packages
```
```
package_bom
```
```
package_install_history
```
```
package_receipts
```
```
python_packages
```
```
yum_sources
```

```
authenticode (Windows)
```
```
startup_items
```
```
launchd
```
```
chrome_extensions
```
```
firefox_addons
```
```
safari_extensions
```
```
patches (Windows)
```
```
npm_packages
```
```
programs (Windows)
```
```
rpm_packages
```
```
rpm_package_files
```
```
windows_update_history
```

https://ian.im/txlf25

Auth and certs

```
authorized_keys
```
```
certificates
```
```
groups
```
```
keychain_items
```
```
known_hosts
```
```
last (logins + logouts)
```
```
logged_in_users
```
```
logon_sessions (Windows)
```
```
password_policy (macOS)
```
```
security_profile_info (Windows)
```

```
selinux_*
```
```
shadow
```
```
ssh_configs
```
```
sudoers
```
```
suid_bin
```
```
user_events
```
```
user_groups
```
```
user_ssh_keys
```
```
users
```

https://ian.im/txlf25

Disk encryption

```
disk_encryption
```
```
bitlocker_info
```

https://ian.im/txlf25

Networking

```
connectivity
```
```
dns_cache (Windows)
```
```
dns_resolvers
```
```
etc_hosts
```
```
interface_addresses
```
```
interface_details
```
```
interface_ipv6
```
```
listening_ports
```
```
process_open_sockets
```
```
routes
```
```
wifi_*
```
```
curl
```

https://ian.im/txlf25

Firewalls

```
alf
```
```
alf_exceptions
```
```
alf_explicit_auths
```
```
windows_firewall_rules
```
```
windows_security_center
```
```
iptables
```

https://ian.im/txlf25

What's running?

```
crontab
```
```
scheduled_tasks (Windows)
```
```
startup_items
```
```
processes
```
```
process_*
```
```
running_apps
```
```
systemd_units
```

https://ian.im/txlf25

Logging

```
windows_event_logs
```
```
syslog_events
```
```
unified_log
```
```
asl
```
```
shell_history
```

https://ian.im/txlf25

Files (including file integrity monitoring)

```
carves
```
```
disk_*
```
```
file
```
```
file_events
```
```
hash
```
```
md_* (software RAID)
```
```
mdls / mdfind (Spotlight)
```

```
mounts
```
```
nfs_shares
```
```
ntfs_*
```
```
office_mru
```
```
process_file_events
```
```
signature
```

https://ian.im/txlf25

Containers

```
docker_*
```
```
lxd_*
```

sudo ln -s $HOME/.docker/run/docker.sock /var/run/docker.sock

https://ian.im/txlf25

Cloud

```
azure_instance_metadata
```
```
azure_instance_tags
```
```
ec2_instance_metadata
```
```
ec2_instance_tags
```

https://ian.im/txlf25

configuration

```
plist
```
```
registry
```
```
kernel_*
```

https://ian.im/txlf25

Time

```
time
```
```
uptime
```
```
load_average
```

https://ian.im/txlf25

But wait, with ATC, there's more!

if you need sqlite in your sqlite

https://ian.im/txlf25

But wait, with extensions, there's more!

for example, fleetd adds 30+ tables

https://ian.im/txlf25

Comprehensiveness over consistency.

you might want an abstraction layer on top.

...at least likely join keys tend to be named the same (pid, uid, path)

https://ian.im/txlf25

Nuance #1: Cross joins for users

osquery queries as the current user
osqueryd generally runs as root
Retired: SELECT * FROM chrome_extensions
Inspired: SELECT cx.* FROM users CROSS JOIN chrome_extensions cx USING (uid)
Some tables work without the cross join, but give incomplete results

Bonus tip: Check out the SQLite docs on SELECT and the SQLite tutorial

https://ian.im/txlf25

Nuance #2: Computation effort

ORDER/LIMIT tend not to reduce computation effort
WHERE, depending on table, does
Some tables (e.g. hash) require a WHERE condition
For file paths, % == glob *; use %% for recursive subdirectory scans
Sometimes you can get a hash more cheaply (e.g. signature table)
Sometimes you need subqueries rather than JOINs

https://ian.im/txlf25

Displaying results

Provide query as an osqueryi argument to output and quit
At the CLI: --json, --csv
Inside the shell: .mode line, .mode column

https://ian.im/txlf25

osqueryd: JSON query log to disk

can also push externally over TLS with host identification

Can run scheduled over time with either the entire result or diffs

https://ian.im/txlf25

Threat Hunting && Indicators of Compromise

SELECT * FROM processes
WHERE on_disk = 0

SELECT COUNT(*) > 0 FROM files
WHERE path = "/my/malware"

SELECT * FROM process_open_sockets
WHERE remote_address = ""

YARA allows for more file contents searching
Use diff logging to catch anomalies

https://ian.im/txlf25

Threat Hunting && Indicators of Compromise

https://ian.im/txlf25

Thanks!

Slides at https://ian.im/txlf25

Questions? Find me here / @ian@phpc.social / @ian.im / @iansltx