su - jive
psql -h 127.0.0.1 -p 5432 -U jive
SELECT count(*) FROM jivepmessage WHERE pmessageid>37000;
Adjust 37000 to make sure that the result is ~1000, so we will parse only the last 1000 messages:
SELECT senderid, count(*) FROM jivepmessage WHERE pmessageid>37000 GROUP BY senderid;
This may return: 39177 | 106
This “senderid” look suspecious, sending 10% of the last 1000 messages. Let’s check the messages:
SELECT * FROM jivepmessage WHERE senderid=39177;
This should return a lot of messages which look very similar, so it is spam.
As I have no idea whether we can simply delete these messages I did set them to read. The notification email was sent anyway, but they should no longer appear as new in the inbox.
UPDATE jivepmessage SET readstatus=1 WHERE senderid=39177;
Finally disable the user:
UPDATE jiveuser set userenabled=0 WHERE userid=39177;
TOP pm senders - just in case that I want to delete some meesages of disabled users (no name behind):
userid | sent messages
8189 | 259 - jadestorm
26604 | 312
30645 | 689
1964 | 277 - dombiak_gaston
30470 | 685
22174 | 118
38443 | 862
30244 | 234
9763 | 101 - slushpupie
30217 | 987
30214 | 233
34301 | 214
34227 | 138
10616 | 175 - dele
7519 | 249 - akrherz
39210 | 332
39177 | 154
7315 | 611 - wroot
29804 | 295
26645 | 187
9231 | 271 - it2000
3 | 252 - matt
29560 | 1939
29556 | 749
29545 | 1099
19278 | 697
31421 | 169
12975 | 225 - webeling
25252 | 182 - rcollier
4739 | 208 - ryang
14844 | 121 - sixthring
31106 | 430