osquery - Instrumentation du système d'exploitation SQL

" Copier Tous les commandements d'osquerie " Générer l'osquerie PDF Guide

osquery est un cadre d'instrumentation du système d'exploitation basé sur SQL qui expose un système d'exploitation comme une base de données relationnelles à haute performance. Cela vous permet d'écrire des requêtes SQL pour explorer les données du système d'exploitation.

Installation

Ubuntu/Debian

# 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

# 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 /
```_

### Fenêtres
```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

Utilisation de base

Shell interactif

# Start interactive osquery shell
osqueryi

# Run basic system queries
SELECT * FROM system_info;
SELECT * FROM users;
SELECT * FROM processes;

Configuration

# 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
    \\\\}
  \\\\}
\\\\}

Renseignements sur le système

Informations matérielles

-- 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;

Information réseau

-- 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;

Suivi des processus et des services

Information sur le processus

-- 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;

Information sur les services

-- 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;

Surveillance de la sécurité

Utilisateur et authentification

-- 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;

Sécurité du système de fichiers

-- 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';

Sécurité du réseau

-- 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 du journal

Registres système

-- 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';

Registres d'application

-- 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;

Demandes de renseignements avancées

Surveillance de la performance

-- 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;

Conformité à la sécurité

-- 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/';

Demandes de renseignements prévues

Exemples de configuration

\\\\{
  "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"
    \\\\}
  \\\\}
\\\\}

Emballages et extensions

Utilisation de Query Packs

# 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"
  \\\\}
\\\\}

Extensions personnalisées

// Example C++ extension
#include <osquery/sdk.h>

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");

Intégration avec SIEM

Intégration de spunk

# 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 Intégration des piles

# 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\\\\}"

Analyse des performances

Optimisation de la configuration

\\\\{
  "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"
  \\\\}
\\\\}

Optimisation des requêtes

-- 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);

Dépannage

Questions communes

# 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

Problèmes de performance

# 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;

Pratiques exemplaires en matière de sécurité

Contrôle d'accès

# 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

Sécurité du réseau

\\\\{
  "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"
  \\\\}
\\\\}

Cette feuille d'osquer complète couvre l'installation, la configuration, la requête, la surveillance et l'intégration avec les outils de sécurité. osquery fournit de puissantes capacités d'instrumentation du système SQL pour la surveillance de la sécurité, la conformité et les opérations d'intervention en cas d'incident.