# Need some help hacking spamdb.sh script



## johnblue (Oct 30, 2010)

Here is a shell script from calomel.org for generating stats about spamd:

(I changed the location of the logfile from /var/log/daemon to /var/log/spamd)


```
#!/bin/sh
#
## Calomel.org .:. spamdb.sh
#

if [ $# -eq 0 ]
   then
echo ""
echo "  Calomel.orgspamdb.sh $arg "
echo "--------------------------------------"
echo "show= all whitelisted ips with hostnames"
echo "stats   = ips in all tables"
echo ""
   exit
 fi

if [ $1 = "show" ]
   then
echo " "
 for i in `spamdb | grep WHITE | awk -F "|" '{print $2}' | sort `;
  do
   echo -n $i; echo -n -e "\t"; host $i | awk '{print $5}'
  done
echo " "
   exit
 fi

if [ $1 = "stats" ]
   then
 echo "Calomel.org .:. spamdb.sh stats"
 echo " "
 echo "Spamd incoming smtp connections"
 echo -n "  grey list attempts = ";cat /var/log/spamd | grep disconnected | grep -vc spamd-greytrap
 echo -n "  spamd-greytrap tar pits= ";cat /var/log/spamd | grep disconnected | grep -c spamd-greytrap
 echo -n "  total connections to spamd = ";cat /var/log/spamd | grep -c disconnected

 echo " "
 echo "Spamd statistics"
 echo -n "  hours spammers have wasted = ";cat /var/log/spamd | grep disconnected | awk '{s += $9} END {print s/3600}'

 echo " "
 echo "Spamdb database classes"
 echo -n "  SPAMTRAP trigger addresses = ";spamdb | grep -c ^SPAMTRAP
 echo -n "  TRAPPED black listed ips   = ";spamdb | grep -c ^TRAPPED
 echo -n "  WHITE listed and clear ips = ";spamdb | grep -c ^WHITE

 echo " "
 echo "Top 10 To: email addresses"
 cat /var/log/spamd | grep " spamd" | grep ": (" | awk '{print $10}' | sort | uniq -c | sort -r | head -10 | tr -d \<\>

 echo " "
 echo "Top 10 remote mail server ips"
 cat /var/log/spamd | grep disconnected | awk '{print $6}' | sort | uniq -c | sort -r | head -10 | tr -d \:
 echo " "
   exit
 fi
```

Going with './spamdb.sh show', ya get stuff like this:


```
109.177.124.33-e \t2(SERVFAIL)
109.177.124.4-e \t2(SERVFAIL)
109.177.124.56-e \t2(SERVFAIL)
109.177.124.68-e \t2(SERVFAIL)
109.177.124.71-e \t2(SERVFAIL)
109.177.127.252-e \t2(SERVFAIL)
109.177.158.100-e \tquality-atm.jlkk231believableo.com.
109.177.158.126-e \tborder.bhtq222oroadtrip.com.
109.177.158.134-e \tgeneration-multicast.fthno223majority.com.
109.177.158.19-e \tomnitech-destservice2.bhtq222oroadtrip.com.
109.177.158.225-e \taviation-accesspoint.hngeo131intouch.com.
```

So that is cool and all, but it is kind of messy since FreeBSD's echo does not know what to do with the '-e' switch and '\t' option.

Switching 
	
	



```
echo -n $i; echo -n -e "\t"; host $i | awk '{print $5}'
```
to

```
echo -n $i; echo -n " | " ; host $i | awk '{print $5}'
```
now outputs:
	
	



```
109.177.124.71 | 2(SERVFAIL)
109.177.127.252 | 2(SERVFAIL)
109.177.158.100 | quality-atm.jlkk231believableo.com.
109.177.158.126 | border.bhtq222oroadtrip.com.
109.177.158.134 | generation-multicast.fthno223majority.com.
109.177.158.19 | omnitech-destservice2.bhtq222oroadtrip.com.
109.177.158.225 | aviation-accesspoint.hngeo131intouch.com.
```
However, (and the reason for this post) the output of spamdb for WHITE-listed hosts looks like this:
	
	



```
WHITE|212.131.235.184|||1287010856|1287013985|1291446028|16|24
```
The last field denotes how many times spamd allowed the mail server traffic to pass to the real MTA.  I would like to have that value included in the final output but I don't know how to make it survive into the code where host is called to resolve IP addresses and stay all on one line.

Doing this just gives me garbage:
	
	



```
spamdb | grep WHITE | awk -F "|" '{print $2,$9}' | sort
```


```
109.177.100.38 | 2(SERVFAIL)
1 | 3(NXDOMAIN)
109.177.101.218 | 2(SERVFAIL)
0 | 3(NXDOMAIN)
109.177.101.69 | 2(SERVFAIL)
0 | 3(NXDOMAIN)
109.177.102.133 | 2(SERVFAIL)
0 | 3(NXDOMAIN)
109.177.102.183 | 2(SERVFAIL)
0 | 3(NXDOMAIN)
109.177.103.111 | 2(SERVFAIL)
0 | 3(NXDOMAIN)
```

Ideally, what I would like to see is:
	
	



```
109.177.158.225 | aviation-accesspoint.hngeo131intouch.com. | 87
```
I want to be able to maintain a static and unique whitelist based upon top talkers for DR purposes, as opposed to just exporting the whitelist.

Thanks in advance.


----------



## jalla (Oct 31, 2010)

> So that is cool and all, but it is kind of messy since FreeBSD's echo does not know what to do with the '-e' switch and '\t' option.



The e and n options don't work in combination. You could rewrite your loop to something like this

```
do
  l="\t`host $i | awk '{print $5}`"
  echo -e ${i}${l}
done
```


----------



## graudeejs (Oct 31, 2010)

You can use printf instead of echo, it understands \t.


----------

