Security Traffic Analysis Platform with ClickHouse

---

A comprehensive platform for security traffic analysis using ClickHouse, featuring real-time PII anonymization, automated attack detection, and vulnerability reproduction validation.

🎯 Purpose

This lab provides practical experience with ClickHouse for security operations and traffic analysis: - HTTP packet data capture and analysis - Real-time PII (Personally Identifiable Information) detection and anonymization - Automated attack pattern detection (Bruteforce, Scanner, Enumeration, EDoS) - Vulnerability reproduction validation and regression testing - Automated triage and severity classification - AI-ready anonymized dataset generation

Whether you're building a security operations platform or analyzing traffic patterns, this lab demonstrates production-ready patterns for handling sensitive packet data at scale.

📊 Dataset Scale

• Normal Traffic: 5,000 HTTP requests
• Attack Scenarios: SQL Injection, JWT exposure, API key leaks
• PII Types Detected: JWT tokens, API keys, emails, phone numbers, credit cards
• Time Window: Real-time + historical (configurable TTL)
• Demo Data: Realistic security traffic analysis scenarios with before/after patch validation

📁 File Structure

security-traffic-analysis/
├── README.md                                   # This file
├── 01-create-database.sql                      # Database setup
├── 02-create-tables.sql                        # Core tables (packets, anonymized, detection)
├── 03-create-materialized-views-realtime.sql   # Real-time MVs (anonymization, PII logging)
├── 04-create-refreshable-mvs.sql               # Refreshable MVs (attack detection, triage)
├── 05-generate-demo-data.sql                   # Demo data generation
├── 06-demo-queries.sql                         # Demo analysis queries
├── 07-utility-functions.sql                    # Utility functions and views
├── 08-vector-search-setup.sql                  # Vector Search setup (attack signatures)
├── 09-vector-embeddings-tables.sql             # Embeddings tables (requests, reports)
├── 10-vector-demo-data.sql                     # Vector demo data generation
├── 11-vector-search-queries.sql                # Vector Search query practice
├── 12-vector-integration.sql                   # Python/API integration guide
└── 99-cleanup.sql                              # Cleanup script

🚀 Quick Start

Execute core labs in sequence:

cd usecase/security-traffic-analysis

# Core Lab (Required)
clickhouse-client --queries-file 01-create-database.sql
clickhouse-client --queries-file 02-create-tables.sql
clickhouse-client --queries-file 03-create-materialized-views-realtime.sql
clickhouse-client --queries-file 04-create-refreshable-mvs.sql
clickhouse-client --queries-file 05-generate-demo-data.sql
clickhouse-client --queries-file 07-utility-functions.sql

# Advanced Lab: Vector Search (Optional, requires ClickHouse 24.8+)
clickhouse-client --queries-file 08-vector-search-setup.sql
clickhouse-client --queries-file 09-vector-embeddings-tables.sql
clickhouse-client --queries-file 10-vector-demo-data.sql
clickhouse-client --queries-file 11-vector-search-queries.sql

Or execute core labs in a loop:

for file in 01-create-database.sql 02-create-tables.sql 03-create-materialized-views-realtime.sql 04-create-refreshable-mvs.sql 05-generate-demo-data.sql 07-utility-functions.sql; do
    echo "Executing $file..."
    clickhouse-client --queries-file "$file"
    echo ""
done

For Vector Search labs:

for file in 08-vector-search-setup.sql 09-vector-embeddings-tables.sql 10-vector-demo-data.sql 11-vector-search-queries.sql; do
    echo "Executing $file..."
    clickhouse-client --queries-file "$file"
    echo ""
done

📖 Detailed Lab Steps

1. Database Setup

clickhouse-client --queries-file 01-create-database.sql

What it does: - Creates security_traffic_analysis database - Sets up USE context

Expected time: ~1 second

---

2. Core Tables Creation

clickhouse-client --queries-file 02-create-tables.sql

