osqueryi: ask a host questions in SQL

The operating system as tables

osquery exposes processes, users, listening ports, packages, startup items, and hundreds of other facts as SQL tables. osqueryi is the interactive shell; it reads live state when you run a query and keeps nothing afterwards. The same queries later run on a schedule across a fleet, so writing them interactively first is time well spent.

.tables lists what the platform supports, and .schema processes shows a table’s columns.

Who is listening

Joining listening_ports to processes answers the first question in many investigations. Filtering out loopback leaves the services reachable from the network.

Snippet

osqueryi --line "SELECT p.name, p.path, l.port, l.address, l.protocol
  FROM listening_ports l JOIN processes p USING (pid)
  WHERE l.address NOT IN ('127.0.0.1', '::1') AND l.port != 0;"

    name = sshd
    path = /usr/sbin/sshd
    port = 22
 address = 0.0.0.0
protocol = 6

    name = python3
    path = /tmp/.cache/srv
    port = 8081
 address = 0.0.0.0
protocol = 6

Binaries that no longer exist on disk

A process whose executable was deleted after launch shows on_disk = 0. Legitimate upgrades cause this briefly; anything that stays that way deserves a look.

Snippet

SELECT pid, name, path, cmdline, start_time FROM processes WHERE on_disk = 0;

SELECT name, path, source, status FROM startup_items;   -- Windows and macOS
SELECT command, path FROM crontab;                       -- Linux and macOS
SELECT name, action, path, enabled FROM scheduled_tasks WHERE enabled = 1;  -- Windows

Worked case: the python listener

The query above shows python3 running from /tmp/.cache/srv on port 8081. The lab exercise planted it, but the steps are the real ones. Check the parent with the processes table, the user with users, and the crontab entry that restarts it. Record pid, path, hash from the hash table, and start time before stopping it, because those facts disappear with the process.

Snippet

SELECT pid, parent, uid, cmdline FROM processes WHERE path = '/tmp/.cache/srv';
SELECT sha256 FROM hash WHERE path = '/tmp/.cache/srv';
SELECT * FROM crontab WHERE command LIKE '%/tmp/.cache%';

Diagram

Triage questions as osquery tables

Listening

Join ports to the processes that own them.

SQL

SELECT p.name, l.port FROM listening_ports l JOIN processes p USING (pid);

Deleted
Find processes running from removed files.
SQL
```
SELECT name, path FROM processes WHERE on_disk = 0;
```
Persistence
List what starts automatically.
SQL
```
SELECT command, path FROM crontab;
```

Record

Hash before you stop anything.

SQL

SELECT sha256 FROM hash WHERE path = '/tmp/.cache/srv';

Check yourself

1. Which column flags a process whose executable was deleted?

2. Which tables answer “what is listening, and which program owns it?”?

3. Why record the hash and start time before stopping a suspicious process?