View difference between Paste ID: f743e7daa and
SHOW:
|
|
- or go back to the newest paste.
1 | - | |
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} |