/forensics-osquery

name: forensics-osquery
description: >
  SQL-powered forensic investigation and system interrogation using osquery to query
  operating systems as relational databases. Enables rapid evidence collection, threat
  hunting, and incident response across Linux, macOS, and Windows endpoints.
  Use when: (1) Investigating security incidents and collecting forensic artifacts,
  (2) Threat hunting across endpoints for suspicious activity, (3) Analyzing running
  processes, network connections, and persistence mechanisms, (4) Collecting system
  state during incident response, (5) Querying file hashes, user activity, and system
  configuration for compromise indicators, (6) Building detection queries for continuous
  monitoring with osqueryd.
version: 0.1.0
maintainer: SirAppSec
category: incident-response
tags: [forensics, osquery, incident-response, threat-hunting, endpoint-detection, dfir, live-forensics, sql]
frameworks: [MITRE-ATT&CK, NIST]
dependencies:
  tools: [osquery]
  platforms: [linux, macos, windows]
references:
  - https://github.com/osquery/osquery
  - https://osquery.io/
  - https://osquery.readthedocs.io/

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](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](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 ser