What it does: - Creates 6 core tables: - http_packets: Raw HTTP request/response packets (TTL: 90 days) - http_packets_anonymized: Anonymized packets for AI training (TTL: 365 days) - attack_detection_agg: Attack pattern aggregates (1-minute windows) - triage_results: Automated triage classification - block_list: Real-time block targets - pii_exposure_log: Detailed PII exposure events (TTL: 180 days) - Adds secondary indexes for efficient queries

Expected time: ~2-3 seconds

---

3. Real-time Materialized Views

clickhouse-client --queries-file 03-create-materialized-views-realtime.sql

What it does: - Creates real-time data processing pipelines: - mv_anonymize_packets: Auto-anonymize incoming packets - Masks JWT tokens, API keys, emails, phone numbers, credit cards - Generates SHA256 hashes for IPs and participant IDs - Records PII types detected as metadata - mv_pii_exposure_log: Logs PII exposure incidents - Severity classification (CRITICAL/HIGH/MEDIUM/LOW) - Context snippets (masked)

Expected time: ~2-3 seconds

---

4. Refreshable Materialized Views

clickhouse-client --queries-file 04-create-refreshable-mvs.sql

What it does: - Creates periodically refreshed analysis views: - mv_attack_detection: Attack pattern detection (refresh: 1 minute) - Bruteforce: Repeated requests to same endpoint - Scanner: Wide endpoint exploration - Enumeration: Sequential ID access patterns - EDoS: High-cost resource consumption - mv_auto_triage: Automated severity classification (refresh: 5 minutes) - mv_auto_block: Automatic blocking for threats > 0.7 score (refresh: 1 minute)

Expected time: ~2-3 seconds

Note: Refreshable MVs require ClickHouse Cloud or recent self-managed versions

---

5. Demo Data Generation

clickhouse-client --queries-file 05-generate-demo-data.sql

What it does: - Generates 5,000 normal HTTP requests - Creates vulnerability scenarios: - SQL Injection attack sequence (before patch) - JWT token exposure - API key leaks in responses - Post-patch validation data - Generates various PII patterns for testing

Expected time: ~5-10 seconds

---

6. Demo Queries

clickhouse-client --queries-file 06-demo-queries.sql

Query scenarios: - PART 1: Vulnerability Reproduction & Regression Testing - Extract request sequences by report ID - Automated reproduction validation - Before/after patch comparison - Auto-triage dashboard - PART 2: Attack Detection - Real-time threat monitoring - Attack type breakdown - Block list management - PART 3: PII Anonymization - Original vs anonymized data comparison - PII exposure statistics - AI training dataset export

Expected time: ~5-30 seconds per query

---

7. Utility Functions and Views

clickhouse-client --queries-file 07-utility-functions.sql

What it does: - Creates reusable PII masking functions - maskJWT, maskEmail, maskPhoneKR, maskCreditCard, maskApiKey - maskAllPII: Comprehensive masking function - Creates threat score calculation functions - calcBruteforceScore, calcScannerScore - Creates convenience views for common queries

Expected time: ~2-3 seconds

---

🔍 Advanced Lab: Vector Search (Optional)

Prerequisites: ClickHouse 24.8+ (Vector Search GA)

This advanced section demonstrates AI-powered threat detection using vector embeddings and semantic similarity search.

8. Vector Search Setup

clickhouse-client --queries-file 08-vector-search-setup.sql

What it does: - Introduces Vector Search concepts and use cases - Creates attack_signatures table with vector embeddings - Stores known attack patterns with metadata (CWE, CVSS, severity) - Sets up HNSW index for fast similarity search - Inserts sample attack patterns (SQLi, XSS, SSRF, etc.)

Key concepts: - Vector embeddings represent attack patterns in high-dimensional space - cosineDistance measures semantic similarity between patterns - HNSW (Hierarchical Navigable Small World) enables fast approximate search

Expected time: ~2-3 seconds

---

9. Embeddings Tables

clickhouse-client --queries-file 09-vector-embeddings-tables.sql

