Deploying Osquery for Endpoint Monitoring
When to Use
Use this skill when:
- Deploying osquery across Windows, macOS, and Linux endpoints for fleet-wide visibility
- Building threat hunting queries using osquery's SQL interface
- Monitoring endpoint compliance (installed software, open ports, running services)
- Integrating osquery data with SIEM or Kolide/Fleet for centralized management
Do not use for real-time alerting (osquery is periodic/on-demand; use EDR for real-time).
Common Misconfigurations & Verification
- Packs declared but never scheduled: a
"packs" entry pointing to a missing/unreadable path is silently skipped — check SELECT name, interval FROM osquery_schedule; and SELECT * FROM osquery_packs; on the endpoint to confirm queries are actually loaded and running.
- Event tables empty because events are off:
process_events, socket_events, and file_events return nothing unless --disable_events=false AND the audit publisher is on (--disable_audit=false, --audit_allow_config=true). On Linux confirm osquery owns auditd (it conflicts with a running auditd/auditbeat).
- Scope too narrow / WHERE filters out hits: the fileless query
WHERE> and the uid >= 1000 user query miss kernel-spawned or service-account activity. Validate WHERE clauses against a known-positive before trusting a clean result.
- Fleet enrollment or result logging broken: confirm hosts appear in FleetDM and that
/var/log/osquery/osqueryd.results.log is filling; an enrolled host with no results log forwards nothing. Differential mode logs only a baseline on first run.
- Verify end-to-end: trigger a watched condition (open a listening port, add a crontab/Run-key per Atomic Red Team T1547/T1053) and confirm the scheduled query emits a row to the results log and into the SIEM at the next interval.
Prerequisites
- Osquery package for target OS (https://osquery.io/downloads)
- Fleet management server (Kolide Fleet or FleetDM) for enterprise deployment
- TLS certificates for secure agent-to-server communication
- Log aggregation pipeline (Filebeat, Fluentd) for osquery result logs
Workflow
Step 1: Install Osquery
# Ubuntu/Debian
export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys $OSQUERY_KEY
add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
apt-get update && apt-get install osquery -y
# Windows (MSI)
# Download from https://osquery.io/downloads/official
msiexec /i osquery-5.12.1.msi /quiet
# macOS
brew install osquery
Step 2: Configure Osquery
// /etc/osquery/osquery.conf (Linux/macOS) or C:\ProgramData\osquery\osquery.conf
{
"options": {
"config_plugin": "filesystem",
"logger_plugin": "filesystem",
"logger_path": "/var/log/osquery",
"disable_logging": "false",
"schedule_splay_percent": "10",
"events_expiry": "3600",
"verbose": "false",
"worker_threads": "2",
"enable_monitor": "true",
"disable_events": "false",
"disable_audit": "false",
"audit_allow_config": "true",
"host_identifier": "hostname",
"enable_syslog": "true"
},
"schedule": {
"process_monitor": {
"query": "SELECT pid, name, path, cmdline, uid, parent FROM processes WHERE
"interval": 300,
"description": "Detect processes running without on-disk binary (fileless)"
},
"listening_ports": {
"query": "SELECT DISTINCT p.name, p.path, lp.port, lp.protocol, lp.address FROM listening_ports lp JOIN processes p ON lp.pid = p.pid WHERE lp.port != 0;",
"interval": 600,
"description": "Monitor listening network ports"
},
"persistence_check": {
"query": "SELECT name, path, source FROM startup_items;",
"interval": 3600,
"description": "Monitor persistence mechanisms"
},
"installed_packages": {
"query": "SELECT name, version, source FROM deb_packages;",
"interval": 86400,
"description": "Daily software inventory"
},
"users_and_groups": {
"query": "SELECT u.username, u.uid, u.gid, u.shell, u.directory FROM users u WHERE u.uid >= 1000;",
"interval": 3600
},
"crontab_monitor": {
"query": "SELECT * FROM crontab;",
"interval": 3600,
"description": "Monitor scheduled tasks"
},
"suid_binaries": {
"query": "SELECT path, username, permissions FROM suid_bin;",
"interval": 86400,
"description": "Detect SUID binaries"
}
},
"packs": {
"incident-response": "/usr/share/osquery/packs/incident-response.conf",
"ossec-rootkit": "/usr/share/osquery/packs/ossec-rootkit.conf",
"vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
}
}
Step 3: Threat Hunting Queries
-- Detect processes with no on-disk binary (potential fileless malware)
SELECT pid, name, path, cmdline FROM processes WHERE
-- Find listening ports not associated with known services
SELECT lp.port, lp.protocol, p.name, p.path
FROM listening_ports lp JOIN processes p ON lp.pid = p.pid
WHERE lp.port NOT IN (22, 80, 443, 3306, 5432);
-- Detect unauthorized SSH keys
SELECT * FROM authorized_keys WHERE NOT key LIKE '%admin-team%';
-- Find recently modified system binaries
SELECT path, mtime, size FROM file
WHERE path LIKE '/usr/bin/%' AND mtime > (strftime('%s', 'now') - 86400);
-- Detect processes connecting to external IPs
SELECT DISTINCT p.name, p.path, pn.remote_address, pn.remote_port
FROM process_open_sockets pn JOIN processes p ON pn.pid = p.pid
WHERE pn.remote_address NOT LIKE '10.%'
AND pn.remote_address NOT LIKE '172.16.%'
AND pn.remote_address NOT LIKE '192.168.%'
AND pn.remote_address != '127.0.0.1'
AND pn.remote_address != '0.0.0.0';
-- Windows: Detect unsigned running executables
SELECT p.name, p.path, a.result AS signature_status
FROM processes p JOIN authenticode a ON p.path = a.path
WHERE a.result != 'trusted';
Step 4: Deploy FleetDM for Centralized Management
# FleetDM provides centralized osquery management
# Deploy FleetDM server, configure agents to report to it
# Agents use TLS enrollment and config from Fleet
# Agent configuration for Fleet:
# --tls_hostname=fleet.corp.com
# --tls_server_certs=/etc/osquery/fleet.pem
# --enroll_secret_path=/etc/osquery/enroll_secret
Key Concepts
| Term |
Definition |
| Osquery |
Open-source endpoint agent that exposes OS state as SQL tables for querying |
| Schedule |
Periodic queries that run at defined intervals and log results |
| Pack |
Collection of related queries grouped for specific use cases (IR, compliance) |
| FleetDM |
Open-source osquery fleet management platform |
| Differential Results |
Osquery logs only changes between query executions, reducing data volume |
Tools & Systems
- Osquery: https://osquery.io/ - endpoint visibility agent
- FleetDM: https://fleetdm.com/ - centralized fleet management
- Kolide: Cloud-based osquery management with Slack integration
- osquery-go: Go client library for osquery extensions
Common Pitfalls
- Query performance: Complex queries with large table scans impact endpoint performance. Use WHERE clauses and test query cost with
EXPLAIN.
- Schedule intervals too aggressive: Running heavy queries every 60 seconds causes CPU spikes. Use 300-3600 second intervals for most queries.
- Not using differential mode: Without differential logging, osquery logs all results every interval. Differential mode logs only changes.
- Missing event tables: Some osquery tables require events framework enabled (process_events, socket_events). Enable with
--disable_events=false.
1---2name: deploying-osquery-for-endpoint-monitoring3description: 'Deploys and configures osquery for real-time endpoint monitoring using SQL-based queries to inspect running4 processes, open ports, installed software, and system configuration. Use when building visibility into endpoint state, threat5 hunting across fleet, or implementing compliance monitoring. Activates for requests involving osquery deployment, endpoint6 visibility, fleet management, or SQL-based endpoint querying.78 '9domain: cybersecurity10subdomain: endpoint-security11tags:12- endpoint13- osquery14- endpoint-monitoring15- threat-hunting16- fleet-management17mitre_attack:18- T154719- T104920- T162021- T1053.00322- T1548.00123- T155224version: 1.0.025author: Krishna Kumar (xalgord)26license: Apache-2.027nist_csf:28- PR.PS-0129- PR.PS-0230- DE.CM-0131- PR.IR-0132metadata:33 source: https://skillmd.com/skills/xalgord/deploying-osquery-for-endpoint-monitoring34---3536# Deploying Osquery for Endpoint Monitoring3738## When to Use3940Use this skill when:41- Deploying osquery across Windows, macOS, and Linux endpoints for fleet-wide visibility42- Building threat hunting queries using osquery's SQL interface43- Monitoring endpoint compliance (installed software, open ports, running services)44- Integrating osquery data with SIEM or Kolide/Fleet for centralized management4546**Do not use** for real-time alerting (osquery is periodic/on-demand; use EDR for real-time).4748## Common Misconfigurations & Verification4950- **Packs declared but never scheduled:** a `"packs"` entry pointing to a missing/unreadable path is silently skipped — check `SELECT name, interval FROM osquery_schedule;` and `SELECT * FROM osquery_packs;` on the endpoint to confirm queries are actually loaded and running.51- **Event tables empty because events are off:** `process_events`, `socket_events`, and `file_events` return nothing unless `--disable_events=false` AND the audit publisher is on (`--disable_audit=false`, `--audit_allow_config=true`). On Linux confirm osquery owns auditd (it conflicts with a running `auditd`/`auditbeat`).52- **Scope too narrow / WHERE filters out hits:** the fileless query `WHERE on_disk = 0` and the `uid >= 1000` user query miss kernel-spawned or service-account activity. Validate WHERE clauses against a known-positive before trusting a clean result.53- **Fleet enrollment or result logging broken:** confirm hosts appear in FleetDM and that `/var/log/osquery/osqueryd.results.log` is filling; an enrolled host with no results log forwards nothing. Differential mode logs only a baseline on first run.54- **Verify end-to-end:** trigger a watched condition (open a listening port, add a crontab/Run-key per Atomic Red Team T1547/T1053) and confirm the scheduled query emits a row to the results log and into the SIEM at the next interval.5556## Prerequisites5758- Osquery package for target OS (https://osquery.io/downloads)59- Fleet management server (Kolide Fleet or FleetDM) for enterprise deployment60- TLS certificates for secure agent-to-server communication61- Log aggregation pipeline (Filebeat, Fluentd) for osquery result logs6263## Workflow6465### Step 1: Install Osquery6667```bash68# Ubuntu/Debian69export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B70apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys $OSQUERY_KEY71add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'72apt-get update && apt-get install osquery -y7374# Windows (MSI)75# Download from https://osquery.io/downloads/official76msiexec /i osquery-5.12.1.msi /quiet7778# macOS79brew install osquery80```8182### Step 2: Configure Osquery8384```jsonc85// /etc/osquery/osquery.conf (Linux/macOS) or C:\ProgramData\osquery\osquery.conf86{87 "options": {88 "config_plugin": "filesystem",89 "logger_plugin": "filesystem",90 "logger_path": "/var/log/osquery",91 "disable_logging": "false",92 "schedule_splay_percent": "10",93 "events_expiry": "3600",94 "verbose": "false",95 "worker_threads": "2",96 "enable_monitor": "true",97 "disable_events": "false",98 "disable_audit": "false",99 "audit_allow_config": "true",100 "host_identifier": "hostname",101 "enable_syslog": "true"102 },103 "schedule": {104 "process_monitor": {105 "query": "SELECT pid, name, path, cmdline, uid, parent FROM processes WHERE on_disk = 0;",106 "interval": 300,107 "description": "Detect processes running without on-disk binary (fileless)"108 },109 "listening_ports": {110 "query": "SELECT DISTINCT p.name, p.path, lp.port, lp.protocol, lp.address FROM listening_ports lp JOIN processes p ON lp.pid = p.pid WHERE lp.port != 0;",111 "interval": 600,112 "description": "Monitor listening network ports"113 },114 "persistence_check": {115 "query": "SELECT name, path, source FROM startup_items;",116 "interval": 3600,117 "description": "Monitor persistence mechanisms"118 },119 "installed_packages": {120 "query": "SELECT name, version, source FROM deb_packages;",121 "interval": 86400,122 "description": "Daily software inventory"123 },124 "users_and_groups": {125 "query": "SELECT u.username, u.uid, u.gid, u.shell, u.directory FROM users u WHERE u.uid >= 1000;",126 "interval": 3600127 },128 "crontab_monitor": {129 "query": "SELECT * FROM crontab;",130 "interval": 3600,131 "description": "Monitor scheduled tasks"132 },133 "suid_binaries": {134 "query": "SELECT path, username, permissions FROM suid_bin;",135 "interval": 86400,136 "description": "Detect SUID binaries"137 }138 },139 "packs": {140 "incident-response": "/usr/share/osquery/packs/incident-response.conf",141 "ossec-rootkit": "/usr/share/osquery/packs/ossec-rootkit.conf",142 "vuln-management": "/usr/share/osquery/packs/vuln-management.conf"143 }144}145```146147### Step 3: Threat Hunting Queries148149```sql150-- Detect processes with no on-disk binary (potential fileless malware)151SELECT pid, name, path, cmdline FROM processes WHERE on_disk = 0;152153-- Find listening ports not associated with known services154SELECT lp.port, lp.protocol, p.name, p.path155FROM listening_ports lp JOIN processes p ON lp.pid = p.pid156WHERE lp.port NOT IN (22, 80, 443, 3306, 5432);157158-- Detect unauthorized SSH keys159SELECT * FROM authorized_keys WHERE NOT key LIKE '%admin-team%';160161-- Find recently modified system binaries162SELECT path, mtime, size FROM file163WHERE path LIKE '/usr/bin/%' AND mtime > (strftime('%s', 'now') - 86400);164165-- Detect processes connecting to external IPs166SELECT DISTINCT p.name, p.path, pn.remote_address, pn.remote_port167FROM process_open_sockets pn JOIN processes p ON pn.pid = p.pid168WHERE pn.remote_address NOT LIKE '10.%'169 AND pn.remote_address NOT LIKE '172.16.%'170 AND pn.remote_address NOT LIKE '192.168.%'171 AND pn.remote_address != '127.0.0.1'172 AND pn.remote_address != '0.0.0.0';173174-- Windows: Detect unsigned running executables175SELECT p.name, p.path, a.result AS signature_status176FROM processes p JOIN authenticode a ON p.path = a.path177WHERE a.result != 'trusted';178```179180### Step 4: Deploy FleetDM for Centralized Management181182```bash183# FleetDM provides centralized osquery management184# Deploy FleetDM server, configure agents to report to it185# Agents use TLS enrollment and config from Fleet186187# Agent configuration for Fleet:188# --tls_hostname=fleet.corp.com189# --tls_server_certs=/etc/osquery/fleet.pem190# --enroll_secret_path=/etc/osquery/enroll_secret191```192193## Key Concepts194195| Term | Definition |196|------|-----------|197| **Osquery** | Open-source endpoint agent that exposes OS state as SQL tables for querying |198| **Schedule** | Periodic queries that run at defined intervals and log results |199| **Pack** | Collection of related queries grouped for specific use cases (IR, compliance) |200| **FleetDM** | Open-source osquery fleet management platform |201| **Differential Results** | Osquery logs only changes between query executions, reducing data volume |202203## Tools & Systems204205- **Osquery**: https://osquery.io/ - endpoint visibility agent206- **FleetDM**: https://fleetdm.com/ - centralized fleet management207- **Kolide**: Cloud-based osquery management with Slack integration208- **osquery-go**: Go client library for osquery extensions209210## Common Pitfalls211212- **Query performance**: Complex queries with large table scans impact endpoint performance. Use WHERE clauses and test query cost with `EXPLAIN`.213- **Schedule intervals too aggressive**: Running heavy queries every 60 seconds causes CPU spikes. Use 300-3600 second intervals for most queries.214- **Not using differential mode**: Without differential logging, osquery logs all results every interval. Differential mode logs only changes.215- **Missing event tables**: Some osquery tables require events framework enabled (process_events, socket_events). Enable with `--disable_events=false`.