Helping resolve the problem of deleting spam Drupal Users and CiviCRM Contacts

When Drupal Users are created a CiviCRM record is automatically created. If those Users are spammers, and therefore need to be Blocked or Deleted, you are faced with the difficulty of getting rid of both their Drupal user and the related CiviCRM record.

To help us with this task and others related to "user and contact" problems we created a module (feature) that gives us some new toys to play with. It evens works on multisites (where you have multiple Drupal sites - or Domain sites - linked to one CiviCRM)

The main functionality of this module is that it allows you to easily identify and delete drupal users (Cancel User Account), and their associated Civicrm Contact Record (Civi contact's are put in the trash). Additionally you can find drupal users for which there is no civicrm contact. After installing you can find this page at admin/people/inactive.

Bulk Deleting Drupal Users after deleting the Civi Contact

You may find that a lot of your spammers can be identified in CiviCRM by simply running a search for where the domain contains .ru, (or via sql where the First Name equals the Last Name - see below), and then deleting those records permanently. That then leaves the problem of identifying the 'orphaned' Drupal Users. So this View lets you filter to any record that does not have a CiviCRM Contact connected to it, and lets you purge the Drupal users in bulk.

Finding records where the Drupal user email and the CiviCRM Contact email do not match

The view also lets you find cases where this data has gotten out of synch, which can help resolve confusion for both the users and admins.

 

Futher options

It is likely this could easily be extended to allow you to find users by IP address and remove them by using the Views 'friendly' uiplog module

NOTE: to avoid creating the spammers in the first place for sites where the site settings (at /admin/config/people/accounts) need to allow users to create their own account (eg you have a Profile set to 'create user)  we often take the simple step of setting up a redirect (Drupal Redirect module) from user/register to a civicrm contribution page (or a webform) since this will usually be enough to deter spammers

Finding and trashing spam records where First Name = Last Name

We also find a lot of spam contacts in civi have a first name which matches the last name. In which case we run this query to find them, export them if need be and let client eyeball them

SELECT civicrm_contact.id, civicrm_contact.first_name, civicrm_contact.last_name, civicrm_email.email FROM civicrm_contact LEFT JOIN civicrm_email ON civicrm_contact.id=civicrm_email.contact_id WHERE civicrm_contact.first_name = civicrm_contact.last_name AND first_name NOT LIKE '%test%';

Then get them in to Trash (avoiding any exceptions eg 1119, etc) with

UPDATE civicrm_contact SET is_deleted = 1 WHERE civicrm_contact.first_name = civicrm_contact.last_name AND first_name NOT LIKE '%test%' AND civicrm_contact.id NOT IN (1199,1231,1279,1322,1334);

The use the UI to 'delete permanently' to ensure all Activities are removed etc. Alternatively you could add them to a civi group to review and then use Delete Permanently

Module can be downloaded from https://github.com/fuzionnz/inactive_user.