What it does: - Creates request_embeddings table for HTTP request vectors - Creates report_knowledge_base table for bug report semantic search - Creates duplicate_report_links table for tracking duplicates - Sets up vector similarity indexes - Creates utility views and functions

Use cases: - Similar attack pattern detection - Duplicate report identification - Semantic search for bug reports - Automated triage prioritization

Expected time: ~2-3 seconds

---

10. Vector Demo Data

clickhouse-client --queries-file 10-vector-demo-data.sql

What it does: - Creates mock embedding functions (SQLi, XSS, SSRF patterns) - Updates attack signatures with vector embeddings - Generates embeddings for sample HTTP requests - Creates sample bug reports with embeddings - Validates embedding dimensions and quality

Note: Uses mock embeddings for demo. Production requires OpenAI API or Sentence Transformers.

Expected time: ~3-5 seconds

---

11. Vector Search Queries

clickhouse-client --queries-file 11-vector-search-queries.sql

Query scenarios: - PART 1: Attack Pattern Detection - Find similar attack patterns for suspicious requests - Match requests to known attack categories - Filter by severity and CVSS score - PART 2: Duplicate Report Detection - Find similar bug reports - Calculate duplicate probability - Analyze duplicate rates by vulnerability type - PART 3: Semantic Search - Natural language queries for bug reports - Hybrid keyword + vector search - Prioritize based on past high-bounty reports - PART 4: Clustering & Analytics - Group similar attack patterns - Outlier detection (anomaly detection) - Attack trend analysis over time - PART 5: Production Queries - Real-time threat scoring system - Automated triage recommendations - Combined heuristic + vector scoring

Expected time: ~5-30 seconds per query

---

12. Python Integration

# View the integration guide
cat 12-vector-integration.sql

What it covers: - OpenAI API Integration: Generate real embeddings using OpenAI - Batch Processing: Efficient embedding generation pipelines - REST API Examples: Flask-based search API endpoints - Sentence Transformers: Free, local embedding generation - Performance Optimization: Caching, batching, query optimization - Production Checklist: Security, monitoring, cost optimization

Key components: - Python scripts for embedding generation - API endpoints for similarity search - Duplicate detection service - Semantic search implementation - Best practices and deployment guide

Expected time: Study material (no execution required)

---

🔧 Use Cases

1️⃣ Vulnerability Reproduction Automation

Goal: Automatically validate bug reports and verify patches

Feature	Description
Sequence Extraction	Retrieve request/response sequence by report ID
Reproduction Validation	Analyze response patterns to confirm vulnerability
Regression Testing	Compare results before/after patch
Auto Triage	Automatic severity classification

Example Query:

SELECT report_id,
       countIf(response_body LIKE '%password%') as sensitive_exposed,
       multiIf(
           countIf(response_body LIKE '%password%') > 0, 'CRITICAL',
           countIf(response_status >= 500) > 0, 'HIGH',
           'MEDIUM'
       ) as verdict
FROM http_packets
WHERE report_id = 'BUG-2024-1234'
GROUP BY report_id;

---

2️⃣ Automated Attack Detection

Goal: Detect abuse and malicious activity in security traffic analysis traffic

Attack Type	Detection Criteria
Bruteforce	Repeated requests to same endpoint, high auth failure rate
Scanner	Wide endpoint exploration, admin path attempts
Enumeration	Sequential ID access patterns
EDoS	Repeated high-cost requests, elevated response times

Auto-blocking: Threats with score > 0.7 automatically added to block list

Example Query:

SELECT source_ip_hash,
       participant_id,
       bruteforce_score, scanner_score,
       is_blocked, block_reason
FROM attack_detection_agg
WHERE bruteforce_score > 0.7 OR scanner_score > 0.7
ORDER BY bruteforce_score DESC;

---

3️⃣ PII Detection and Anonymization

Goal: Automatically detect and mask sensitive information in responses

