Model Context Protocol

# RunReveal Detection Engineering Guide
 
Best practices for SQL scheduled detections, Sigma real-time rules, and validation. **Always validate against actual log data first—never assume schemas or field names.**
 
## Detection Type Selection
 
**SQL (scheduled):** Batch analysis, aggregations, cross-source correlation, historical patterns. Requires `{from:DateTime}` and `{to:DateTime}`; schedule via cron (e.g. `*/15 * * * *`). Order WHERE by primary key; use indexed columns (`get_table_schema`). Default 15-min; use 5-min for near-real-time. If Sigma fails, deploy as SQL with 5-min schedule.
 
**Sigma (real-time):** Single-event pattern matching, sub-minute alerting. Create via RunReveal UI only (not MCP/API). Parser may not support all Sigma operators.
 
## Pre-Detection Validation (MANDATORY)
 
Before writing any detection:
 
1. **Schema** — Get table schema (columns, types, indexes, primary key).
2. **Sample data** — `SELECT * FROM target_table WHERE receivedAt >= now() - INTERVAL 7 DAY ORDER BY receivedAt DESC LIMIT 5` to confirm field names and values (case-sensitive).
3. **Event distribution** — `SELECT eventName, count() FROM target_table WHERE receivedAt >= now() - INTERVAL 7 DAY GROUP BY eventName ORDER BY cnt DESC LIMIT 20` to confirm relevant event types exist.
4. **Platform** — Group by platform/env to avoid Windows-only detections in Mac-only environments.
5. **Test logic** — Run detection WHERE clause with `LIMIT 5`; confirm field access works and execution time <1500ms.
6. **Document** — Platform, event types, data volume, limitations (e.g. "Mac only, no Windows—PowerShell N/A").
 
## Field Usage
 
Use **normalized columns** when available (indexed, faster). Use JSON extraction only when the field is not a table column. Check schema first.
 
- Prefer: `WHERE ImageFileName = '/bin/bash' AND CommandLine LIKE '%curl%' AND eventName = 'ProcessCreate'`
- JSON only when not normalized: `JSONExtractString(rawLog, 'ComputerName') AS ComputerName`
 
## SQL Detection Creation
 
**Template:** Always time-bound with `{from:DateTime}` / `{to:DateTime}`. Select from your table with `receivedAt >= {from:DateTime} AND receivedAt < {to:DateTime}` plus indexed fields first, then detection logic. Order WHERE by primary key for partition pruning.
 
**Rules:** (1) Use indexed fields early; avoid functions on them. (2) Targets: 15-min <1000ms, 1-hour <2000ms.
 
**ClickHouse:** Maps `map['key']`, `actor['email']`. JSON: `JSONExtractString(rawLog,'key')`, `JSONExtractInt`, `JSONExtractBool`; nested: `JSONExtractString(rawLog,'p','c')`. Use `count()` not `count(*)`. Arrays: `has(arr,'val')`, `arrayExists(x->x='val',arr)`.
 
## Naming
 
- **Display name:** Clear, human-readable (e.g. *Suspicious Shell Script Execution*).
- **Name (slug):** Alphanumeric and hyphens only, 3–128 chars, lowercase (e.g. `suspicious-shell-script-execution`). No underscores or spaces.
 
## MITRE ATT&CK
 
- **mitreAttacks:** `TA####` only (e.g. `TA0001`). Pattern: `^TA\d{4}$`.
- **mitreTechniques:** `T####` or `T####.###` (e.g. `T1078`, `T1136.001`). Sub-technique must be 3 digits. Invalid IDs (e.g. `t1078`, `T1136.1`) are rejected.
- Example: `mitreAttacks: [TA0001, TA0005]` and `mitreTechniques: [T1078, T1059.004]`.
- **Sigma YAML:** Use `level` for severity (not `severity`); use lowercase `riskscore` and `notificationnames`. `mitreAttacks` and `mitreTechniques` stay camelCase.
 
## Testing Checklist
 
