osquery Host Security Detection in Practice: EDR Endpoint Monitoring Configuration Guide

osquery treats the OS as a database. SQL-query processes, network connections, files, and login events. Core of lightweight EDR. Full path: install, base queries, scheduled collection, alerting.

Use Cases

osquery host security detection covers three cases: EDR endpoint monitoring—stand in for parts of commercial EDR with continuous process, network, and file monitoring; incident forensics—SQL-query suspicious processes, anomalous connections, and login records after an event; host asset inventory—bulk-list software, users, ports, and startup items. osquery queries system state read-only. Low overhead. Fits large-scale server fleets.

Prerequisites

• Linux server (Debian/Ubuntu or CentOS/RHEL)
• root (required for install and config)
• Basic SQL
• Optional: Fleet/Zeek or similar management plane (this guide uses standalone osqueryd)

How It Works

osquery maps OS subsystems to virtual tables: processes is the process list, listening_ports is listeners, file is the filesystem, last is login records. Built-in collectors populate each table in real time. Query interactively with osqueryi, or run scheduled queries via the osqueryd daemon and write results to logs. OS-as-database: one SQL surface for security analysis. Sharply cuts endpoint monitoring complexity.

Steps

1. Install osquery

`# Debian/Ubuntu
curl -L https://pkg.osquery.io/deb/osquery_5.14.1-1.linux_amd64.deb -o osquery.deb
sudo dpkg -i osquery.deb

# CentOS/RHEL
curl -L https://pkg.osquery.io/rpm/osquery-5.14.1-1.el8.x86_64.rpm -o osquery.rpm
sudo rpm -ivh osquery.rpm

# Verify
osqueryi --version`

If the download is slow, run sudo apt install -y osquery (some distro repos already ship it). Install provides both osqueryi (interactive) and osqueryd (daemon).

Steps (cont.)

2. Interactive queries on core tables

`# Enter the interactive shell
sudo osqueryi

# List all available tables
.tables

# Query listening ports and corresponding processes
SELECT p.name, lp.port, lp.address
FROM listening_ports lp
JOIN processes p ON lp.pid = p.pid
WHERE lp.port > 1024;`

Core tables: processes (processes), listening_ports (listeners), users (users), crontab (cron), file (files), last (logins), os_version (OS version). Inspect schema with .schema <table>.

3. Write osqueryd monitor config

Create the daemon config. Define the scheduled query pack. Edit /etc/osquery/osquery.conf:

`{
  "schedule": {
    "listening_ports": {
      "query": "SELECT DISTINCT p.name, lp.port, lp.address FROM listening_ports lp JOIN processes p ON lp.pid = p.pid WHERE lp.port > 1024;",
      "interval": 300
    },
    "login_events": {
      "query": "SELECT username, host, time FROM last ORDER BY time DESC LIMIT 50;",
      "interval": 600
    },
    "crontab_changes": {
      "query": "SELECT path, command, month, day, hour, minute FROM crontab;",
      "interval": 300
    },
    "unexpected_root_processes": {
      "query": "SELECT name, path FROM processes WHERE uid = 0 AND name NOT IN ('sshd','systemd','bash','nginx','mysql');",
      "interval": 300
    }
  },
  "logger": {
    "path": "/var/log/osquery/",
    "snapshot": true
  }
}`

Notes: schedule defines query jobs; interval is seconds; logger.path is the results log directory; snapshot: true records full results each run (not diffs).

4. Start the daemon

`# Config validation (syntax check)
sudo osqueryd --config_path=/etc/osquery/osquery.conf --config_check

# Start and enable on boot
sudo systemctl enable osqueryd
sudo systemctl start osqueryd

# Check running status
sudo systemctl status osqueryd

# View collection logs
sudo tail -f /var/log/osquery/osqueryd.results.log`

Use --verbose for debug logs. When healthy, osqueryd.results.log receives scheduled results on each interval.

5. Anomaly detection and alerting

Match the results log with a shell script:

`# Check for unexpected root processes and alert every 5 minutes
#!/bin/bash
LOG=/var/log/osquery/osqueryd.results.log
grep "unexpected_root_processes" $LOG | tail -n 5 | while read line; do
  echo "[$(date)] Detected unexpected root process: $line" >> /var/log/osquery/alert.log
  # Can be replaced with webhook/email notification
done`

A more complete setup: wire in Fleet (open-source osquery manager) for multi-host query packs and alert rules. Enterprise EDR capability.

Verify

Confirm osquery monitoring works:

• osqueryd --config_check returns no errors; config syntax is valid
• systemctl status osqueryd shows active (running)
• After one query interval, inspect /var/log/osquery/osqueryd.results.log; results are written
• Run sudo osqueryi -c "SELECT * FROM processes WHERE name LIKE '%nc%'" to confirm SQL works

FAQ

Q1: osqueryd fails to start with a config file error. What now?

Run osqueryd --config_path=/etc/osquery/osquery.conf --config_check to locate the syntax error. Common causes: trailing comma in JSON, missing interval, misspelled table name. Fix, then systemctl start osqueryd. Validate a single query with osqueryi --json.

Q2: Does osquery conflict with commercial EDR?

No. osquery is a read-only query engine. It does not intercept syscalls. Coexists with most security products. Avoid duplicate monitoring of the same resources (log noise). If a commercial EDR is already on the host and coverage overlaps, treat osquery as a supplemental collector—enable only queries the EDR does not cover.

Q3: Will large file-table queries burn resources?

Yes. Full-disk file scans are expensive. In production, constrain paths—e.g. /etc, /tmp, /var/www—and use --file with glob patterns. Tune interval. Do not schedule high-cost queries frequently.

Summary

osquery unifies host security collection via SQL. Cost-effective lightweight EDR and host IDS. Path: install, interactive query, schedule, daemon, log alerts. Production: keep query packs lean, rotate logs, monitor sensitive directories only, then fold into Fleet for central management. Makes endpoint monitoring deployable and sustainable.