Building a Self-Hosted EDR with osquery on Linux: Monitor Processes, Network, and File Changes Using SQL

Context & Why I Switched to osquery

Six months ago I was running OSSEC on a handful of VPS servers, drowning in log noise with no easy way to ask targeted questions about my systems. What I actually wanted was: “Show me every process that opened a connection to a foreign IP in the last 10 minutes.” With traditional logging stacks, answering that means parsing log files, chaining greps, and hoping the right data was captured in the right format.

osquery fixes that. Instead of tailing logs, you query your system like a relational database. Every running process, open socket, loaded kernel module, cron job, and user account becomes a row in a SQL table. JOIN across tables. Filter with WHERE clauses. Schedule queries to run every 30 seconds. After six months in production, here is my honest take on building a lightweight, self-hosted EDR around it.

Worth noting the positioning: osquery is not a replacement for Wazuh or Suricata. Those tools handle log aggregation and network IDS well. osquery is built for on-demand forensic questions about endpoint state — what is running right now, what changed in the last hour, which process opened that socket. They work well together.

Installation

Ubuntu / Debian

Meta open-sourced osquery in 2014 and donated it to the Linux Foundation in 2019. Official packages live at pkg.osquery.io. Setup takes about two minutes:

# Modern key management (Ubuntu 22.04+ / Debian 11+)
# apt-key is deprecated — use gpg --dearmor with signed-by instead
curl -fsSL https://pkg.osquery.io/deb/pubkey.gpg | \
  sudo gpg --dearmor -o /etc/apt/keyrings/osquery.gpg

echo "deb [arch=amd64 signed-by=/etc/apt/keyrings/osquery.gpg] https://pkg.osquery.io/deb deb main" | \
  sudo tee /etc/apt/sources.list.d/osquery.list

# Install
sudo apt-get update && sudo apt-get install osquery -y

# Verify
osqueryi --version

CentOS / RHEL

curl -L https://pkg.osquery.io/rpm/GPG | \
  sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
sudo yum-config-manager --add-repo https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
sudo yum install osquery -y

Two binaries land on your system: osqueryi (interactive SQL shell, ideal for ad-hoc investigation) and osqueryd (the daemon that runs scheduled queries and writes results to disk continuously).

Configuration

Creating the Main Config File

osqueryd reads its config from /etc/osquery/osquery.conf. This is where you define scheduled queries, file integrity monitoring (FIM) paths, and log destinations.

sudo mkdir -p /etc/osquery
sudo nano /etc/osquery/osquery.conf

Here is the config I settled on after several iterations in production:

{
  "options": {
    "config_plugin": "filesystem",
    "logger_plugin": "filesystem",
    "logger_path": "/var/log/osquery",
    "disable_logging": "false",
    "log_result_events": "true",
    "schedule_splay_percent": "10",
    "utc": "true"
  },
  "schedule": {
    "running_processes": {
      "query": "SELECT pid, name, path, cmdline, uid, parent FROM processes WHERE path != '';",
      "interval": 60
    },
    "network_connections": {
      "query": "SELECT p.pid, p.name, lc.local_address, lc.local_port, lc.remote_address, lc.remote_port FROM process_open_sockets lc LEFT JOIN processes p USING(pid) WHERE lc.remote_address NOT IN ('', '0.0.0.0', '::');",
      "interval": 30
    },
    "user_accounts": {
      "query": "SELECT username, uid, gid, directory, shell FROM users;",
      "interval": 300
    },
    "cron_jobs": {
      "query": "SELECT command, path, minute, hour FROM crontab;",
      "interval": 300
    },
    "suid_binaries": {
      "query": "SELECT path, username, groupname, permissions FROM suid_bin;",
      "interval": 3600
    },
    "kernel_modules": {
      "query": "SELECT name, size, status, address FROM kernel_modules;",
      "interval": 3600
    }
  },
  "file_paths": {
    "system_configs": [
      "/etc/%%",
      "/etc/ssh/%%"
    ],
    "user_ssh": [
      "/root/.ssh/%%",
      "/home/%/.ssh/%%"
    ],
    "system_binaries": [
      "/usr/bin/%%",
      "/usr/sbin/%%",
      "/bin/%%",
      "/sbin/%%"
    ]
  },
  "exclude_paths": {
    "system_configs": ["/etc/mtab"]
  }
}

Under schedule, each query runs at its defined interval and logs output as JSON. Under file_paths, osquery registers those directories with the kernel audit subsystem and emits events on CREATE, MODIFY, or DELETE. Note the glob syntax: %% matches recursively across subdirectories, while a single % matches exactly one path segment.

Enabling the Linux Audit Framework for FIM

FIM events need the kernel audit subsystem running. Create /etc/osquery/osquery.flags:

--disable_audit=false
--audit_allow_config=true
--audit_allow_process_events=true
--audit_allow_sockets=true

Starting the Daemon

sudo systemctl enable osqueryd
sudo systemctl start osqueryd
sudo systemctl status osqueryd

Logs go to /var/log/osquery/osqueryd.results.log as newline-delimited JSON. Each entry carries the query name, a Unix timestamp, an action field (added / removed for differential queries), and the row data itself.

