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.
  1. Look for all STATUS.ok files found in the directory structure that is using the default ghettovcb setup (aka VM_Hostname/VM_Hostname-DATE ).
  2. Copy over all files in that directory to a corresponding location on the "Backup VM Host"
  3. Edit the Display names of all backups to read " Date - VM Hostname "  
  4. Copy the vmdk using vmkfstools to keep or covert the backup to a thin provisioned version
  5. verify and log everything along the way
  6. only keep X copies on the backup VM HOST
  7. Auto removes Moved copied from NFS host
  8. 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
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
     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
         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
          fi
         fi

        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
          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
          fi

         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
          
         fi
        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
        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 
      fi 
     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
   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
    fi
   else
    echo "Skipping : ${vmversion}"
   fi
  fi

 done

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
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
    fi
   fi
  fi
 done

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
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
  fi
 done

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





1-6 of 6