smartctl data to MySQL

com.user.SSDsmart.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
	<key>Label</key>
	<string>com.user.SSDsmart</string>

	<key>ProgramArguments</key>
	<array>
		<string>/Users/guy/OneDrive/MyApps/readsmart.sh</string>
	</array>

	<key>RunAtLoad</key>
		<true/>
		
	<key>StartInterval</key>
	    <integer>900</integer>
</dict>
</plist>

readsmart.sh

#!/usr/bin/env bash -e
smart=`/usr/local/bin/smartctl -Aj disk0`
curl -k -F "device=Apple_1TB_iMac2020" -F "token=Apricot-4Contented-Carless-Spiritism-Clubs7-Trinity" -F "data=$smart" https://egregius.be/smartupd.php
exit

receivesmart.php

<?php
if (isset($_REQUEST['token'])&&$_REQUEST['token']=='Apricot-4Contented-Carless-Spiritism-Clubs7-Trinity') {
	$date=strftime('%F', time());
	echo $date.PHP_EOL;
	if ($_REQUEST['device']=='KINGSTON_SA400S37120G') {
		$data=json_decode($_REQUEST['data'], true);
		foreach ($data['ata_smart_attributes']['table'] as $d) {
			if ($d['id']==9) $PowerOn=$d['raw']['value'];
			elseif ($d['id']==12) $cycles=$d['raw']['value'];
			elseif ($d['id']==241) $GB=$d['raw']['value'];
		}
		echo 'KINGSTON_SA400S37120G	'.$GB.'GB	'.$PowerOn.'u	cycles:'.$cycles.PHP_EOL;
		$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
		$db->query("INSERT INTO KINGSTON_SA400S37120G (date, written, poweron, cycles) VALUES ('$date','$GB','$PowerOn','$cycles') ON DUPLICATE KEY UPDATE written='$GB', poweron='$PowerOn', cycles='$cycles';");
	} elseif ($_REQUEST['device']=='SAMSUNG_970EVOPLUS') {
		$data=json_decode($_REQUEST['data'], true);
		$PowerOn=$data['nvme_smart_health_information_log']['power_on_hours'];
		$cycles=$data['nvme_smart_health_information_log']['power_cycles'];
		$GB=round(($data['nvme_smart_health_information_log']['data_units_written']*512)/1024/1024);
		echo 'SAMSUNG_970EVOPLUS	'.$GB.'GB	'.$PowerOn.'u	cycles:'.$cycles.PHP_EOL;
		$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
		$db->query("INSERT INTO SAMSUNG_970EVOPLUS (date, written, poweron, cycles) VALUES ('$date','$GB','$PowerOn','$cycles') ON DUPLICATE KEY UPDATE written='$GB', poweron='$PowerOn', cycles='$cycles';");
	} elseif ($_REQUEST['device']=='SAMSUNG_840EVO250') {
		$data=json_decode($_REQUEST['data'], true);
		foreach ($data['ata_smart_attributes']['table'] as $d) {
			if ($d['id']==9) $PowerOn=$d['raw']['value'];
			elseif ($d['id']==12) $cycles=$d['raw']['value'];
			elseif ($d['id']==241) $GB=round(($d['raw']['value']*512)/1024/1024/1024, 0);
		}
		echo 'SAMSUNG_840EVO250	'.$GB.'GB	'.$PowerOn.'u	cycles:'.$cycles.PHP_EOL;
		$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
		$query="INSERT INTO SAMSUNG_840EVO250 (date, written, poweron, cycles) VALUES ('$date','$GB','$PowerOn','$cycles') ON DUPLICATE KEY UPDATE written='$GB', poweron='$PowerOn', cycles='$cycles';";
		echo $query.PHP_EOL;
		$db->query($query);
	} elseif ($_REQUEST['device']=='SAMSUNG_860PRO256') {
		$data=json_decode($_REQUEST['data'], true);
		foreach ($data['ata_smart_attributes']['table'] as $d) {
			if ($d['id']==9) $PowerOn=$d['raw']['value'];
			elseif ($d['id']==12) $cycles=$d['raw']['value'];
			elseif ($d['id']==241) $GB=round(($d['raw']['value']*512)/1024/1024/1024, 0);
		}
		echo 'SAMSUNG_860PRO256	'.$GB.'GB	'.$PowerOn.'u	cycles:'.$cycles.PHP_EOL;
		$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
		$db->query("INSERT INTO SAMSUNG_860PRO256 (date, written, poweron, cycles) VALUES ('$date','$GB','$PowerOn','$cycles') ON DUPLICATE KEY UPDATE written='$GB', poweron='$PowerOn', cycles='$cycles';");
	} elseif ($_REQUEST['device']=='SAMSUNG_860QVO1TB') {
		$data=json_decode($_REQUEST['data'], true);
		foreach ($data['ata_smart_attributes']['table'] as $d) {
			if ($d['id']==9) $PowerOn=$d['raw']['value'];
			elseif ($d['id']==12) $cycles=$d['raw']['value'];
			elseif ($d['id']==241) {
				print_r($d);
				echo $d['raw']['value'].PHP_EOL;
				$GB=round(($d['raw']['value']*512)/1024/1024/1024, 0);
			}
		}
		echo 'SAMSUNG_860QVO1TB	'.$GB.'GB	'.$PowerOn.'u	cycles:'.$cycles.PHP_EOL;
		$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
		$db->query("INSERT INTO SAMSUNG_860QVO1TB (date, written, poweron, cycles) VALUES ('$date','$GB','$PowerOn','$cycles') ON DUPLICATE KEY UPDATE written='$GB', poweron='$PowerOn', cycles='$cycles';");
	} elseif ($_REQUEST['device']=='Apple_1TB_iMac2020') {
		$data=json_decode($_REQUEST['data'], true);
		foreach ($data['nvme_smart_health_information_log'] as $k=>$v) {
			echo $k.' = '.$v.PHP_EOL;
			if ($k=='power_on_hours') $PowerOn=$v;
			elseif ($k=='power_cycles') $cycles=$v;
			elseif ($k=='data_units_written') {
				$GB=round(($v*512)/1024/1024, 0);
			}
		}
		$msg= 'Apple_1TB_iMac2020	'.$GB.'GB	'.$PowerOn.'u	cycles:'.$cycles.PHP_EOL;
		echo $msg;
		$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
		$db->query("INSERT INTO `Apple_1TB_iMac2020` (date, written, poweron, cycles) VALUES ('$date','$GB','$PowerOn','$cycles') ON DUPLICATE KEY UPDATE written='$GB', poweron='$PowerOn', cycles='$cycles';");
	}
}