Before deploy: data validated (real logs), platform verified, `query` tested (expected or zero results), execution time <1500ms, exclusions for known-good tools, field access validated, MITRE mapped, severity and notifications set.
 
## Pitfalls
 
**Don't:** Create without checking logs; use platform-specific rules for wrong env; skip performance test; assume zero results = broken detection.
**Do:** Run schema + sample + event-distribution `query`s; test WHERE with LIMIT; document platform and limitations; add exclusions and investigation guidance.
 
## SQL vs Sigma
 
**SQL:** Aggregations, cross-source, complex logic, or when Sigma fails (use 5-min SQL). **Sigma:** Single-event pattern, sub-minute alerting. Default to SQL with 5-min unless sub-minute is required.
 
## Investigation Fields
 
Include where available: user (username, email), system (hostname, srcIP, dstIP), event (name/type, time, ID), process (name, command line, PID, parent), resource (name, type, ID). Use actual source field names; alias for clarity.

Recommended Sigma Rules
1. Suspicious Script Execution from Temporary Directories (Defense Evasion: T1027)
Detection Goal: Identify scripts (shell, Python, Perl) executed from temporary or user-writable directories, often used by adversaries to avoid persistence detection.
Sigma Rule:
title: Suspicious Script Execution from Temporary Directory
id: crowdstrike-temp-script-exec-001
status: experimental
description: Detects execution of shell scripts or interpreters from temporary directories which may indicate obfuscated files or malicious payloads
logsource:
  product: crowdstrike
  service: fdr
detection:
  selection:
    event_simpleName: 'ProcessRollup2'
    ImageFileName|contains:
      - '/tmp/'
      - '/var/tmp/'
      - '/dev/shm/'
      - '/private/tmp/'
      - '/private/var/tmp/'
    ImageFileName|endswith:
      - '/bash'
      - '/sh'
      - '/zsh'
      - '/python'
      - '/python3'
      - '/perl'
      - '/ruby'
  filter_legit:
    ParentBaseFileName:
      - 'installer'
      - 'softwareupdate'
      - 'com.apple.installer'
  condition: selection and not filter_legit
falsepositives:
  - Legitimate software installation processes
  - System update mechanisms
  - Development environments with frequent script execution
level: medium
tags:
  - attack.defense_evasion
  - attack.t1027
  - attack.t1059.004
mitreAttacks: [TA0005]
mitreTechniques: [T1027.001, T1059.004]
riskscore: 50
 
2. Unexpected Executable Written to Non-Standard Location (Defense Evasion: T1564.001)
Detection Goal: Detect new executables written to unusual paths (user directories, hidden folders, or non-standard system locations) that adversaries use to hide files.
Sigma Rule:
title: Executable Written to Non-Standard Location
id: crowdstrike-unusual-exec-write-001
status: experimental
description: Detects new executable files written to non-standard locations that may indicate defense evasion through file hiding
logsource:
  product: crowdstrike
  service: fdr
detection:
  selection_event:
    event_simpleName: 'NewExecutableWritten'
  selection_paths:
    TargetFileName|contains:
      - '/Users/'
      - '/tmp/'
      - '/var/tmp/'
      - '/.config/'
      - '/.local/'
      - '/Library/Application Support/'
      - '/private/tmp/'
  filter_development:
    ContextBaseFileName:
      - 'xcodebuild'
      - 'clang'
      - 'gcc'
      - 'cmake'
      - 'cargo'
      - 'go'
  filter_installers:
    TargetFileName|contains:
      - 'Install'
      - '.app/Contents/'
  condition: selection_event and selection_paths and not filter_development and not filter_installers
falsepositives:
  - Software development and compilation activities
  - Application installation processes
  - Build systems and CI/CD pipelines
level: medium
tags:
  - attack.defense_evasion
  - attack.t1564.001
  - attack.persistence
  - attack.t1543
mitreAttacks: [TA0005, TA0003]
mitreTechniques: [T1564.001, T1543.001]
riskscore: 55
 
