osquery Forensics & Incident Response

Overview

osquery transforms operating systems into queryable relational databases, enabling security analysts to investigate compromises using SQL rather than traditional CLI tools. This skill provides forensic investigation workflows, common detection queries, and incident response patterns for rapid evidence collection across Linux, macOS, and Windows endpoints.

Core capabilities:

• SQL-based system interrogation for process, network, file, and user analysis
• Cross-platform forensic artifact collection (Linux, macOS, Windows)
• Live system analysis without deploying heavyweight forensic tools
• Threat hunting queries mapped to MITRE ATT&CK techniques
• Scheduled monitoring with osqueryd for continuous detection
• Integration with SIEM and incident response platforms

Quick Start

Interactive Investigation (osqueryi)

# Launch interactive shell
osqueryi

# Check running processes
SELECT pid, name, path, cmdline, uid FROM processes WHERE name LIKE '%suspicious%';

# Identify listening network services
SELECT DISTINCT processes.name, listening_ports.port, listening_ports.address, processes.pid, processes.path
FROM listening_ports
JOIN processes USING (pid)
WHERE listening_ports.address != '127.0.0.1';

# Find processes with deleted executables (potential malware)
SELECT name, path, pid, cmdline FROM processes WHERE on_disk = 0;

# Check persistence mechanisms (Linux/macOS cron jobs)
SELECT command, path FROM crontab;

One-Liner Forensic Queries

# Single query execution
osqueryi --json "SELECT * FROM logged_in_users;"

# Export query results for analysis
osqueryi --json "SELECT * FROM processes;" > processes_snapshot.json

# Check for suspicious kernel modules (Linux)
osqueryi --line "SELECT name, used_by, status FROM kernel_modules WHERE name NOT IN (SELECT name FROM known_good_modules);"

Core Workflows

Workflow 1: Initial Incident Response Triage

For rapid assessment of potentially compromised systems:

Progress: [ ] 1. Collect running processes and command lines [ ] 2. Identify network connections and listening ports [ ] 3. Check user accounts and recent logins [ ] 4. Examine persistence mechanisms (scheduled tasks, startup items) [ ] 5. Review suspicious file modifications and executions [ ] 6. Document findings with timestamps and process ancestry [ ] 7. Export evidence to JSON for preservation

Work through each step systematically. Use bundled triage script for automated collection.

Execute triage: ./scripts/osquery_triage.sh > incident_triage_$(date +%Y%m%d_%H%M%S).json

Workflow 2: Threat Hunting for Specific TTPs

When hunting for specific MITRE ATT&CK techniques:

1. Select Target Technique

   • Identify technique from threat intelligence (e.g., T1055 - Process Injection)
   • Map technique to observable system artifacts
   • See references/mitre-attack-queries.md for pre-built queries

2. Build Detection Query

   • Identify relevant osquery tables (processes, file_events, registry, etc.)
   • Join tables to correlate related artifacts
   • Use references/table-guide.md for schema reference

3. Execute Hunt

   -- Example: Hunt for credential dumping (T1003)
   SELECT p.pid, p.name, p.cmdline, p.path, p.parent, pm.permissions
   FROM processes p
   JOIN process_memory_map pm ON p.pid = pm.pid
   WHERE p.name IN ('mimikatz.exe', 'procdump.exe', 'pwdump.exe')
      OR p.cmdline LIKE '%sekurlsa%'
      OR (pm.path = '/etc/shadow' OR pm.path LIKE '%SAM%');
   

4. Analyze Results

   • Review process ancestry and command-line arguments
   • Check file hashes against threat intelligence
   • Document timeline of suspicious activity

5. Pivot Investigation

   • Use findings to identify additional indicators
   • Query related artifacts (network connections, files, registry)
   • Expand hunt scope if compromise confirmed

Workflow 3: Persistence Mechanism Analysis

Detecting persistence across platforms:

Linux/macOS Persistence:

-- Cron jobs
SELECT * FROM crontab;

-- Systemd services (Linux)
SELECT name, path, status, source FROM systemd_units WHERE source != '/usr/lib/systemd/system';