PII Type	Pattern	Masked Result
JWT Token	eyJ...	[JWT_REDACTED]
API Key	"api_key": "..."	"api_key":"[REDACTED]"
Email	user@example.com	[EMAIL_REDACTED]
Phone (KR)	010-1234-5678	[PHONE_REDACTED]
Credit Card	4111-1111-1111-1111	[CARD_REDACTED]

Anonymized Data: Safe for AI training and public disclosure

Example Query:

SELECT pii_type, count() as exposure_count,
       countIf(severity = 'CRITICAL') as critical_count
FROM pii_exposure_log
WHERE event_date = today()
GROUP BY pii_type
ORDER BY exposure_count DESC;

---

🛠 Prerequisites

System Requirements: - ClickHouse Version: 23.8+ (for Refreshable MVs on Cloud, 24.1+ for self-managed) - Memory: Minimum 4GB RAM (8GB+ recommended) - Disk Space: 10GB for demo data - CPU: 2+ cores recommended

Software: - ClickHouse client installed - Access to ClickHouse Cloud or self-managed instance - Terminal or SQL client

Permissions: - CREATE DATABASE - CREATE TABLE - CREATE VIEW - INSERT data

---

💡 Performance Tips

1. Index Usage: - Bloom filter indexes added for report_id, source_ip, and response_status - Leverage these for fast filtering

2. Partitioning: - All tables partitioned by month (toYYYYMM) - Use partition pruning in WHERE clauses: WHERE event_date >= today() - 30

3. TTL Management: - Raw packets: 90 days - Anonymized data: 365 days - PII logs: 180 days - Adjust TTL based on compliance requirements

4. Query Optimization: - Use FORMAT PrettyCompactMonoBlock for readable output - Apply LIMIT for large result sets - Use materialized views instead of repeated aggregations

5. Refreshable MVs: - Adjust refresh intervals based on monitoring needs - Consider compute costs on ClickHouse Cloud

---

🔍 Troubleshooting

Issue: "Table already exists" error

Solution: Run cleanup script first

clickhouse-client --queries-file 99-cleanup.sql

Issue: Refreshable MVs not supported

Symptom: Error creating mv_attack_detection Solution: - Ensure ClickHouse version 23.8+ (Cloud) or 24.1+ (self-managed) - Alternative: Use regular MVs with INSERT SELECT scheduled queries

Issue: Out of memory during data generation

Symptom: Query fails during 05-generate-demo-data.sql Solution: - Reduce numbers() range in INSERT statements - Generate data in smaller batches - Increase available memory

Issue: Slow query performance

Solution: - Check partition pruning: EXPLAIN SELECT ... - Verify indexes: SHOW CREATE TABLE http_packets - Use SYSTEM RELOAD DICTIONARIES if using dictionaries - Check merge status: SELECT * FROM system.merges

Issue: PII not being detected

Symptom: detected_pii_types array is empty Solution: - Verify regex patterns in mv_anonymize_packets - Check source data format matches patterns - Test regex with SELECT match(response_body, 'eyJ[A-Za-z0-9_-]{10,}...')

---

🧹 Cleanup

To remove all demo data and objects:

clickhouse-client --queries-file 99-cleanup.sql

What it removes: - All materialized views - All regular views - All functions - All tables - Database (optional - commented out by default)

Expected time: ~2-3 seconds

---

📚 Key Learning Points

1. Real-time Data Processing: Materialized views for instant data transformation
2. PII Handling: Regex-based detection and masking strategies
3. Threat Detection: Pattern-based scoring for different attack types
4. Data Lifecycle: TTL management for compliance and cost optimization
5. Query Performance: Partitioning, indexing, and aggregation optimization
6. Automation: Refreshable MVs for periodic analysis updates
7. Audit Trail: Comprehensive logging of security events
8. Vector Search (Advanced): AI-powered threat detection using semantic similarity - Attack pattern recognition with embeddings - Duplicate report detection using cosine distance - Semantic search for bug reports - Hybrid traditional + AI-based threat scoring - Production-ready integration patterns

