1337skills.com

Appearance

osquery - SQL-Based Operating System Instrumentation ​

osquery is a SQL-based operating system instrumentation framework that exposes an operating system as a high-performance relational database. This allows you to write SQL-based queries to explore operating system data.

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

Basic Usage ​

Interactive Shell ​

bash
# Start interactive osquery shell
osqueryi

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

Configuration ​

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 Queries ​

Hardware Information ​

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;

Network Information ​

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;

Process and Service Monitoring ​

Process Information ​

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;

Security Monitoring ​

User and Authentication ​

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;

File System Security ​

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

Network Security ​

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;

Log Analysis ​

System Logs ​

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

Application Logs ​

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;

Advanced Queries ​

Performance Monitoring ​

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;

Security Compliance ​

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

Scheduled Queries ​

Configuration Examples ​

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

Packs and Extensions ​

Using 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"
  }
}

Custom Extensions ​

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

Integration with SIEM ​

Splunk Integration ​

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

Performance Tuning ​

Configuration Optimization ​

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

Query Optimization ​

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

Troubleshooting ​

Common Issues ​

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

Performance Issues ​

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;

Security Best Practices ​

Access Control ​

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

Network Security ​

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

This comprehensive osquery cheatsheet covers installation, configuration, querying, monitoring, and integration with security tools. osquery provides powerful SQL-based system instrumentation capabilities for security monitoring, compliance, and incident response operations.