Skip to main content

ClickHouse Log Aggregation

ClickHouse Log Aggregation & Investigation

Architecture Overview

Log Pipeline:

  • Terra (server): systemd logs + docker container logs → fluent-bit
  • opnSense (firewall): filterlog (firewall rules) + system logs → fluent-bit
  • All sources → ClickHouse (system_logs table with embeddings)

ClickHouse Access

  • URL: https://logs.flora.family
  • User: flobot
  • Database: default
  • Table: system_logs

Table Schema

CREATE TABLE default.system_logs (
  timestamp DateTime64(3),
  host String,
  unit String,
  message String,
  priority Int8,
  log_id String,
  embedding Array(Float32),  -- 768-dim vectors
  source String DEFAULT 'systemd'
)
ENGINE = ReplacingMergeTree
ORDER BY log_id

Log Sources

Terra System & Docker

  • Volume: ~100-200 logs/min (after filtering)
  • Filtering: WARN level, no [GIN] logs (Ollama framework)
  • Embedded: Yes

opnSense Firewall

  • Port 1514 (filterlog): Packet-level rules, sampled 10%

    • source='firewall'
    • Not embedded (CSV format)

  • Port 1515 (system): Services/alerts, full volume

    • source='firewall_system'
    • Embedded for vector search

Embedding Pipeline

Performance (Feb 16):

  • Speed: 10ms/embedding (250 in 2.5s)
  • Throughput: 6,000 embeddings/hour
  • Model: nomic-embed-text (768-dimensional)
  • GPU: Quadro P2200 @ 5% utilization

Coverage:

  • systemd: 13.23%
  • docker: 5.93%
  • firewall_system: 1.42%

Vector Search Example

# Get embedding from Ollama
curl -X POST https://ollama.flora.family/api/embeddings \
  -H 'Content-Type: application/json' \
  -d '{"model":"nomic-embed-text","prompt":"network issues"}'

# Search ClickHouse by semantic similarity
curl -u flobot:password 'https://logs.flora.family/?query=SELECT timestamp, message, cosineDistance(embedding, [array,of,floats]) as score FROM system_logs WHERE length(embedding)>0 ORDER BY score ASC LIMIT 10'

Key Technologies

  • ClickHouse: Columnar database, vector search via cosineDistance
  • fluent-bit: Log collection & processing (syslog + tail inputs)
  • Ollama: Local embedding model (nomic-embed-text)
  • ReplacingMergeTree: Automatic deduplication on INSERT

Key Learnings

  1. ReplacingMergeTree quirk: Must insert complete rows (all columns) for deduplication to work
  2. Batch operations >> individual updates: 60x performance difference
  3. High-volume logs need filtering: Firewall logs are per-packet; sampling essential
  4. Timestamp handling in fluent-bit: Remove ! from os.date() to use server local time (not UTC)
  5. Embedding models don't use KV caching: Only LLMs do; embeddings are single forward passes

Future Work

  • Add luna backup server logs (when back online)
  • Parse firewall log CSV columns for structured queries
  • Real-time anomaly detection via vector search
  • Async embedding pipeline for >10K/hour throughput
  • Log retention policies & archival
Back to top