Ticket #1341 (reopened enhancement)

Opened 3 years ago

Last modified 3 years ago

Delete spam users from ckan

Reported by: kindly Owned by: kindly
Priority: awaiting triage Milestone: ckan-backlog
Component: ckan Keywords:
Cc: Repository: ckan
Theme: none

Description

Spam users where added to thedatahub and we need to clean them.

Change History

comment:1 Changed 3 years ago by kindly

  • Status changed from new to closed
  • Resolution set to fixed

This was run to delete the users and their mistaken revisions that where created.

BEGIN;
delete from revision r where r.id in (
    select r.id from "user" join revision r on r.author = "user".name 
    left join resource_revision rr on rr.revision_id = r.id 
    left join package_revision pr on pr.revision_id = r.id 
    left join group_revision gr on gr.revision_id = r.id  
    where "user".created between '2011-08-15' and '2011-09-06'
    and gr.id is null and rr.id is null and pr.id is null 
    and ("user".name similar to '%[0-9]' or "user".fullname similar to '[A-Z][a-z]*[A-Z]%') 
    and "user".name not like 'http%'
);
delete from "user" u where u.id in (
    select "user".id from "user" 
    left join revision r on r.author = "user".name 
    where r.id is null and "user".created between '2011-08-15' and '2011-09-06' 
    and ("user".name similar to '%[0-9]' or "user".fullname similar to '[A-Z][a-z]*[A-Z]%') 
    and "user".name not like 'http%'
);
COMMIT;

comment:2 Changed 3 years ago by dread

The majority of our 6000 users are still spammers. Can we simply elimenate those who've not made any revisions? Even if we take a few genuine users with it, these are pretty dead accounts anyway.

comment:3 Changed 3 years ago by dread

  • Status changed from closed to reopened
  • Resolution fixed deleted

Reopening - this is still a serious problem. We can't link to the user page because it is so embarrassing: http://thedatahub.org/user

comment:4 Changed 3 years ago by kindly

  • Milestone changed from ckan-sprint-2011-09-12 to ckan-backlog
Note: See TracTickets for help on using tickets.