🔍 Endpoint Visibility with osquery and AI-Assisted Triage

For years my answer to “what is actually running on that box right now?” was to SSH in, run ps, ss -tlnp, and crontab -l, squint, and hope I remembered to check the same things on the other forty hosts. It does not scale, and it does not catch the thing that started yesterday and stopped before you looked. osquery fixes that by exposing the operating system as a SQL database you can query consistently across the whole fleet, on a schedule, with the results shipped somewhere you can search them later.

The catch is that SQL over operating-system internals is its own dialect, and the tables are deep. This is where I lean on AI as a fast junior analyst: it knows the osquery schema, it drafts the query, and it helps me read the output. But every query I run against production is one I have read and understood first, and every “this looks malicious” verdict gets human eyes before anyone touches the host. Defensive monitoring only.

Treat your fleet as one big read-only database

The mental shift osquery asks for is that you stop logging into individual machines and start asking the fleet questions. “Which hosts have a process listening on a port that isn’t in my allowlist?” is one query, run everywhere.

I start interactively to learn the schema:

osqueryi --line "SELECT name, path, pid FROM processes WHERE on_disk = 0;"

That query finds processes whose executable has been deleted from disk while still running, a classic sign of a tampered or in-memory payload. When I don’t know which table holds what I want, I describe the goal to the AI:

Using the osquery schema, write a query that lists listening TCP sockets joined to the owning process name and path. Read-only detection query, no host changes.

It reliably produces the process_open_sockets join I’d have spent ten minutes looking up. I run it in osqueryi first to confirm it returns sane data before I add it to a scheduled pack.

Build a small, sharp pack of scheduled queries

A scheduled query pack is a JSON file of named queries with intervals. The temptation is to collect dozens; the discipline is to keep a handful that actually mean something. I ask the AI to draft candidates, then I cut hard.

{
  "queries": {
    "unexpected_listeners": {
      "query": "SELECT pos.pid, p.name, p.path, pos.local_port FROM process_open_sockets pos JOIN processes p USING (pid) WHERE pos.local_port NOT IN (22, 443, 9100);",
      "interval": 3600,
      "description": "Listeners outside the expected set"
    },
    "new_setuid_binaries": {
      "query": "SELECT path, mode, uid FROM suid_bin;",
      "interval": 86400,
      "description": "Inventory of setuid binaries for drift detection"
    }
  }
}

Pro Tip: pair scheduled queries with osquery’s snapshot-vs-differential logging. Differential mode emits only what changed since last run, so a new setuid binary or a freshly opened port shows up as an event instead of being buried in a full re-list every hour.

Use AI to turn a vague worry into a concrete query

The real value shows up when an alert fires elsewhere and you need to answer a specific question across the fleet fast. Say threat intel flags a suspicious binary hash. I describe what I have:

I have a SHA256 hash of a suspicious file. Write an osquery query that finds any file under common executable paths matching that hash, and a second query that checks whether any currently running process maps it. Detection only.

The model drafts the hash table join and the process_memory_map lookup. I read both, confirm the paths are reasonable for our environment, and run them read-only. I never hand the AI real internal hostnames, secrets, or sensitive paths; I describe the structure and substitute placeholders.

Triage the output instead of drowning in it

A fleet-wide query can return thousands of rows, and most are benign. I export the results and have the AI help me cluster and prioritize, not decide:

Here is sanitized output: process names, ports, and counts across the fleet, with hostnames removed. Group these into expected versus anomalous, and explain why each anomalous group stands out. Do not assume malicious; explain the signal.

That framing matters. I want the model to surface why something is unusual so I can verify it, not to hand me a confident “malware” label I’ll rubber-stamp. The human still makes the call. When something genuinely looks like an incident, I route the structured findings through the incident response dashboard so the triage notes and timeline live in one place.

Watch for drift, the slow kind of compromise

Not every threat is a dramatic intrusion. Often it’s drift: a setuid bit that appeared, a cron entry nobody documented, a kernel module that loaded after a sloppy install. osquery is excellent at baselining. I snapshot the answer to a query today, ask the AI to write a diff-friendly version, and compare next week:

osqueryi --json "SELECT name, path FROM kernel_modules ORDER BY name;" > modules-baseline.json

Then a weekly comparison flags additions. The AI is handy for generating the comparison logic and explaining what each new module does, but I confirm against vendor docs before deciding anything is wrong.

Keep the loop tight and human-gated

The workflow that holds up: AI drafts the query, I read and run it read-only, AI helps cluster the output, I make the verdict. None of those steps lets the model change a host or close an alert on its own. I keep the reusable detection prompts in a shared prompt workspace so the team queries consistently, and the broader detection-and-audit set lives in the DevOps security prompt pack alongside the rest of our prompts library.

For the model itself, I’ve had good results with Claude for schema-aware query drafting and with Warp’s terminal AI when I want suggestions inline while I’m already in osqueryi.

The takeaway

osquery gives you a consistent, queryable view of every machine you run, which is the foundation of defensive visibility. AI lowers the barrier to writing good queries and reading their output, acting as the fast junior analyst who drafts and explains while you verify and decide. Keep it read-only against production, keep real secrets and hostnames out of the prompts, and make sure a human owns every “this is suspicious” call. Browse the rest of the security hardening category for the detection and audit guides that pair with this one.