Scripting - techinfo

Google Sheets - Auto update/insert "Last Upated" column on change

posted May 5, 2017, 3:48 AM by Chris Franklin [ updated May 5, 2017, 3:49 AM ]

After a cell has been changed the script will look through the header row for a Column called "Last Updated" and update or insert the date and current time into the cell on that row under the column called "Last Updated"

Text Box

function columnnumber2letter(column) {

var temp, letter = '';

while (column > 0) {

temp = (column - 1) % 26;

letter = String.fromCharCode(temp + 65) + letter;

column = (column - temp - 1) / 26;

}

return letter;

}

function onEdit() {

var sheet = SpreadsheetApp.getActiveSheet();

var activecell = sheet.getActiveCell();

var temp = sheet.getDataRange().getValues();

var lastupdatecolumn = temp[0].indexOf('Last Updated') + 1;

if ( activecell.getRowIndex() > 1 ) {

if ( activecell.getColumn() != lastupdatecolumn ) { //checks the column

var row = activecell.getRow();

var time = new Date();

time = Utilities.formatDate(time, "GMT-05:00", "yyyyMMdd HH:mm");

sheet.getRange(columnnumber2letter(lastupdatecolumn) + row.toString()).setValue(time);

};

VMWare copy and keep thin provisioning scripts & register the VM on a backup VM server

posted Mar 31, 2017, 6:54 AM by Chris Franklin [ updated Mar 31, 2017, 7:17 AM ]

I use ghettovcb for backing up my VM Hosts to my file servers via NFS. I do this as I can't quickly and easily copy them directly to my backup VM host(s). As VMWare refuses to acknowledge that it's users want and haven been asking for remote access to datastores since the beginning of time. So here is my stop gap solution that only uses the tools built into VMWare (like ghettovcb) to copy from a NFS mount to the backup VM host, Register the latest good backup & unregister the old copy of that same server

VM Host ( ghettovcb via cron )=> NFS Share => Backup VM Host (My Scripts Via Cron)

This script does the fallowing.

Look for all STATUS.ok files found in the directory structure that is using the default ghettovcb setup (aka VM_Hostname/VM_Hostname-DATE ).
Copy over all files in that directory to a corresponding location on the "Backup VM Host"
Edit the Display names of all backups to read " Date - VM Hostname "
Copy the vmdk using vmkfstools to keep or covert the backup to a thin provisioned version
verify and log everything along the way
only keep X copies on the backup VM HOST
Auto removes Moved copied from NFS host
Only copys the STATUS.ok file the backup VM Host after everything else is done and verified aka no reported errors)

copy_vm_to_backup.sh

## config vars

max_copies=10

vmstore_backup="backups"

vmstore_restore="datastore1"

log="/vmfs/volumes/${vmstore_backup}/scripts/logs/esxi-backups.$(date +%s).log"

debug=1

vmkfstools="$(which vmkfstools)"

## functions

logit() {

if [ $debug -eq 1 ]; then

echo "$1"

fi

echo "$1" >> "${log}"

}

logit "========= Starting Copy ========="

if [ ! -d "${log}" ]; then

mdkir -p "${log}"

if [ $? -eq 0 ]; then

debug=1

logit "ERROR : mdkir -p '${log}'"

else

logit "Created Logs dir : '${log}'"

fi

## the work

find /vmfs/volumes/${vmstore_backup}/ -type d -maxdepth 1 -mindepth 1 | while read vmdirs; do

# Reset skip this vm

vmskip=0

if [ -d "${vmdirs}" ]; then

vmname=$(basename "${vmdirs}")

logit "Found dir : ${vmdirs}"

logit "VM name : ${vmname}"

if [ "${vmdirs}" != "scripts" ]; then

find "${vmdirs}" -type f -iname "STATUS.ok" | while read vmbackup; do

logit "Found Completed backup file : ${vmbackup}"

vmbackupdir=$(dirname "${vmbackup}")

vmrestoredir=$(dirname "${vmbackup/${vmstore_backup}/${vmstore_restore}}")

logit "vmbackupdir : ${vmbackupdir}"

logit "vmrestoredir : ${vmrestoredir}"

if [ -e "${vmrestoredir}/BACKUP.err" ]; then

logit "Removing old ERROR file"

rm -f "${vmrestoredir}/BACKUP.err"

fi

logit "Looking into : ${vmbackupdir}"

find "${vmbackupdir}" -type f | while read vmbackupfiles; do

vmbackupfilesdir=$(dirname "${vmbackupfiles}")

vmbackupfilesname=$(basename "${vmbackupfiles}")

vmbackupfilesextension="${vmbackupfilesname##*.}"

vmrestorefiles="${vmbackupfiles/$vmstore_backup/$vmstore_restore}"

logit "Found file : ${vmbackupfiles}"

if [ ! -d "${vmrestoredir}" ]; then

logit "Creating dir : ${vmrestoredir}"

mkdir -p "${vmrestoredir}"

if [ $? -ne 0 ]; then

vmskip=1

logit "ERROR : mkdir -p '${vmrestoredir}'"

fi

if [ $vmskip -eq 0 ]; then

case "${vmbackupfilesname}" in

"STATUS.ok") ## Ignore the STATUS.ok file

