View difference between Paste ID: f743e7daa and

-
+#!/usr/bin/sh
 #mailstat.sh
 
 #a couple of temp files
 TEMPFILE="/home/peloruso/${RANDOM}${RANDOM}${RANDOM}"
 XSRTEMP="/home/peloruso/${RANDOM}${RANDOM}${RANDOM}"
 
 #set these to your correct information
 SQL_USER=mysqluser
 SQL_PW=password
 SQL_DB=mysqldatabase
 SQL_TABLE=mysqltable
 
 #procmail sends the current message to the program in STDIN, so use tee to put the message in
 #a file so we can use it over and over
 tee ${TEMPFILE}
 
 
 #requires the X-SHA1SUM header to exist.
 # :0w:${PMDIR}/sha1sum.lock
 # SHA1SUM=|/usr/bin/sha1sum
 #
 # :0fw:${PMDIR}/formail.lock
 # | /usr/bin/formail -A "X-SHA1SUM: ${SHA1SUM}"
 SHA1SUM=`/usr/bin/formail -x "X-SHA1SUM:"<${TEMPFILE}`
 SHA1SUM=`echo ${SHA1SUM}|sed 's/^ *//;s/ -$//'`
 
 
 #requires the X-PM-User header to exist.
 # :0fw:${PMDIR}/formail.lock
 # | /usr/bin/formail -A "X-PM-User: ${USER}"
 PMUSER=`/usr/bin/formail -x "X-PM-User:"<${TEMPFILE}`
 PMUSER=`echo ${PMUSER}|sed 's/^ *//'`
 
 
 #in .spamassassin/local.cf, add the following line to create the necessary header
 #add_header all Rules _TESTS(,)_
 XSR=`/usr/bin/formail -x "X-Spam-Rules:"<${TEMPFILE}`
 
 
 #in .spamassassin/local.cf, add the following line to create the necessary header
 #add_header all Score _SCORE_
 SCORE=`/usr/bin/formail -x "X-Spam-Score:"<${TEMPFILE}`
 SCORE=`echo ${SCORE}|sed 's/^ *//'`
 
 
 #requires the X-Orig-Class header to exist.
 # :0fw:${PMDIR}/formail.lock
 # * X-Spam-Level: RRR
 # | /usr/bin/formail -A "X-Orig-Class: SPAM"
 #
 # :0Efw:${PMDIR}/formail.lock
 # | /usr/bin/formail -A "X-Orig-Class: HAM"
 ORIG_CLASS=`/usr/bin/formail -x "X-Orig-Class:"<${TEMPFILE}`
 ORIG_CLASS=`echo ${ORIG_CLASS}|sed 's/^ *//'`
 
 
 #requires the X-Sender-in-whitelist header to exist
 # :0fw:${PMDIR}/formail.lock
 # * ^X-Spam-Report:.*USER_IN_WHITELIST
 # | /usr/bin/formail -A "X-Sender-in-whitelist: Yes"
 #
 # :0Efw:${PMDIR}/formail.lock
 # | /usr/bin/formail -A "X-Sender-in-whitelist: No"
 SENDER_IN_WHITELIST=`/usr/bin/formail -x "X-Sender-in-whitelist:"<${TEMPFILE}`
 SENDER_IN_WHITELIST=`echo ${SENDER_IN_WHITELIST}|sed 's/^ *//'`
 
 
 #requires the X-Sender-in-blacklist header to exist
 # :0fw:${PMDIR}/formail.lock
 # * ^X-Spam-Report:.*USER_IN_BLACKLIST
 # | /usr/bin/formail -A "X-Sender-in-blacklist: Yes"
 #
 # :0Efw:${PMDIR}/formail.lock
 # | /usr/bin/formail -A "X-Sender-in-blacklist: No"
 SENDER_IN_BLACKLIST=`/usr/bin/formail -x "X-Sender-in-blacklist:"<${TEMPFILE}`
 SENDER_IN_BLACKLIST=`echo ${SENDER_IN_BLACKLIST}|sed 's/^ *//'`
 
 
 #pipe the X-Spam-Rules header through 'tr' and 'sed' to format correctly.
 #specifically, change all of the commas to newlines using tr (put one rule on each line)
 #then make sure there are no blank lines using sed
 /usr/bin/formail -x "X-Spam-Rules" <${TEMPFILE}|  tr ',' '\n' |sed '/^$/d'>${XSRTEMP}
 
 
 #used to determine if the mail is one of my approved "commercial" emails, or for a mailing list
 #for example,
 # :0fw:${PMDIR}/formail.lock
 # | /usr/bin/formail -A "X-folder-delivered-to: ${DEFAULT}Lists/cur"
 #
 #There are only two users on this system (me and my wife).  If there were a lot of users, this
 #would not be a good way to go about doing this.
 DEST=`/usr/bin/formail -x "X-folder-delivered-to:"<${TEMPFILE}`
 DEST=`echo ${DEST}|sed 's/^ *//'`
 DEST=`echo ${DEST}|sed 's/\/home\/peloruso\/mail\/pelorus.org\/skip\/\.//'`
 DEST=`echo ${DEST}|sed 's/\/home\/peloruso\/mail\/pelorus.org\/suzanne\/\.//'`
 DEST=`echo ${DEST}|sed 's/\/cur.*//'`
 
 #count the number of rules that were hit
 RULECOUNT=`wc -l ${XSRTEMP}|grep [0-9]* -o`
 
 
 #add the columns to the database.  I "try" to add a column for every rule that was hit.  Of course,
 #if the column already exists, it will not recreate the column, so it generates an error in the
 #procmail log.  Also build "Q2", which is similar to the ALTER statement, except that it will be
 #used in the INSERT statement.  Note the XSRTEMP redirection after the 'done' statement on the
 #last line.  So, each time through the do loop, this will try to add a new column with the name
 #of the rule.  It will also build Q2 which is used in the QUERY statement a little later.
 #Q2 will look something like this (starting with the first comma):
 # , 'BAYES_99', 'AWL', 'HTML_MESSAGE'
 while read rules
 do
    mysql -u ${SQL_USER} --password=${SQL_PW} -e"ALTER TABLE \`$SQL_DB\`.\`$SQL_TABLE\` ADD $rules BOOL NOT NULL DEFAULT 0"
    Q2="$Q2, \`$rules\`"
 done <${XSRTEMP}
 
 
 #Now build a string that will basically have a ,'1' for each rule that was hit.  The logic here
 #is that if the rule was listed here, it was hit, so the value will be '1'.  Since I don't
 #update the values of *ALL* columns--just the ones that were actually hit, they all get a '1'
 #So, like Q2 above, this will build a string for the QUERY statement below that will look like
 #this (starting with the first comma)
 # , '1', '1'
 for (( i=0; i<$RULECOUNT; i++))
 do
   RULESTRING="${RULESTRING}, '1'"
 done
 
 #Calculate the values of WHITELISTED and BLACKLISTED
 if [ "$SENDER_IN_WHITELIST" = Yes ]
 then
   WHITELISTED=1
 else
   WHITELISTED=0
 fi
 
 if [ "$SENDER_IN_BLACKLIST" = Yes ]
 then
   BLACKLISTED=1
 else
   BLACKLISTED=0
 fi
 
 
 #determine if the message was an approved commercial email, or from a mailing list
 #If there are more than two, a case statement would be more appropriate
 if [ "$DEST" = Lists ]
 then
   MAILING_LIST=1
 else
   MAILING_LIST=0
 fi
 
 if [ "$DEST" = Commercial ]
 then
   COMMERCIAL=1
 else
   COMMERCIAL=0
 fi
 
 
 #Now I have all of the variables.  I can build my QUERY.
 #A typical query will look like this:
 # INSERT INTO `peloruso_mailstats`.`messages` (`sha1sum`, `username`, `orig_class`, `score`,
 # `mail_list`, `commercial`, `whitelist`, `blacklist`, `AWL`, `BAYES_20`, `NORMAL_HTTP_TO_IP`,
 # `ONLINE_PHARMACY`, `SPF_PASS`, `TVD_VISIT_PHARMA`, `URIBL_JP_SURBL`, `URIBL_OB_SURBL`, `URICOUNTRY_DE`)
 # VALUES ('f3e4406dab1313c9586bcfcf01ec8332582a071a', 'skip', 'SPAM', '10.0', '0', '0', '0' , '0' ,
 # '1', '1', '1', '1', '1', '1', '1', '1', '1')
 QUERY="INSERT INTO \`${SQL_DB}\`.\`${SQL_TABLE}\` (\`sha1sum\`, \`username\`, \`orig_class\`, \`score\`, \`mail_list\`, \`commercial\`, \`whitelist\`, \`blacklist\`$Q2) \
   VALUES ('${SHA1SUM}', '${PMUSER}', '${ORIG_CLASS}', '${SCORE}', '${MAILING_LIST}', '${COMMERCIAL}', '${WHITELISTED}' , '${BLACKLISTED}' ${RULESTRING})"
 
 #I do save a copy of the query for debugging
 echo "QUERY=${QUERY}">>/home/peloruso/mailstat.log
 
 #execute the query
 mysql -u ${SQL_USER} --password=${SQL_PW} -e "${QUERY}"
 
 #delete the temp files
 rm -f ${TEMPFILE} ${XSRTEMP}