System Status Report for SQL Server Running on Linux

#!/bin/bash 
# File name : status_report.sh
# Author Graham Okely B app Sc (IT)
 
# Comments begin with a #
# Wait for the SQL Server services to commence before running the report.
# m is minutes, test this you may need to add more delay time on bigger systems.
sleep 1m 
 
# Create the report file in the /tmp directory as the /tmp area is cleaned on boot up.
# The $$ means the process number
REPORT=/tmp/status_report.$$
 
# Commence the report with the server host name.
SERVER=`hostname`
# The echo -n means no carriage return, joining the follow line.
# A single > means wipe the file and add output to the file
echo -n 'Report for host : ' > $REPORT
# A double >> means append data to the file
echo $SERVER >> $REPORT
echo '' >> $REPORT
 
date >>$REPORT
# Two single quotes will add a line feed on the report
echo '' >> $REPORT
echo -n 'Current user : ' >> $REPORT
 
# The whoami comments gives the current username
whoami  >> $REPORT
echo '' >> $REPORT
 
echo 'Disk Free Space' >> $REPORT
echo '' >> $REPORT
# Get the disk free and sort it by smallest free space to most free space
# The df command shows us the disk space free
# -h Means human readable format please.
# Use the awk command to arrange the columns into the most important on the left.
# Use the sort command to order the report so that the LEAST FREE space is first.
# That means you only need to spot the upper left value to see the least free drive.
# Use grep to remove lines and put the report into a simple format.
df -h --total | awk '{printf " %-10s %-10s %-10s %-10s %-20s %-35s \n", $5,$2,$3,$4,$6,$1}' | grep Use >>$REPORT
df -h --total | awk '{printf " %-10s %-10s %-10s %-10s %-20s %-35s \n", $5,$2,$3,$4,$6,$1}' | grep -v Use | grep -v total | sort -nr >>$REPORT
df -h --total | awk '{printf " %-10s %-10s %-10s %-10s %-20s %-35s \n", $5,$2,$3,$4,$6,$1}' | grep total >>$REPORT
 
 
echo '' >> $REPORT
echo 'SQL Server Status' >> $REPORT
echo 'systemctl status mssql-server -q | head -5 ' >> $REPORT
# The systemctl command gives us a report on Linux systems.
systemctl status mssql-server -q | head -5 >>$REPORT
 
echo '' >> $REPORT
echo -n 'Users logged in:' >>$REPORT
who  >>$REPORT
 
echo '' >> $REPORT
# List databases available
echo 'Databases :' >>$REPORT
 
# You can add commands here as you see fit.
# This lists the databases available.
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P XXXXX -Q "Select [name] from sys.databases Order by [name]" | tail -n +3 >>$REPORT
# Show total space used
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P XXXXX -Q " Select Convert(Decimal(10,2),(Sum(size * 8.00)/1024.00/1024.00)) As GB_Total From master.sys.master_files" >>$REPORT
 
# Show the report, comment this out when automating the report.
cat $REPORT
 
# Email the report
# Use the host name in the email subject line so it easy to see which server rebooted.
cat $REPORT | mail -s $SERVER monitoring_group@your_host.com
 
# Clean up the report
rm $REPORT