SIEM Engineering: Detection as Code

In a Security Data Lake, detections are not configured through a GUI with dropdowns and checkboxes. They are SQL queries stored in a Git repository, reviewed via pull request, tested against sample data, and deployed through a CI/CD pipeline. This is Detection as Code — applying software engineering discipline to security rules.

The Ingestion and Normalization Pipeline

Raw logs from different sources arrive in wildly different formats. CloudTrail uses one JSON schema, VPC Flow Logs use a space-delimited text format, and your EDR vendor uses yet another proprietary format. Before you can query across them, you need to normalize them.

Stage 1 — Ingest: Raw logs land in an S3 bucket, typically partitioned by date and source for efficient querying:

s3://security-lake/cloudtrail/year=2024/month=03/day=15/
s3://security-lake/vpc-flow/year=2024/month=03/day=15/
s3://security-lake/edr/year=2024/month=03/day=15/

Stage 2 — Normalize: An ETL job (AWS Glue, Apache Spark, or dbt) transforms raw logs into a standard schema. The industry standard is OCSF (Open Cybersecurity Schema Framework), an open specification backed by AWS, Splunk, CrowdStrike, and others. OCSF defines common field names like actor.user.name, dst_endpoint.ip, and severity_id so that a CloudTrail event and a CrowdStrike event can be queried with the same SQL.

Stage 3 — Optimize: Normalized data is written as Parquet files. Parquet is a columnar storage format — instead of storing each row together (like a CSV), it stores each column together. When your SQL query asks WHERE src_ip = '10.0.1.5', Parquet only reads the src_ip column from disk, skipping all other columns entirely. For security data with hundreds of fields but queries that touch only 3–5, this can reduce query time by 10–100x.

Stage 4 — Query and Alert: Detection rules are SQL queries that run on a schedule (every 5 minutes, every hour). If the query returns any rows, an alert fires.

Example Detection Rules (SQL)

Rule 1: Root login without MFA

-- Severity: CRITICAL
-- MITRE ATT&CK: T1078 (Valid Accounts)
SELECT
  event_time,
  source_ip_address,
  user_agent
FROM cloudtrail_logs
WHERE
  user_identity_type = 'Root'
  AND additional_event_data_mfa_used = 'No'
  AND event_time >= NOW() - INTERVAL '1' HOUR

Rule 2: New IAM admin policy attached outside business hours

-- Severity: HIGH
-- Rationale: Attackers often escalate privileges at night to avoid detection
SELECT
  event_time,
  user_identity_username,
  request_parameters_policy_arn,
  source_ip_address
FROM cloudtrail_logs
WHERE
  event_name = 'AttachUserPolicy'
  AND request_parameters_policy_arn LIKE '%AdministratorAccess%'
  AND HOUR(event_time) NOT BETWEEN 8 AND 18

Rule 3: EC2 instance communicating with known malicious IP

-- Severity: CRITICAL
-- Requires: threat_intel table updated daily from threat feeds
SELECT
  f.src_addr,
  f.dst_addr,
  f.bytes,
  t.threat_category
FROM vpc_flow_logs f
JOIN threat_intel_ips t ON f.dst_addr = t.ip_address
WHERE f.start_time >= NOW() - INTERVAL '1' HOUR

Each of these SQL files lives in a Git repository. A pull request to add or modify a detection rule triggers a CI pipeline that lints the SQL, runs it against a sample dataset, and requires a peer review from another security engineer before merging.