ignore=1

;;

*"-flat.vmdk") ## Ignore any -flat vhd files

logit "checking if flat file is an orphan. : '${vmrestorefiles}' or '${vmrestorefiles/-flat.vmdk/.vmdk}.converted'"

if [ -e "${vmrestorefiles}" ]; then

logit "Found restored flat file"

if [ ! -e "${vmrestorefiles/-flat.vmdk/.vmdk}.converted" ]; then

logit "Found no converted flag though : '${vmrestorefiles/-flat.vmdk/.vmdk}.converted'"

if [ -e "${vmrestorefiles/-flat.vmdk/.vmdk}.convert.log" ]; then

if [ $(grep -c '100% done.' "${vmrestorefiles/-flat.vmdk/.vmdk}.convert.log") -eq 1 ]; then

logit "Found convert.log & it reads the conver was 100%. Creating converted flag file : '${vmrestorefiles/-flat.vmdk/.vmdk}.converted'"

touch "${vmrestorefiles/-flat.vmdk/.vmdk}.converted"

else

logit "ERROR : No converted flag found, convert file does NOT read 100% done. Leaving file"

fi

else

logit "Found no converted log : '${vmrestorefiles/-flat.vmdk/.vmdk}.convert.log'"

fi

if [ -e "${vmrestorefiles/-flat.vmdk/.vmdk}.converted" ]; then

logit "Found left over flat file"

if [ ! -e "${vmbackupfiles/-flat.vmdk/.vmdk}" ]; then

logit "Removing left over flat (orphan)"

rm -f "${vmbackupfiles}"

if [ $? -ne 0 ]; then

logit "ERROR : Removing left over flat (orphan) : rm -f '${vmbackupfiles}'"

fi

;;

*".vmdk") ## Convert to thin (or at least keep thin provisioning)

vmrestorefilesflat="${vmrestorefiles%.*}-flat.vmdk"

if [ ! -e "${vmrestorefiles}.converted" ]; then

logit "Looking for any preveous convert attemps : '${vmrestorefiles}' or '${vmrestorefilesflat}'"

if [ -e "${vmrestorefiles}" ] || [ -e "${vmrestorefilesflat}" ]; then

if [ -e "${vmrestorefiles}" ]; then

logit "Removing preveous convert attemp : '${vmrestorefiles}'"

rm -f "${vmrestorefiles}"

if [ $? -ne 0 ]; then

logit "ERROR : removing preveous convert attemp : rm -f '${vmrestorefiles}'"

fi

if [ -e "${vmrestorefilesflat}" ]; then

logit "Removing preveous convert attemp (flat) : '${vmrestorefilesflat}'"

rm -f "${vmrestorefilesflat}"

if [ $? -ne 0 ]; then

logit "ERROR : removing preveous convert attemp : rm -f '${vmrestorefilesflat}'"

fi

logit "Convert : Start : $(date)"

logit "Converting : '${vmbackupfiles}' => '${vmrestorefiles}'"

$vmkfstools -d thin -i "${vmbackupfiles}" "${vmrestorefiles}" > "${vmrestorefiles}.convert.log"

if [ $? -eq 0 ] && [ $(grep -c '100% done.' "${vmrestorefiles}.convert.log") -eq 1 ]; then

logit "Convert : End : $(date)"

touch "${vmrestorefiles}.converted"

logit "Created : ${vmrestorefiles}.converted"

logit "Looking for vmdk file : '${vmbackupfiles}'"