-- Launch Agents/Daemons (macOS)
SELECT name, path, program, run_at_load FROM launchd WHERE run_at_load = 1;

-- Bash profile modifications
SELECT * FROM file WHERE path IN ('/etc/profile', '/etc/bash.bashrc', '/home/*/.bashrc', '/home/*/.bash_profile');

Windows Persistence:

-- Registry Run keys
SELECT key, name, path, type FROM registry WHERE key LIKE '%Run%' OR key LIKE '%RunOnce%';

-- Scheduled tasks
SELECT name, action, path, enabled FROM scheduled_tasks WHERE enabled = 1;

-- Services
SELECT name, display_name, status, path, start_type FROM services WHERE start_type = 'AUTO_START';

-- WMI event consumers
SELECT name, command_line_template FROM wmi_cli_event_consumers;

Review results for:

• Unusual executables in startup locations
• Base64-encoded or obfuscated commands
• Executables in temporary or user-writable directories
• Recently modified persistence mechanisms

Workflow 4: Network Connection Analysis

Investigating suspicious network activity:

-- Active network connections with process details
SELECT p.name, p.pid, p.path, p.cmdline, ps.remote_address, ps.remote_port, ps.state
FROM processes p
JOIN process_open_sockets ps ON p.pid = ps.pid
WHERE ps.remote_address NOT IN ('127.0.0.1', '::1', '0.0.0.0')
ORDER BY ps.remote_port;

-- Listening ports mapped to processes
SELECT DISTINCT p.name, lp.port, lp.address, lp.protocol, p.path, p.cmdline
FROM listening_ports lp
LEFT JOIN processes p ON lp.pid = p.pid
WHERE lp.address NOT IN ('127.0.0.1', '::1')
ORDER BY lp.port;

-- DNS lookups (requires events table or process monitoring)
SELECT name, domains, pid FROM dns_resolvers;

Investigation checklist:

• Identify non-standard listening ports (not 80, 443, 22, 3389)
• Check processes with external connections
• Review destination IPs against threat intelligence
• Correlate connections with process execution timeline
• Validate legitimate business purpose for connections

Workflow 5: File System Forensics

Analyzing file modifications and suspicious files:

-- Recently modified files in sensitive locations
SELECT path, filename, size, mtime, ctime, md5, sha256
FROM hash
WHERE path LIKE '/etc/%' OR path LIKE '/tmp/%' OR path LIKE 'C:\Windows\Temp\%'
  AND mtime > (strftime('%s', 'now') - 86400);  -- Last 24 hours

-- Executable files in unusual locations
SELECT path, filename, size, md5, sha256
FROM hash
WHERE (path LIKE '/tmp/%' OR path LIKE '/var/tmp/%' OR path LIKE 'C:\Users\%\AppData\%')
  AND (filename LIKE '%.exe' OR filename LIKE '%.sh' OR filename LIKE '%.py');

-- SUID/SGID binaries (Linux/macOS) - potential privilege escalation
SELECT path, filename, mode, uid, gid
FROM file
WHERE (mode LIKE '%4%' OR mode LIKE '%2%')
  AND path LIKE '/usr/%' OR path LIKE '/bin/%';

File analysis workflow:

1. Identify suspicious files by location and timestamp
2. Extract file hashes (MD5, SHA256) for threat intel lookup
3. Review file permissions and ownership
4. Check for living-off-the-land binaries (LOLBins) abuse
5. Document file metadata for forensic timeline

Forensic Query Patterns

Pattern 1: Process Analysis

Standard process investigation queries:

-- Processes with network connections
SELECT p.pid, p.name, p.path, p.cmdline, ps.remote_address, ps.remote_port
FROM processes p
JOIN process_open_sockets ps ON p.pid = ps.pid;

-- Process tree (parent-child relationships)
SELECT p1.pid, p1.name AS process, p1.cmdline,
       p2.pid AS parent_pid, p2.name AS parent_name, p2.cmdline AS parent_cmdline
FROM processes p1
LEFT JOIN processes p2 ON p1.parent = p2.pid;

-- High-privilege processes (UID 0 / SYSTEM)
SELECT pid, name, path, cmdline, uid, euid FRO