WesandClaude Sonnet 4.5
27afedb32e
Fetches SMART data from Scrutiny's InfluxDB and calculates failure probabilities using the exact BackBlaze tables from SnapRAID source. Key features: - Calculates both v12 (with attr 193) and v13 (without attr 193) algorithms - v12 matches SnapRAID pre-v13.0 (includes Load Cycle Count) - v13 matches modern SnapRAID v13.0+ (excludes Load Cycle Count) - Stores both values for trending and comparison - Correctly applies bit masks (16-bit for 187/188, 32-bit for others) - Annualizes monthly rates and applies Poisson distribution - Logs to PostgreSQL with device metadata Solved the mystery: SnapRAID v12 uses attribute 193 which was removed in v13.0. Load cycle count has massive impact on failure predictions for some drives (80% vs 4% difference). 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
59 lines
2.2 KiB
SQL
59 lines
2.2 KiB
SQL
-- PostgreSQL schema for SMART data logging
|
|
|
|
-- Main table for SMART metrics snapshots
|
|
CREATE TABLE IF NOT EXISTS smart_metrics (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
device_path VARCHAR(255) NOT NULL,
|
|
serial_number VARCHAR(255) NOT NULL,
|
|
model VARCHAR(255),
|
|
disk_role VARCHAR(50), -- e.g., 'd1', 'd2', 'parity', '2-parity', or '-' for unused
|
|
|
|
-- Basic metrics (from SnapRAID smart output)
|
|
temperature_celsius INTEGER,
|
|
power_on_days INTEGER,
|
|
error_count INTEGER,
|
|
size_tb DECIMAL(10, 2),
|
|
|
|
-- Calculated failure probability
|
|
failure_probability_pct DECIMAL(5, 2), -- 0.00 to 100.00
|
|
|
|
-- Raw SMART attributes (key-value pairs as JSONB)
|
|
smart_attributes JSONB,
|
|
|
|
-- Indexes for efficient querying
|
|
CONSTRAINT unique_device_timestamp UNIQUE (device_path, timestamp)
|
|
);
|
|
|
|
-- Index on serial number for device history queries
|
|
CREATE INDEX IF NOT EXISTS idx_smart_metrics_serial ON smart_metrics(serial_number);
|
|
|
|
-- Index on timestamp for time-range queries
|
|
CREATE INDEX IF NOT EXISTS idx_smart_metrics_timestamp ON smart_metrics(timestamp DESC);
|
|
|
|
-- Index on device_path for per-device queries
|
|
CREATE INDEX IF NOT EXISTS idx_smart_metrics_device ON smart_metrics(device_path);
|
|
|
|
-- Index on JSONB attributes for querying specific SMART values
|
|
CREATE INDEX IF NOT EXISTS idx_smart_attributes_gin ON smart_metrics USING gin (smart_attributes);
|
|
|
|
-- View for latest metrics per device
|
|
CREATE OR REPLACE VIEW smart_latest AS
|
|
SELECT DISTINCT ON (device_path)
|
|
*
|
|
FROM smart_metrics
|
|
ORDER BY device_path, timestamp DESC;
|
|
|
|
-- View for high-risk devices (failure probability > 50%)
|
|
CREATE OR REPLACE VIEW smart_high_risk AS
|
|
SELECT *
|
|
FROM smart_latest
|
|
WHERE failure_probability_pct > 50.0
|
|
ORDER BY failure_probability_pct DESC;
|
|
|
|
COMMENT ON TABLE smart_metrics IS 'Historical SMART metrics collected from Scrutiny InfluxDB';
|
|
COMMENT ON COLUMN smart_metrics.failure_probability_pct IS 'BackBlaze-based 1-year failure probability (0-100%)';
|
|
COMMENT ON COLUMN smart_metrics.smart_attributes IS 'Raw SMART attributes as JSON (attribute_id -> value)';
|
|
COMMENT ON VIEW smart_latest IS 'Latest SMART metrics for each device';
|
|
COMMENT ON VIEW smart_high_risk IS 'Devices with >50% annual failure probability';
|