smart.php

<?php
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');
?>
<html>
	<head>
		<title>SSD TBW</title>
		<style>
			table{border:1px solid grey;border-spacing:0px;}
			th{text-align:center;vertical-align:bottom;border:1px solid grey;}
			td{text-align:center;vertical-align:bottom;border:1px solid grey;}
			.odd{background-color:#DDD;}
			.left{text-align:left;}
			.right{text-align:right;}
		</style>
	</head>
	<body>
		<h3>Analysis of several SSD drives</h3>

<?php
$time=time();
$data['SAMSUNG_840EVO250']['name']='Samsung 840 EVO 250GB';
$data['SAMSUNG_970EVOPLUS']['name']='Samsung 970 EVO PLUS 500GB';
$data['KINGSTON_SA400S37120G']['name']='Kingston SA400S37 120GB';
$data['SAMSUNG_860PRO256']['name']='Samsung 860 PRO 256GB';
$data['SAMSUNG_860QVO1TB']['name']='Samsung 860 QVO 1TB';
$data['Apple_1TB_iMac2020']['name']='Apple SSD AP1024N';

$data['SAMSUNG_840EVO250']['startdate']=strtotime("2013/09/05");
$data['SAMSUNG_970EVOPLUS']['startdate']=strtotime("2020/02/21");
$data['KINGSTON_SA400S37120G']['startdate']=strtotime("2017/10/26");
$data['SAMSUNG_860PRO256']['startdate']=strtotime("2019/07/01");
$data['SAMSUNG_860QVO1TB']['startdate']=strtotime("2019/04/16");
$data['Apple_1TB_iMac2020']['startdate']=strtotime("2020/08/24");

$data['SAMSUNG_840EVO250']['gbw']=240*1024;
$data['SAMSUNG_970EVOPLUS']['gbw']=300*1024;
$data['KINGSTON_SA400S37120G']['gbw']=40*1024;
$data['SAMSUNG_860PRO256']['gbw']=300*1024;
$data['SAMSUNG_860QVO1TB']['gbw']=360*1024;
$data['Apple_1TB_iMac2020']['gbw']=600*1024;

$data['SAMSUNG_840EVO250']['info']='iMac macOS High Sierra';
$data['SAMSUNG_970EVOPLUS']['info']='Proxmox data drive for pfSense, Domoticz and Windows 10';
$data['KINGSTON_SA400S37120G']['info']='Proxmox system drive, earlier data drive replaced by the Samsung 970';
$data['SAMSUNG_860PRO256']['info']='Proxmox system and data drive for several Windows 2016 servers';
$data['SAMSUNG_860QVO1TB']['info']='Proxmox system and data drive for several Windows 2016 servers';
$data['Apple_1TB_iMac2020']['info']='iMac 27" 2020 macOS Catalina';


$db=new PDO("mysql:host=localhost;dbname=dbname;",'user','password');
$ssds=array('KINGSTON_SA400S37120G','SAMSUNG_970EVOPLUS','Apple_1TB_iMac2020','SAMSUNG_840EVO250','SAMSUNG_860PRO256','SAMSUNG_860QVO1TB');
foreach($ssds as $ssd) {
	$stmt=$db->query("select date, written, poweron, cycles from `$ssd` order by date asc;");
	while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
		$d[$row['date']][$ssd]['written'] = $row['written'];
		$d[$row['date']][$ssd]['poweron'] = $row['poweron'];
		$d[$row['date']][$ssd]['cycles'] = $row['cycles'];
	}
}
$header='<thead>
				<tr>
					<th rowspan="3">Date</th>';