if [ -e "${vmbackupfiles}" ]; then

echo "Removing Converted VHD : ${vmbackupfiles}"

rm -f "${vmbackupfiles}"

fi

logit "Looking for flat file : '${vmbackupfilesdir}/${vmbackupfilesname%.*}-flat.vmdk'"

if [ -e "${vmbackupfilesdir}/${vmbackupfilesname%.*}-flat.vmdk" ]; then

echo "Removing Converted Flat VHD : ${vmbackupfiles%.*}-flat.vmdk"

rm -f "${vmbackupfilesdir}/${vmbackupfilesname%.*}-flat.vmdk"

fi

else

logit "Convert : End : $(date)"

logit "ERROR : $vmkfstools -d thin -i '${vmbackupfiles}' '${vmrestorefiles}'"

vmskip=1

fi

else

logit "Already Convereted found : '${vmrestorefiles}.converted'"

if [ $(grep -c '100% done.' "${vmrestorefiles}.convert.log") -eq 1 ]; then

logit "Converted log reports 100% complete : grep -c '100% done.' '${vmrestorefiles}.convert.log'"

logit "Looking for left over vmdk file : '${vmbackupfiles}'"

if [ -e "${vmbackupfiles}" ]; then

echo "Removing Converted VHD : ${vmbackupfiles}"

rm -f "${vmbackupfiles}"

fi

logit "Looking for left over flat file : '${vmbackupfilesdir}/${vmbackupfilesname%.*}-flat.vmdk'"

if [ -e "${vmbackupfilesdir}/${vmbackupfilesname%.*}-flat.vmdk" ]; then

echo "Removing Converted Flat VHD : ${vmbackupfiles}"

rm -f "${vmbackupfilesdir}/${vmbackupfilesname%.*}-flat.vmdk"

fi

;;

*".vmx") ## Copy and modify base config

vmbackupdate="${vmrestoredir%%_*}"

vmbackupdate="${vmbackupdate##*/}"

vmbackupdate="${vmbackupdate/${vmname}-/}"

cp -f "${vmbackupfiles}" "${vmrestorefiles}.org"

if [ $? -eq 0 ]; then

logit "Copied Config : ${vmrestorefiles}.org"

cat "${vmrestorefiles}.org" | sed -e 's/^displayName\ \=\ \".*"$/displayName\ \=\ \"'"${vmbackupdate} ${vmname}"'"/' > "${vmrestorefiles}"

if [ $? -eq 0 ]; then

logit "Mod'ed Config : ${vmrestorefiles}"

rm -f "${vmbackupfiles}"

else

logit "ERROR : Mod'ing Config : ${vmrestorefiles}"

vmskip=1

fi

else

logit "ERROR : Copying Config : ${vmbackupfiles} => ${vmrestorefiles}.org"

vmskip=1

fi

;;

*) ## Default copy any non known file

if [ ! -d "${vmrestoredir}" ]; then

mkdir -p "${vmrestoredir}"

if [ $? -ne 0 ]; then

logit "ERROR : mkdir -p '${vmrestoredir}'"

vmskip=1

fi

if [ -d "${vmrestoredir}" ]; then

cp -f "${vmbackupfiles}" "${vmrestorefiles}"

if [ $? -eq 0 ]; then

logit "Copied MISC file : ${vmbackupfiles}"

rm -f "${vmbackupfiles}"

else

logit "ERROR : Coping MISC file : cp -f '${vmbackupfiles}' '${vmrestorefiles}'"

vmskip=1

fi

else

logit "ERROR : Coping MISC file : no dir '${vmrestorefiles}'"

vmskip=1

fi

;;

esac

fi

if [ $vmskip -eq 1 ]; then

touch ${vmrestoredir}/BACKUP.err

fi

done

if [ $(find "${vmbackupdir}" -type f | wc -l) -eq 1 ] && [ ! -e "${vmrestoredir}/BACKUP.err" ]; then

logit "Found only remaning file : $(find '${vmbackupdir}' -type f)"

touch "${vmrestoredir}/STATUS.ok"

if [ $? -eq 0 ]; then

logit "Created STATUS.ok file : ${vmrestoredir}/STATUS.ok"

rm -f "${vmbackupdir}/STATUS.ok"

if [ $? -ne 0 ]; then

logit "ERROR : unable to delete backup source STATUS.ok : rm -f '${vmbackupdir}/STATUS.ok'"

