Osquery Endpoint Visibility & Threat Hunting

Introduction

Osquery is an open-source endpoint instrumentation framework created by Facebook (now Meta) that exposes the operating system as a relational database. Instead of parsing /proc, reading registry hives, or scripting WMI calls, you write standard SQL queries against virtual tables that represent running processes, open sockets, installed packages, cron jobs, autorun entries, kernel modules, and hundreds of other system artefacts.

This makes ad-hoc investigation dramatically faster — SELECT name, pid, cmdline FROM processes WHERE on_disk = 0 instantly surfaces in-memory-only processes across a fleet of thousands of hosts. The same query syntax works on Linux, macOS, and Windows, making osquery a platform-agnostic layer for endpoint visibility.

In this guide you will:

Install osqueryi (interactive shell) and osqueryd (daemon) on Ubuntu and Windows
Explore the virtual table schema and write detection queries covering the most common attack techniques
Configure scheduled queries for continuous monitoring
Forward query results to a SIEM or log aggregator
Build a threat hunting query pack targeting persistence mechanisms, lateral movement indicators, and credential access

Prerequisites

One or more hosts to instrument: Ubuntu 22.04/24.04, RHEL/Rocky 9, or Windows Server 2019+
sudo (Linux) or Administrator (Windows) access
SQL familiarity — only SELECT, WHERE, JOIN, GROUP BY are needed
Optional but recommended: Wazuh, Elastic, or Splunk for central log collection

Step 1 — Install Osquery on Ubuntu / Debian

Add the official osquery APT repository and install the package:

# Import signing key
curl -L https://pkg.osquery.io/deb/pubkey.gpg | sudo apt-key add -
 
# Add repository
sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
 
# Install
sudo apt-get update && sudo apt-get install -y osquery
 
# Verify installation
osqueryi --version

Expected output:

osqueryi version 5.14.1

Step 2 — Install Osquery on RHEL / Rocky Linux

# Add RPM repository
curl -L https://pkg.osquery.io/rpm/osquery-s3-rpm-repo.repo \
  | sudo tee /etc/yum.repos.d/osquery.repo
 
# Install
sudo dnf install -y osquery
 
# Verify
osqueryi --version

Step 3 — Install Osquery on Windows

Download the MSI installer from the official releases page and install silently:

# Download latest MSI
$url = "https://pkg.osquery.io/windows/osquery-5.14.1.msi"
$dest = "$env:TEMP\osquery.msi"
Invoke-WebRequest -Uri $url -OutFile $dest
 
# Silent install
Start-Process msiexec.exe -ArgumentList "/i $dest /quiet /norestart" -Wait
 
# Add to PATH for the current session
$env:PATH += ";C:\Program Files\osquery"
 
# Verify
osqueryi.exe --version

Step 4 — Explore the Interactive Shell

Launch osqueryi to start an interactive SQL session:

sudo osqueryi

List all available virtual tables:

.tables

Inspect the schema of a specific table:

.schema processes
.schema listening_ports
.schema users

Run your first query — list all listening network services:

SELECT pid, address, port, protocol, family
FROM listening_ports
WHERE family = 2    -- IPv4
ORDER BY port;

Show running processes and their binary path on disk:

SELECT p.pid, p.name, p.cmdline, p.path, f.size
FROM processes AS p
LEFT JOIN file AS f ON p.path = f.path
ORDER BY p.start_time DESC
LIMIT 20;

Find processes running from unusual directories (a common malware staging location):

SELECT pid, name, path, cmdline, uid
FROM processes
WHERE path LIKE '/tmp/%'
   OR path LIKE '/dev/shm/%'
   OR path LIKE '%AppData%Temp%'
   OR path LIKE '%AppData%Roaming%';

Step 5 — Configure osqueryd (Daemon Mode)

The osqueryd daemon runs scheduled queries and writes results to a log file or sends them to a remote endpoint. Create the configuration directory and a base config:

sudo mkdir -p /etc/osquery /var/log/osquery

Create /etc/osquery/osquery.conf:

