osquery - SQL-basiertes Betriebssystem Instrumentierung
osquery ist ein SQL-basiertes Betriebssystem-Instrumentierungs-Framework, das ein Betriebssystem als leistungsstarke relationale Datenbank darstellt. Damit können Sie SQL-basierte Abfragen schreiben, um Betriebssystemdaten zu erkunden.
Installation
Ubuntu/Debian
```bash
Add osquery repository
curl -L https://pkg.osquery.io/deb/pubkey.gpg|sudo apt-key add - sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
Install osquery
sudo apt update sudo apt install osquery
Start osquery daemon
sudo systemctl enable osqueryd sudo systemctl start osqueryd ```_
CentOS/RHEL/Fedora
```bash
Add osquery repository
curl -L https://pkg.osquery.io/rpm/GPG|sudo rpm --import - sudo yum-config-manager --add-repo https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
Install osquery
sudo yum install osquery
Start osquery daemon
sudo systemctl enable osqueryd sudo systemctl start osqueryd ```_
macOS
```bash
Using Homebrew
brew install osquery
Or download from GitHub releases
curl -LO https://github.com/osquery/osquery/releases/latest/download/osquery.pkg sudo installer -pkg osquery.pkg -target / ```_
Windows
```powershell
Download and install MSI package
Invoke-WebRequest -Uri "https://github.com/osquery/osquery/releases/latest/download/osquery.msi" -OutFile "osquery.msi" Start-Process msiexec.exe -Wait -ArgumentList '/I osquery.msi /quiet'
Start osquery service
Start-Service osqueryd ```_
Basisnutzung
Interaktive Shell
```bash
Start interactive osquery shell
osqueryi
Run basic system queries
SELECT * FROM system_info; SELECT * FROM users; SELECT * FROM processes; ```_
Konfiguration
```bash
Default configuration file locations
Linux: /etc/osquery/osquery.conf
macOS: /var/osquery/osquery.conf
Windows: C:\ProgramData\osquery\osquery.conf
Example configuration
\\{ "options": \\{ "config_plugin": "filesystem", "logger_plugin": "filesystem", "log_result_events": false, "schedule_splay_percent": 10, "pidfile": "/var/osquery/osquery.pidfile", "events_expiry": "3600", "database_path": "/var/osquery/osquery.db", "verbose": false, "worker_threads": "2", "enable_monitor": true \\}, "schedule": \\{ "system_info": \\{ "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;", "interval": 3600 \\} \\} \\} ```_
System Information Quers
Informationen zum Thema
```sql -- System information SELECT hostname, cpu_brand, cpu_type, cpu_subtype, cpu_logical_cores, physical_memory, hardware_vendor, hardware_model FROM system_info;
-- CPU information SELECT device_id, device, model, description, max_clock_speed, current_clock_speed, characteristics FROM cpu_info;
-- Memory information SELECT * FROM memory_info;
-- Disk information SELECT device, path, fstype, blocks_size, blocks, blocks_free, blocks_available, inodes, inodes_free FROM mounts; ```_
Informationen zum Netzwerk
```sql -- Network interfaces SELECT interface, address, mask, broadcast, point_to_point, type FROM interface_addresses;
-- Network routes SELECT destination, netmask, gateway, source, flags, interface, mtu FROM routes;
-- Listening ports SELECT pid, port, protocol, family, address, path FROM listening_ports;
-- Network connections SELECT pid, socket, family, protocol, local_address, local_port, remote_address, remote_port, state FROM process_open_sockets; ```_
Prozess- und Serviceüberwachung
Informationen zum Thema
```sql -- Running processes SELECT pid, name, path, cmdline, state, cwd, root, uid, gid, euid, egid, suid, sgid, on_disk, wired_size, resident_size, total_size, user_time, system_time, disk_bytes_read, disk_bytes_written, start_time, parent FROM processes;
-- Process tree SELECT p.pid, p.name, p.parent, pp.name AS parent_name FROM processes p LEFT JOIN processes pp ON p.parent = pp.pid;
-- High CPU processes SELECT pid, name, percent_processor_time FROM processes WHERE percent_processor_time > 10; ```_
Service Information
```sql -- System services (Linux) SELECT name, status, pid, path, module_path, win32_exit_code FROM services;
-- Systemd services (Linux) SELECT name, source, status, pid, fragment_path FROM systemd_units WHERE source = 'systemd';
-- Startup items SELECT name, path, args, type, source, status, username FROM startup_items; ```_
Sicherheitsüberwachung
Benutzer und Authentifizierung
```sql -- User accounts SELECT uid, gid, username, description, directory, shell, uuid FROM users;
-- User groups SELECT gid, groupname, comment FROM groups;
-- User group membership SELECT uid, gid FROM user_groups;
-- Login events SELECT username, time, host, tty, pid, type FROM last;
-- SSH keys SELECT uid, path, encrypted, key_type FROM user_ssh_keys; ```_
Dateisystem Sicherheit
```sql -- SUID/SGID files SELECT path, permissions, uid, gid, size, mtime, ctime FROM file WHERE (permissions LIKE '%s%' OR permissions LIKE '%S%') AND path LIKE '/usr/bin/%';
-- World-writable files SELECT path, permissions, uid, gid, size, mtime FROM file WHERE permissions LIKE '%w%' AND path LIKE '/tmp/%';
-- Recently modified files SELECT path, size, mtime, ctime, md5, sha1 FROM file WHERE mtime > (strftime('%s', 'now') - 86400) AND path LIKE '/etc/%';
-- File integrity monitoring SELECT target_path, category, time, action, uid, gid FROM file_events WHERE category = 'modified'; ```_
Netzwerksicherheit
```sql -- Suspicious network connections SELECT pid, path, local_address, local_port, remote_address, remote_port, state FROM process_open_sockets WHERE remote_address NOT LIKE '127.%' AND remote_address NOT LIKE '192.168.%' AND remote_address NOT LIKE '10.%';
-- ARP table SELECT address, mac, permanent, interface FROM arp_cache;
-- DNS cache SELECT name, type, class, flag, ttl, rdata FROM dns_cache; ```_
Analyse der Ergebnisse
Systemprotokolle
```sql -- System log entries SELECT datetime, host, ident, message FROM syslog WHERE datetime > datetime('now', '-1 hour');
-- Authentication logs SELECT datetime, host, ident, message FROM syslog WHERE ident = 'sshd' OR ident = 'sudo';
-- Kernel messages SELECT datetime, host, ident, message FROM syslog WHERE ident = 'kernel'; ```_
Anwendungsprotokolle
```sql -- Web server logs (if configured) SELECT datetime, method, uri, status, bytes, referer, agent FROM apache_access_log WHERE status >= 400;
-- Application crashes SELECT datetime, pid, path, version, exception_type, exception_codes, crashed_thread FROM crashes; ```_
Erweiterte Abfragen
Leistungsüberwachung
```sql -- System load SELECT period, average FROM load_average;
-- Memory usage SELECT memory_total, memory_free, buffers, cached, swap_cached, swap_total, swap_free FROM memory_info;
-- Disk I/O SELECT name, reads, read_merges, read_sectors, read_time, writes, write_merges, write_sectors, write_time, io_time, weighted_io_time FROM disk_stats;
-- Network I/O SELECT interface, bytes_sent, bytes_received, packets_sent, packets_received, errors_in, errors_out, drop_in, drop_out FROM interface_details; ```_
Sicherheit
```sql -- Password policy compliance SELECT uid, username, password_status, last_change, min_days, max_days, warning_days FROM shadow;
-- Firewall rules SELECT chain, policy, target, protocol, src_port, dst_port, src_ip, dst_ip, iniface, outiface FROM iptables;
-- Installed packages SELECT name, version, source, size, arch FROM deb_packages WHERE name LIKE '%ssh%';
-- Certificate information SELECT common_name, organization, organization_unit, serial_number, issuer_common_name, valid_from, valid_to, key_algorithm, key_usage, subject_key_id FROM certificates WHERE path = '/etc/ssl/certs/'; ```_
Geplante Abfragen
Konfigurationsbeispiele
json
\\\\{
"schedule": \\\\{
"system_info": \\\\{
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
"interval": 3600,
"description": "Basic system information"
\\\\},
"network_connections": \\\\{
"query": "SELECT pid, path, local_address, local_port, remote_address, remote_port FROM process_open_sockets WHERE remote_address != '';",
"interval": 300,
"description": "Active network connections"
\\\\},
"file_changes": \\\\{
"query": "SELECT target_path, category, time, action FROM file_events WHERE category = 'modified' AND path LIKE '/etc/%';",
"interval": 60,
"description": "File system changes in /etc"
\\\\},
"user_logins": \\\\{
"query": "SELECT username, time, host, tty FROM last WHERE time > (strftime('%s', 'now') - 3600);",
"interval": 300,
"description": "Recent user logins"
\\\\}
\\\\}
\\\\}_
Verpackungen und Erweiterungen
Verwendung von Query Packs
```bash
Download community packs
git clone https://github.com/osquery/osquery.git cp osquery/packs/*.conf /etc/osquery/packs/
Configure packs in osquery.conf
\\{ "packs": \\{ "incident-response": "/etc/osquery/packs/incident-response.conf", "it-compliance": "/etc/osquery/packs/it-compliance.conf", "vuln-management": "/etc/osquery/packs/vuln-management.conf" \\} \\} ```_
Kundenspezifische Erweiterungen
```cpp // Example C++ extension
include
class ExampleTable : public TablePlugin \\{ private: TableColumns columns() const override \\{ return \\{ std::make_tuple("example_text", TEXT_TYPE, ColumnOptions::DEFAULT), std::make_tuple("example_integer", INTEGER_TYPE, ColumnOptions::DEFAULT), \\}; \\}
QueryData generate(QueryContext& request) override \\{ QueryData results; Row r; r["example_text"] = "example"; r["example_integer"] = INTEGER(1); results.push_back(r); return results; \\} \\};
REGISTER_EXTERNAL(ExampleTable, "table", "example"); ```_
Integration mit SIEM
Integration von Splunk
```bash
Configure osquery to output JSON logs
\\{ "options": \\{ "logger_plugin": "filesystem", "logger_path": "/var/log/osquery" \\} \\}
Splunk inputs.conf
[monitor:///var/log/osquery/osqueryd.results.log] disabled = false index = osquery sourcetype = osquery:results ```_
ELK Stack Integration
```yaml
Filebeat configuration
filebeat.inputs: - type: log enabled: true paths: - /var/log/osquery/osqueryd.results.log json.keys_under_root: true json.add_error_key: true fields: logtype: osquery fields_under_root: true
output.elasticsearch: hosts: ["localhost:9200"] index: "osquery-%\\{+yyyy.MM.dd\\}" ```_
Leistung Tuning
Konfigurationsoptimierung
json
\\\\{
"options": \\\\{
"worker_threads": "4",
"events_expiry": "86400",
"events_max": "50000",
"schedule_splay_percent": "10",
"schedule_default_interval": "3600",
"database_path": "/var/osquery/osquery.db",
"pidfile": "/var/osquery/osquery.pidfile",
"host_identifier": "hostname",
"enable_monitor": true,
"monitor_utilization_limit": "20",
"monitor_delay": "60"
\\\\}
\\\\}_
Queroptimierung
```sql -- Use indexes and limits SELECT * FROM processes WHERE pid = 1234; SELECT * FROM file WHERE path = '/etc/passwd';
-- Avoid expensive queries SELECT COUNT(*) FROM (SELECT * FROM file WHERE path LIKE '/%');
-- Use time constraints SELECT * FROM process_events WHERE time > (strftime('%s', 'now') - 3600); ```_
Fehlerbehebung
Gemeinsame Themen
```bash
Check osquery daemon status
sudo systemctl status osqueryd
View osquery logs
sudo journalctl -u osqueryd -f tail -f /var/log/osquery/osqueryd.INFO
Test configuration
osqueryd --config_path=/etc/osquery/osquery.conf --config_check
Debug mode
osqueryd --verbose --config_path=/etc/osquery/osquery.conf
Check database integrity
osqueryi --database_path=/var/osquery/osquery.db .schema ```_
Leistungsfragen
```bash
Monitor resource usage
top -p $(pgrep osqueryd) iostat -x 1
Check query performance
osqueryi --profile=1 .timer on SELECT * FROM processes;
Optimize database
osqueryi VACUUM; ANALYZE; ```_
Sicherheit Best Practices
Zugriffskontrolle
```bash
Restrict configuration file permissions
sudo chmod 600 /etc/osquery/osquery.conf sudo chown root:root /etc/osquery/osquery.conf
Secure log directory
sudo chmod 750 /var/log/osquery sudo chown osquery:osquery /var/log/osquery
Use dedicated user
sudo useradd -r -s /bin/false osquery ```_
Netzwerksicherheit
json
\\\\{
"options": \\\\{
"tls_hostname": "osquery.example.com",
"tls_server_certs": "/etc/osquery/server.crt",
"enroll_tls_endpoint": "/api/v1/osquery/enroll",
"config_tls_endpoint": "/api/v1/osquery/config",
"logger_tls_endpoint": "/api/v1/osquery/log",
"disable_enrollment": false,
"enroll_secret_path": "/etc/osquery/enroll_secret"
\\\\}
\\\\}_
Dieses umfassende osquery cheatsheet umfasst Installation, Konfiguration, Abfrage, Überwachung und Integration mit Sicherheitstools. osquery bietet leistungsstarke SQL-basierte System-Instrumente für Sicherheitsüberwachung, Compliance und Notfall-Reaktionsoperationen.