foreach ($ssds as $ssd)	$header.='
					<th colspan="6">'.$ssd.'</th>';
$header.='				</tr>
				<tr>';
foreach ($ssds as $ssd) $header.='
					<th colspan="3">Total</th>
					<th colspan="3">Today</th>';
$header.='
				</tr>
				<tr>';
foreach ($ssds as $ssd) $header.='
					<th>GB<br>Written</th>
					<th>Power<br>on</th>
					<th>Power<br>cycles</th>
					<th>GB<br>Written</th>
					<th>Power<br>on</th>
					<th>Power<br>cycles</th>';
$header.='
				</tr>
			</thead>';
			
//print_r($d);
echo '
		<table>';
echo $header;
echo '			
			<tbody>';
foreach($ssds as $ssd) {
	${'GB'.$ssd}=0;
	${'ON'.$ssd}=0;
	${'CY'.$ssd}=0;
}
foreach ($d as $k=>$v) {
	$day=strftime("%e", strtotime($k));
	$weekday=strftime("%w", strtotime($k));
	
	
	if ($day==1) echo $header;
		if ($weekday==0||$weekday==6) echo '
				<tr class="odd">';
		else echo '
				<tr>';
	echo '
					<td nowrap>'.$k.'</td>';
	foreach($ssds as $ssd) {
		${'percent'.$ssd}=${'GB'.$ssd}/$data[$ssd]['gbw']*100;
		${'age'.$ssd}=strtotime($k)-$data[$ssd]['startdate'];
		${'EOL'.$ssd}=strftime("%e-%m-%Y", floor($data[$ssd]['startdate']+((${'age'.$ssd}/${'percent'.$ssd})*100)));

		echo '
					<td title="End of life expectancy at this time = '.${'EOL'.$ssd}.'">'.number_format($v[$ssd]['written'], 0, ',', '.').'</td>
					<td>'.$v[$ssd]['poweron'].'</td>
					<td>'.$v[$ssd]['cycles'].'</td>
					<td>';
		if (${'GB'.$ssd}>0&&$v[$ssd]['written']>${'GB'.$ssd})echo number_format($v[$ssd]['written']-${'GB'.$ssd}, 0);
		echo '</td>
					
					<td>';
		if (${'ON'.$ssd}>0&&$v[$ssd]['poweron']>${'ON'.$ssd})echo number_format($v[$ssd]['poweron']-${'ON'.$ssd}, 0);
		echo '</td>
					
					<td>';
		if (${'CY'.$ssd}>0&&$v[$ssd]['cycles']>${'CY'.$ssd})echo number_format($v[$ssd]['cycles']-${'CY'.$ssd}, 0);
		echo '</td>';
		if ($v[$ssd]['written']>0) ${'GB'.$ssd}=$v[$ssd]['written'];
		if ($v[$ssd]['poweron']>0) ${'ON'.$ssd}=$v[$ssd]['poweron'];
		if ($v[$ssd]['cycles']>0) ${'CY'.$ssd}=$v[$ssd]['cycles'];
	}
	echo '
				</tr>';
}
echo '
			</tbody>
			<tfoot>
				<tr>
					<th rowspan="3">Status</th>';
