osquery - SQL-Based Operating System Instrumentation

"Display: flex; brecha: 10px; margen-bottom: 20px;" butbutton onclick="navigator.clipboard.writeText(document.querySelector('pre').textContent)"style="background: #007acc; color: blanco; frontera: ninguno; relleno: 8px 16px; border-radius: 4px; cursor: pointer;" Copiado Todos los comandos de osquería butbutton onclick="window.print()"style="background: #28a745; color: blanco; frontera: ninguno; relleno: 8px 16px; border-radius: 4px; cursor: pointer;" Generar osquery PDF Guía = ■/div titulada

osquery es un marco de instrumentación de sistema operativo basado en SQL que expone un sistema operativo como una base de datos relacional de alto rendimiento. Esto le permite escribir consultas basadas en SQL para explorar los datos del sistema operativo.

Instalación

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

# 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

# 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

Uso básico

Shell interactivo

# Start interactive osquery shell
osqueryi

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

Configuración

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

Información del sistema

Información de hardware

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

Network Information

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

Supervisión de procesos y servicios

Información sobre procesos

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

Información del Servicio

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

Vigilancia de la seguridad

Usuario y autenticación

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

Seguridad del sistema de archivos

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

Network Security

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

Análisis de registros

Logros del sistema

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

Registros de aplicaciones

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

Consultas avanzadas

Supervisión de la ejecución

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

Cumplimiento de la seguridad

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

Consultas programadas

Ejemplos de configuración

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

Paquetes y extensiones

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

Extensiones personalizadas

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

Integración con SIEM

Splunk Integration

# 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

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

Performance Tuning

Optimización de configuración

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

Optimización de consultas

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

Solución de problemas

Cuestiones comunes

# 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

Cuestiones de ejecución

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

Prácticas óptimas de seguridad

Control de acceso

# 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

Network Security

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

Esta hoja de trampolín completa cubre la instalación, configuración, consulta, monitoreo e integración con herramientas de seguridad. osquery proporciona potentes capacidades de instrumentación de sistemas basadas en SQL para operaciones de monitoreo de seguridad, cumplimiento y respuesta a incidentes.