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#<#\<\;#g;s#>#\>\;#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