fi

else

logit "ERROR : Creating STATUS.ok : touch '${vmrestoredir}/STATUS.ok'"

fi

if [ $? -eq 0 ]; then

rm -f "${vmbackupdir}/STATUS.ok"

fi

vmbackupdir="${vmbackupdir//../}"

case "${vm2delete}" in

*[!/]*/)

vmbackupdir=${vmbackupdir%"${vmbackupdir##*[!/]}"}

;;

esac

if [ -d "vmbackupdir" ]; then

if [ "${vmbackupdir}" != "/vmfs/volumes/${vmstore_backup}" ] && [ "${vmbackupdir}" != "/vmfs/volumes" ] && [ "${vmbackupdir}" != "/vmfs" ]; then

rm -rf "${vmbackupdir}"

if [ $? -eq 0 ]; then

logit "Removed : '${vmbackupdir}'"

else

logit "ERROR : rm -rf '${vmbackupdir}'"

fi

else

logit "ERROR : Found remaning files : $(find ${vmbackupdir} -type f)"

fi

done

fi

else

logit "Found misc file : ${vmdirs}"

fi

done

logit "======= Start Old Removal ======="

find /vmfs/volumes/${vmstore_restore}/ -type d -maxdepth 1 -mindepth 1 -not -iname ".*" | while read vmbase; do

foundvms=$(find "${vmbase}" -type f $ -name "STATUS.ok" -o -name "BACKUP.err" $ | wc -l)

logit "Old Copies Count : ${foundvms} for ${vmbase}"

if [ $foundvms -gt $max_copies ]; then

logit "Over Max Copies : ${vmbase} by $(($foundvms - $max_copies))"

ls -1cr "${vmbase}" | head -n $(($foundvms - $max_copies)) | while read vmdelete; do

vm2delete="${vmbase}/${vmdelete}/"

vm2delete="${vm2delete//../}"

case "${vm2delete}" in

*[!/]*/)

vm2delete=${vm2delete%"${vm2delete##*[!/]}"}

;;

esac

logit "Removing Old Copy : ${vmbase}/${vmdelete}"

if [ "${vm2delete}" != "/vmfs/volumes/${vmstore_restore}" ] && [ "${vm2delete}" != "/vmfs/volumes" ] && [ "${vm2delete}" != "/vmfs" ]; then

echo rm -rf "${vm2delete}"

if [ $? -eq 0 ]; then

logit "Removed : ${vm2delete}"

else

logit "ERROR : Removing Old Copy : '${vmbase}/${vmdelete}'"

fi

done

fi

done

logit "======= Completely Done ========="

This script does all the registering and un-registering of the backups.

register_guests.sh

vmstore_backup="datastore1"

find "/vmfs/volumes/${vmstore_backup}/" -type d -maxdepth 1 -mindepth 1 -not -iname "scripts" | while read vmname; do

ls -1r "${vmname}"/*/STATUS.ok | head -n 1 | while read vmstatusfile; do

## Setup some vars

vmpath=$(dirname "${vmstatusfile}")

vmversion=${vmpath##*/}

vmname=${vmpath%/*}

vmname=${vmname##*/}

vmdate=${vmpath##*/}

vmdate=${vmdate/${vmname}-/}

vmdate=${vmdate%_*} #2017-03-27

vmdisplayname="${vmname}_${vmdate}"

# See if this VM is already reg.

if [ $(vim-cmd vmsvc/getallvms | grep -c -i "${vmname}") -eq 0 ]; then

# if not reg it

echo "Adding : ${vmversion}"

find "${vmpath}" -iname "*.vmx" -exec vim-cmd solo/registervm '{}' \;

else

# test is this version is already reged, if not unreg the existing and reg this opne

if [ $(vim-cmd vmsvc/getallvms | grep -c "${vmversion}") -eq 0 ]; then

# get vmid of exiting vm guest

vmid=$(vim-cmd vmsvc/getallvms | grep -i "${vmname}" | awk '{print $1}')

# chec kthat it is not on. if it is, we must be using it and SHOULD NOT remove it

if [ $(vim-cmd vmsvc/power.getstate ${vmid} | grep -c "Powered off") ]; then

vim-cmd vmsvc/unregiste ${vmid}

# check that it was removed

if [ $(vim-cmd vmsvc/getallvms | grep -c -i "${vmname}") -eq 0 ]; then

# add the new one

echo "Adding : ${vmversion}"

find "${vmpath}" -iname "*.vmx" -exec vim-cmd solo/registervm '{}' \;

fi

else

echo "Skipping : ${vmversion}"

fi

done

Backup Samba USER sids

posted Oct 21, 2016, 6:22 AM by Chris Franklin [ updated Oct 21, 2016, 6:25 AM ]

Simple bash script

Requirements

Bash
sqlite3
samba

samba_backup_users_sids.bash

#!/bin/bash

PDBEDIT=$(which pdbedit)

SQLITE=$(which sqlite3)

DB=/etc/samba/backup_users_sid.sqlite

function _create_db() {

echo "CREATE TABLE users (uid INT primary key,sid);" | $SQLITE "${DB}"

}

if [ ! -e "${DB}" ]; then

_create_db

fi

function _sid_lookup() {

USERN=$1

$PDBEDIT -L -v "${USERN}" | while IFS=":" read KEY VALUE; do

case "${KEY}" in

"User SID") echo "${VALUE// /}";;

esac

done

}

