powered by Jive Software

Disable spam accounts and delete spam

psql -h 127.0.0.1 -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:

#!/bin/sh
# works with 4.5.5.3
# 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"
START_MSG=0
NR_MSGS=50 DATE=`date +%Y.%m.%d-%H:%M:%S`
URL='http://'${DOMAIN}'/recent-updates!moreContentResults.jspa?containerID=1&containerType=14&recursive=false&visibleTypes=102-1-18-800-38&filterEnabled=true&start='${START_MSG}'&numResults='${NR_MSGS}'&containerSize=LARGE&root=true&showProjectContent=false'
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`
do
  #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}" != "" ]
  then
    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
  fi
done
cat ${TMP_DIR}/${DATE}.txt

% Information related to '91.224.160.0 - 91.224.161.255'
inetnum:        91.224.160.0 - 91.224.161.255
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?