Want more features on Pastebin? Sign Up, it's FREE!
Guest

Skip

By: a guest on Aug 2nd, 2008  |  syntax: None  |  size: 6.46 KB  |  views: 41  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
This paste has a previous version, view the difference. Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. #!/usr/bin/sh
  2. #mailstat.sh
  3.  
  4. #a couple of temp files
  5. TEMPFILE="/home/peloruso/${RANDOM}${RANDOM}${RANDOM}"
  6. XSRTEMP="/home/peloruso/${RANDOM}${RANDOM}${RANDOM}"
  7.  
  8. #set these to your correct information
  9. SQL_USER=mysqluser
  10. SQL_PW=password
  11. SQL_DB=mysqldatabase
  12. SQL_TABLE=mysqltable
  13.  
  14. #procmail sends the current message to the program in STDIN, so use tee to put the message in
  15. #a file so we can use it over and over
  16. tee ${TEMPFILE}
  17.  
  18.  
  19. #requires the X-SHA1SUM header to exist.
  20. # :0w:${PMDIR}/sha1sum.lock
  21. # SHA1SUM=|/usr/bin/sha1sum
  22. #
  23. # :0fw:${PMDIR}/formail.lock
  24. # | /usr/bin/formail -A "X-SHA1SUM: ${SHA1SUM}"
  25. SHA1SUM=`/usr/bin/formail -x "X-SHA1SUM:"<${TEMPFILE}`
  26. SHA1SUM=`echo ${SHA1SUM}|sed 's/^ *//;s/ -$//'`
  27.  
  28.  
  29. #requires the X-PM-User header to exist.
  30. # :0fw:${PMDIR}/formail.lock
  31. # | /usr/bin/formail -A "X-PM-User: ${USER}"
  32. PMUSER=`/usr/bin/formail -x "X-PM-User:"<${TEMPFILE}`
  33. PMUSER=`echo ${PMUSER}|sed 's/^ *//'`
  34.  
  35.  
  36. #in .spamassassin/local.cf, add the following line to create the necessary header
  37. #add_header all Rules _TESTS(,)_
  38. XSR=`/usr/bin/formail -x "X-Spam-Rules:"<${TEMPFILE}`
  39.  
  40.  
  41. #in .spamassassin/local.cf, add the following line to create the necessary header
  42. #add_header all Score _SCORE_
  43. SCORE=`/usr/bin/formail -x "X-Spam-Score:"<${TEMPFILE}`
  44. SCORE=`echo ${SCORE}|sed 's/^ *//'`
  45.  
  46.  
  47. #requires the X-Orig-Class header to exist.
  48. # :0fw:${PMDIR}/formail.lock
  49. # * X-Spam-Level: RRR
  50. # | /usr/bin/formail -A "X-Orig-Class: SPAM"
  51. #
  52. # :0Efw:${PMDIR}/formail.lock
  53. # | /usr/bin/formail -A "X-Orig-Class: HAM"
  54. ORIG_CLASS=`/usr/bin/formail -x "X-Orig-Class:"<${TEMPFILE}`
  55. ORIG_CLASS=`echo ${ORIG_CLASS}|sed 's/^ *//'`
  56.  
  57.  
  58. #requires the X-Sender-in-whitelist header to exist
  59. # :0fw:${PMDIR}/formail.lock
  60. # * ^X-Spam-Report:.*USER_IN_WHITELIST
  61. # | /usr/bin/formail -A "X-Sender-in-whitelist: Yes"
  62. #
  63. # :0Efw:${PMDIR}/formail.lock
  64. # | /usr/bin/formail -A "X-Sender-in-whitelist: No"
  65. SENDER_IN_WHITELIST=`/usr/bin/formail -x "X-Sender-in-whitelist:"<${TEMPFILE}`
  66. SENDER_IN_WHITELIST=`echo ${SENDER_IN_WHITELIST}|sed 's/^ *//'`
  67.  
  68.  
  69. #requires the X-Sender-in-blacklist header to exist
  70. # :0fw:${PMDIR}/formail.lock
  71. # * ^X-Spam-Report:.*USER_IN_BLACKLIST
  72. # | /usr/bin/formail -A "X-Sender-in-blacklist: Yes"
  73. #
  74. # :0Efw:${PMDIR}/formail.lock
  75. # | /usr/bin/formail -A "X-Sender-in-blacklist: No"
  76. SENDER_IN_BLACKLIST=`/usr/bin/formail -x "X-Sender-in-blacklist:"<${TEMPFILE}`
  77. SENDER_IN_BLACKLIST=`echo ${SENDER_IN_BLACKLIST}|sed 's/^ *//'`
  78.  
  79.  
  80. #pipe the X-Spam-Rules header through 'tr' and 'sed' to format correctly.
  81. #specifically, change all of the commas to newlines using tr (put one rule on each line)
  82. #then make sure there are no blank lines using sed
  83. /usr/bin/formail -x "X-Spam-Rules" <${TEMPFILE}|  tr ',' '\n' |sed '/^$/d'>${XSRTEMP}
  84.  
  85.  
  86. #used to determine if the mail is one of my approved "commercial" emails, or for a mailing list
  87. #for example,
  88. # :0fw:${PMDIR}/formail.lock
  89. # | /usr/bin/formail -A "X-folder-delivered-to: ${DEFAULT}Lists/cur"
  90. #
  91. #There are only two users on this system (me and my wife).  If there were a lot of users, this
  92. #would not be a good way to go about doing this.
  93. DEST=`/usr/bin/formail -x "X-folder-delivered-to:"<${TEMPFILE}`
  94. DEST=`echo ${DEST}|sed 's/^ *//'`
  95. DEST=`echo ${DEST}|sed 's/\/home\/peloruso\/mail\/pelorus.org\/skip\/\.//'`
  96. DEST=`echo ${DEST}|sed 's/\/home\/peloruso\/mail\/pelorus.org\/suzanne\/\.//'`
  97. DEST=`echo ${DEST}|sed 's/\/cur.*//'`
  98.  
  99. #count the number of rules that were hit
  100. RULECOUNT=`wc -l ${XSRTEMP}|grep [0-9]* -o`
  101.  
  102.  
  103. #add the columns to the database.  I "try" to add a column for every rule that was hit.  Of course,
  104. #if the column already exists, it will not recreate the column, so it generates an error in the
  105. #procmail log.  Also build "Q2", which is similar to the ALTER statement, except that it will be
  106. #used in the INSERT statement.  Note the XSRTEMP redirection after the 'done' statement on the
  107. #last line.  So, each time through the do loop, this will try to add a new column with the name
  108. #of the rule.  It will also build Q2 which is used in the QUERY statement a little later.
  109. #Q2 will look something like this (starting with the first comma):
  110. # , 'BAYES_99', 'AWL', 'HTML_MESSAGE'
  111. while read rules
  112. do
  113.    mysql -u ${SQL_USER} --password=${SQL_PW} -e"ALTER TABLE \`$SQL_DB\`.\`$SQL_TABLE\` ADD $rules BOOL NOT NULL DEFAULT 0"
  114.    Q2="$Q2, \`$rules\`"
  115. done <${XSRTEMP}
  116.  
  117.  
  118. #Now build a string that will basically have a ,'1' for each rule that was hit.  The logic here
  119. #is that if the rule was listed here, it was hit, so the value will be '1'.  Since I don't
  120. #update the values of *ALL* columns--just the ones that were actually hit, they all get a '1'
  121. #So, like Q2 above, this will build a string for the QUERY statement below that will look like
  122. #this (starting with the first comma)
  123. # , '1', '1'
  124. for (( i=0; i<$RULECOUNT; i++))
  125. do
  126.   RULESTRING="${RULESTRING}, '1'"
  127. done
  128.  
  129. #Calculate the values of WHITELISTED and BLACKLISTED
  130. if [ "$SENDER_IN_WHITELIST" = Yes ]
  131. then
  132.   WHITELISTED=1
  133. else
  134.   WHITELISTED=0
  135. fi
  136.  
  137. if [ "$SENDER_IN_BLACKLIST" = Yes ]
  138. then
  139.   BLACKLISTED=1
  140. else
  141.   BLACKLISTED=0
  142. fi
  143.  
  144.  
  145. #determine if the message was an approved commercial email, or from a mailing list
  146. #If there are more than two, a case statement would be more appropriate
  147. if [ "$DEST" = Lists ]
  148. then
  149.   MAILING_LIST=1
  150. else
  151.   MAILING_LIST=0
  152. fi
  153.  
  154. if [ "$DEST" = Commercial ]
  155. then
  156.   COMMERCIAL=1
  157. else
  158.   COMMERCIAL=0
  159. fi
  160.  
  161.  
  162. #Now I have all of the variables.  I can build my QUERY.
  163. #A typical query will look like this:
  164. # INSERT INTO `peloruso_mailstats`.`messages` (`sha1sum`, `username`, `orig_class`, `score`,
  165. # `mail_list`, `commercial`, `whitelist`, `blacklist`, `AWL`, `BAYES_20`, `NORMAL_HTTP_TO_IP`,
  166. # `ONLINE_PHARMACY`, `SPF_PASS`, `TVD_VISIT_PHARMA`, `URIBL_JP_SURBL`, `URIBL_OB_SURBL`, `URICOUNTRY_DE`)
  167. # VALUES ('f3e4406dab1313c9586bcfcf01ec8332582a071a', 'skip', 'SPAM', '10.0', '0', '0', '0' , '0' ,
  168. # '1', '1', '1', '1', '1', '1', '1', '1', '1')
  169. QUERY="INSERT INTO \`${SQL_DB}\`.\`${SQL_TABLE}\` (\`sha1sum\`, \`username\`, \`orig_class\`, \`score\`, \`mail_list\`, \`commercial\`, \`whitelist\`, \`blacklist\`$Q2) \
  170.   VALUES ('${SHA1SUM}', '${PMUSER}', '${ORIG_CLASS}', '${SCORE}', '${MAILING_LIST}', '${COMMERCIAL}', '${WHITELISTED}' , '${BLACKLISTED}' ${RULESTRING})"
  171.  
  172. #I do save a copy of the query for debugging
  173. echo "QUERY=${QUERY}">>/home/peloruso/mailstat.log
  174.  
  175. #execute the query
  176. mysql -u ${SQL_USER} --password=${SQL_PW} -e "${QUERY}"
  177.  
  178. #delete the temp files
  179. rm -f ${TEMPFILE} ${XSRTEMP}
clone this paste RAW Paste Data