Disable spam accounts and delete spam

psql -h -p 5432 -U jive
select count(*) from jiveuser where email like '%@s-e-arch.com';
select * from jiveuser where email like '%@s-e-arch.com';
UPDATE jiveuser SET userenabled=0 WHERE email like '%@s-e-arch.com';
select username, userenabled from jiveuser where email like '%@s-e-arch.com'; # Get all hotmail.com accounts which are enabled
select username,email from jiveuser where email like '%@hotmail.com' and userenabled=1; # Get the IP address for a specific message
select * from jivemessageprop where messageid=123;

Getting and disabling the usernames is one thing, one needs to delete also the messages. For s-e-arch.com one can safely delete the users as they use a domain to register users, so they have unlimited email addresses.

I may setup a script which runs daily to create a list of all new messages with external links. It currently produces ugly output, but this seems to be good enough:

[href-link-to-msg - all links]

161252 - a class=“jive-link-external-small” href=“http://rox-xmlrpc.sourceforge.net/niotut/index.html” target="_blank">http://rox-xmlrpc.sourceforge.net/niotut/index.html a class=“jive-link-external-small” href=“http://mailman.jabber.org/pipermail/jdev/2005-February/020106.html” target="_blank">http://mailman.jabber.org/pipermail/jdev/2005-February/020106.html

177845 - a class=“jive-link-external-small” href=“http://en.wikipedia.org/wiki/WYSIWYG”>WYSIWYG a class=“jive-link-external-small” href=“http://martin-weusten.de/sprite/”>cool things with JavaScript a class=“jive-link-external-small” href=“http://www.creativyst.com/Prod/3/” target="_blank">http://www.creativyst.com/Prod/3/

current script version:

# works with
# Logout, locate the "recent content" widget and press there the refresh button.
# Use "Firebug", "Tamper Data" or "Fiddler2" to log the request.
# Modify URL and POST_DATA DOMAIN="community.igniterealtime.org"
NR_MSGS=50 DATE=`date +%Y.%m.%d-%H:%M:%S`
TMP_DIR="/tmp/spam" rm -rf "${TMP_DIR}"
mkdir "${TMP_DIR}"
cd "${TMP_DIR}"
wget -O render-widget.html "${URL}"
wget -i render-widget.html -F -p --base="http://${DOMAIN}"
cd "${TMP_DIR}/${DOMAIN}/message"
for f in `ls`
  #echo $f
  EXT_URLS=`cat $f | tr '<' '\n' | egrep "https?://" | egrep -v "http://(issues|community|www|svn|fisheye).igniterealtime.org[\"/]|http://www.w3.org/|http://www.jivesoftware.com[\?/]|http://www.google-analytics.com/|http://(xmpp|jabber).org/|http://jetty.mortbay.org/|http://[a-z]+[:/]|http://etherx.jabber.org/streams|http://[^\.]+[:/]" `
#echo "${EXT_URLS}"
  if [ "${EXT_URLS}" != "" ]
    EXT_URLS=`echo ${EXT_URLS} | sed 's#<#\&lt\;#g;s#>#\&gt\;#g;'`
         echo "<a href='http://${DOMAIN}/message/$f'>$f</a>${EXT_URLS}<hr/>" >>${TMP_DIR}/${DATE}.txt
cat ${TMP_DIR}/${DATE}.txt

% Information related to ' -'
inetnum: -
netname:        Bergdorf-network
descr:          Bergdorf Group Ltd.
country:        NL

address: 3A Little Denmark Complex, 147 Main Street, PO Box 4473, Road Town, Torola, British Virgin Islands VG1110

person: Agnes Jouaneau
address: A Little Denmark Complex, 147 Main Street, PO Box 4473
address: Road Town, Torola, VG1110
address: British Virgin Islands
phone: +44 20 81333030
fax-no: +44 20 81333030
abuse-mailbox: *****@bergdorf-group.com

Is it possible to see from what IP some accounts have been registered?