{
  "options": {
    "config_plugin": "filesystem",
    "logger_plugin": "filesystem",
    "logger_path": "/var/log/osquery",
    "schedule_splay_percent": 10,
    "utc": true,
    "host_identifier": "hostname",
    "enable_monitor": true
  },
 
  "schedule": {
    "process_list": {
      "query": "SELECT pid, name, path, cmdline, uid, parent FROM processes;",
      "interval": 300,
      "description": "Full process snapshot every 5 minutes"
    },
 
    "listening_ports": {
      "query": "SELECT pid, address, port, protocol FROM listening_ports WHERE family = 2;",
      "interval": 300,
      "description": "Active listening sockets"
    },
 
    "user_ssh_keys": {
      "query": "SELECT * FROM user_ssh_keys;",
      "interval": 3600,
      "description": "Authorized SSH keys — detect new additions"
    },
 
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 3600,
      "description": "Cron persistence check"
    },
 
    "startup_items": {
      "query": "SELECT name, path, source FROM startup_items;",
      "interval": 3600,
      "description": "Autostart items (Linux: init, systemd; Windows: registry run keys)"
    }
  }
}

Enable and start the service:

# systemd service ships with the package
sudo systemctl enable osqueryd
sudo systemctl start osqueryd
sudo systemctl status osqueryd

Verify logs are being written:

sudo tail -f /var/log/osquery/osqueryd.results.log | python3 -m json.tool

Step 6 — Threat Hunting Query Pack

Create /etc/osquery/packs/threat-hunting.conf — a collection of detection-focused queries:

{
  "queries": {
 
    "processes_no_disk": {
      "query": "SELECT pid, name, cmdline, uid FROM processes WHERE on_disk = 0;",
      "interval": 300,
      "description": "Processes running entirely in memory (fileless malware indicator)"
    },
 
    "suspicious_parent_child": {
      "query": "SELECT p.pid, p.name, p.cmdline, p.path, pp.name AS parent_name, pp.cmdline AS parent_cmdline FROM processes AS p JOIN processes AS pp ON p.parent = pp.pid WHERE (pp.name IN ('winword.exe','excel.exe','outlook.exe','powerpnt.exe','mshta.exe','wscript.exe','cscript.exe') AND p.name IN ('cmd.exe','powershell.exe','pwsh.exe','wmic.exe','certutil.exe','bitsadmin.exe')) OR (pp.name = 'svchost.exe' AND p.name NOT IN ('dllhost.exe','WerFault.exe','conhost.exe','taskhostw.exe','runtimebroker.exe','sihost.exe'));",
      "interval": 120,
      "description": "Office/scripting parent spawning shell children — common maldoc indicator"
    },
 
    "unsigned_network_processes": {
      "query": "SELECT p.pid, p.name, p.path, p.cmdline, s.signed FROM processes AS p LEFT JOIN signature AS s ON p.path = s.path JOIN process_open_sockets AS pos ON p.pid = pos.pid WHERE s.signed = 0 AND pos.remote_address NOT IN ('', '0.0.0.0', '::') AND pos.remote_port > 0;",
      "interval": 300,
      "description": "Unsigned binaries with active outbound network connections",
      "platform": "windows"
    },
 
    "new_local_admins": {
      "query": "SELECT u.username, u.uid, u.gid, g.groupname FROM users AS u JOIN user_groups AS ug ON u.uid = ug.uid JOIN groups AS g ON ug.gid = g.gid WHERE g.groupname IN ('sudo','wheel','admin','administrators');",
      "interval": 3600,
      "description": "All members of privileged local groups — diff to detect privilege escalation"
    },
 
    "cron_persistence": {
      "query": "SELECT command, path, minute, hour, day_of_month, month, day_of_week FROM crontab WHERE path NOT IN ('/etc/crontab','/etc/cron.d/') OR command NOT LIKE '/usr/%';",
      "interval": 1800,
      "description": "Unusual cron entries that may indicate persistence"
    },
 
    "kernel_modules": {
      "query": "SELECT name, size, used_by, status FROM kernel_modules;",
      "interval": 3600,
      "description": "Loaded kernel modules — rootkit persistence detection",
      "platform": "linux"
    },
 
    "suid_binaries": {
      "query": "SELECT path, filename, size, type, permissions FROM file WHERE (path LIKE '/usr/%' OR path LIKE '/bin/%' OR path LIKE '/sbin/%') AND permissions LIKE '%s%' AND type = 'regular';",
      "interval": 86400,
      "description": "SUID/SGID binaries — enumerate for privilege escalation candidates",
      "platform": "linux"
    },
 
    "registry_run_keys": {
      "query": "SELECT key, name, data FROM registry WHERE key IN ('HKEY_LOCAL_MACHINE\\\\SOFTWARE\\\\Microsoft\\\\Windows\\\\CurrentVersion\\\\Run','HKEY_LOCAL_MACHINE\\\\SOFTWARE\\\\Microsoft\\\\Windows\\\\CurrentVersion\\\\RunOnce','HKEY_CURRENT_USER\\\\SOFTWARE\\\\Microsoft\\\\Windows\\\\CurrentVersion\\\\Run','HKEY_CURRENT_USER\\\\SOFTWARE\\\\Microsoft\\\\Windows\\\\CurrentVersion\\\\RunOnce');",
      "interval": 1800,
      "description": "Registry autorun persistence keys",
      "platform": "windows"
    },
 
    "lsass_memory_access": {
      "query": "SELECT p.pid, p.name, p.path, p.cmdline FROM processes AS p JOIN process_open_sockets AS pos ON p.pid = pos.pid WHERE p.name IN ('procdump.exe','mimikatz.exe','pypykatz.exe','lsassy.exe','sekurlsa.dll') OR p.cmdline LIKE '%lsass%' OR p.cmdline LIKE '%sekurlsa%';",
      "interval": 60,
      "description": "Credential dumping tools or LSASS access attempts",
      "platform": "windows"
    },
 
    "world_writable_setuid": {
      "query": "SELECT path, permissions FROM file WHERE (path LIKE '/tmp/%' OR path LIKE '/var/tmp/%' OR path LIKE '/dev/shm/%') AND permissions LIKE '%x%';",
      "interval": 300,
      "description": "Executable files in temp directories — staging area for exploits",
      "platform": "linux"
    }
  }
}