---

📝 License

MIT — same as the rest of the repository.

---

👤 Author

Created: 2026-01-31 Updated: 2026-02-01

---

From the notes site (migrated, not verified)

Moved on 2026-10-06 from the author's notes site (clickhouse.kr); not re-run here. English is an LLM-assisted translation.

Background: challenges in a security-testing environment

Running penetration tests, red-team exercises or external security-validation programs raises a few common challenges.

1) Validation bottleneck

• Tens to hundreds of security events occur every day
• Each event's reproducibility has to be checked by hand
• Classifying "not reproducible" events takes a lot of time

2) Identifying anomalous traffic

• Behavior outside the test scope has to be detected
• Malicious behavior such as bruteforce, scanners and DDoS has to be identified
• It is hard to tell normal test activity from anomalous behavior

3) Risk of exposing sensitive data

• Real user data can end up in responses during testing
• JWTs, API keys and personal data are stored in logs as they are
• Compliance issues (GDPR, personal-data protection law)

Why ClickHouse?

Feature	Use in security traffic analysis
Very fast aggregation	Detect threat patterns across millions of packets in real time
Column-oriented storage	Quickly scan only specific fields such as HTTP headers or bodies
Materialized View	Real-time anonymization, automatic aggregation pipelines
Regex functions	Detect and mask sensitive-data patterns
TTL management	Automatic cleanup by retention period

Use case 1: security-event sequence analysis and patch verification

Pull the requests that belong to one event (event_id) in time order to rebuild the attack sequence, then compare before and after a patch to check for regressions.

-- Full request sequence of a SQL Injection event
SELECT
    timestamp,
    request_method,
    request_uri,
    response_status,
    substring(response_body, 1, 80) as response_preview
FROM security_analytics.http_packets
WHERE event_id = 'SEC-2024-1234'
ORDER BY timestamp;

The note's example result: before the patch, /api/users/1' OR '1'='1 returned 500 (SQL syntax error), and a request with SELECT password after a semicolon returned 200 with password_hash exposed; after the patch the same requests returned 400 (Invalid input).

Automatic verdicts classify severity from the response body and status code.

SELECT
    event_id,
    count() as total_requests,
    countIf(response_status >= 500) as server_errors,
    countIf(response_body LIKE '%password%') as sensitive_exposed,

    -- automatic verdict
    multiIf(
        countIf(response_body LIKE '%password%') > 0,
            'CRITICAL - sensitive data exposed',
        countIf(response_status >= 500) > 0,
            'HIGH - triggers server error',
        'LOW - needs further review'
    ) as verdict
FROM security_analytics.http_packets
WHERE event_id = 'SEC-2024-1234'
GROUP BY event_id;

Regression verification splits the data into phases by time and compares vuln_triggered.

SELECT
    if(timestamp < now() - INTERVAL 36 HOUR,
       '1_BEFORE_PATCH', '2_AFTER_PATCH') as phase,
    count() as requests,
    countIf(response_body LIKE '%password_hash%') as vuln_triggered,
    if(countIf(response_body LIKE '%password_hash%') > 0,
       'vulnerable', 'fixed') as status
FROM security_analytics.http_packets
WHERE event_id = 'SEC-2024-1234'
GROUP BY phase
ORDER BY phase;

The note's example result: in BEFORE_PATCH, 3 of 4 requests triggered the vulnerability; in AFTER_PATCH, 0 of 2.

Use case 2: real-time detection and blocking of anomalous traffic

The note scores anomaly types from traffic patterns (request count, unique endpoints, response time, error rate) and hands detected sources to a block list and WAF integration.

Type	Condition	Score
BRUTEFORCE	more than 100 requests, fewer than 5 unique endpoints	0.95
SCANNER	more than 50 unique endpoints, more than 100 requests	0.95
EDoS	average response time over 5 s, more than 50 requests	0.95
ENUM	more than 20 requests matching the /users/ID pattern	0.90

Use case 3: automatic anonymization of response data

