Submitted by brett.ferringer on Thu, 06/17/2021 - 20:17

This page is to showcase some of my projects that I have been working on.



Thu, 06/17/2021 - 20:33

I wrote the following batch script to automatically run SQL commands and log the results. This particular script was originally meant for automated select queries.

@echo off

set SName="<ip or server name>"
set DbName="<databaseName>"
set RtnFolder=<output folder>
set mydate=%date:~10,4%-%date:~4,2%-%date:~7,2%
set logName=<log name>

@echo on

rem checks to see if the logfile exists
IF NOT EXIST %~dp0\logs\%logName%.log (
    ECHO Name,Status,Date,Time>>"%~dp0logs\%logName%.log"

rem adding line to log that the shows the start of the full process
ECHO %logName%,Start,%DATE%,%TIME%>>"%~dp0logs\%logName%.log"

rem running through the sql commands in the sql folder to run all select queries available
rem this logs each start and finish to the log file
rem this only picks up .sql files. All other files are ignored
FOR %%i IN ("%~dp0sql\*.sql") DO ECHO %%~ni,Start,%DATE%,%TIME%>>"%~dp0logs\%logName%.log"^
 && sqlcmd -S %SName% -E -d %DbName% -i "%%i" -v PARM1=%mydate% -o "%RtnFolder%%%~ni - %mydate%.csv" -W -w 999 -s"|"^
 && IF ERRORLEVEL 1 (ECHO %%~ni,FAIL,%DATE%,%TIME%>>"%~dp0logs\%logName%.log") ELSE (ECHO %%~ni,Finish,%DATE%,%TIME%>>"%~dp0logs\%logName%.log")

rem adding line to log the end of the full process
ECHO %logName%,Finish,%DATE%,%TIME%>>"%~dp0logs\%logName%.log"


Thu, 06/17/2021 - 20:49

 This script is meant to automate my Linux server update process. It logs the results to my Maria DB database.


mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE processes SET tmp = '' WHERE tmp = 'sSM$serverName'"
mysql --host=$dip --user=$dun --password=$dpw $dn -e "INSERT INTO processes (name, status, start, tmp, server) VALUES ('systemMaintenance', 'start', now(), 'sSM$serverName', '$serverName')"

apt-get update
apt-get -y upgrade

apt-get -y autoremove
apt-get autoclean

mysql --host=$dip --user=$dun --password=$dpw $dn -e "UPDATE processes SET end = now(), tmp = '', status = 'finished' WHERE tmp = 'sSM$serverName'"