Shell Script Execute on Remote Machine

Shell Script Execute on Remote Machine

I remember my previous company had this similar script.

#!/bin/bash

#####################################
# how to run
# ./dump-css-devices-to-csv.sh HOST PORT SYS_USER DB_NAME USER PASSWORD
#####################################

start=$SECONDS

DEFAULT_DB_HOST="localhost"
DEFAULT_PORT=22
DEFAULT_SYSTEM_USER="root"
DEFAULT_DB_USER=“admin"
DEFAULT_DB_TABLE=“test"

DB_HOST=${1:-$DEFAULT_DB_HOST}
PORT=${2:-$DEFAULT_PORT}
SYSTEM_USER=${3:-$DEFAULT_SYSTEM_USER}
DB_TABLE=${4:-$DEFAULT_DB_TABLE}
DB_USER=${5:-$DEFAULT_DB_USER}

echo "Prepare the Remote DB Server ENV for DUMP"
ssh -i ~/.ssh/sky -p $PORT $SYSTEM_USER@$DB_HOST "rm -fr /home/sillycat/css_device_dumps"
ssh -i ~/.ssh/sky -p $PORT $SYSTEM_USER@$DB_HOST "mkdir /home/sillycat/css_device_dumps"
ssh -i ~/.ssh/sky -p $PORT $SYSTEM_USER@$DB_HOST "touch /home/sillycat/css_device_dumps/css_devices_only_csv.txt"
ssh -i ~/.ssh/sky -p $PORT $SYSTEM_USER@$DB_HOST "chmod 666 /home/sillycat/css_device_dumps/css_devices_only_csv.txt"

echo "Start to dump devices to CSV"

ssh -i ~/.ssh/sky -p $PORT $SYSTEM_USER@$DB_HOST "export PGPASSWORD=$6;psql -U $DB_USER -w -d $DB_TABLE -h localhost -c \"COPY (
SELECT
DISTINCT ON (dap.serialnumber, dap.platform) dap.serialnumber as \"serialNumber\",
dap.platform,
dap.uuid as deviceUUID,
dap.enabled,
dap.pairingtoken as \"pairingToken\",
dap.currentfirmwareversion as \"firmware\",
dap.timezoneoffsetinminutes as \"timezoneOffsetinMinutes\",
dap.pairingstatus as \"pairingStatus\",
dap.lastlogindate as \"lastLoginDate\",
ua.usagetype as \"usageType\",
ua.uuid as \"userUUID\",
ua.displayName AS \"systemName\",
ua.extension AS \"extension\",
re.remoteIP as \"remoteIP\",
ug.uuid AS \"groupUUID\"
FROM
sillycat_4_1.deviceaccountpairing dap JOIN
sillycat_4_1.useraccount AS ua ON ua.id = dap.useraccountid JOIN
sillycat_4_1.usergroup AS ug ON (ua.groupid = ug.id) LEFT JOIN
sillycat_4_1.remoteendpoint AS re on (dap.serialNumber = re.serialNumber)
ORDER BY
dap.serialnumber, dap.platform, dap.lastlogindate DESC )
TO '/home/sillycat/css_device_dumps/css_devices_only_csv.txt' WITH (FORMAT CSV, HEADER, DELIMITER ',', ENCODING 'UTF8', FORCE_QUOTE *)\";"

echo "Download the CSV to Local"
# remove any old csv files already on disk
rm -f css_devices_only_csv.txt
# get users file from css database
sudo scp -i ~/.ssh/sky -P $PORT $SYSTEM_USER@$DB_HOST:/home/sillycat/css_device_dumps/css_devices_only_csv.txt ./css_devices_only_csv.txt

end=$SECONDS
duration=$(( end - start ))
echo "Script completed in $duration seconds."


Reference:
https://zaiste.net/a_few_ways_to_execute_commands_remotely_using_ssh/