When a raw packet arrives, a Materialized View writes it to an anonymized table in real time. Identifiers (tester_id, source_ip) become SHA256 hashes, the response body is masked with regexes (JWT, API key, email and so on), and the detected PII types (detected_pii_types) are recorded alongside. For example {token: eyJhbG..., email: ...} becomes {token: [JWT_REDACTED], email: [EMAIL_REDACTED]}.

CREATE MATERIALIZED VIEW security_analytics.mv_anonymize_packets
TO security_analytics.http_packets_anonymized
AS
SELECT
    packet_id,
    session_id,
    event_id,
    hex(SHA256(tester_id)) as tester_id_hash,
    timestamp,
    request_method,
    -- ... (the response body is then masked with replaceRegexpAll)

Conclusion

Use case	How ClickHouse is used	Expected effect
Security-event sequence analysis	Sequence extraction + pattern matching	80% shorter validation time
Anomalous traffic detection	Refreshable MV + scoring	Real-time blocking of anomalous behavior
Response-data anonymization	Regex + real-time MV	Compliance + AI use
Vector Search	Embeddings + similarity search	Variant-attack detection + automatic duplicate-event handling

"80% shorter validation time" is the note's stated expected effect, not a measurement.

Extensions: Grafana dashboards and real-time alerts (Slack/PagerDuty); WAF integration, CI/CD integration and automated regression tests; better Vector Search, anomaly-detection models, automatic classifiers and RAG-based analysis; more data sources such as DNS query logs, TLS handshakes and application logs.

The note summarizes that Refreshable Materialized Views let ClickHouse run periodic batch jobs without a separate scheduler, keeping the architecture simple. Adding Vector Search makes it possible to detect variant attack patterns that regexes miss, filter out duplicate events automatically, and search past security events in natural language.

---

security-traffic-analysis/
├── README.md                                   # 이 파일
├── 01-create-database.sql                      # 데이터베이스 설정
├── 02-create-tables.sql                        # 핵심 테이블 (패킷, 비식별화, 탐지)
├── 03-create-materialized-views-realtime.sql   # 실시간 MV (비식별화, PII 로깅)
├── 04-create-refreshable-mvs.sql               # Refreshable MV (공격탐지, 트리아지)
├── 05-generate-demo-data.sql                   # 데모 데이터 생성
├── 06-demo-queries.sql                         # 데모 분석 쿼리
├── 07-utility-functions.sql                    # 유틸리티 함수 및 뷰
├── 08-vector-search-setup.sql                  # Vector Search 설정 (공격 시그니처)
├── 09-vector-embeddings-tables.sql             # 임베딩 테이블 (요청, 리포트)
├── 10-vector-demo-data.sql                     # Vector 데모 데이터 생성
├── 11-vector-search-queries.sql                # Vector Search 쿼리 실습
├── 12-vector-integration.sql                   # Python/API 통합 가이드
└── 99-cleanup.sql                              # 정리 스크립트

cd usecase/security-traffic-analysis

# 핵심 실습 (필수)
clickhouse-client --queries-file 01-create-database.sql
clickhouse-client --queries-file 02-create-tables.sql
clickhouse-client --queries-file 03-create-materialized-views-realtime.sql
clickhouse-client --queries-file 04-create-refreshable-mvs.sql
clickhouse-client --queries-file 05-generate-demo-data.sql
clickhouse-client --queries-file 07-utility-functions.sql

# 고급 실습: Vector Search (선택, ClickHouse 24.8+ 필요)
clickhouse-client --queries-file 08-vector-search-setup.sql
clickhouse-client --queries-file 09-vector-embeddings-tables.sql
clickhouse-client --queries-file 10-vector-demo-data.sql
clickhouse-client --queries-file 11-vector-search-queries.sql

for file in 01-create-database.sql 02-create-tables.sql 03-create-materialized-views-realtime.sql 04-create-refreshable-mvs.sql 05-generate-demo-data.sql 07-utility-functions.sql; do
    echo "Executing $file..."
    clickhouse-client --queries-file "$file"
    echo ""
