Noteworthy SMART stats gathering into MySQL / MariaDB / AWS Aurora / RDS

record-smart-stats.sh

#!/bin/bash -e

# https://gist.github.com/tomfanning/2a69ba71b2371b2e6149b49d43198614

#CREATE TABLE IF NOT EXISTS `smartstats` (
#  `id` int(11) NOT NULL AUTO_INCREMENT,
#  `host` varchar(50) NOT NULL,
#  `timestamp` datetime NOT NULL,
#  `diskserial` varchar(50) NOT NULL,
#  `attribute` tinyint(4) unsigned NOT NULL,
#  `value` int(11) NOT NULL,
#  PRIMARY KEY (`id`)
#) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=40 ;
#ALTER TABLE  `smartstats` ADD UNIQUE (`host`,`timestamp`,`diskserial`,`attribute`);
#
#CREATE TABLE IF NOT EXISTS `smartattribs` (
#  `id` int(11) NOT NULL,
#  `name` varchar(255) DEFAULT NULL,
#  PRIMARY KEY (`id`)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#INSERT INTO `smartattribs` (`id`, `name`) VALUES
#(5, 'Reallocated sector count'),
#(9, 'Power-on hours count'),
#(193, 'Load cycle count'),
#(194, 'Temperature'),
#(197, 'Current pending sectors'),
#(198, 'Off-line uncorrectable sectors');

dbhost=
dbname=
dbuser=
dbpass=

dt=`date '+%Y-%m-%d %H:%M:%S'`
smartctl --scan | cut -f 1 -d "#" | tr -s ' ' | while read devicestr
do
  smartoutput=$(smartctl -a $devicestr || true)
  serial=$(echo "$smartoutput" | grep "Serial number:\|Serial Number:" | tr -s ' ' | cut -f 3- -d " ")
  model=$(echo "$smartoutput" | grep "Device Model:" | tr -s ' ' | cut -f 3- -d " ")
  echo "$smartoutput" | grep "5 Reallocated_Sector_Ct\|187 Reported_Uncorrect\|188 Command_Timeout\|197 Current_Pending_Sector\|198 Offline_Uncorrectable\|9 Power_On_Hours\|194 Temperature_Celsius" | awk '{$1=$1};1' | cut -d " " -f 1,10 | sed "s/ /,/" | while read smartval
  do
    attrib=$(echo "$smartval" | cut -f 1 -d ",")
    val=$(echo "$smartval" | cut -f 2 -d ",")
    mysql -h $dbhost -u $dbuser --password=$dbpass $dbname -e "insert into smartstats (host,timestamp,diskserial,attribute,value) values ('$(hostname)','$dt','$serial',$attrib,$val) on duplicate key update value=$val;"
  done
done


#query:
#SELECT smartstats.*, smartattribs.name FROM smartstats
#left join smartattribs on smartattribs.id = smartstats.attribute
#WHERE timestamp = (select max(timestamp) from smartstats)
#order by diskserial, attribute

Stick it in some file, point it at a database with that table created, chmod +x, stick this in crontab:
0 * * * * root /root/record-smart-stats.sh