powered by Jive Software

SPAM - Private Messages (database)

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

Wow. I send a lot of messages Actually 90% of them are replies.