Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/bin/bash
DT=`date +"%d%m%y-%H%M%S"`
####################################################
# Copyright (C) 2012
# Program: vbmailqueue.sh
# by George Liu (eva2000) vbtechsupport.com
# Updated: June 3rd, 2012 AEST
SCRIPTNAME='vbmailqueue.sh'
SCRIPTURL='vbtechsupport.com'
SCRIPTAUTHOR='George Liu (eva2000)'
VER='0.0.3'
####################################################
# bash script to see how large vB 4.2.x new cron based mail queue is
# https://www.vbulletin.com/forum/content.php/775-What-is-Cron-Based-Email-Sending
# and provide some stats to better manage settings at
# AdminCP -> vBulletin Options -> Email Options
# for
# Use Cron Based Sending = Yes
# Number of Emails to Send Per Batch =
# Email Send Delay =
####################################################
# [ How to use script ]
# There 5 primary config variables to edit:
# 1. vB database name
# 2. vB table prefix if you set one in config.php
# 3. MySQL username
# 4. MySQL password
# 5. MySQL host leave at localhost if MySQL on same server other wise need to set
# remote IP to your MySQL remote server
####################################################
# Primary Configuration Variables
DBNAME='vb420'
TABLEPREFIX=''
USER='root'
PASS=''
MYSQLHOST='localhost'
####################################################
# Optional Configuration Variables
LOGDIR='/root/vbmailqueuelogs'
VBMAILIDSTATSFILE="${LOGDIR}/mailqueue_$DT.log"
####################################################
MAILQUEUETABLE="${TABLEPREFIX}mailqueue"
if [ -z $PASS ]; then
MYSQLADMINOPT=''
else
MYSQLADMINOPT="-u $USER -p$PASS -h $MYSQLHOST"
fi
if [ ! -d "$LOGDIR" ]; then
mkdir $LOGDIR
fi
#if [ ! -f "$VBMAILIDSTATSFILE" ]; then
#touch $VBMAILIDSTATSFILE
#fi
####################################################
byline() {
echo "------------------------------------------------------------------"
echo "$SCRIPTNAME $VER - $SCRIPTAUTHOR $SCRIPTURL"
}
listsaved() {
echo -e "Stats Output saved at:\n ${LOGDIR}/$(ls -rt ${LOGDIR} | grep $DT)"
echo "------------------------------------------------------------------"
ls -lhrt ${LOGDIR}
echo "------------------------------------------------------------------"
}
viewmailqueue() {
byline | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo "As at: `date`" | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
mysql $MYSQLADMINOPT -t -e "select mailqueueid, dateline from $MAILQUEUETABLE order by mailqueueid ASC;" $DBNAME | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
listsaved | tee -a $VBMAILIDSTATSFILE
}
countqueue() {
byline | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo "As at: `date`" | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
#echo "vBulletin Mail Queue IDs currently at: " | tee -a $VBMAILIDSTATSFILE
MQIDQ=$(mysql $MYSQLADMINOPT -N -t -e "select mailqueueid, dateline from $MAILQUEUETABLE order by mailqueueid ASC;" $DBNAME | grep -Ev '+--' | awk '{print $2}')
declare -a arrayc
arrayc=(${MQIDQ})
echo "First Mail Queue ID: ${arrayc[0]}" | tee -a $VBMAILIDSTATSFILE
echo -n "Last Mail Queue ID: " | tee -a $VBMAILIDSTATSFILE
echo $MQIDQ | awk '{ print $NF }' | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo -n "vBulletin Mail Queue row size is: " | tee -a $VBMAILIDSTATSFILE
COUNTQUEUE=${#arrayc[@]}
echo "$COUNTQUEUE" | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
echo "vBulletin email settings are set as follows:"
mysql $MYSQLADMINOPT -t -e "SELECT varname, grouptitle, value, defaultvalue FROM ${TABLEPREFIX}setting WHERE grouptitle LIKE 'email';" $DBNAME | tee -a $VBMAILIDSTATSFILE
echo "------------------------------------------------------------------" | tee -a $VBMAILIDSTATSFILE
listsaved | tee -a $VBMAILIDSTATSFILE
}
debug() {
MQIDTEST=$(mysql $MYSQLADMINOPT -N -t -e "select mailqueueid, dateline from $MAILQUEUETABLE order by mailqueueid ASC;" $DBNAME | grep -Ev '+--' | awk '{print $2}')
declare -a array
array=(${MQIDTEST})
echo "${#array[@]}"
echo $MQIDTEST | awk '{ print $NF }'
}
####################################################
case "$1" in
view)
viewmailqueue
;;
count)
countqueue
;;
debug)
debug
;;
*)
echo "$0 view"
echo "$0 count"
echo "$0 debug"
;;
esac
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.