makeshift abusereport generator script

+ add temporary abusereport generator script

2 files changed, 103 insertions, 1 deletions

diff --git a/.gitignore b/.gitignore
index d99bddd..c17170a 100755
--- a/.gitignore
+++ b/.gitignore
@@ -129,5 +129,5 @@ pip-selfcheck.json
 # do not index databases or logfiles
 *.log
 *.db
-abuse*
+abuse-*
 spam*
diff --git a/abusereport-domain.sh b/abusereport-domain.sh
new file mode 100755
index 0000000..91590db
--- /dev/null
+++ b/abusereport-domain.sh
@@ -0,0 +1,102 @@
+#!/bin/sh
+#
+# This script will generate an abuse report based on data stored in spam.db
+# for a supplied XMPP domain.
+#
+# The report will contain:
+#
+#  - the abuse contact for the server IP
+#  - a form letter with evidence
+#  - an attachment with the bot JIDs
+#  - an attachment with the top 10 most often message body values
+
+domain=$1
+
+[ -z "$domain" ] && {
+	sqlite3 -column -header spam.db "SELECT COUNT(*) AS messages,COUNT(DISTINCT user) AS bots,domain AS 'domain' FROM spam GROUP BY domain ORDER BY 1 DESC LIMIT 10"
+	return
+}
+
+date=$(date +%F)
+
+srv=$( ( dig +short SRV "_xmpp-client._tcp.$domain" | grep . || echo "0 0 5222 $domain" ) | sort -n | sed -e 's/[[:digit:]]\+[[:space:]]\+//g' -e 's/\.$//')
+ips=$domain
+if [ "$srv" ] ; then
+	# resolve the XMPP server, filter out host names (CNAMEs),
+	# aggregate into one line
+	ips=$(dig +short $srv | grep -v '\.$' | tr '\n' ' ')
+
+	for ip in $ips ; do
+		whois=$(whois -b $ip | grep -v '^%'|grep -v '^$')
+		abuse=$(echo "$whois"|awk '/^abuse-mailbox:/ {print $2}')
+	done
+fi
+
+SUBJECT="XMPP spam report for $domain / $ips"
+SUMMARY=$(sqlite3 -column -header spam.db "SELECT COUNT(*) AS messages,COUNT(DISTINCT user) AS bots,domain FROM spam WHERE domain='$domain'")
+
+
+cat <<EOF
+$whois
+
+Subject: $SUBJECT
+
+XMPP domain: $domain
+Server:      $srv
+Jabber IP:   $ips
+
+$SUMMARY
+
+EOF
+
+(
+cat <<EOF
+XMPP domain: $domain
+Server:      $srv
+Jabber IP:   $ips
+
+Hi,
+
+the above mentioned server is used as an open relay to send vast amounts
+of XMPP spam to different unrelated servers, such as the server I
+administer.
+
+Spammers are using the In-Band-Registration feature on that server to
+create a large number of accounts, and to send mass messages to my
+users.
+
+Please contact the server owner to disable In-Band-Registration, to take
+measures against spam relaying or to shut down the XMPP service.
+
+Also please find attached a list of the bot accounts and an excerpt of
+the spam messages sent to my service.
+
+$SUMMARY
+
+
+Kind regards,
+
+$NAME
+
+EOF
+) > abuse-$date-$domain.txt
+
+LOGS=abuse-$date-$domain-logs.txt
+JIDS=abuse-$date-$domain-JIDs.txt
+
+sqlite3 spam.db "SELECT char(10)||MIN(ts)||' - '||MAX(ts)||char(10)||COUNT(*)||' messages:'||char(10)||'========================================================================'||char(10)||message||char(10)||'========================================================================' FROM spam WHERE domain='$domain' GROUP BY message ORDER BY COUNT(*) DESC LIMIT 10" > $LOGS
+
+# first / last record
+echo "first seen:" $(sqlite3 spam.db "SELECT ts FROM spam WHERE domain='$domain' ORDER BY ts LIMIT 1")
+echo "last seen:" $(sqlite3 spam.db "SELECT ts FROM spam WHERE domain='$domain' ORDER BY ts DESC LIMIT 1")
+
+# without number of messages
+sqlite3 spam.db "SELECT user || '@' || domain as jid FROM spam WHERE domain='$domain' GROUP BY user ORDER BY 1" > $JIDS
+# with number of messages
+#sqlite3 spam.sqlite "SELECT COUNT(*),user || '@' || domain as jid FROM spam WHERE domain='$domain' GROUP BY user ORDER BY 2"
+
+echo $LOGS
+echo $JIDS
+#cat abuse-$date-$domain.txt
+
+#echo mutt $abuse -i abuse-$date-$domain.txt -s \"$SUBJECT\" -a $LOGS -a $JIDS