Reference the pack from your main config by adding to /etc/osquery/osquery.conf:

{
  "packs": {
    "threat-hunting": "/etc/osquery/packs/threat-hunting.conf"
  }
}

Restart the daemon to load the pack:

sudo systemctl restart osqueryd

Step 7 — Forward Logs to a SIEM

Option A: Wazuh Agent (JSON log file monitor)

If you have Wazuh deployed, add a <localfile> stanza to /var/ossec/etc/ossec.conf on the host:

<localfile>
  <log_format>json</log_format>
  <location>/var/log/osquery/osqueryd.results.log</location>
  <label key="source">osquery</label>
</localfile>

Restart the Wazuh agent:

sudo systemctl restart wazuh-agent

Option B: Elastic Filebeat

Add a Filebeat input targeting the osquery log:

filebeat.inputs:
  - type: filestream
    id: osquery-results
    paths:
      - /var/log/osquery/osqueryd.results.log
    parsers:
      - ndjson:
          target: osquery
          add_error_key: true
    fields:
      dataset: osquery.result
    fields_under_root: true
 
output.elasticsearch:
  hosts: ["https://your-elastic:9200"]
  username: "filebeat_writer"
  password: "${ELASTIC_PASSWORD}"
  ssl.certificate_authorities: ["/etc/filebeat/ca.crt"]

Option C: osquery TLS Logger Plugin (native remote logging)

Configure osqueryd to post results directly to a Fleet server or custom TLS endpoint by setting in osquery.conf:

{
  "options": {
    "logger_plugin": "tls",
    "logger_tls_endpoint": "/api/v1/osquery/log",
    "logger_tls_period": 60,
    "tls_hostname": "fleet.your-domain.internal",
    "enroll_secret_path": "/etc/osquery/enroll.secret"
  }
}

This is the recommended approach when using FleetDM — an open-source osquery fleet manager that provides a web UI, live query runner, and vulnerability database integration.

Step 8 — Live Query Investigation Workflow