done

for file in 08-vector-search-setup.sql 09-vector-embeddings-tables.sql 10-vector-demo-data.sql 11-vector-search-queries.sql; do
    echo "Executing $file..."
    clickhouse-client --queries-file "$file"
    echo ""
done

clickhouse-client --queries-file 01-create-database.sql

clickhouse-client --queries-file 02-create-tables.sql

clickhouse-client --queries-file 03-create-materialized-views-realtime.sql

clickhouse-client --queries-file 04-create-refreshable-mvs.sql

clickhouse-client --queries-file 05-generate-demo-data.sql

clickhouse-client --queries-file 06-demo-queries.sql

clickhouse-client --queries-file 07-utility-functions.sql

clickhouse-client --queries-file 08-vector-search-setup.sql

clickhouse-client --queries-file 09-vector-embeddings-tables.sql

clickhouse-client --queries-file 10-vector-demo-data.sql

clickhouse-client --queries-file 11-vector-search-queries.sql

# 통합 가이드 확인
cat 12-vector-integration.sql

SELECT report_id,
       countIf(response_body LIKE '%password%') as sensitive_exposed,
       multiIf(
           countIf(response_body LIKE '%password%') > 0, 'CRITICAL',
           countIf(response_status >= 500) > 0, 'HIGH',
           'MEDIUM'
       ) as verdict
FROM http_packets
WHERE report_id = 'BUG-2024-1234'
GROUP BY report_id;

SELECT source_ip_hash,
       participant_id,
       bruteforce_score, scanner_score,
       is_blocked, block_reason
FROM attack_detection_agg
WHERE bruteforce_score > 0.7 OR scanner_score > 0.7
ORDER BY bruteforce_score DESC;

SELECT pii_type, count() as exposure_count,
       countIf(severity = 'CRITICAL') as critical_count
FROM pii_exposure_log
WHERE event_date = today()
GROUP BY pii_type
ORDER BY exposure_count DESC;

clickhouse-client --queries-file 99-cleanup.sql

clickhouse-client --queries-file 99-cleanup.sql

-- SQL Injection 이벤트의 전체 요청 시퀀스
SELECT
    timestamp,
    request_method,
    request_uri,
    response_status,
    substring(response_body, 1, 80) as response_preview
FROM security_analytics.http_packets
WHERE event_id = 'SEC-2024-1234'
ORDER BY timestamp;

SELECT
    event_id,
    count() as total_requests,
    countIf(response_status >= 500) as server_errors,
    countIf(response_body LIKE '%password%') as sensitive_exposed,

    -- 자동 판정
    multiIf(
        countIf(response_body LIKE '%password%') > 0,
            'CRITICAL - 민감정보 노출',
        countIf(response_status >= 500) > 0,
            'HIGH - 서버 에러 유발',
        'LOW - 추가 검토 필요'
    ) as verdict
FROM security_analytics.http_packets
WHERE event_id = 'SEC-2024-1234'
GROUP BY event_id;

SELECT
    if(timestamp < now() - INTERVAL 36 HOUR,
       '1_BEFORE_PATCH', '2_AFTER_PATCH') as phase,
    count() as requests,
    countIf(response_body LIKE '%password_hash%') as vuln_triggered,
    if(countIf(response_body LIKE '%password_hash%') > 0,
       'vulnerable', 'fixed') as status
FROM security_analytics.http_packets
WHERE event_id = 'SEC-2024-1234'
GROUP BY phase
ORDER BY phase;

CREATE MATERIALIZED VIEW security_analytics.mv_anonymize_packets
TO security_analytics.http_packets_anonymized
AS
SELECT
    packet_id,
    session_id,
    event_id,
    hex(SHA256(tester_id)) as tester_id_hash,
    timestamp,
    request_method,
    -- ... (이하 응답 본문을 replaceRegexpAll로 마스킹)