$PDBEDIT -L | while IFS=":" read USERN USERUID TRASH; do

if [ "${USERN}" == "${USERN/\$/}" ]; then

SID=$(_sid_lookup "${USERN}")

echo "INSERT OR IGNORE INTO users (uid,sid) VALUES ('${USERUID}','${SID}');" | $SQLITE "${DB}"

fi

done

Example restore

Example Restore

if [ -e /etc/samba/backup_users_sid.sqlite ]; then

SID=$(/usr/bin/sqlite3 /etc/samba/backup_users_sid.sqlite "SELECT sid FROM users WHERE uid=${uid}")

else

SID=""

fi

if [ -z "${SID}" ]; then

(echo "${password}"; echo "${password}";) | /usr/bin/pdbedit -t -a -u "${username}" > /dev/null 2>&1

else

(echo "${password}"; echo "${password}";) | /usr/bin/pdbedit -t -a -U ${SID} -u "${username}" > /dev/null 2>&1

fi

Bash - Auto Tailing new files in a directory

posted Dec 23, 2015, 9:07 AM by Chris Franklin [ updated Dec 23, 2015, 9:56 AM ]

I had a simple problem, I had multiple log files I needed to monitor and have scripts act on the results. But tail does stop looking at a file if it "disappears" and doesn't look for new files in a Dir once it starts. And after looking at the alternatives xtail and multitail i decided I was better off coming up with my own way to dealing with these issues.

New version no grep inline with tail (little bit better)

Version 2

#!/bin/bash

logs_name="*-sshd-*"

logs_path=/var/log

notify_hashs=/tmp/.notify

if [[ ! -d "${notify_hashs}" ]]; then

mkdir -p "${notify_hashs}"

if [[ ! -d "${notify_hashs}" ]]; then

echo "Could NOT create hash storage : ${notify_hashs}"

exit

fi

(

while [ 1 ]; do

## We store the hashs in a flat file. The Hashs are just flags for lines that

## we have already send out notfies about.

hashs="${notify_hashs}/$(date +%Y%m%d).hash"

## Why check if the file exists since touching is harmless... right?

touch "${hashs}"

(

## This sub process watches for newer/deleted versions of the listed files.

## And if any are found it will exit thus forcing the tail process to also

## exit and restart.

found_first=$(find ${logs_path} -maxdepth 1 -mindepth 1 -print0 -iname "${logs_name}")

repeat=1

## Loop forever unless we are told otherwise.

## During each loop we look for any changes in the files we are looking for.

while [ ${repeat} -eq 1 ]; do

sleep 0.5

## Find will return all the found files in one (possibly) LONG string.

## Hashing would shorten the string and reduce memory memory usage abit but

## it would also require launching other process which would use more CPU

## and temporary memory until the hash is made... EVERY loop!

found_current=$(find ${logs_path} -maxdepth 1 -mindepth 1 -print0 -iname "${logs_name}")

if [[ $found_current != $found_first ]]; then

repeat=0

fi

done

) &

## Here is where we tail the files in question, sending erros to /dev/null and only grabbing the lines we care about.

tail --pid="$!" -q -F ${logs_path}/${logs_name} 2>/dev/null | while read line; do

## Skip all lines that start with "==" OR blank

if [[ ! "$line" =~ ^\=\= ]] && [[ ! -z "$line" ]]; then

if [[ "$line" =~ :\ (Accepted|Failed) ]]; then

if [[ ! "$line" =~ ((www.bbhcsd.org|files).*10.80.16.10) ]]; then

hash=$(echo "${line}" | md5sum -)

if [ $(grep -c -m 1 "${hash}" "${hashs}") -eq 0 ]; then

echo $line

echo "${hash}" >> "${hashs}"

/scripts/notify_with_slack.bash -t "${line}"

fi

done

) &