foreach ($ssds as $ssd) echo '
					<th colspan="6">'.$ssd.'</th>';
echo '				</tr>
				<tr>';
foreach ($ssds as $ssd) echo '
					<th colspan="3">% TBW</th>
					<th colspan="3">EOL Expectancy</th>';	
echo '
				</tr>
				<tr>';
foreach ($ssds as $ssd) {
	if (${'GB'.$ssd}>0 ) {
		${'percent'.$ssd}=${'GB'.$ssd}/$data[$ssd]['gbw']*100;
		${'age'.$ssd}=$time-$data[$ssd]['startdate'];
		${'EOL'.$ssd}=strftime("%e-%m-%Y", floor($data[$ssd]['startdate']+((${'age'.$ssd}/${'percent'.$ssd})*100)));
		${'TB'.$ssd}=${'GB'.$ssd}/1024;
		//${'EOL'.$ssd}=$data[$ssd]['startdate']+((${'age'.$ssd}/${'percent'.$ssd})*100);
	}
}
foreach ($ssds as $ssd) echo '
					<th colspan="3">'.(number_format(${'percent'.$ssd}, 2, ',', '.')).' %</th>
					<th colspan="3">'.(${'EOL'.$ssd}).'</th>';	
echo '
				</tr>
			</tfoot>
		</table>';
echo '
		<br>
		<br>
		<table>
			<thead>
				<tr>
					<th>Name</th>
					<th>In use since</th>
					<th>GB / day</th>
					<th>TBW</th>
					<th>TBW expected</th>
					<th>Info</th>
				</tr>
			</thead>
			<tbody>';
foreach ($ssds as $ssd) {
	$age=($time-$data[$ssd]['startdate'])/86400;
	$avgday=(${'TB'.$ssd}*1024)/$age;
	echo '
		<tr>
			<td class="left">'.$data[$ssd]['name'].'</td>
			<td class="right">'.strftime("%d-%m-%Y", $data[$ssd]['startdate']).'</td>
			<td class="right">'.number_format($avgday, 1, ',', ' ').' GB</td>
			<td class="right">'.number_format(${'TB'.$ssd}, 1, ',', ' ').' TB</td>
			<td class="right">'.number_format($data[$ssd]['gbw']/1024, 0, ',', ' ').' TB</td>
			<td class="left">'.$data[$ssd]['info'].'</td>
		</tr>';
}
echo '
			</tbody>
		</table>
	</body>
</html>';