collect server info and put it into mysql

collecthdd.sh

#!/bin/bash

smartctl --scan-open -j | jq .devices[] | jq '.name+" -d "+.type' -r | grep -v scsi > /tmp/tmpfs/devices.txt

cat /tmp/tmpfs/devices.txt | while read xdevice
do
        smartctl -a $xdevice -j > /tmp/tmpfs/smart.json
        passed=$(cat /tmp/tmpfs/smart.json | jq .smart_status.passed -r)
        poh=$(cat /tmp/tmpfs/smart.json | jq .power_on_time.hours -r)
        defects=$(cat /tmp/tmpfs/smart.json | jq .scsi_grown_defect_list -r)
        capacity=$(cat /tmp/tmpfs/smart.json | jq .user_capacity.bytes -r)
        rotation=$(cat /tmp/tmpfs/smart.json | jq .rotation_rate -r)
        serno=$(cat /tmp/tmpfs/smart.json | jq .serial_number -r)
        model=$(cat /tmp/tmpfs/smart.json | jq .model_name -r)
        uncorrected=$(cat /tmp/tmpfs/smart.json | jq '.scsi_error_counter_log.read.total_uncorrected_errors+.scsi_error_counter_log.write.total_uncorrected_errors+.scsi_error_counter_log.verify.total_uncorrected_errors' -r)

        if [ "$passed" = "true" ]; then
                passed=1
        else
                passed=0
        fi

        echo "insert into hdds (serno, model, passed, poh, defects, capacity, rotation, uncorrected) values ('$serno', '$model', $passed, $poh, $defects, $capacity, $rotation, $uncorrected) on duplicate key update passed=$passed, defects=$defects, uncorrected=$uncorrected;" | mysql -u inventory -pinventory inventory --host vorash.smeg
done

collectram.sh

#!/bin/bash

dmidecode -t memory | grep "Memory Device" -A 20 | awk -v RS= '{print > ("/tmp/tmpfs/ram-" NR ".txt")}'

for i in $(ls /tmp/tmpfs/ram-*.txt); do
        size=$(cat $i | egrep "Size:" | grep GB | tail -n 1)
        if [ x"${size}" = "x" ]; then
                continue
        fi

        size=$(cat $i | grep "^\sSize:" | awk '{print $2}')
        speed=$(cat $i | grep "^\sSpeed:" | awk '{print $2}')
        serno=$(cat $i | grep "^\sSerial Number:" | awk '{print $3}')
        manuf=$(cat $i | grep "^\sManufacturer:" | awk '{print $2}')
        model=$(cat $i | grep "^\sPart Number:" | awk '{print $3}')

        echo "speed: $speed .. serno: $serno .. manuf: $manuf .. size: $size"

        echo "insert into ram (serno, model, manufacturer, speed, capacity) values ('$serno', '$model', '$manuf', $speed, $size) on duplicate key update model='$model', capacity=$size, speed=$speed;" | mysql -u inventory -pinventory inventory --host vorash.smeg

done

db.sql

DROP TABLE IF EXISTS `chassis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `chassis` (
  `serno` varchar(32) DEFAULT NULL,
  `type` varchar(32) DEFAULT NULL,
  `tested` tinyint(1) DEFAULT NULL,
  `hasraid` tinyint(1) DEFAULT NULL,
  `sold` int(11) DEFAULT NULL,
  UNIQUE KEY `serno` (`serno`),
  KEY `sold` (`sold`),
  CONSTRAINT `chassis_ibfk_1` FOREIGN KEY (`sold`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `hdds`
--

DROP TABLE IF EXISTS `hdds`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `hdds` (
  `serno` varchar(32) DEFAULT NULL,
  `model` varchar(64) DEFAULT NULL,
  `passed` tinyint(1) DEFAULT NULL,
  `poh` int(11) DEFAULT NULL,
  `defects` int(11) DEFAULT NULL,
  `rotation` int(11) DEFAULT NULL,
  `uncorrected` int(11) DEFAULT NULL,
  `capacity` bigint(20) DEFAULT NULL,
  `sold` int(11) DEFAULT NULL,
  UNIQUE KEY `serno` (`serno`),
  KEY `sold` (`sold`),
  CONSTRAINT `hdds_ibfk_1` FOREIGN KEY (`sold`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `orders`
--

DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `towhom` varchar(64) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ram`
--