3. CrowdStrike API Access from Unexpected IP Addresses (Initial Access: T1078.004)
Detection Goal: Detect CrowdStrike API authentication from IPs outside expected organizational ranges, potentially indicating compromised API credentials or unauthorized access.
Sigma Rule:
title: CrowdStrike API Access from Unexpected Geographic Location
id: crowdstrike-api-suspicious-geo-001
status: experimental
description: Detects CrowdStrike API authentication events from unexpected countries or IP ranges that may indicate compromised credentials
logsource:
  product: crowdstrike
  service: event_logs
detection:
  selection_auth:
    eventName: 'APIActivityAuditEvent'
    requestPath: '/oauth2/token'
    statusCode: '201'
  filter_expected_locations:
    srcASCountryCode:
      - 'US'
      - 'CA'
  condition: selection_auth and not filter_expected_locations
falsepositives:
  - Legitimate access from cloud infrastructure (AWS, GCP, Azure) in various regions
  - Remote work from international locations
  - Third-party integration vendors
level: high
tags:
  - attack.initial_access
  - attack.t1078.004
  - attack.credential_access
  - attack.t1110
mitreAttacks: [TA0001, TA0006]
mitreTechniques: [T1078.004, T1110.001]
riskscore: 75
 
Implementation Guidance
Pre-Deployment Validation
Before creating these rules in RunReveal, validate event coverage:
 
-- Verify ProcessRollup2 events with suspicious paths
SELECT event_simpleName, ImageFileName, CommandLine, ParentBaseFileName, count() as cnt
FROM crowdstrike_data_logs
WHERE receivedAt >= now() - INTERVAL 7 DAY AND event_simpleName = 'ProcessRollup2'
  AND (ImageFileName LIKE '%/tmp/%' OR ImageFileName LIKE '%/var/tmp/%')
GROUP BY event_simpleName, ImageFileName, CommandLine, ParentBaseFileName
ORDER BY cnt DESC LIMIT 20;
 
-- Verify NewExecutableWritten events
SELECT JSONExtractString(rawLog, 'TargetFileName') as target_file, JSONExtractString(rawLog, 'ContextBaseFileName') as context_process, count() as cnt
FROM crowdstrike_data_logs
WHERE receivedAt >= now() - INTERVAL 7 DAY AND event_simpleName = 'NewExecutableWritten'
GROUP BY target_file, context_process ORDER BY cnt DESC LIMIT 20;
 
-- Verify API authentication events
SELECT requestPath, statusCode, srcASCountryCode, srcIP, count() as cnt
FROM crowdstrike_event_logs
WHERE receivedAt >= now() - INTERVAL 7 DAY AND eventName = 'APIActivityAuditEvent' AND requestPath = '/oauth2/token'
GROUP BY requestPath, statusCode, srcASCountryCode, srcIP ORDER BY cnt DESC;
 
Expected MITRE Coverage Impact
These three rules will provide coverage for:
Defense Evasion (TA0005): T1027.001, T1564.001
Execution (TA0002): T1059.004
Persistence (TA0003): T1543.001
Initial Access (TA0001): T1078.004
Credential Access (TA0006): T1110.001
 
This represents 5 tactics and 7 techniques, establishing a strong detection foundation for CrowdStrike telemetry.
 
Next Steps
Create rules via RunReveal UI: Navigate to Detections → Create Detection → Select Sigma format
Tune filters: Adjust country codes in Rule #3 and path exclusions in Rules #1-2 based on organizational baselines
Set notification channels: Configure Slack, email, or webhook notifications per rule severity
Monitor for 48-72 hours: Collect signal data before enabling alerting to reduce false positives
Document investigation workflows: Create runbooks for each rule with escalation criteria
 
For additional Defense Evasion coverage (process injection, DLL side-loading, masquerading), reference CrowdStrike's event catalog at https://falcon.crowdstrike.com/documentation/86/event-explorer.