ClickHouse Log Aggregation

ClickHouse Log Aggregation & Investigation

Architecture Overview

Log Pipeline:Pipeline (Migrated to Vector - Feb 2026):

• Terra (server): systemd logsJournald + dockerDocker container logscontainers → fluent-bitVector
• opnSenseLuna (backup server): Journald → Vector

Pluto (gateway): Journald + nftables (firewall): → Vector Vector Pipeline: filterlogUnified (firewallJinja2 rules)Ansible +template. systemConverts all logs →to fluent-bitAmerica/New_York (EST) before storage. 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

TerraVector SystemMigration &(Feb Docker2026)

• Volume:Retired Fluent-Bit: ~100-200Entire logs/minlab (aftermoved filtering)to Vector for better performance and easier Ansible management.
• Filtering:EST Alignment: WARNAll level,timestamps noacross [GIN]the logspipeline (Ollamaare framework)now surgically aligned to Eastern Time.
• Embedded:Container Naming: YesDocker logs now show friendly names (e.g., ollama, bazarr) instead of raw hex IDs.

opnSenseNoise FirewallManagement & Filtering

• Port 1514 (filterlog):Ollama/GIN: Packet-levelSampled rules,1:100 sampledto 10%

  prevent
  database
• source='firewall'wear.
• NotSanoid: embeddedRunning (CSVin format)

--quiet mode on Terra and Luna. Recursive Port 1515 (system):Loops: Services/alerts,Patched fullVRL volumefilters to prevent source='firewall_system'log-brain from logging its own status updates to ClickHouse. EmbeddedContext forTruncation: vectorembeddings_worker.py searchnow truncates logs at 3,072 characters to stay within Ollama context windows.

Embedding Pipeline

Performance (Feb 16)2026):

• Speed:Coverage: 10ms/embeddingReached (250100% invectorization 2.5s)

Throughput:coverage 6,000for embeddings/hourall essential services. Model: nomic-embed-text (768-dimensional) GPU: Quadro P2200 @for 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 INSERTprocessing.

Key Learnings

Timezone consistency is critical: Migrating from UTC to local time in the pipeline saves massive mental overhead during investigations. Priority Intelligence: Mapping systemd priorities (0-7) into the database allows for instant "Error-only" dashboards. Recursive logging is a DDoS: Always filter your log-aggregator's own logs out of the stream. ReplacingMergeTree quirk: Must insert complete rows (all columns) for deduplication to work Batch operations >> individual updates: 60x performance difference High-volume logs need filtering: Firewall logs are per-packet; sampling essential Timestamp handling in fluent-bit: Remove ! from os.date() to use server local time (not UTC) Embedding models don't use KV caching: Only LLMs do; embeddings are single forward passeswork.

Future Work

• Add luna backup server logs

(when backAlign online)all hosts to EST Parse firewall log CSV columns for structured queries (Pluto implementation) Real-time anomaly detection via vector search Async embedding pipeline for >10K/hour throughput  Log retention policies & archival