Is there a way to automate removing the contact records of Drupal accounts that are blocked such as because of spam? I am using the spambot module to block accounts and remove their spam.

I fear that there are thousands of bogus records in our CiviCRM with no way to confirm their legitimacy.

Drupal 7 and CiviCRM 4.7.27

3 Answers 3


Spitballing as I haven't tried this, but if the CiviGroup Roles Sync module allows you to sync a Drupal role into a CiviCRM group, you could:

  1. Add all blocked accounts to a (new/temporary) Drupal role.
  2. Sync that role to a group in CiviCRM.
  3. Load all contacts in that group in the CiviCRM UI and delete/trash them.
  4. Delete Drupal users (and temp role) if desired.
  • That sounds super sensible - i forget that Group sync can run in that direction
    – petednz - fuzion
    Mar 2, 2018 at 4:11
  • This is a great idea. I don't have a role created for blocked accounts. I'm trying to think of the other half of this, which would automatically give all blocked accounts a special role. Maybe a Drupal rule would suffice? Thanks for the ideas!
    – Christia
    Oct 2, 2018 at 17:47

We didn't find a great way of dealing with this. I think we found the best approach was to use Drupal Views and VBO and probably civicrm entities so that we could grab the civi records of the users who we wanted to cull, and then delete the civi record and then delete the user record. If I can dig out the View I will share

  • I found the module but seems it needs some branches pulling back to main so pls ping me in a week if this hasn't been updated
    – petednz - fuzion
    Feb 28, 2018 at 1:11
  • I can build something like that. I already have views that use VBO and civicrm entities, but I was hoping to use it with spambot. I'll mull on that, good idea.
    – Christia
    Feb 28, 2018 at 1:52

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 Pemr


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.