One early lesson: generate strong, unique credentials for any log aggregation endpoint or admin panel you put in front of these logs. I use the password generator at toolcraft.app/en/tools/security/password-generator for server passwords — it runs entirely in the browser with no data sent over the network. When you are hardening security infrastructure, that distinction matters.

Verification & Monitoring

Interactive Investigation with osqueryi

Drop into the interactive shell and start asking forensic questions immediately. No new query language to learn — just plain SQL:

-- Every process listening on a non-standard port
SELECT p.pid, p.name, p.path, lp.port, lp.protocol
FROM listening_ports lp
JOIN processes p USING(pid)
WHERE lp.port NOT IN (22, 80, 443, 3306)
ORDER BY lp.port;

-- Processes with outbound connections to public IPs
SELECT p.name, p.pid, p.cmdline,
       s.remote_address, s.remote_port
FROM process_open_sockets s
JOIN processes p USING(pid)
WHERE s.remote_address NOT LIKE '10.%'
  AND s.remote_address NOT LIKE '192.168.%'
  AND s.remote_address NOT LIKE '172.16.%'
  AND s.remote_address != '127.0.0.1'
  AND s.remote_address != '';

-- Files in /etc modified in the last hour
SELECT path, mtime, size
FROM file
WHERE path LIKE '/etc/%'
  AND mtime > (strftime('%s','now') - 3600)
ORDER BY mtime DESC;

-- Root-owned processes not in a known-safe list
SELECT pid, name, uid, path, cmdline
FROM processes
WHERE uid = 0
  AND name NOT IN ('systemd','sshd','cron','rsyslogd','agetty');

Differential Queries for Change Detection

Differential mode is osquery’s killer feature for EDR work. When osqueryd runs a scheduled query, it compares the current result set to the previous run. Only changed rows get written to the log — new rows tagged added, removed rows tagged removed.

That makes the SUID binary query above an automatic change detector. A new SUID binary appearing on disk shows up as an added row within the hour. That is exactly how I caught a cryptominer on one of my servers — the malware dropped a setuid wrapper binary, and osquery flagged it within two scheduling cycles, roughly two hours after the compromise.

Reading File Integrity Events

-- FIM events from the last 10 minutes (requires audit enabled)
SELECT target_path, action, time, md5, sha256
FROM file_events
WHERE time > (strftime('%s', 'now') - 600)
ORDER BY time DESC;

Spotting UPDATED events on /etc/passwd, /etc/sudoers, or any binary under /usr/bin outside a maintenance window means something changed that should not have. Investigate before assuming it was routine.

Building a Simple Alert Pipeline

For a small fleet — say, 5 to 20 servers — tailing the results log and scripting alerts is completely workable without a full SIEM:

#!/bin/bash
# /usr/local/bin/osquery-alert.sh
# Tail osquery results and alert on suspicious patterns

LOGFILE="/var/log/osquery/osqueryd.results.log"
ALERT_LOG="/var/log/osquery-alerts.log"

tail -F "$LOGFILE" | while read -r line; do
  # Alert on any new SUID binary
  if echo "$line" | grep -q '"name":"suid_binaries"' && \
     echo "$line" | grep -q '"action":"added"'; then
    echo "$(date -u): NEW SUID BINARY: $line" >> "$ALERT_LOG"
    # Insert your notification (curl to Telegram, sendmail, etc.)
  fi

  # Alert on outbound connections to high ports
  if echo "$line" | grep -q '"name":"network_connections"'; then
    REMOTE_PORT=$(echo "$line" | python3 -c \
      "import sys,json; d=json.loads(sys.stdin.read()); \
      print(d.get('columns',{}).get('remote_port','0'))" 2>/dev/null)
    if [[ "$REMOTE_PORT" =~ ^[0-9]+$ ]] && [[ "$REMOTE_PORT" -gt 1024 ]]; then
      echo "$(date -u): SUSPICIOUS OUTBOUND $REMOTE_PORT: $line" >> "$ALERT_LOG"
    fi
  fi
done

# Run as a background service
sudo chmod +x /usr/local/bin/osquery-alert.sh
nohup /usr/local/bin/osquery-alert.sh &

Verifying the Scheduled Queries Are Running

-- Check osquery daemon status
SELECT pid, version, config_hash, config_valid
FROM osquery_info;

-- Confirm scheduled queries are executing
SELECT name, executions, last_executed, average_memory, last_status
FROM osquery_schedule;

-- List all available tables on this host
SELECT name, description
FROM osquery_registry
WHERE type = 'table'
ORDER BY name;

After six months, the biggest surprise was not catching active intrusions — though osquery did flag that cryptominer within two hours. The real payoff is baseline visibility: knowing exactly what is running, what is connecting where, and what changed in /etc last Tuesday at 3 AM. That situational awareness beats reactive alerting every time.

SQL also makes onboarding teammates painless. A developer who knows almost nothing about Linux security can write SELECT * FROM processes WHERE name = 'python3' and immediately understand what they are looking at. Low barrier to entry is why osquery stays in my standard server setup even when heavier commercial tools are available.