During an active incident, use osqueryi to answer triage questions interactively:

Q: Is any process making outbound connections on unusual ports?

SELECT p.name, p.pid, p.cmdline, pos.remote_address, pos.remote_port
FROM processes AS p
JOIN process_open_sockets AS pos ON p.pid = pos.pid
WHERE pos.remote_address NOT IN ('127.0.0.1','::1','')
  AND pos.remote_port NOT IN (80, 443, 53, 22, 123)
  AND pos.remote_port > 0;

Q: Which users have logged in recently?

SELECT username, type, tty, host, time, pid
FROM last
ORDER BY time DESC
LIMIT 30;

Q: Are there any new SUID binaries since the system baseline?

SELECT path, permissions, mtime
FROM file
WHERE (path LIKE '/usr/%' OR path LIKE '/bin/%')
  AND permissions LIKE '%s%'
  AND mtime > (strftime('%s','now') - 86400);  -- changed in last 24h

Q: What scripts have been executed from /tmp?

SELECT pid, name, path, cmdline, start_time
FROM processes
WHERE path LIKE '/tmp/%'
   OR cmdline LIKE '%/tmp/%'
   OR cmdline LIKE '%/dev/shm/%';

Q: Are any scheduled tasks (Windows) pointing to temp paths?

SELECT name, action, path, enabled
FROM scheduled_tasks
WHERE action LIKE '%Temp%'
   OR action LIKE '%AppData%'
   OR action LIKE '%ProgramData%';

Verification

Confirm osqueryd is running and results are being logged:

# Service status
sudo systemctl status osqueryd
 
# Log volume (should grow as scheduled queries fire)
sudo wc -l /var/log/osquery/osqueryd.results.log
 
# Verify pack is loaded — check INFO log for pack load messages
sudo grep -i "pack" /var/log/osquery/osqueryd.INFO | tail -20
 
# Test a live query from CLI (bypasses daemon, instant result)
sudo osqueryi --json "SELECT name, pid, uid FROM processes WHERE uid = 0 LIMIT 10;"

On Windows, verify service status:

Get-Service osqueryd | Select-Object Status, DisplayName
 
# View osquery log
Get-Content "C:\Program Files\osquery\log\osqueryd.results.log" -Tail 20

Troubleshooting

osqueryd fails to start — "config plugin failed"

Check the config file for JSON syntax errors. osquery is strict about trailing commas:

python3 -m json.tool /etc/osquery/osquery.conf

Scheduled queries produce no results

Confirm the schedule interval has elapsed. For initial testing, temporarily set "interval": 10 on a simple query, watch the results log, then restore the production interval.

Virtual table not found error in osqueryi

Some tables are platform-specific (e.g., registry on Windows only, kernel_modules on Linux only). Check the osquery table documentation for platform availability markers.

High CPU usage from osqueryd

Heavy queries (large directory traversals with file) can spike CPU. Use schedule_splay_percent to stagger queries across the fleet and increase intervals for expensive queries like suid_binaries.

Log rotation causing missed events

Install logrotate configuration for the osquery log directory to avoid unbounded disk growth while preserving 7 days of history:

cat << 'EOF' | sudo tee /etc/logrotate.d/osquery
/var/log/osquery/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    copytruncate
}
EOF

Summary

Osquery turns every endpoint into a queryable data source using standard SQL. You have now:

Installed osquery on Linux and Windows hosts
Explored the virtual table schema using osqueryi
Configured osqueryd with a base schedule for continuous monitoring
Deployed a threat-hunting query pack covering fileless malware, credential dumping, persistence mechanisms, and suspicious parent-child process relationships
Integrated log output with Wazuh, Elastic Filebeat, or FleetDM's native TLS logger
Built an ad-hoc investigation workflow for incident response triage

The real power of osquery scales with your fleet. Once you have osqueryd running across dozens or hundreds of hosts feeding into a central SIEM, a single query can correlate behaviour across the entire environment in seconds — a capability that previously required expensive EDR agents. Pair osquery with a tool like Velociraptor (covered separately) for even deeper forensic collection when a scheduled query flags suspicious activity.