Old Version with grep & tail

logwatcher.sshd.bash

#!/bin/bash

logs_name="*-sshd-*"

logs_path=/var/log

notify_hashs=/tmp/.notify

if [[ ! -d "${notify_hashs}" ]]; then

mkdir -p "${notify_hashs}"

if [[ ! -d "${notify_hashs}" ]]; then

echo "Could NOT create hash storage : ${notify_hashs}"

exit

fi

(

while [ 1 ]; do

hashs="${notify_hashs}/$(date +%Y%m%d).hash"

touch "${hashs}"

(

## This sub process watches for newer versions of the listed files.

## And if any are found it will exit thus forcing the tail process to also exit.

found_first=$(find ${logs_path} -maxdepth 1 -mindepth 1 -print0 -iname "${logs_name}")

repeat=1

while [ ${repeat} -eq 1 ]; do

sleep 0.5

found_current=$(find ${logs_path} -maxdepth 1 -mindepth 1 -print0 -iname "${logs_name}")

if [[ $found_current != $found_first ]]; then

repeat=0

fi

done

) &

tail --pid="$!" -q -F ${logs_path}/${logs_name} 2>/dev/null | grep --line-buffer -E ": (Accepted|Failed)" | grep --line-buffer -v -E "((www.nomadcf.com|files).*10.80.16.10)" | while read line; do

## Skip all lines that start with "==" OR blank

if [[ ! $line =~ ^\=\= ]] && [[ $line ]]; then

hash=$(echo "${line}" | md5sum -)

if [ $(grep -c -m 1 "${hash}" "${hashs}") -eq 0 ]; then

echo "${hash}" >> "${hashs}"

/scripts/notify_with_slack.bash -t "${line}"

fi

done

) &

PHP sockets send auth email (gmail)

posted Sep 10, 2015, 7:16 AM by Chris Franklin

Code

