osquery turns any os into a sql database for threat hunting

your os is basically a database and nobody told you

here's a weird but useful idea: every process, file, user account, and network connection on your machine is just a row of data. osquery takes that idea and runs with it. it wraps your entire operating system in a sql interface, so instead of clicking through task manager or grepping log files, you write a query. same syntax on windows, linux, and mac. one tool, one language, three platforms.

this matters for threat hunting because attackers love hiding in the gap between "what's running" and "what's on disk." there's a classic trick where malware deletes its own binary right after it launches. the process keeps running in memory, doing whatever it wants, but if you go looking for the file that spawned it, it's gone. no file, no easy signature scan, no obvious thing to submit to antivirus. osquery can spot that gap in one line.

the query that catches self-deleting malware

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

let's break this down piece by piece.

osqueryi is the interactive shell, basically a sql prompt but for your operating system instead of a database server.

SELECT name,path,pid tells it which columns you want back: the process name, the file path it was launched from, and its process id.

FROM processes means we're querying the processes table, which is osquery's live view of every running process on the machine right now.

WHERE on_disk=0 is the important part. osquery checks the path each process claims to run from and asks: does that file actually still exist? if the answer is no, on_disk gets set to 0. a legit process that's still running from a real file will show on_disk=1. a process whose binary got deleted after launch, which is exactly what a lot of malware and post-exploitation tooling does to dodge disk-based detection, shows up as on_disk=0.

run this on a clean, normal machine and you'll usually get zero rows back. that's the point. any result here deserves a second look.

tying it to network activity

a process running from a deleted binary is suspicious on its own, but it gets a lot more concrete when you can show it's also talking to the network. that's the second query:

osqueryi "SELECT * FROM listening_ports WHERE port=4444;"

4444 shows up here as an example because it's a well known default port for certain shell and payload tools, but the real habit you want to build is checking listening_ports in general and cross referencing the pid against your on_disk=0 results. if the same pid shows up in both queries, meaning it's running from a deleted file and it's got a port open waiting for a connection, you've basically found a backdoor. that's not a maybe, that's a "go isolate this box" moment.

why this beats poking around manually

task manager and ps aux both show you processes, but neither one natively tells you whether the underlying binary still exists on disk. you'd have to manually cross check every suspicious pid against its file path, one at a time, hoping you don't miss anything. osquery does that check across every single process in one query. it also means you can write this once and run it identically on a windows laptop, a linux server, and a mac, instead of learning three different sets of tools and syntax for the same job.

it also plays nice with scheduled checks. osquery can run as a background daemon (osqueryd) and log results over time, so you're not just checking once, you're building a baseline of what normal looks like on your own systems and getting flagged when something drifts from it.

how to actually use this to defend yourself

install osquery on the machines you're responsible for, not someone else's. run the on_disk=0 query as a first pass on any system you suspect might be compromised, and don't panic if you see something odd, just investigate the pid further with lsof or netstat equivalents before you assume the worst. set up osqueryd with a basic scheduled query pack so this check runs automatically instead of relying on you remembering to do it. pair it with file integrity monitoring so you get alerted the moment a binary disappears, not just when you happen to go looking.

the takeaway

self deletion is a cheap trick that works great against tools that only look at the filesystem. osquery closes that gap by comparing what's running in memory against what's actually still sitting on disk, and it does it in a language you probably already half know. learn a handful of these queries, run them on your own machines regularly, and you turn a classic evasion technique into a one liner that outs it every time.