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).
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-monitoring
3description: 'Deploys and configures osquery for real-time endpoint monitoring using
4 SQL-based queries to inspect running processes, open ports, installed software,
5 and system configuration. Use when building visibility into endpoint state, threat
6 hunting across fleet, or implementing compliance monitoring. Activates for requests
7 involving osquery deployment, endpoint visibility, fleet management, or SQL-based
8 endpoint querying.
9
10 '
11domain: cybersecurity
12subdomain: endpoint-security
13tags:
14- endpoint
15- osquery
16- endpoint-monitoring
17- threat-hunting
18- fleet-management
19mitre_attack:
20- T1547.001
21- T1053.005
22- T1543.003
23- T1057
24- T1071.001
25version: 1.0.0
26author: mahipal
27license: Apache-2.0
28nist_csf:
29- PR.PS-01
30- PR.PS-02
31- DE.CM-01
32- PR.IR-01
33source: https://github.com/mukul975/Anthropic-Cybersecurity-Skills
34source_commit: 04450304b12645cb2b974ab96d28c0664758a88d
35metadata:
36 source: https://skillmd.com/skills/yanacuti1121/deploying-osquery-for-endpoint-monitoring
37---
38
39# Deploying Osquery for Endpoint Monitoring
40
41## When to Use
42
43Use this skill when:
44- Deploying osquery across Windows, macOS, and Linux endpoints for fleet-wide visibility
45- Building threat hunting queries using osquery's SQL interface
46- Monitoring endpoint compliance (installed software, open ports, running services)
47- Integrating osquery data with SIEM or Kolide/Fleet for centralized management
48
49**Do not use** for real-time alerting (osquery is periodic/on-demand; use EDR for real-time).
50
51## Prerequisites
52
53- Osquery package for target OS (https://osquery.io/downloads)
54- Fleet management server (Kolide Fleet or FleetDM) for enterprise deployment
55- TLS certificates for secure agent-to-server communication
56- Log aggregation pipeline (Filebeat, Fluentd) for osquery result logs
57
58## Workflow
59
60### Step 1: Install Osquery
61
62```bash
63# Ubuntu/Debian
64export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
65apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys $OSQUERY_KEY
66add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
67apt-get update && apt-get install osquery -y
68
69# Windows (MSI)
70# Download from https://osquery.io/downloads/official
71msiexec /i osquery-5.12.1.msi /quiet
72
73# macOS
74brew install osquery
75```
76
77### Step 2: Configure Osquery
78
79```json
80// /etc/osquery/osquery.conf (Linux/macOS) or C:\ProgramData\osquery\osquery.conf
81{
82 "options": {
83 "config_plugin": "filesystem",
84 "logger_plugin": "filesystem",
85 "logger_path": "/var/log/osquery",
86 "disable_logging": "false",
87 "schedule_splay_percent": "10",
88 "events_expiry": "3600",
89 "verbose": "false",
90 "worker_threads": "2",
91 "enable_monitor": "true",
92 "disable_events": "false",
93 "disable_audit": "false",
94 "audit_allow_config": "true",
95 "host_identifier": "hostname",
96 "enable_syslog": "true"
97 },
98 "schedule": {
99 "process_monitor": {
100 "query": "SELECT pid, name, path, cmdline, uid, parent FROM processes WHERE on_disk = 0;",
101 "interval": 300,
102 "description": "Detect processes running without on-disk binary (fileless)"
103 },
104 "listening_ports": {
105 "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;",
106 "interval": 600,
107 "description": "Monitor listening network ports"
108 },
109 "persistence_check": {
110 "query": "SELECT name, path, source FROM startup_items;",
111 "interval": 3600,
112 "description": "Monitor persistence mechanisms"
113 },
114 "installed_packages": {
115 "query": "SELECT name, version, source FROM deb_packages;",
116 "interval": 86400,
117 "description": "Daily software inventory"
118 },
119 "users_and_groups": {
120 "query": "SELECT u.username, u.uid, u.gid, u.shell, u.directory FROM users u WHERE u.uid >= 1000;",
121 "interval": 3600
122 },
123 "crontab_monitor": {
124 "query": "SELECT * FROM crontab;",
125 "interval": 3600,
126 "description": "Monitor scheduled tasks"
127 },
128 "suid_binaries": {
129 "query": "SELECT path, username, permissions FROM suid_bin;",
130 "interval": 86400,
131 "description": "Detect SUID binaries"
132 }
133 },
134 "packs": {
135 "incident-response": "/usr/share/osquery/packs/incident-response.conf",
136 "ossec-rootkit": "/usr/share/osquery/packs/ossec-rootkit.conf",
137 "vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
138 }
139}
140```
141
142### Step 3: Threat Hunting Queries
143
144```sql
145-- Detect processes with no on-disk binary (potential fileless malware)
146SELECT pid, name, path, cmdline FROM processes WHERE on_disk = 0;
147
148-- Find listening ports not associated with known services
149SELECT lp.port, lp.protocol, p.name, p.path
150FROM listening_ports lp JOIN processes p ON lp.pid = p.pid
151WHERE lp.port NOT IN (22, 80, 443, 3306, 5432);
152
153-- Detect unauthorized SSH keys
154SELECT * FROM authorized_keys WHERE NOT key LIKE '%admin-team%';
155
156-- Find recently modified system binaries
157SELECT path, mtime, size FROM file
158WHERE path LIKE '/usr/bin/%' AND mtime > (strftime('%s', 'now') - 86400);
159
160-- Detect processes connecting to external IPs
161SELECT DISTINCT p.name, p.path, pn.remote_address, pn.remote_port
162FROM process_open_sockets pn JOIN processes p ON pn.pid = p.pid
163WHERE pn.remote_address NOT LIKE '10.%'
164 AND pn.remote_address NOT LIKE '172.16.%'
165 AND pn.remote_address NOT LIKE '192.168.%'
166 AND pn.remote_address != '127.0.0.1'
167 AND pn.remote_address != '0.0.0.0';
168
169-- Windows: Detect unsigned running executables
170SELECT p.name, p.path, a.result AS signature_status
171FROM processes p JOIN authenticode a ON p.path = a.path
172WHERE a.result != 'trusted';
173```
174
175### Step 4: Deploy FleetDM for Centralized Management
176
177```bash
178# FleetDM provides centralized osquery management
179# Deploy FleetDM server, configure agents to report to it
180# Agents use TLS enrollment and config from Fleet
181
182# Agent configuration for Fleet:
183# --tls_hostname=fleet.corp.com
184# --tls_server_certs=/etc/osquery/fleet.pem
185# --enroll_secret_path=/etc/osquery/enroll_secret
186```
187
188## Key Concepts
189
190| Term | Definition |
191|------|-----------|
192| **Osquery** | Open-source endpoint agent that exposes OS state as SQL tables for querying |
193| **Schedule** | Periodic queries that run at defined intervals and log results |
194| **Pack** | Collection of related queries grouped for specific use cases (IR, compliance) |
195| **FleetDM** | Open-source osquery fleet management platform |
196| **Differential Results** | Osquery logs only changes between query executions, reducing data volume |
197
198## Tools & Systems
199
200- **Osquery**: https://osquery.io/ - endpoint visibility agent
201- **FleetDM**: https://fleetdm.com/ - centralized fleet management
202- **Kolide**: Cloud-based osquery management with Slack integration
203- **osquery-go**: Go client library for osquery extensions
204
205## Common Pitfalls
206
207- **Query performance**: Complex queries with large table scans impact endpoint performance. Use WHERE clauses and test query cost with `EXPLAIN`.
208- **Schedule intervals too aggressive**: Running heavy queries every 60 seconds causes CPU spikes. Use 300-3600 second intervals for most queries.
209- **Not using differential mode**: Without differential logging, osquery logs all results every interval. Differential mode logs only changes.
210- **Missing event tables**: Some osquery tables require events framework enabled (process_events, socket_events). Enable with `--disable_events=false`.