powered by Jive Software

Handling of SEO / SPAM accounts

I plan to write some scripts which will scan and modify users using a psql query.

a) It should run daily and remove the homepage for users with freemail (@gmail.com, @yahoo.com/co.uk/…, @hotmail.com/fr/br/…) addresses. This may affect some real users but as the profile page is not indexed by Google it does not really hurt. Users with other email addresses (like @jivesoftware.com, @example.com, …) will not be affected.

Actually I want to set the homepage to https://+email-domain (eg https://yahoo.com/) so one can identify the modified users easy.

b) It should run weekly and remove the homepage for users with 0 threads+blogs+docs which were inactive for ten days.

c) It should run monthly and delete all users with 0 threads+blogs+docs if the last login was 360 days ago (no matter whether they have a homepage or not, no matter whether they are disabled or not). If somebody did register without using the account for 360 days then it is likely save to delete the account - no content is removed. Deleting the users will reduce the user base and allow to re-use the username for someone else.

One may need to use a REST call to remove the users completely as the user data is stored in multiple tables.

All other inactive users which did create content will be disabled (The UserDecayTask disables users after 12 months of inactivity by default), this is currently already the case.

Please like or comment if you do (not) agree.

Some SQL statements

select count(*) from jiveuser;
-- 37023 users select count(*) from jiveuser where to_timestamp(lastloggedin/1000) < now()::date - 360;
-- 30804 did not login within the last 360 days SELECT count(*) from jiveuser where (
((select count(*) from jivemessage where jivemessage.userid = jiveuser.userid) = 0) and
((select count(*) from jivedocument where jivedocument.userid = jiveuser.userid) = 0 )and
((select count(*) from jiveblogpost where jiveblogpost.userid = jiveuser.userid) = 0) and
to_timestamp(lastloggedin/1000) < now()::date - 360
);
-- 15599 old users with 0 content - they should be deleted
-- 19544 users with 0 content (4000 "active" users with 0 content) SELECT userid,username,to_timestamp(lastloggedin/1000)::date, email from jiveuser where (
((select count(*) from jivemessage where jivemessage.userid = jiveuser.userid) = 0) and
((select count(*) from jivedocument where jivedocument.userid = jiveuser.userid) = 0 )and
((select count(*) from jiveblogpost where jiveblogpost.userid = jiveuser.userid) = 0) and
(to_timestamp(lastloggedin/1000) < now()::date - 360) and
(jiveuser.username!='admin')
) limit 3; userid |  username   | lastloggedin |             email
--------+-------------+--------------+--------------------------------
      4 | imbriaco    | 2007-11-29   | mark.imbriaco@pobox.com
      5 | br          | 2007-11-29   | braoult@bigfoot.com
      6 | curbst      | 2007-11-29   | zheng.jianbing@china-motion.co