function SendMail($ServerName, $Port, $Username, $Password, $ToEmail, $FromEmail, $Subject, $Body, $Attachments='' ) {

/* Attachments work like this array('Attachment Name'=>'ATTACHMENT AS A STRING','Next Attachment Name'=>'ATTACHMENT 2 AS A STRING') */

$smtp = fsockopen($ServerName, $Port);

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 220;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed Connect";

fputs($smtp, "HELO $ServerName\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 250;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed Helo";

fputs($smtp, "AUTH LOGIN\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 334;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed AUTH";

fputs($smtp, base64_encode($Username)."\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 334;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed Username";

fputs($smtp, base64_encode($Password)."\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 235;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed Password";

fputs($smtp, "MAIL From:<$FromEmail>\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 250;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed MAIL";

fputs($smtp, "RCPT To:<$ToEmail>\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 250;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed RCPT";

fputs($smtp, "DATA\r\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 354;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed DATA";

fputs($smtp, "From: $FromEmail\r\n");

fputs($smtp, "To: $ToEmail\r\n");

if ($Attachments != '') {

$ContentBoundry = '----=_NextPart_'.md5(date('U').uniqid('NCF',TRUE));

fputs($smtp, "MIME-Version: 1.0\r\n");

fputs($smtp, 'Content-Type: multipart/mixed; boundary="'.$ContentBoundry.'"'."\r\n");

}

fputs($smtp, "Subject: $Subject\r\n\r\n");

if ($Attachments == '') {

fputs($smtp, "$Body\r\n.\r\n");

} else {

$NewBody = "\r\n";

$NewBody .= '--'.$ContentBoundry."\r\n";

$NewBody .= 'Content-Type: text/plain; charset="UTF-8"'."\r\n";

$NewBody .= 'Content-Transfer-Encoding: 8bit'."\r\n\r\n";

$NewBody .= $Body."\r\n";

foreach ($Attachments as $Filename => $Attachment) {

$NewBody .= '--'.$ContentBoundry."\r\n";

$NewBody .= 'Content-Type: application/octet-stream; name="'.$Filename.'"'."\r\n";

$NewBody .= 'Content-Transfer-Encoding: base64'."\r\n";

$NewBody .= 'Content-Disposition: attachment; filename="'.$Filename.'"'."\r\n\r\n";

$NewBody .= chunk_split(base64_encode($Attachment));

$NewBody .= "\r\n";

}

$NewBody .= $ContentBoundry."--\r\n\r\n";

fputs($smtp, $NewBody."\r\n.\r\n");

}

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 250;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed BODY";

fputs($smtp, "QUIT\n");

$InputBuffer = fgets($smtp, 1024);

$ErrorCode = 221;

if(substr($InputBuffer,0,3) != "$ErrorCode") return "Failed QUIT";

fclose($smtp);

return TRUE;

}

How to use it

$TheAttachments = array('CurriculumReviewCycle.pdf'=>file_get_contents('/var/www/html/CurriculumReviewCycle.pdf'));

// SendMail($ServerName, $Port, $Username, $Password, $ToEmail, $FromEmail, $Subject, $Body, $Attachments='' )

SendMail('smtp.gmail.com', 25, 'franklinc@fromdomain', 'Password', 'cfranklin@todomain', 'franklinc@fromdomain', 'Attach - Test - subject', 'What up dog! - the body', $TheAttachments);

Bash & Mysql Transactions Example

posted Aug 14, 2015, 7:23 PM by Chris Franklin

Here is a simple example, no loops just some straight statements outputted via echo and piped all at "once" through a single mysql

Code - Simple

#!/bin/bash

(

echo "BEGIN;"

echo "USE database;"

echo "INSERT INTO table (column1,column2) VALUES ('1','bob');"

echo "INSERT INTO table (column1,column2) VALUES ('2','tom');"

echo "INSERT INTO table (column1,column2) VALUES ('3','bill');"

echo "DELETE FROM table WHERE column2='bob' LIMIT 2;"

echo "COMMIT;"

) | mysql

All sql statements are loaded VIA a temporary files

File - loadme.sql

BEGIN;

USE database;

INSERT INTO table (column1,column2) VALUES ('1','bob');

INSERT INTO table (column1,column2) VALUES ('2','tom');

INSERT INTO table (column1,column2) VALUES ('3','bill');

DELETE FROM table WHERE column2='bob' LIMIT 2;

COMMIT;

Code

#!/bin/bash

cat loadme.sql | mysql

Same as above, but with out the translation statements in the sql

File - loadme.sql

INSERT INTO table (column1,column2) VALUES ('1','bob');

INSERT INTO table (column1,column2) VALUES ('2','tom');

INSERT INTO table (column1,column2) VALUES ('3','bill');

DELETE FROM table WHERE column2='bob' LIMIT 2;

Code

#!/bin/bash

(

echo "BEGIN"

cat loadme.sql

echo "COMMIT"

) | mysql

Load some columns from a CSV file

File - loadme.csv

id,first_name,last_name,email,country,ip_address

1,Michelle,George,mgeorge0@ebay.com,China,123.202.75.179

2,Sara,Evans,sevans1@hc360.com,Finland,134.70.196.70

3,Lawrence,Chavez,lchavez2@about.com,China,18.196.142.206

4,Roy,Carpenter,rcarpenter3@walmart.com,Albania,82.181.123.213

5,Shirley,Green,sgreen4@ucoz.ru,Libya,64.253.188.146

6,Adam,Wood,awood5@hhs.gov,Russia,221.26.34.134

7,Wayne,Stephens,wstephens6@nature.com,Russia,56.65.157.158

8,Theresa,Little,tlittle7@dmoz.org,Afghanistan,69.137.112.37

9,Angela,Sanchez,asanchez8@mayoclinic.com,Indonesia,105.61.90.239

10,Michael,Watson,mwatson9@nih.gov,China,252.233.46.123

11,Frank,Gonzalez,fgonzaleza@rambler.ru,Indonesia,93.237.180.19

12,Juan,Allen,jallenb@lycos.com,United States,218.237.224.77

13,Jack,Hall,jhallc@buzzfeed.com,Russia,4.72.91.28

14,Julie,Berry,jberryd@businesswire.com,China,217.110.87.152

15,Joshua,Simpson,jsimpsone@tinyurl.com,China,12.56.150.26

16,Mary,Hudson,mhudsonf@blogtalkradio.com,Tanzania,134.103.90.216

17,Johnny,Cunningham,jcunninghamg@ustream.tv,Malawi,240.198.146.11

18,Keith,Reynolds,kreynoldsh@huffingtonpost.com,Brazil,132.158.192.170

19,Tina,Bryant,tbryanti@so-net.ne.jp,Ukraine,241.208.105.145

20,David,Lawson,dlawsonj@vkontakte.ru,Finland,168.173.240.164

21,Mark,Nelson,mnelsonk@slate.com,China,184.186.226.254

22,Paul,Welch,pwelchl@businesswire.com,Sweden,13.152.202.44

23,Joyce,Jenkins,jjenkinsm@geocities.jp,Indonesia,8.103.77.179

24,Ernest,Morrison,emorrisonn@sakura.ne.jp,China,11.58.115.47

25,Brian,Bryant,bbryanto@imageshack.us,Poland,198.246.131.204

26,Carl,Fox,cfoxp@upenn.edu,Indonesia,1.89.229.49

27,Doris,Murray,dmurrayq@taobao.com,China,239.230.60.255

28,Doris,Bowman,dbowmanr@jiathis.com,Kazakhstan,71.87.138.11

29,Alan,Young,ayoungs@google.co.uk,Portugal,32.163.140.204

30,Evelyn,Montgomery,emontgomeryt@163.com,France,144.2.32.81

31,Alice,Daniels,adanielsu@quantcast.com,Ireland,247.130.69.39

32,Robert,Marshall,rmarshallv@fotki.com,Canada,116.30.129.9

33,Lois,Green,lgreenw@drupal.org,Indonesia,42.74.77.233

34,Arthur,Day,adayx@ucla.edu,North Korea,89.154.95.21

35,Patricia,Cox,pcoxy@ustream.tv,Turkmenistan,105.242.56.235

36,Antonio,Rice,aricez@jigsy.com,France,199.55.140.129

37,Billy,Simpson,bsimpson10@opensource.org,Mexico,209.115.2.51

38,Dorothy,Welch,dwelch11@list-manage.com,Ethiopia,198.175.147.200

39,Paul,Daniels,pdaniels12@ca.gov,Indonesia,227.236.178.238

40,Larry,Peters,lpeters13@vimeo.com,Jordan,226.138.52.216

41,Brandon,Williams,bwilliams14@skype.com,Russia,105.27.15.197

42,Elizabeth,Day,eday15@weebly.com,China,229.25.28.126

43,Ann,Henry,ahenry16@reference.com,China,42.97.38.8

44,Jimmy,Porter,jporter17@icq.com,France,223.26.225.195

45,Victor,Bowman,vbowman18@merriam-webster.com,Philippines,16.246.77.183

46,Brenda,Jackson,bjackson19@blog.com,Indonesia,128.43.154.62

47,Louis,Bryant,lbryant1a@mit.edu,Indonesia,95.22.26.179

48,Janet,Stephens,jstephens1b@usa.gov,Kazakhstan,231.160.86.102

49,Russell,Little,rlittle1c@joomla.org,Sweden,211.97.195.35

50,Keith,Ruiz,kruiz1d@sciencedaily.com,Israel,179.80.105.231

Code

#!/bin/bash

(

echo "BEGIN;"

echo "USE database;"

while IFS=',' read id first_name last_name email country ip_address; do

echo "UPDATE table SET colum2='${email}' WHERE colum1='${id}';"

done < loadme.csv

echo "COMMIT;"

) | mysql

Roll Back if ALL the rows aren't inserted

Code

#!/bin/bash

# using the < stops wc from printing the file name

totalrows=$(wc -l < loadme.csv)

(

echo "BEGIN;"

echo "USE database;"

echo "DELETE FROM table;"

while IFS=',' read id first_name last_name email country ip_address; do

echo "INSERT INTO table (colum1,colum2) VALUES ('${id}','${email}');"

done < loadme.csv

echo "IF ((SELECT COUNT(*) FROM table) = ${totalrows}) THEN"

echo "COMMIT;"

echo "ELSE"

echo "ROLLBACK;"

echo "END IF;"

) | mysql