DROP TABLE IF EXISTS `ram`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ram` (
  `serno` varchar(32) DEFAULT NULL,
  `model` varchar(32) DEFAULT NULL,
  `manufacturer` varchar(32) DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `sold` int(11) DEFAULT NULL,
  UNIQUE KEY `serno` (`serno`),
  KEY `sold` (`sold`),
  CONSTRAINT `ram_ibfk_1` FOREIGN KEY (`sold`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

docollect.sh

#!/bin/bash

mount -o remount,rw /
ipmitool lan set 2 ipsrc dhcp
ipmitool lan set 1 ipsrc dhcp

sleep 60

mkdir /tmp/tmpfs || :
mountpoint -q /tmp/tmpfs || mount -t tmpfs tmpfs /tmp/tmpfs

/www/prometheus/hw-exporter/sbin/node-smart.py --out_path=/tmp/tmpfs/smart.txt
/www/prometheus/hw-exporter/sbin/node-raid.py --out_path=/tmp/tmpfs/raid.txt

tmpfile=/tmp/tmpfs/message.txt

echo "Hello, I am deblive and I just booted:" > $tmpfile
echo " IP: $(ip a l | grep "inet "| grep -v "127.0.0.1" | awk '{print $2}' |cut -d"/" -f1)" >> $tmpfile
dmidecode -t system |grep "Product Name" >> $tmpfile
SERNO=$(dmidecode --string system-serial-number | tee -a $tmpfile)
dmidecode -t bios | grep "Version: " >> $tmpfile
echo "=== BMC ===" >> $tmpfile
ipmitool lan print | grep "IP Address  " >> $tmpfile
ipmitool mc info | grep "Firmware Revision" >> $tmpfile
echo "=== BMC users ===" >> $tmpfile
ipmitool user list 1 | egrep -i "root|admin|dohled" >> $tmpfile
ipmitool user list 2 | egrep -i "root|admin|dohled" >> $tmpfile
echo "=== memory sticks ===" >> $tmpfile
dmidecode -t memory | grep -i "Size: " | grep -v "No Module" >> $tmpfile
echo "=== HDDs ===" >> $tmpfile
cat /tmp/tmpfs/smart.txt >> $tmpfile
cat /tmp/tmpfs/raid.txt >> $tmpfile

echo "=== this is all, BYE ===" >> $tmpfile

SERNO=$(echo $SERNO | cut -d":" -f2)

/root/collecthdd.sh
/root/collectram.sh

hasraid=0
lspci | egrep -i "sas|raid|perc|areca"
if [ $? -eq 0 ]; then
        hasraid=1
fi

model=$(dmidecode --string system-product-name)

echo "insert into chassis (serno, type, tested, hasraid) values ('$SERNO', '$model', 0, $hasraid) on duplicate key update type='$model';" | mysql -u inventory -pinventory inventory --host vorash.smeg


mail -s "Boot info $SERNO" booted@victorianfox.com < $tmpfile


hastested=$(mysql -u inventory -pinventory inventory --host vorash.smeg -s -N -e "select tested from chassis where serno='$SERNO'")

if [ $hastested -eq 0 ]; then
        stress-ng --vm $(nproc) --vm-bytes $(($(cat /proc/meminfo  | grep "MemFree" | awk '{print $2}')/1024-2048))M --vm-method all --verify -t 30m -v
        if [ $? -eq 0 ]; then
                echo "update chassis set tested=1 where serno='$serno';" | mysql -u inventory -pinventory inventory --host vorash.smeg
        fi
fi